如何写oracle脚本文件路径,dbca建库脚本解析

平台：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-