详解dbca建库生成的脚本的运行内容

最新推荐文章于 2021-01-18 23:21:12 发布

qq_31333883

最新推荐文章于 2021-01-18 23:21:12 发布

阅读量416

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

本文详细解析了使用Oracle Database Configuration Assistant (DBCA) 创建数据库的过程中涉及的多个脚本及其功能，包括环境配置、用户密码设置、数据文件恢复及数据库初始化等关键步骤。

摘要由CSDN通过智能技术生成

我们在使用dbca使用general purpose创建数据库的时候，会创建一个scripts的文件夹，文件夹中包含了如下的一堆脚本：
cloneDBCreation.sql
initocpTemp.ora
lockAccount.sql
ocp.sql
postScripts.sql
CloneRmanRestore.sql
init.ora
ocp.sh
postDBCreation.sql
rmanRestoreDatafiles.sql
今天我们的目的就是研究下这些脚本就是做了哪些事情，或者说，Oracle是如何使用这堆脚本来创建数据库的
这对脚本中，首先执行的是：

1、ocp.sh
[code lang="shell"]
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/ocp/adump
mkdir -p /u01/app/oracle/admin/ocp/dpdump
mkdir -p /u01/app/oracle/admin/ocp/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/ocp
mkdir -p /u01/app/oracle/fast_recovery_area
mkdir -p /u01/app/oracle/fast_recovery_area/ocp
mkdir -p /u01/app/oracle/product/11.2.0/db/dbs
mkdir -p /u01/oradata/ocp
umask ${OLD_UMASK}
ORACLE_SID=ocp; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: ocp:/u01/app/oracle/product/11.2.0/db:Y
/u01/app/oracle/product/11.2.0/db/bin/sqlplus /nolog @/home/oracle/scripts/ocp.sql
[/code]

（1）Oracle首先会创建与数据库相关的所有文件夹
（2）设定Oracle的环境变量，SID，PATH
（3）提示我们需要在/etc/oratab中添加
ocp:/u01/app/oracle/product/11.2.0/db:Y
这步操作的作用是：只有在/etc/oratab中有记录的数据库，后期才能够被dbca这个oui工作所管理。简单来讲，如果你添加这段话，那么后期我们可以通过dbca来删除我们的数据库。
（4）调用/home/oracle/scripts/ocp.sql脚本

2、ocp.sql脚本
[code lang="sql"]
set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
ACCEPT sysmanPassword CHAR PROMPT 'Enter new password for SYSMAN: ' HIDE
ACCEPT dbsnmpPassword CHAR PROMPT 'Enter new password for DBSNMP: ' HIDE
host /u01/app/oracle/product/11.2.0/db/bin/orapwd file=/u01/app/oracle/product/11.2.0/db/dbs/orapwocp force=y
@/home/oracle/scripts/CloneRmanRestore.sql
@/home/oracle/scripts/cloneDBCreation.sql
[/code]
（1）设定Oracle的4个特殊用户的密码：sys，system，sysman，dbsnmp
（2）创建密码文件
（3）调用CloneRmanRestore.sql脚本
（4）调用cloneDBCreation.sql

3、CloneRmanRestore.sql
[code lang="sql"]
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /home/oracle/scripts/CloneRmanRestore.log append
startup nomount pfile="/home/oracle/scripts/init.ora";
@/home/oracle/scripts/rmanRestoreDatafiles.sql;
spool off
[/code]
（1）通过调用init.ora参数文件，将数据库启动nomount状态
（2）调用rmanRestoreDatafiles.sql脚本

4、rmanRestoreDatafiles.sql
[code lang="sql"]
set verify off;
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/oradata/ocp/system01.dbf', 0, 'SYSTEM');
      dbms_backup_restore.restoreDataFileTo(2, '/u01/oradata/ocp/sysaux01.dbf', 0, 'SYSAUX');
      dbms_backup_restore.restoreDataFileTo(3, '/u01/oradata/ocp/undotbs01.dbf', 0, 'UNDOTBS1');
      dbms_backup_restore.restoreDataFileTo(4, '/u01/oradata/ocp/users01.dbf', 0, 'USERS');
    dbms_output.put_line(' Restoring ... ');
    dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.2.0/db/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;
[/code]
（1）这个脚本就只有一个作用，通过读取/u01/app/oracle/product/11.2.0/db/assistants/dbca/templates/Seed_Database.dfb这个备份片，使用rman恢复的方式来创建数据库
（2）需要注意！restore出来的数据库，此时处于mount的状态

5、cloneDBCreation.sql
[code lang="sql"]
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /home/oracle/scripts/cloneDBCreation.log append
Create controlfile reuse set database "ocp"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/oradata/ocp/system01.dbf',
'/u01/oradata/ocp/sysaux01.dbf',
'/u01/oradata/ocp/undotbs01.dbf',
'/u01/oradata/ocp/users01.dbf'
LOGFILE GROUP 1 ('/u01/oradata/ocp/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/oradata/ocp/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/oradata/ocp/redo03.log') SIZE 51200K RESETLOGS;
exec dbms_backup_restore.zerodbid(0);
shutdown immediate;
startup nomount pfile="/home/oracle/scripts/initocpTemp.ora";
Create controlfile reuse set database "ocp"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/oradata/ocp/system01.dbf',
'/u01/oradata/ocp/sysaux01.dbf',
'/u01/oradata/ocp/undotbs01.dbf',
'/u01/oradata/ocp/users01.dbf'
LOGFILE GROUP 1 ('/u01/oradata/ocp/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/oradata/ocp/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/oradata/ocp/redo03.log') SIZE 51200K RESETLOGS;
alter system enable restricted session;
alter database "ocp" open resetlogs;
exec dbms_service.delete_service('seeddata');
exec dbms_service.delete_service('seeddataXDB');
alter database rename global_name to "ocp";
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/ocp/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/11.2.0/db/demo/schema/mkplug.sql &&sysPassword change_on_install change_on_install change_on_install change_on_install change_on_install change_on_install example.dmp example01.dfb /u01/oradata/ocp/example01.dbf /home/oracle/scripts/ /u01/app/oracle/product/11.2.0/db/assistants/dbca/templates/ "\'SYS/&&sysPassword as SYSDBA\'";
connect "SYS"/"&&sysPassword" as SYSDBA
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup restrict pfile="/home/oracle/scripts/initocpTemp.ora";
select sid, program, serial#, username from v$session;
alter database character set INTERNAL_CONVERT AL32UTF8;
alter database national character set INTERNAL_CONVERT UTF8;
alter user sys account unlock identified by "&&sysPassword";
alter user system account unlock identified by "&&systemPassword";
alter system disable restricted session;
[/code]
（1）通过Create controlfile语句重新创建控制文件
（2）通过dbms_backup_restore.zerodbid(0)重新设定dbid
（3）通过initocpTemp.ora参数文件重启数据库到mount状态
（4）通过Create controlfile语句重新创建控制文件，并以open resetlogs的方式打开数据库
（5）删除seeddata，seeddataXDB两个service
（6）设定数据的global_name
（7）添加temp表空间
（8）通过example01.dbf导入sample schema
（9）根据initocpTemp.ora重启数据库
（10）修改数据库的字符集与国家字符集
（11）解锁sys，system用户并重新设定其密码