Oracle使用DBCA建立資料庫指令碼追蹤學習案例(一)
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
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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29762980/viewspace-1310817/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle使用DBCA建立資料庫指令碼追蹤學習案例(二)Oracle資料庫指令碼
- AIX下使用dbca建立oracle資料庫AIOracle資料庫
- 2 Day DBA-使用DBCA建立和管理資料庫—使用DBCA建立資料庫資料庫
- 指令碼建立Oracle的資料庫指令碼Oracle資料庫
- 2.3.1 有關使用DBCA建立資料庫資料庫
- 2 Day DBA-使用DBCA建立資料庫資料庫
- Oralce 10g 使用DBCA建立資料庫資料庫
- dbca -silent建立rac資料庫資料庫
- oracle 學習總結篇一: 資料庫的建立Oracle資料庫
- 2 Day DBA-使用DBCA建立和管理資料庫—啟動DBCA資料庫
- 2.3 通過DBCA建立資料庫資料庫
- dbca silent靜默建立資料庫資料庫
- 2.3.2 關於使用互動式DBCA建立資料庫資料庫
- 2 Day DBA-使用DBCA建立和管理資料庫資料庫
- OCP課程25：管理Ⅰ之使用DBCA建立資料庫資料庫
- oracle資料庫使用rman備份指令碼Oracle資料庫指令碼
- oracle 11g使用DBCA以非互動(靜默)方式建立資料庫Oracle資料庫
- isql指令碼編寫建立資料庫SQL指令碼資料庫
- 建立sqlserver資料庫指令碼 範例SQLServer資料庫指令碼
- 利用Zipkin追蹤Mysql資料庫呼叫鏈MySql資料庫
- Oracle9i 官方文件建立資料庫指令碼的一個錯誤Oracle資料庫指令碼
- 【DATAGUARD 學習】使用duplicate 建立物理standby 資料庫資料庫
- ORACLE 使用DBCA安裝資料庫出現錯誤Oracle資料庫
- Activiti學習筆記一：建立資料庫表筆記資料庫
- 建立管理MySQL資料庫的shell指令碼MySql資料庫指令碼
- 建立SQL Server資料庫指令碼 範例SQLServer資料庫指令碼
- 2.3.3 關於使用非互動式/靜默DBCA建立資料庫資料庫
- oracle 10g手工建立案例（含指令碼）Oracle 10g指令碼
- 搭建資料追蹤系統
- 使用 SonarQube 追蹤程式碼問題
- Sqlserver在映象資料庫上建立快照指令碼SQLServer資料庫指令碼
- 簡單資料庫及表建立shell指令碼資料庫指令碼
- 【資料庫使用-oracle索引的建立和分類】一資料庫Oracle索引
- 資料庫監控指令碼（一）資料庫指令碼
- Oracle資料庫跟蹤SQLOracle資料庫SQL
- 如何不使用DBCA在Oracle 11中刪除資料庫Oracle資料庫
- ORACLE資料庫定時執行一些指令碼。Oracle資料庫指令碼
- shell oracle 建立使用者指令碼Oracle指令碼