DBCA创建数据库简单方便，但如果想详细了解其中间过程需要关注其生成的创建脚本，保存一份需要时修改些内容就可以简单用其创建数据库了(最好的同版本).下面对该方面内容进行了学习.
博文内容分为两部分，第一部分是网上转载的DBCA生成脚本内容和功能;第二部分是自己在本地Linux服务器是生成的脚本重新生成新的数据库的全部过程(脚本生成库之前将用DBCA创建的库先drop掉)，方便以后学习追踪.
首先使用DBCA工具创建一份建库脚本，整个脚本文件包含如下文件，感觉应该是从BAT文件开始入手，打开BAT文件查看内容如下：
mkdir f:/oracle/product/10.2.0/admin/test/adump
mkdir f:/oracle/product/10.2.0/admin/test/bdump
mkdir f:/oracle/product/10.2.0/admin/test/cdump
mkdir f:/oracle/product/10.2.0/admin/test/dpdump
mkdir f:/oracle/product/10.2.0/admin/test/pfile
mkdir f:/oracle/product/10.2.0/admin/test/udump
mkdir f:/oracle/product/10.2.0/db_1/cfgtoollogs/dbca/test
mkdir f:/oracle/product/10.2.0/db_1/dbs
mkdir f:/oracle/product/10.2.0/oradata/test
set ORACLE_SID=test
f:/oracle/product/10.2.0/db_1/bin/oradim.exe -new -sid TEST -startmode manual -spfile
f:/oracle/product/10.2.0/db_1/bin/oradim.exe -edit -sid TEST -startmode auto -srvcstart system
f:/oracle/product/10.2.0/db_1/bin/sqlplus /nolog @f:/test/scripts/test.sql
发现原来ORACLE先创建了一批相关的文件目录，于是效仿在%oracle_home%下建立对应的目录。此处有两种方法可供选择：
1.通过WINDOWS的可视界面创建
2.通过命令行工具使用如下命令创建（直接偷他的了）
mkdir f:/oracle/product/10.2.0/admin/test/adump
mkdir f:/oracle/product/10.2.0/admin/test/bdump
mkdir f:/oracle/product/10.2.0/admin/test/cdump
mkdir f:/oracle/product/10.2.0/admin/test/dpdump
mkdir f:/oracle/product/10.2.0/admin/test/pfile
mkdir f:/oracle/product/10.2.0/admin/test/udump
mkdir f:/oracle/product/10.2.0/db_1/cfgtoollogs/dbca/test
mkdir f:/oracle/product/10.2.0/db_1/dbs
mkdir f:/oracle/product/10.2.0/oradata/test
创建完成相关目录后，继续向下，发现他在BAT文件中执行了set ORACLE_SID=test，设置环境变量，它设置我也照着设置，进入CMD，直接输入set ORACLE_SID=test
完成环境变量设置后继续向下，
f:/oracle/product/10.2.0/db_1/bin/oradim.exe -new -sid TEST -startmode manual -spfile
f:/oracle/product/10.2.0/db_1/bin/oradim.exe -edit -sid TEST -startmode auto -srvcstart system
他创建并编辑了一个新的实例（不知道此处为何要先创建它以manual方式启动后有更改为auto 方式启动，望高手指点迷津），既然他创建了一个实例，我也同样创建一个实例，在CMD中输入 oradim –new –sid test创建一个名为test的实例。
在完成实例创建后，发现他在执行如下语句：
f:/oracle/product/10.2.0/db_1/bin/sqlplus /nolog @f:/test/scripts/test.sql
我对此步理解为使用/nolog方式登录sqlplus然后执行名为test.sql的文件，打开test.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
f:/oracle/product/10.2.0/db_1/bin/orapwd.exe
file=f:/oracle/product/10.2.0/db_1/database/PWDtest.ora
password=&&sysPassword force=y
@f:/test/scripts/CloneRmanRestore.sql
@f:/test/scripts/cloneDBCreation.sql
@f:/test/scripts/postScripts.sql
host "echo SPFILE=‘f:/oracle/product/10.2.0/db_1/dbs/spfiletest.ora‘ > f:/oracle/product/10.2.0/db_1/database/inittest.ora"
@f:/test/scripts/postDBCreation.sql
研读后发现他先是在%oracle_home%/database下创建了SYS用户登录认证的密码文件，于是效仿他的操作，进入CMD，执行
orapwd file=f:/oracle/product/10.2.0/db_1/database/PWDtest.ora password=test force=y,在%oracle_home%/database下创建一个名为PWDtest.ora的密码文件，且指定密码为test.
创建完成密码文件后，发现他接着执行了名为CloneRmanRestore.sql的文件，打开CloneRmanRestore.sql后发现内容如下
connect "SYS"/"&&sysPassword" as SYSDBA --使用刚才创建的SYS密码以DBA方式连接
set echo on
spool f:/test/scripts/CloneRmanRestore.log --记录日志，不管它
startup nomount pfile="f:/test/scripts/init.ora"; --以init.ora中参数启动数据库为nomount模式
@f:/test/scripts/rmanRestoreDatafiles.sql; --执行rmanRestoreDatafiles.sql
于是参照文档使用/NOLOG方式登录sqlplus,使用conn sys/test as sysdba连接，连接成功后执行 startup nomount pfile="f:/test/scripts/init.ora"启动数据库，发现报错信息如下
SQL> startup nomount pfile="f:/test/scripts/init.ora"
ORA-01031: insufficient privileges
将init.ora文件复制到%oracle_home%/database下改名为inittest.ora，
使用SQL> startup nomount 启动
启动完成后，他执行名为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, ‘f:/oracle/product/10.2.0/oradata/test/SYSTEM01.DBF‘, 0, ‘SYSTEM‘);
dbms_backup_restore.restoreDataFileTo(2, ‘f:/oracle/product/10.2.0/oradata/test/UNDOTBS01.DBF‘, 0, ‘UNDOTBS1‘);
dbms_backup_restore.restoreDataFileTo(3, ‘f:/oracle/product/10.2.0/oradata/test/SYSAUX01.DBF‘, 0, ‘SYSAUX‘);
dbms_backup_restore.restoreDataFileTo(4, ‘f:/oracle/product/10.2.0/oradata/test/USERS01.DBF‘, 0, ‘USERS‘);
dbms_output.put_line(‘ Restoring ... ‘);
dbms_backup_restore.restoreBackupPiece(‘f:/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;
之所以这样，是因为使用DBCA创建数据库时，数据文件是从种子数据库Seed_Database.dfb中恢复出来的，避免了创建文件及字典对象等信息，提高数据库的创建速度。执行后，他在%oracle_home%/oradata/的对应数据库文件下恢复出来四个文件，分别为SYSTEM01.DBF、UNDOTBS01.DBF、SYSAUX01.DBF、USERS01.DBF。
回到前面test.sql文件中，接下来被执行的语句是：@f:/test/scripts/cloneDBCreation.sql，打开cloneDBCreation.sql仔细阅读后，发现使用DBCA创建数据库时采用的是 “克隆”一个数据库的方式，由于上一步执行rmanRestoreDatafiles.sql时我们重种子数据库中恢复出来了数据文件，因此接下来执行的语句就是要在恢复出来的文件上进行“克隆”并对其进行改造。
首先
Create controlfile reuse set database "test"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
‘f:/oracle/product/10.2.0/oradata/test/SYSTEM01.DBF‘,
‘f:/oracle/product/10.2.0/oradata/test/UNDOTBS01.DBF‘,
‘f:/oracle/product/10.2.0/oradata/test/SYSAUX01.DBF‘,
‘f:/oracle/product/10.2.0/oradata/test/USERS01.DBF‘
LOGFILE GROUP 1 (‘f:/oracle/product/10.2.0/oradata/test/redo01.log‘) SIZE 51200K,
GROUP 2 (‘f:/oracle/product/10.2.0/oradata/test/redo02.log‘) SIZE 51200K,
GROUP 3 (‘f:/oracle/product/10.2.0/oradata/test/redo03.log‘) SIZE 51200K RESETLOGS;
使用上边的语句创建控制文件，然后通过执行exec dbms_backup_restore.zerodbid(0);清空数据文件头的部分信息，zeroDbid主要用于清除数据文件头的3类信息：Database id信息、Checksum信息和Checksum符号位信息。
信息清除后，执行shutdown immediate;
startup nomount pfile="f:/test/scripts/inittestTemp.ora";
重启数据库，此时重启时使用了inittestTemp.ora文件，区别于最初重启时的init.ora文件，在末尾处多了_no_recovery_through_resetlogs=true，查阅相关资料后得知这个参数用于限制恢复能否跨越resetlogs，对于数据库的恢复来说，resetlogs通常意味着不完全恢复，在数据库resetlogs打开之后，控制文件中的很多信息被改写，在Oracle 10g之前，如果数据库resetlogs打开，那么将不再能够通过当前的控制文件再次进行resetlogs点之前的恢复，而Oracle 10g改变了这个历史。在Oracle 10g中，即使通过resetlogs方式打开了数据库，Oracle仍然支持再次从resetlogs时间点之前进行恢复；在Clone数据库时，Oracle设置这个参数为True，意思就是不允许再次进行跨越resetlogs时间点的恢复。然后使用
Create controlfile reuse set database "test"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
‘f:/oracle/product/10.2.0/oradata/test/SYSTEM01.DBF‘,
‘f:/oracle/product/10.2.0/oradata/test/UNDOTBS01.DBF‘,
‘f:/oracle/product/10.2.0/oradata/test/SYSAUX01.DBF‘,
‘f:/oracle/product/10.2.0/oradata/test/USERS01.DBF‘
LOGFILE GROUP 1 (‘f:/oracle/product/10.2.0/oradata/test/redo01.log‘) SIZE 51200K,
GROUP 2 (‘f:/oracle/product/10.2.0/oradata/test/redo02.log‘) SIZE 51200K,
GROUP 3 (‘f:/oracle/product/10.2.0/oradata/test/redo03.log‘) SIZE 51200K RESETLOGS;
重写控制文件。
接下来Oracle设置restricted session模式，resetlogs打开数据库：
alter system enable restricted session;
alter database "test" open resetlogs;
修改global_name，添加临时文件等：
alter database rename global_name to "test";
ALTER
TABLESPACE TEMP ADD TEMPFILE
‘f:/oracle/product/10.2.0/oradata/test/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;
由于种子数据库的字符集通常与用户要求的不符，接下来Oracle通过内部操作强制更改了字符集、国家字符集（这个内容在后面的章节有详细的介绍）：
alter database character set INTERNAL_CONVERT ZHS16GBK;
alter database national character set INTERNAL_CONVERT AL16UTF16;
最后修改用户口令，禁用restricted session模式，这个克隆过程执行完毕：
alter user sys identified by "&&sysPassword";
alter user system identified by "&&systemPassword";
alter system disable restricted session;
至此，完成了通过克隆方式创建数据库的过程。
完成以上步骤以后，ORACLE继续执行postScripts.sql已完成相应的维护工作，打开该文件发现内容如下：
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool f:/test/scripts/postScripts.log
@f:/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;
@f:/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
alter user CTXSYS account unlock identified by change_on_install;
connect "CTXSYS"/"change_on_install"
@f:/oracle/product/10.2.0/db_1/ctx/admin/defaults/dr0defdp.sql;
@f:/oracle/product/10.2.0/db_1/ctx/admin/defaults/dr0defin.sql "SIMPLIFIED CHINESE";
connect "SYS"/"&&sysPassword" as SYSDBA
execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
commit;
spool off
最后执行的脚本是postDBCreation.sql，在这个脚本中将创建spfile，解锁SYSMAN、DBSNMP用户，编译失效对象并配置DB Control：
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool f:/test/scripts/postDBCreation.log
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile=‘f:/oracle/product/10.2.0/db_1/dbs/spfiletest.ora‘ FROM pfile=‘f:/test/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
f:/oracle/product/10.2.0/db_1/bin/emca.bat -config dbcontrol db -silent
-DB_UNIQUE_NAME test -PORT 1521 -EM_HOME f:/oracle/product/10.2.0/db_1
-LISTENER LISTENER -SERVICE_NAME test -SYS_PWD &&sysPassword
-SID test -ORACLE_HOME f:/oracle/product/10.2.0/db_1 -DBSNMP_PWD
&&dbsnmpPassword -HOST 0d819df6bbbd490 -LISTENER_OH
f:/oracle/product/10.2.0/db_1 -LOG_FILE f:/test/scripts/emConfig.log
-SYSMAN_PWD &&sysmanPassword;
spool f:/test/scripts/postDBCreation.log
exit;
到此处，整个数据库创建脚本就已经执行完成，通过这个过程我们创建了一个名为TEST的数据库。
------------------------------------------第二部分（使用生成脚本手动创建新库日志全集）--------------------------
[oracle@dg scripts]$ pwd
/u01/app/oracle/admin/taxi/scripts
[H[J[oracle@dg scripts]$ ll
[00mtotal 40
-rwxr-xr-x 1 oracle oinstall 2804 Oct 16 15:41 [01;32mcloneDBCreation.sql
-rwxr-xr-x 1 oracle oinstall 277 Oct 16 15:41 [01;32mCloneRmanRestore.sql
-rwxr-xr-x 1 oracle oinstall 2053 Oct 16 15:41 [01;32minit.ora
-rwxr-xr-x 1 oracle oinstall 2089 Oct 16 15:41 [01;32minittaxiTemp.ora
-rwxr-xr-x 1 oracle oinstall 507 Oct 16 15:41 [01;32mlockAccount.sql
-rwxr-xr-x 1 oracle oinstall 1030 Oct 16 15:41 [01;32mpostDBCreation.sql
-rwxr-xr-x 1 oracle oinstall 568 Oct 16 15:41 [01;32mpostScripts.sql
-rwxr-xr-x 1 oracle oinstall 1364 Oct 16 15:41 [01;32mrmanRestoreDatafiles.sql
-rwxr-xr-x 1 oracle oinstall 685 Oct 16 15:41 [01;32mtaxi.sh
-rwxr-xr-x 1 oracle oinstall 688 Oct 16 15:41 [01;32mtaxi.sql
[m[oracle@dg scripts]$ pwd
/u01/app/oracle/admin/taxi/scripts
[oracle@dg scripts]$
[oracle@dg scripts]$ sh taxi.sh
You should Add this entry in the /etc/oratab: taxi:/u01/app/oracle/product/11.2.0/db_1:Y
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 16 16:57:25 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter new password for SYS:
Enter new password for SYSTEM:
Enter new password for SYSMAN:
Enter new password for DBSNMP:
Enter password for SYS:
Connected to an idle instance.
SQL> spool /u01/app/oracle/admin/taxi/scripts/CloneRmanRestore.log append
SQL> startup nomount pfile="/u01/app/oracle/admin/taxi/scripts/init.ora";
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size 2212776 bytes
Variable Size 335547480 bytes
Database Buffers 838860800 bytes
Redo Buffers 9232384 bytes
SQL> @/u01/app/oracle/admin/taxi/scripts/rmanRestoreDatafiles.sql;
SQL> set verify off;
SQL> set echo off;
TO_CHAR(SYSTIMEST
-----------------
20141016 04:57:46
Allocating device....
Specifying datafiles...
Specifing datafiles...
Restoring ...
Restore done.
PL/SQL procedure successfully completed.
TO_CHAR(SYSTIMEST
-----------------
20141016 04:59:13
Connected.
SQL> spool /u01/app/oracle/admin/taxi/scripts/cloneDBCreation.log append
SQL> Create controlfile reuse set database "taxi"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 100
7 Datafile
8 ‘/u01/app/oracle/oradata/taxi/system01.dbf‘,
9 ‘/u01/app/oracle/oradata/taxi/sysaux01.dbf‘,
10 ‘/u01/app/oracle/oradata/taxi/undotbs01.dbf‘,
11 ‘/u01/app/oracle/oradata/taxi/users01.dbf‘
12 LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/taxi/redo01.log‘) SIZE 51200K,
13 GROUP 2 (‘/u01/app/oracle/oradata/taxi/redo02.log‘) SIZE 51200K,
14 GROUP 3 (‘/u01/app/oracle/oradata/taxi/redo03.log‘) SIZE 51200K RESETLOGS;
Control file created.
SQL> exec dbms_backup_restore.zerodbid(0);
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile="/u01/app/oracle/admin/taxi/scripts/inittaxiTemp.ora";
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size 2212776 bytes
Variable Size 335547480 bytes
Database Buffers 838860800 bytes
Redo Buffers 9232384 bytes
SQL> Create controlfile reuse set database "taxi"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 100
7 Datafile
8 ‘/u01/app/oracle/oradata/taxi/system01.dbf‘,
9 ‘/u01/app/oracle/oradata/taxi/sysaux01.dbf‘,
10 ‘/u01/app/oracle/oradata/taxi/undotbs01.dbf‘,
11 ‘/u01/app/oracle/oradata/taxi/users01.dbf‘
12 LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/taxi/redo01.log‘) SIZE 51200K,
13 GROUP 2 (‘/u01/app/oracle/oradata/taxi/redo02.log‘) SIZE 51200K,
14 GROUP 3 (‘/u01/app/oracle/oradata/taxi/redo03.log‘) SIZE 51200K RESETLOGS;
Control file created.
SQL> alter system enable restricted session;
System altered.
SQL> alter database "taxi" open resetlogs;
Database altered.
SQL> exec dbms_service.delete_service(‘seeddata‘);
PL/SQL procedure successfully completed.
SQL> exec dbms_service.delete_service(‘seeddataXDB‘);
PL/SQL procedure successfully completed.
SQL> alter database rename global_name to "taxi";
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/app/oracle/oradata/taxi/temp01.dbf‘ SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
Tablespace altered.
SQL> select tablespace_name from dba_tablespaces where tablespace_name=‘USERS‘;
TABLESPACE_NAME
------------------------------
USERS
SQL> alter system disable restricted session;
System altered.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> @/u01/app/oracle/product/11.2.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/11.2.0/db_1/assistants/dbca/templates/example.dmp /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/example01.dfb /u01/app/oracle/oradata/taxi/example01.dbf /u01/app/oracle/admin/taxi/scripts/ "\‘SYS/&&sysPassword as SYSDBA\‘";
SQL> Rem
SQL> Rem $Header: mkplug.sql 27-jun-2007.09:00:22 glyon Exp $
SQL> Rem
SQL> Rem mkplug.sql
SQL> Rem
SQL> Rem Copyright (c) 2001, 2007, Oracle. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem mkplug.sql - plug in transportable tablespace EXAMPLE
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem tbd
SQL> Rem
SQL> Rem NOTES
SQL> Rem The EXAMPLE tablespace only contains the Sample Schemas
SQL> Rem - CAUTION: Never use the Sample Schemas for
SQL> Rem anything other than demos and examples
SQL> Rem - USAGE: tbd
SQL> Rem - LOG FILES: The log files are written
SQL> Rem to the equivalent of $ORACLE_HOME/demo/schema/log
SQL> Rem If you edit the log file location further down in this
SQL> Rem script, use absolute pathnames
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem glyon 06/27/07 - grant CWM_USER role to SH user
SQL> Rem bmccarth 05/29/07 - need territory american on external table
SQL> Rem pabingha 02/26/07 - LRG 2871657 use dimension_exceptions
SQL> Rem cbauwens 05/02/05 - bug4054905 Date & Time format
SQL> Rem cbauwens 04/19/05 - fix privs for SH and BI
SQL> Rem cbauwens 12/03/04 - add call to olp_v3.sql for cube metadata
SQL> Rem cbauwens 10/29/04 - modifying privs after deprecation of connect
SQL> Rem cbauwens 07/26/04 - remove stylesheet tab
SQL> Rem rsahani 09/08/04 - privileges granted must be same
SQL> Rem as granted when creating schema
SQL> Rem jcjeon 03/30/04 - fix lrg1628995
SQL> Rem huzhao 01/28/04 - validate certain AQ within IX schema after TTS import
SQL> Rem cbauwens 11/18/03 - lrg1582814
SQL> Rem cbauwens 08/21/03 - OMF support
SQL> Rem cbauwens 08/05/03 - profits view
SQL> Rem cbauwens 08/05/03 - company_id
SQL> Rem cbauwens 06/19/03 - bug_2878871
SQL> Rem cbauwens 06/18/03 - bug_2878871
SQL> Rem ahunold 03/27/03 - Objects not transported: lrg 1348159
SQL> Rem ahunold 03/01/03 - Bug 2828348
SQL> Rem ahunold 02/10/03 - grants AFTER mk_dir, TS ver. query
SQL> Rem ahunold 01/27/03 - RMAN restore
SQL> Rem ahunold 01/16/03 - CONNECT errors
SQL> Rem ahunold 12/11/02 - password variables, line continuation
SQL> Rem ahunold 09/30/02 - procedures, directories
SQL> Rem ahunold 09/25/02 - imp logfile
SQL> Rem ahunold 09/18/02 - Created
SQL> Rem
SQL>
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 999
SQL> SET ECHO OFF
specify password for SYS as parameter 1:
specify password for HR as parameter 2:
specify password for OE as parameter 3:
specify password for PM as parameter 4:
specify password for IX as parameter 5:
specify password for SH as parameter 6:
specify password for BI as parameter 7:
specify INPUT metadata import file as parameter 8:
specify INPUT database backup file for tablespace EXAMPLE as parameter 9:
specify OUTPUT database file for tablespace EXAMPLE as parameter 10:
specify OUTPUT log directory as parameter 11:
Sample Schemas are being plugged in ...
Connected.
TO_CHAR(SYSTIMEST
-----------------
20141016 04:59:56
1 row selected.
User created.
User created.
User created.
User created.
User created.
User created.
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> CREATE OR REPLACE DIRECTORY data_file_dir AS ‘/u01/app/oracle/product/11.2.0/db_1/demo/schema/sales_history/‘;
Directory created.
SQL> CREATE OR REPLACE DIRECTORY log_file_dir AS ‘/u01/app/oracle/product/11.2.0/db_1/demo/schema/log/‘;
Directory created.
SQL> CREATE OR REPLACE DIRECTORY media_dir AS ‘/u01/app/oracle/product/11.2.0/db_1/demo/schema/product_media/‘;
Directory created.
SQL>
SQL> GRANT READ ON DIRECTORY media_dir TO pm;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY log_file_dir TO sh;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY data_file_dir TO sh;
Grant succeeded.
SQL> GRANT WRITE ON DIRECTORY log_file_dir TO sh;
Grant succeeded.
SQL> EXECUTE DBMS_DATAPUMP_UTL.REPLACE_DEFAULT_DIR;
PL/SQL procedure successfully completed.
SQL> EXECUTE ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
PL/SQL procedure successfully completed.
SQL>
SQL> GRANT CREATE SESSION TO hr;
Grant succeeded.
SQL> GRANT ALTER SESSION TO hr;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO hr;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO hr;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO hr;
Grant succeeded.
SQL> GRANT CREATE VIEW TO hr;
Grant succeeded.
SQL> GRANT RESOURCE TO hr;
Grant succeeded.
SQL> GRANT execute ON sys.dbms_stats TO hr;
Grant succeeded.
SQL>
SQL> GRANT CREATE SESSION TO oe;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO oe;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO oe;
Grant succeeded.
SQL> GRANT CREATE VIEW TO oe;
Grant succeeded.
SQL> GRANT RESOURCE TO oe;
Grant succeeded.
SQL> GRANT CREATE MATERIALIZED VIEW TO oe;
Grant succeeded.
SQL> GRANT QUERY REWRITE TO oe;
Grant succeeded.
SQL> GRANT execute ON sys.dbms_stats TO oe;
Grant succeeded.
SQL>
SQL> GRANT CONNECT TO pm;
Grant succeeded.
SQL> GRANT RESOURCE TO pm;
Grant succeeded.
SQL> GRANT execute ON sys.dbms_stats TO pm;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY media_dir TO pm;
Grant succeeded.
SQL>
SQL> GRANT CONNECT TO ix;
Grant succeeded.
SQL> GRANT RESOURCE TO ix;
Grant succeeded.
SQL>
SQL> GRANT aq_administrator_role TO ix;
Grant succeeded.
SQL> GRANT aq_user_role TO ix;
Grant succeeded.
SQL>
SQL> GRANT ALTER SESSION TO ix;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO ix;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO ix;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO ix;
Grant succeeded.
SQL> GRANT CREATE SESSION TO ix;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO ix;
Grant succeeded.
SQL> GRANT CREATE TABLE TO ix;
Grant succeeded.
SQL> GRANT CREATE VIEW TO ix;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO ix;
Grant succeeded.
SQL> GRANT CREATE INDEXTYPE TO ix;
Grant succeeded.
SQL> GRANT CREATE OPERATOR TO ix;
Grant succeeded.
SQL> GRANT CREATE PROCEDURE TO ix;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO ix;
Grant succeeded.
SQL> GRANT CREATE TABLE TO ix;
Grant succeeded.
SQL> GRANT CREATE TRIGGER TO ix;
Grant succeeded.
SQL> GRANT CREATE TYPE TO ix;
Grant succeeded.
SQL> GRANT CREATE SESSION TO ix;
Grant succeeded.
SQL>
SQL> GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO ix;
Grant succeeded.
SQL>
SQL> GRANT EXECUTE ON sys.dbms_stats TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_AQ TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_AQADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_CAPTURE_ADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO ix;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_STREAMS_ADM TO ix;
Grant succeeded.
SQL> GRANT SELECT ANY DICTIONARY TO ix;
Grant succeeded.
SQL>
SQL> EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( -
> privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, -
> grantee => ‘ix‘, -
> grant_option => FALSE);
PL/SQL procedure successfully completed.
SQL>
SQL> EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( -
> privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, -
> grantee => ‘ix‘, -
> grant_option => FALSE);
PL/SQL procedure successfully completed.
SQL>
SQL> GRANT CREATE SESSION TO sh;
Grant succeeded.
SQL> GRANT CREATE TABLE TO sh;
Grant succeeded.
SQL> GRANT CREATE VIEW TO sh;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO sh;
Grant succeeded.
SQL> GRANT ALTER SESSION TO sh;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO sh;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO sh;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO sh;
Grant succeeded.
SQL> GRANT CREATE DIMENSION TO sh;
Grant succeeded.
SQL> GRANT QUERY REWRITE TO sh;
Grant succeeded.
SQL> GRANT CREATE MATERIALIZED VIEW TO sh;
Grant succeeded.
SQL> GRANT CREATE VIEW TO sh;
Grant succeeded.
SQL> GRANT RESOURCE TO sh;
Grant succeeded.
SQL> GRANT select_catalog_role TO sh;
Grant succeeded.
SQL> GRANT cwm_user TO sh;
Grant succeeded.
SQL> GRANT execute ON sys.dbms_stats TO sh;
Grant succeeded.
SQL> rem ALTER USER sh GRANT CONNECT THROUGH olapsvr;
SQL>
SQL> GRANT CREATE SESSION TO bi;
Grant succeeded.
SQL> GRANT CREATE TABLE TO bi;
Grant succeeded.
SQL> GRANT CREATE VIEW TO bi;
Grant succeeded.
SQL> GRANT CREATE CLUSTER TO bi;
Grant succeeded.
SQL> GRANT ALTER SESSION TO bi;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO bi;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO bi;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO bi;
Grant succeeded.
SQL> GRANT RESOURCE TO bi;
Grant succeeded.
SQL>
SQL> --
SQL> -- Restoring database file backup
SQL> -- (Using RMAN works in OMF, OCFS, raw devices and in normal file systems)
SQL> --
SQL>
SQL>
SQL> set echo off;
TO_CHAR(SYSTIMEST
-----------------
20141016 05:00:14
1 row selected.
Allocating device....
Specifying datafiles...
Specifing datafiles...
Restoring ...
Restore done.
PL/SQL procedure successfully completed.
1 row selected.
TO_CHAR(SYSTIMEST
-----------------
20141016 05:00:31
1 row selected.
Import: Release 11.2.0.1.0 - Production on Thu Oct 16 17:00:32 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing SYS‘s objects into SYS
. importing SYS‘s objects into SYS
. importing HR‘s objects into HR
. . importing table "REGIONS"
. . importing table "COUNTRIES"
. . importing table "LOCATIONS"
. . importing table "DEPARTMENTS"
. . importing table "JOBS"
. . importing table "EMPLOYEES"
. . importing table "JOB_HISTORY"
. importing OE‘s objects into OE
. . importing table "CUSTOMERS"
. . importing table "WAREHOUSES"
. . importing table "ORDER_ITEMS"
. . importing table "ORDERS"
. . importing table "INVENTORIES"
. . importing table "PRODUCT_INFORMATION"
. . importing table "PRODUCT_DESCRIPTIONS"
. . importing table "PROMOTIONS"
. importing PM‘s objects into PM
. . importing table "ONLINE_MEDIA"
. . importing table "PRINT_MEDIA"
. importing IX‘s objects into IX
. . importing table "ORDERS_QUEUETABLE"
. . importing table "AQ$_ORDERS_QUEUETABLE_S"
. . importing table "AQ$_ORDERS_QUEUETABLE_T"
. . importing table "AQ$_ORDERS_QUEUETABLE_H"
. . importing table "AQ$_ORDERS_QUEUETABLE_L"
. . importing table "AQ$_ORDERS_QUEUETABLE_G"
. . importing table "AQ$_ORDERS_QUEUETABLE_I"
. . importing table "STREAMS_QUEUE_TABLE"
. . importing table "AQ$_STREAMS_QUEUE_TABLE_S"
. . importing table "AQ$_STREAMS_QUEUE_TABLE_T"
. . importing table "AQ$_STREAMS_QUEUE_TABLE_H"
. . importing table "AQ$_STREAMS_QUEUE_TABLE_L"
. . importing table "AQ$_STREAMS_QUEUE_TABLE_G"
. . importing table "AQ$_STREAMS_QUEUE_TABLE_I"
. . importing table "AQ$_STREAMS_QUEUE_TABLE_C"
. importing SH‘s objects into SH
. . importing table "SALES"
. . importing table "COSTS"
. . importing table "TIMES"
. . importing table "PRODUCTS"
. . importing table "CHANNELS"
. . importing table "PROMOTIONS"
. . importing table "CUSTOMERS"
. . importing table "COUNTRIES"
. . importing table "SUPPLEMENTARY_DEMOGRAPHICS"
. . importing table "CAL_MONTH_SALES_MV"
. . importing table "FWEEK_PSCAT_SALES_MV"
. importing HR‘s objects into HR
. importing OE‘s objects into OE
. importing PM‘s objects into PM
. importing SH‘s objects into SH
. importing OE‘s objects into OE
. importing PM‘s objects into PM
. importing IX‘s objects into IX
. importing SH‘s objects into SH
. . importing table "DR$SUP_TEXT_IDX$I"
. . importing table "DR$SUP_TEXT_IDX$K"
. . importing table "DR$SUP_TEXT_IDX$R"
. . importing table "DR$SUP_TEXT_IDX$N"
. importing OE‘s objects into OE
. importing IX‘s objects into IX
. importing HR‘s objects into HR
About to enable constraints...
. importing OE‘s objects into OE
. importing SYS‘s objects into SYS
Import terminated successfully without warnings.
Connected.
TO_CHAR(SYSTIMEST
-----------------
20141016 05:01:39
1 row selected.
Tablespace altered.
TABLESPACE_NAME FILE_NAME STATUS
--------------- ---------------------------------------------- ---------
SYSTEM /u01/app/oracle/oradata/taxi/system01.dbf AVAILABLE
SYSAUX /u01/app/oracle/oradata/taxi/sysaux01.dbf AVAILABLE
UNDOTBS1 /u01/app/oracle/oradata/taxi/undotbs01.dbf AVAILABLE
USERS /u01/app/oracle/oradata/taxi/users01.dbf AVAILABLE
EXAMPLE /u01/app/oracle/oradata/taxi/example01.dbf AVAILABLE
5 rows selected.
Creating sequences, views, procedures and objects privileges for HR ...
TO_CHAR(SYSTIMEST
-----------------
20141016 05:01:40
1 row selected.
Connected.
Sequence created.
Sequence created.
Sequence created.
View created.
Procedure created.
Trigger created.
Trigger altered.
Procedure created.
Trigger created.
Commit complete.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Creating synonyms, sequences, views and functions for OE ...
TO_CHAR(SYSTIMEST
-----------------
20141016 05:01:43
1 row selected.
Connected.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Sequence created.
View created.
View created.
View created.
View created.
View created.
View created.
Function created.
View created.
View created.
Creating XML schema, XML folders, OC subschema and objects privileges for OE ...
TO_CHAR(SYSTIMEST
-----------------
20141016 05:01:44
1 row selected.
specify password for OE as parameter 1:
PROMPT password for SYS as parameter 2:
Connected.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
1* GRANT alter session TO oe
Grant succeeded.
View created.
View created.
Grant succeeded.
Session altered.
Package created.
Warning: Package Body created with compilation errors.
View created.
Package altered.
View altered.
Grant succeeded.
Trigger created.
Synonym created.
Grant succeeded.
Call completed.
Session altered.
Session altered.
Function created.
no rows selected
Synonym created.
Grant succeeded.
Package created.
no rows selected
Synonym created.
Grant succeeded.
Package created.
no rows selected
Package body created.
no rows selected
Synonym created.
Grant succeeded.
Package created.
no rows selected
Package body created.
no rows selected
Grant succeeded.
Synonym created.
Package created.
no rows selected
Package body created.
no rows selected
Synonym created.
Grant succeeded.
PL/SQL procedure successfully completed.
Connected.
DROP DIRECTORY SS_OE_XMLDIR
*
ERROR at line 1:
ORA-04043: object SS_OE_XMLDIR does not exist
Directory created.
Commit complete.
Connected.
Revoke succeeded.
Connected.
Connected.
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Call completed.
Call completed.
PL/SQL procedure successfully completed.
Connected.
Revoke succeeded.
Connected.
Connected.
Revoke succeeded.
Revoke succeeded.
Revoke succeeded.
Package dropped.
Package dropped.
Package dropped.
Package dropped.
Package dropped.
Trigger dropped.
View dropped.
Connected.
Session altered.
...creating subschema OC in OE
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type created.
Type body created.
Type created.
Type body created.
Type created.
Type body created.
Table created.
View created.
View created.
View created.
View created.
View created.
View created.
Trigger created.
Trigger created.
Commit complete.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Type altered.
3 rows updated.
8 rows updated.
6 rows updated.
4 rows updated.
Commit complete.
Type body altered.
Type body altered.
Type body altered.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Creating dimensions, materialized views, external table and object privileges for SH ...
TO_CHAR(SYSTIMEST
-----------------
20141016 05:02:38
1 row selected.
Connected.
Dimension created.
Commit complete.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
Dimension created.
PL/SQL procedure successfully completed.
no rows selected
TO_CHAR(SYSTIMEST
-----------------
20141016 05:02:46
1 row selected.
View created.
Materialized view created.
Materialized view created.
Table created.
Creating OLAP metadata ...
<<<<< CREATE CWMLite Metadata for the Sales History Schema >>>>>
-
<<<<< CREATE CATALOG sh_cat for Sales History >>>>>
No catalog to drop
CWM Collect Garbage
-
<<<<< CREATE the Sales CUBE >>>>>
Sales amount, Sales quantity
<TIMES CHANNELS PRODUCTS CUSTOMERS PROMOTIONS >
Drop SALES_CUBE prior to recreation
No cube to drop
Add dimensions -
to SALES_CUBE and map the foreign keys
Create measures -
for SALES_CUBE and map to columns in the fact table
Set default aggregation method -
to SUM for all measures over TIME
Add SALES_CUBE to the catalog
SALES_CUBE successfully added to sh_cat
-
<<<<< CREATE the Cost CUBE >>>>>
Unit Cost, Unit Price < TIMES PRODUCTS CHANNELS PROMOTIONS >
Drop COST_CUBE prior to recreation
No cube to drop
Add dimensions -
to COST_CUBE and map the foreign keys
Create measures -
for COST_CUBE and map to columns in the fact table
Set default aggregation method -
to SUM for all measures over TIME
Add COST_CUBE to the catalog
COST_CUBE successfully added to sh_cat
-
<<<<< TIME DIMENSION >>>>>
Dimension - display name, description and plural name
Level - display name and description
Hierarchy - display name and description
- default calculation hierarchy
- default display hierarchy
Level Attributes - name, display name, description
Drop dimension attributes prior to re-creation
Create dimension attributes and add their level attributes
- Long Description created
- Short Description created
- Period Number of Days created
- Period End Date created
Classify entity descriptor use
- Time dimension
- Long description
- Day name
- Calendar month description
- Calendar quarter description
- Fiscal month description
- Fiscal quarter description
- Short Description
- Day name
- Calendar month description
- Calendar quarter description
- Fiscal month description
- Fiscal quarter description
- Time Span
- Days in calendar month
- Days in calendar quarter
- Days in calendar year
- Days in fiscal month
- Days in fiscal quarter
- Days in fiscal year
- End Date
- End of calendar month
- End of calendar quarter
- End of calendar year
- End of fiscal month
- End of fiscal quarter
- End of fiscal year
-
<<<<< CUSTOMERS DIMENSION >>>>>
Dimension - display name, description and plural name
Level - display name and description
Hierarchy - display name and description
- default calculation hierarchy
- default display hierarchy
Level Attributes - name, display name, description
Drop dimension attributes prior to re-creation
No attribute to drop
No attribute to drop
No attribute to drop
No attribute to drop
No attribute to drop
No attribute to drop
No attribute to drop
No attribute to drop
No attribute to drop
No attribute to drop
No attribute to drop
No attribute to drop
Create dimension attributes and add their level attributes
- Long Description created
- Short Description created
- Other Customer Information created
Classify entity descriptor use
- Long Description
- Short Description
<<<<< PRODUCTS DIMENSION >>>>>
Dimension - display name, description and plural name
Level - display name and description
Hierarchy - display name and description
- default calculation hierarchy
- default display hierarchy
Level Attributes - name, display name, description
Drop dimension attributes prior to re-creation
No attribute to drop
Create dimension attributes and add their level attributes
- Long Description created
- Short Description created
Classify entity descriptor use
- Long Description
- Short Description
-
<<<<< PROMOTIONS DIMENSION >>>>>
Dimension - display name, description and plural name
Level - display name and description
Hierarchy - display name and description
- default calculation hierarchy
- default display hierarchy
Level Attributes - name, display name, description
Drop dimension attributes prior to re-creation
No attribute to drop
Create dimension attributes and add their level attributes
- Long Description created
- Short Description created
Classify entity descriptor use
- Long Description
- Short Description
-
<<<<< CHANNELS DIMENSION >>>>>
Dimension - display name, description and plural name
Level - display name and description
Hierarchy - display name and description
- default calculation hierarchy
- default display hierarchy
Level Attributes - name, display name, description
Drop dimension attributes prior to re-creation
No attribute to drop
Create dimension attributes and add their level attributes
- Long Description created
- Short Description created
Classify entity descriptor use
- Long Description
- Short Description
-
<<<<< FINAL PROCESSING >>>>>
- Changes have been committed
PL/SQL procedure successfully completed.
Commit complete.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Creating views, synonyms for BI ...
TO_CHAR(SYSTIMEST
-----------------
20141016 05:03:00
1 row selected.
specify password for BI as parameter 1:
Connected.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Commit complete.
Connected.
PL/SQL procedure successfully completed.
Connected.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
mkplug.sql DONE
TO_CHAR(SYSTIMEST
-----------------
20141016 05:03:05
1 row selected.
Connected.
Database closed.
Database dismounted.
ORACLE instance shut down.
Connected to an idle instance.
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size 2212776 bytes
Variable Size 335547480 bytes
Database Buffers 838860800 bytes
Redo Buffers 9232384 bytes
Database mounted.
Database opened.
SID PROGRAM SERIAL#
---------- ------------------------------------------------ ----------
USERNAME
------------------------------
2 oracle@dg.com (PMON) 1
3 oracle@dg.com (VKTM) 1
4 oracle@dg.com (GEN0) 1
5 oracle@dg.com (DIAG) 1
6 oracle@dg.com (DBRM) 1
7 oracle@dg.com (PSP0) 1
8 oracle@dg.com (DIA0) 1
9 oracle@dg.com (MMAN) 1
10 oracle@dg.com (DBW0) 1
11 oracle@dg.com (LGWR) 1
12 oracle@dg.com (CKPT) 1
13 oracle@dg.com (SMON) 1
14 oracle@dg.com (RECO) 1
15 oracle@dg.com (MMNL) 1
16 oracle@dg.com (MMON) 1
17 sqlplus@dg.com (TNS V1-V3) 3
SYS
21 oracle@dg.com (QMNC) 2
17 rows selected.
Database altered.
Database altered.
User altered.
User altered.
System altered.
Connected.
SQL> spool /u01/app/oracle/admin/taxi/scripts/postScripts.log append
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/dbmssml.sql;
SQL> CREATE OR REPLACE LIBRARY dbms_sumadv_lib AS ‘/u01/app/oracle/product/11.2.0/db_1/lib/libqsmashr.so‘;
2 /
Library created.
SQL> execute dbms_datapump_utl.replace_default_dir;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter session set current_schema=ORDSYS;
Session altered.
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/ordlib.sql;
SQL> create or replace library ORDIMLIBS as ‘/u01/app/oracle/product/11.2.0/db_1/lib/libordim11.so‘;
2 /
Library created.
SQL> create or replace library ORDIMLIBT trusted as static;
2 /
Library created.
SQL>
SQL> alter session set current_schema=SYS;
Session altered.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> execute ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
PL/SQL procedure successfully completed.
SQL> @/u01/app/oracle/admin/taxi/scripts/lockAccount.sql
SQL> SET VERIFY OFF
SQL> set echo on
SQL> spool /u01/app/oracle/admin/taxi/scripts/lockAccount.log append
SQL> BEGIN
2 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN (‘OPEN‘, ‘LOCKED‘, ‘EXPIRED‘) AND USERNAME NOT IN (
3 ‘SYS‘,‘SYSTEM‘) )
4 LOOP
5 dbms_output.put_line(‘Locking and Expiring: ‘ || item.USERNAME);
6 execute immediate ‘alter user ‘ ||
7 sys.dbms_assert.enquote_name(
8 sys.dbms_assert.schema_name(
9 item.USERNAME),false) || ‘ password expire account lock‘ ;
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL> spool off
SQL> @/u01/app/oracle/admin/taxi/scripts/postDBCreation.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/taxi/scripts/postDBCreation.log append
SQL> select ‘utl_recomp_begin: ‘ || to_char(sysdate, ‘HH:MI:SS‘) from dual;
‘UTL_RECOMP_BEGIN:‘||TO_CH
--------------------------
utl_recomp_begin: 05:06:45
1 row selected.
SQL> execute utl_recomp.recomp_serial();
PL/SQL procedure successfully completed.
SQL> select ‘utl_recomp_end: ‘ || to_char(sysdate, ‘HH:MI:SS‘) from dual;
‘UTL_RECOMP_END:‘||TO_CH
------------------------
utl_recomp_end: 05:07:20
1 row selected.
SQL> execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> create
spfile=‘/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletaxi.ora‘ FROM
pfile=‘/u01/app/oracle/admin/taxi/scripts/init.ora‘;
File created.
Oracle使用DBCA创建数据库脚本追踪学习案例(一)
原文：http://blog.itpub.net/29119536/viewspace-1300706/
