手工创建数据库(DBCA做了那些事情)

环境:

OS:Red Hat Linux As5

DB:10.2.0.1

我们在使用DBCA创建数据库的时候,通过保存创建脚本可以发现DBCA创建数据库后在$ORACLE_BASE/admin/oracl/scripts目录下生成了如下文件,我们可以利用这些文件进行手工创建数据库.

scripts目录下生成了如下文件.

init.ora                 -- 初始化参数文件
initoraclTemp.ora        -- 临时初始化脚本

oracl.sh                 -- 创建相应目录, 同时执行oracl.sql
oracl.sql                -- 创建密码文件,同时执行CloneRmanRestore.sql,cloneDBCreation.sql,postScripts.sql,postDBCreation.sql
CloneRmanRestore.sql     -- 执行rmanRestoreDatafiles.sql脚本
rmanRestoreDatafiles.sql -- 使用dbms_backup_restore从Seed_Database.dfb中restore数据文件
cloneDBCreation.sql      -- 创建数据库脚本
postScripts.sql          -- 执行dbmssml.sql,ordlib.sql脚本
postDBCreation.sql       -- 创建spfile,同时编译对象

 

1.oracl.sh(创建相应目录, 同时执行oracl.sql,这里应该是$ORACLE_SID.sh,我这里的ORACLE_ID为oracl)

#!/bin/sh

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

2.oracl.sql(创建密码文件,同时执行CloneRmanRestore.sql,cloneDBCreation.sql,postScripts.sql,postDBCreation.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
host /u01/app/oracle/product/10.2.0/db_1/bin/orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapworacl password=&&sysPassword force=y
@/u01/app/oracle/admin/oracl/scripts/CloneRmanRestore.sql
@/u01/app/oracle/admin/oracl/scripts/cloneDBCreation.sql
@/u01/app/oracle/admin/oracl/scripts/postScripts.sql
@/u01/app/oracle/admin/oracl/scripts/postDBCreation.sql

 

3.CloneRmanRestore.sql(执行rmanRestoreDatafiles.sql脚本)

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

4.rmanRestoreDatafiles.sql(使用dbms_backup_restore从Seed_Database.dfb中restore数据文件)

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/oracl/system01.dbf', 0, 'SYSTEM');
      dbms_backup_restore.restoreDataFileTo(2, '/u01/app/oracle/oradata/oracl/undotbs01.dbf', 0, 'UNDOTBS1');
      dbms_backup_restore.restoreDataFileTo(3, '/u01/app/oracle/oradata/oracl/sysaux01.dbf', 0, 'SYSAUX');
      dbms_backup_restore.restoreDataFileTo(4, '/u01/app/oracle/oradata/oracl/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;

5.cloneDBCreation.sql(创建数据库脚本)

connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/oracl/scripts/cloneDBCreation.log
Create controlfile reuse set database "oracl"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/app/oracle/oradata/oracl/system01.dbf',
'/u01/app/oracle/oradata/oracl/undotbs01.dbf',
'/u01/app/oracle/oradata/oracl/sysaux01.dbf',
'/u01/app/oracle/oradata/oracl/users01.dbf'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/oracl/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/oracl/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/oracl/redo03.log') SIZE 51200K RESETLOGS;
exec dbms_backup_restore.zerodbid(0);
shutdown immediate;
startup nomount pfile="/u01/app/oracle/admin/oracl/scripts/initoraclTemp.ora";
Create controlfile reuse set database "oracl"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/app/oracle/oradata/oracl/system01.dbf',
'/u01/app/oracle/oradata/oracl/undotbs01.dbf',
'/u01/app/oracle/oradata/oracl/sysaux01.dbf',
'/u01/app/oracle/oradata/oracl/users01.dbf'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/oracl/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/oracl/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/oracl/redo03.log') SIZE 51200K RESETLOGS;
alter system enable restricted session;
alter database "oracl" open resetlogs;
alter database rename global_name to "oracl";
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/oracl/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;
alter user sys identified by "&&sysPassword";
alter user system identified by "&&systemPassword";
alter system disable restricted session;

 

6.postScripts.sql(执行dbmssml.sql,ordlib.sql脚本)
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/oracl/scripts/postScripts.log
@/u01/app/oracle/product/10.2.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.2.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
7.postDBCreation.sql(创建spfile,同时编译对象)

connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/oracl/scripts/postDBCreation.log
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup mount pfile="/u01/app/oracle/admin/oracl/scripts/init.ora";
alter database archivelog;
alter database open;
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='/u01/app/oracle/product/10.2.0/db_1/dbs/spfileoracl.ora' FROM pfile='/u01/app/oracle/admin/oracl/scripts/init.ora';
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup ;
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;
spool /u01/app/oracle/admin/oracl/scripts/postDBCreation.log

说明:

手工创建数据库的时候只要执行oracl.sh($ORACLE_SID.sh),前提是必须安装oracle数据库软件.

 

--The End--