[007]创建库时，都做了什么？

使用dbca创建库时，dbca都做了哪些操作呢？

在Liunx5+oracle10.1.0中，做如下分析，会和windows下略有不同。

 

首先得到dbca建库的脚本，具体操作如下：

1. 启动dbca，默认建库，Global Database Name = SID = db03

2. 选择创建Sample Schemas， 选择字符集

3. 第12步，仅选择Generate Database Creation Scripts

这样就在默认的目录中得到相应的脚本

 /u01/app/oracle/admin/db03/scripts

 

一览一下所有脚本：

[oracle@localhost scripts]$ ll
total 36
-rw-r----- 1 oracle oinstall  245 Aug 11 07:26 CloneRmanRestore.sql
-rw-r----- 1 oracle oinstall 2652 Aug 11 07:26 cloneDBCreation.sql
-rwxr-xr-x 1 oracle oinstall  692 Aug 11 07:26 db03.sh
-rwxr-xr-x 1 oracle oinstall  687 Aug 11 07:26 db03.sql
-rw-r----- 1 oracle oinstall 2473 Aug 11 07:26 init.ora
-rw-r----- 1 oracle oinstall 2486 Aug 11 07:26 initdb03Temp.ora
-rw-r----- 1 oracle oinstall 1209 Aug 11 07:26 postDBCreation.sql
-rw-r----- 1 oracle oinstall  574 Aug 11 07:26 postScripts.sql
-rw-r----- 1 oracle oinstall 1348 Aug 11 07:26 rmanRestoreDatafiles.sql

 

 

创建的入口文件是：db03.sh

[oracle@localhost scripts]$ cat db03.sh
#!/bin/sh

mkdir -p /u01/app/oracle/admin/db03/adump
mkdir -p /u01/app/oracle/admin/db03/bdump
mkdir -p /u01/app/oracle/admin/db03/cdump
mkdir -p /u01/app/oracle/admin/db03/dpdump
mkdir -p /u01/app/oracle/admin/db03/pfile
mkdir -p /u01/app/oracle/admin/db03/udump
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/oracle/oradata/db03
mkdir -p /u01/app/oracle/product/10.1.0/db_1/cfgtoollogs/dbca/db03
mkdir -p /u01/app/oracle/product/10.1.0/db_1/dbs
ORACLE_SID=db03; export ORACLE_SID
echo You should Add this entry in the /etc/oratab: db03:/u01/app/oracle/product/10.1.0/db_1:Y
/u01/app/oracle/product/10.1.0/db_1/bin/sqlplus /nolog @/u01/app/oracle/admin/db03/scripts/db03.sql

创建相应的目录

设置ORACLE_SID。windows在这里还有一个创建服务的语句

执行db03.sql

db03.sql的内容

[oracle@localhost scripts]$ cat db03.sql
set verify off
PROMPT specify a password for sys as parameter 1;
DEFINE sysPassword = &1
PROMPT specify a password for system as parameter 2;
DEFINE systemPassword = &2
PROMPT specify a password for sysman as parameter 3;
DEFINE sysmanPassword = &3
PROMPT specify a password for dbsnmp as parameter 4;
DEFINE dbsnmpPassword = &4
host /u01/app/oracle/product/10.1.0/db_1/bin/orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapwdb03 password=&&sysPassword force=y
@/u01/app/oracle/admin/db03/scripts/CloneRmanRestore.sql
@/u01/app/oracle/admin/db03/scripts/cloneDBCreation.sql
@/u01/app/oracle/admin/db03/scripts/postScripts.sql
@/u01/app/oracle/admin/db03/scripts/postDBCreation.sql

设置密码

 创建老大的密码文件。参照这个：http://atkinson2010.iteye.com/admin/blogs/752195

 执行对应sql

 

 

CloneRmanRestore.sql的内容

[oracle@localhost scripts]$ cat CloneRmanRestore.sql
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/db03/scripts/CloneRmanRestore.log
startup nomount pfile="/u01/app/oracle/admin/db03/scripts/init.ora";
@/u01/app/oracle/admin/db03/scripts/rmanRestoreDatafiles.sql;

写日志到CloneRmanRestore.log
使用init.ora，将db启动到nomount

执行rmanRestoreDatafiles.sql

 

rmanRestoreDatafiles.sql的内容

[oracle@localhost scripts]$ cat 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/db03/system01.dbf', 0, 'SYSTEM');
      dbms_backup_restore.restoreDataFileTo(2, '/u01/app/oracle/oradata/db03/undotbs01.dbf', 0, 'UNDOTBS1');
      dbms_backup_restore.restoreDataFileTo(3, '/u01/app/oracle/oradata/db03/sysaux01.dbf', 0, 'SYSAUX');
      dbms_backup_restore.restoreDataFileTo(4, '/u01/app/oracle/oradata/db03/users01.dbf', 0, 'USERS');
    dbms_output.put_line(' Restoring ... ');
    dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/10.1.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;

取得当前时间，创建数据文件system01.dbf，undotbs01.dbf，sysaux01.dbf，users01.dbf。

使用Seed_Database.dfb对数据库进行恢复。

 

cloneDBCreation.sql的内容

