分类： Oracle
2010-03-27 10:06:12
平台：Redhat AS4 UPDATE 7,Oracle 10gR2
ORACLE_BASE: /u01/app/oracle，SID:orcl1
dbca的建库脚本是使用模板来创建数据库，其优点是使用模板创建数据库通常速度都会很快，原因就在于数据文件是从种子数据库中恢复出来的，而不需 要创建文件及字典对象等信息。
一、 创建目录:
mkdir -p /u01/app/oracle/admin/orcl1/adump
mkdir -p /u01/app/oracle/admin/orcl1/bdump
mkdir -p /u01/app/oracle/admin/orcl1/cdump
mkdir -p /u01/app/oracle/admin/orcl1/dpdump
mkdir -p /u01/app/oracle/admin/orcl1/pfile
mkdir -p /u01/app/oracle/admin/orcl1/udump
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/oracle/oradata/orcl1
mkdir -p /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/dbca/orcl1
mkdir -p /u01/app/oracle/product/10.2.0/db_1/dbs
二、 设置环境变量
ORACLE_SID=orcl1; export ORACLE_SID
三、 在/etc/oratab中添加实例启动项
orcl1:/u01/app/oracle/product/10.2.0/db_1:Y
四、 创建密码文件(password file)：
以nolog方式登录后，执行
host /u01/app/oracle/product/10.2.0/db_1/bin/orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl1 password=&sysPassword force=y
输入密码
五、CloneRmanRestore.sql(使用指定的参数文件以nomount方式打开数据库，调用 rmanRestoreDatafiles.sql)
connect sys/orcl as SYSDBA
set echo on
spool /u01/app/oracle/admin/orcl1/scripts/CloneRmanRestore.log
startup nomount pfile="/u01/app/oracle/admin/orcl1/scripts/init.ora"; //使用指定的参数打开数据库
@/u01/app/oracle/admin/orcl1/scripts/rmanRestoreDatafiles.sql;
六、rmanRestoreDatafiles.sql(从种子文件中取出数据文件)
set echo off;
set serveroutput on;
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual; //脚本开始运行时间
variable devicename varchar2(255);
declare
omfname varchar2(512) := NULL;
done boolean;
begin
dbms_output.put_line(' ');
dbms_output.put_line(' Allocating device.... ');
dbms_output.put_line(' Specifying datafiles... ');
:devicename := dbms_backup_restore.deviceAllocate;
dbms_output.put_line(' Specifing datafiles... ');
dbms_backup_restore.restoreSetDataFile;
dbms_backup_restore.restoreDataFileTo(1, '/u01/app/oracle/oradata/orcl1/system01.dbf', 0, 'SYSTEM');
dbms_backup_restore.restoreDataFileTo(2, '/u01/app/oracle/oradata/orcl1/undotbs01.dbf', 0, 'UNDOTBS1');
dbms_backup_restore.restoreDataFileTo(3, '/u01/app/oracle/oradata/orcl1/sysaux01.dbf', 0, 'SYSAUX');
dbms_backup_restore.restoreDataFileTo(4, '/u01/app/oracle/oradata/orcl1/users01.dbf', 0, 'USERS');
dbms_output.put_line(' Restoring ... ');
dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/10.2.0/db_1/assistants/dbca/templates/Seed_Database.dfb', done);//种子文件
if done then
dbms_output.put_line(' Restore done.');
else
dbms_output.put_line(' ORA-XXXX: Restore failed ');
end if;
dbms_backup_restore.deviceDeallocate;
end;
/
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual; //脚本结束运行时间
//dbms_backup_restore包的一些背景知识：
当通过RMAN进行数据库备份时，RMAN会将多个数据文件写出到一个或多个备份文件（称为备份集） 中，RMAN的相关的备份信息或者存储在控制文件中，或者存储在RMAN的专用目录数据库（Catalog）中，如果RMAN的备份信息丢失，那么通常备 份集中的文件是没有办法读取出来的，其他工具无法识别RMAN的备份集文件；而dbms_backup_restore就是针对这种情况提供的一种解决方 案，dbms_backup_restore可以在数据库nomount状态下调用，直接从备份集中读取数据文件，功能十分强大。
DBMS_BACKUP_RESTORE包由dbmsbkrs.sql和prvtbkrs.plb这两个脚本 创建，创建数据库时执行的catproc.sql 脚本会调用这两个脚本以创建包，这些脚本文件可以在$ORACLE_HOME/rdbms/admin目录下找到，脚本文件中对包的内容有详细的介绍。
七、cloneDBCreation.sql(重建控制文件并修改相关参数)
connect sys/orcl as SYSDBA
set echo on
spool cloneDBCreation.log
Create controlfile reuse set database "orcl1"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile //指定数据文件
'/u01/app/oracle/oradata/orcl1/system01.dbf',
'/u01/app/oracle/oradata/orcl1/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl1/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl1/users01.dbf'
//指定日志文件组
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl1/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/orcl1/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/orcl1/redo03.log') SIZE 51200K RESETLOGS;
exec dbms_backup_restore.zerodbid(0);
/*zeroDbid是包中的一个过程，用于清空数据文件头的部分信息，新的dbid在之后的控制文件创建时可以被计算，对于数据库克隆，这是必须 的。
zeroDbid有一个输入参数，即文件号：
PROCEDURE zeroDbid(fno IN binary_integer);
当fno==0时，控制文件中包含的所有数据文件头都将被清零，zeroDbid主要用于清除数据文件头的3类信息：Database id信息、Checksum信息和Checksum符号位信息。*/
shutdown immediate;
startup nomount pfile="/u01/app/oracle/admin/orcl1/scripts/initorcl1Temp.ora";
Create controlfile reuse set database "orcl1"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/app/oracle/oradata/orcl1/system01.dbf',
'/u01/app/oracle/oradata/orcl1/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl1/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl1/users01.dbf'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl1/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/orcl1/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/orcl1/redo03.log') SIZE 51200K RESETLOGS;
alter system enable restricted session;
alter database "orcl1" open resetlogs;
alter database rename global_name to "orcl1";
//添加临时表空间
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl1/temp01.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
select tablespace_name from dba_tablespaces where tablespace_name='USERS';
select sid, program, serial#, username from v$session;
//修改字符集
alter database character set INTERNAL_CONVERT WE8ISO8859P1;
alter database national character set INTERNAL_CONVERT AL16UTF16;
//修改sys/system密码
alter user sys identified by "&&sysPassword";
alter user system identified by "&&systemPassword";
alter system disable restricted session;
initorcl1Temp.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Archive
###########################################
log_archive_dest_1=""
……………
_no_recovery_through_resetlogs=true
/*_no_recovery_through_resetlogs FALSE no recovery through this resetlogs operation
这个参数用于限制恢复能否跨越resetlogs，对于数据库的恢复来说，resetlogs通常意味着不完 全恢复，在数据库resetlogs打开之后，控制文件中的很多信息被改写，在Oracle 10g之前，如果数据库resetlogs打开，那么将不再能够通过当前的控制文件再次进行resetlogs点之前的恢复，而Oracle 10g改变了这个历史。
在Oracle 10g中，即使通过resetlogs方式打开了数据库，Oracle仍然支持再次从resetlogs时间点之前进行恢复；在Clone数据库 时，Oracle设置这个参数为True，意思就是不允许再次进行跨越resetlogs时间点的恢复。*/
八、postScripts.sql(建库后脚本)
connect sys/orcl as SYSDBA
set echo on
spool /u01/app/oracle/admin/orcl1/scripts/postScripts.log
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbmssml.sql;
#CREATE OR REPLACE LIBRARY dbms_sumadv_lib AS '/u01/app/oracle/product/10.2.0/db_1/lib/libqsmashr.so';
创建或替换dbms_sumadv_lib库，具体作用有待研究(strings、objdump)
execute dbms_datapump_utl.replace_default_dir;
commit;
connect sys/orcl as SYSDBA
alter session set current_schema=ORDSYS; //ORDSYS为Oracle interMedia 管理员账户
@/u01/app/oracle/product/10.2.0/db_1/ord/im/admin/ordlib.sql;
#create or replace library ORDIMLIBS as '/u01/app/oracle/product/10.2.0/db_1/lib/libordim10.so';
/
create or replace library ORDIMLIBT trusted as static;
/
Rem ordimasf.jar is only available via download from the
Rem Oracle Technology Network website. Go to the interMedia section of
Rem
Rem
Rem Uncomment the following lines after downloading ordimasf.jar.
-- BEGIN
-- dbms_java.loadjava('-synonym -schema ORDSYS -resolve -grant PUBLIC ord/jlib/ordimasf.jar');
-- EXCEPTION
-- WHEN OTHERS THEN
-- NULL;
-- END;
-- /
//切换至SYS账户
alter session set current_schema=SYS;
connect sys/orcl as SYSDBA
execute ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
commit;
spool off
九、postDBCreation.sql(创建spfile、解除账号锁定、重编译无效对象、重建em资料库)
connect sys/orcl as SYSDBA
set echo on
spool /u01/app/oracle/admin/orcl1/scripts/postDBCreation.log
connect sys/orcl as SYSDBA
set echo on
#创建spfile
create spfile='/u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl1.ora' FROM pfile='/u01/app/oracle/admin/orcl1/scripts/init.ora';
#重启实例以使用spfile启动数据库
shutdown immediate;
connect sys/orcl as SYSDBA
startup ;
#解除SYSMAN/DBSNMP账号锁定
alter user SYSMAN identified by "&&sysmanPassword" account unlock;
alter user DBSNMP identified by "&&dbsnmpPassword" account unlock;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
# UTL_RECOMP.RECOMP_SERIAL(
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);
重新编译某个schema(user)下的无效对象，如果为NULL则编译整个数据库。flags用于内部测试。
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
#配置emca资料库，参考
host /u01/app/oracle/product/10.2.0/db_1/bin/emca -config dbcontrol db -silent -DB_UNIQUE_NAME orcl1 -PORT 1521 -EM_HOME /u01/app/oracle/product/10.2.0/db_1 -LISTENER LISTENER -SERVICE_NAME orcl1 -SYS_PWD &&sysPassword -SID orcl1 -ORACLE_HOME /u01/app/oracle/product/10.2.0/db_1 -DBSNMP_PWD &&dbsnmpPassword -HOST localhost.localdomain -LISTENER_OH /u01/app/oracle/product/10.2.0/db_1 -LOG_FILE /u01/app/oracle/admin/orcl1/scripts/emConfig.log -SYSMAN_PWD &&sysmanPassword;
spool /u01/app/oracle/admin/orcl1/scripts/postDBCreation.log
官方文档：
循序渐进Oracle:
Oracle XE自带的数据库如何创建：
-EOF-