[oracle@localhost scripts]$ cat  cloneDBCreation.sql
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/db03/scripts/cloneDBCreation.log
Create controlfile reuse set database "db03"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/app/oracle/oradata/db03/system01.dbf',
'/u01/app/oracle/oradata/db03/undotbs01.dbf',
'/u01/app/oracle/oradata/db03/sysaux01.dbf',
'/u01/app/oracle/oradata/db03/users01.dbf'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/db03/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/db03/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/db03/redo03.log') SIZE 51200K RESETLOGS;
exec dbms_backup_restore.zerodbid(0);
shutdown immediate;
startup nomount pfile="/u01/app/oracle/admin/db03/scripts/initdb03Temp.ora";
Create controlfile reuse set database "db03"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/app/oracle/oradata/db03/system01.dbf',
'/u01/app/oracle/oradata/db03/undotbs01.dbf',
'/u01/app/oracle/oradata/db03/sysaux01.dbf',
'/u01/app/oracle/oradata/db03/users01.dbf'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/db03/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/db03/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/db03/redo03.log') SIZE 51200K RESETLOGS;
alter system enable restricted session;
alter database "db03" open resetlogs;
alter database rename global_name to "db03";
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/db03/temp01.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
select tablespace_name from dba_tablespaces where tablespace_name='USERS';
alter system disable restricted session;
connect "SYS"/"&&sysPassword" as SYSDBA
@/u01/app/oracle/product/10.1.0/db_1/demo/schema/mkplug.sql &&sysPassword change_on_install change_on_install change_on_install change_on_install change_on_install change_on_install /u01/app/oracle/product/10.1.0/db_1/assistants/dbca/templates/example.dmp /u01/app/oracle/product/10.1.0/db_1/assistants/dbca/templates/example01.dfb /u01/app/oracle/oradata/db03/example01.dbf /u01/app/oracle/admin/db03/scripts/ "\'SYS/&&sysPassword as SYSDBA\'";
connect "SYS"/"&&sysPassword" as SYSDBA
shutdown immediate;
startup pfile="/u01/app/oracle/admin/db03/scripts/initdb03Temp.ora";
alter system enable restricted session;
select sid, program, serial#, username from v$session;
alter database character set INTERNAL_CONVERT WE8ISO8859P1;
alter database national character set INTERNAL_CONVERT AL16UTF16;
alter user sys identified by "&&sysPassword";
alter user system identified by "&&systemPassword";
alter system disable restricted session;

创建日志文件

使用spfile，重启到nomount。

创建控制文件。数据库open

设置临时表空间

创建Sample Schemas

重启至open

 

postScripts.sql

[oracle@localhost scripts]$ cat postScripts.sql
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/db03/scripts/postScripts.log
@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/dbmssml.sql;
execute dbms_datapump_utl.replace_default_dir;
commit;
connect "SYS"/"&&sysPassword" as SYSDBA
alter session set current_schema=ORDSYS;
@/u01/app/oracle/product/10.1.0/db_1/ord/im/admin/ordlib.sql;
alter session set current_schema=SYS;
connect "SYS"/"&&sysPassword" as SYSDBA
connect "SYS"/"&&sysPassword" as SYSDBA
execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
commit;
spool off

写日志到postScripts.log

执行dbmssml.sql
执行ordlib.sql

 

dbmssml.sql

[oracle@localhost admin]$ cat dbmssml.sql
CREATE OR REPLACE LIBRARY dbms_sumadv_lib AS '/u01/app/oracle/product/10.1.0/db_1/lib/libqsmashr.so';
/

 

ordlib.sql

[oracle@localhost admin]$ cat ordlib.sql
create or replace library ORDIMLIBS as '/u01/app/oracle/product/10.1.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 http://otn.oracle.com.
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;
-- /

 

以上两个sql不知道作用

 

postDBCreation.sql

[oracle@localhost scripts]$ cat postDBCreation.sql
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/db03/scripts/postDBCreation.log
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='/u01/app/oracle/product/10.1.0/db_1/dbs/spfiledb03.ora' FROM pfile='/u01/app/oracle/admin/db03/scripts/init.ora';
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup ;
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();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
host /u01/app/oracle/product/10.1.0/db_1/bin/emca -config dbcontrol db -silent -DB_UNIQUE_NAME db03 -PORT 1521 -EM_HOME /u01/app/oracle/product/10.1.0/db_1 -LISTENER LISTENER -SERVICE_NAME db03 -SYS_PWD &&sysPassword -SID db03 -ORACLE_HOME /u01/app/oracle/product/10.1.0/db_1 -DBSNMP_PWD &&dbsnmpPassword -HOST localhost.localdomain -LISTENER_OH /u01/app/oracle/product/10.1.0/db_1 -LOG_FILE /u01/app/oracle/admin/db03/scripts/emConfig.log -SYSMAN_PWD &&sysmanPassword;
spool /u01/app/oracle/admin/db03/scripts/postDBCreation.log
exit;

写日志到postDBCreation.log
创建spfile

重启到open

给sysman, dbsnmp解锁

配置em

 

 

以上就是默认情况下dbca创建数据的所有脚本。

注意rmanRestoreDatafiles.sql中的

dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/10.1.0/db_1/assistants/dbca/templates/Seed_Database.dfb', done);

无论在第2步选择Data Warehouse, General Purpose或者Transaction Processing， 都是使用Seed_Database.dfb'进行恢复的，也就是说这三种库的本质是一样的！！！