使用DG庫資料檔案建開發測試庫步驟
1.根據現有的檔案建立prof_$ORACLE_SID檔案：
cp prof_gs30xn prof_gs30xa
然後修改ORACLE_SID的值gs30xa
eg：
stty erase "^H"
umask 022
ORACLE_SID=gs30xa
ORACLE_BASE=/paic/stg/oracle/10g/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.0
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
ORA_NLS10=$ORACLE_HOME/nls/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$ORACLE_HOME/bin:/usr/sbin:/usr/local/bin:/usr/ccs/bin:$PATH
NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export PS1 ORACLE_SID ORACLE_BASE ORACLE_HOME NLS_LANG ORA_NLS10 LD_LIBRARY_PATH NLS_DATE_FORMAT
export PATH
PS1="`hostname`:10g:$ORACLE_SID > "
TERM=vt100;export TERM
#SHELL_PATH=/etc/paic/shell; export SHELL_PATH
TEMP=$HOME/tmp
export TEMP
EDITOR=vi
export EDITOR
2.建立相關的路徑，將$SID替換成你要建庫的sid，如gs30xa
cd $ORACLE_BASE/admin
mkdir $SID
mkdir $ORACLE_BASE/admin/$SID/adump
mkdir $ORACLE_BASE/admin/$SID/bdump
mkdir $ORACLE_BASE/admin/$SID/cdump
mkdir $ORACLE_BASE/admin/$SID/udump
3.建立並修改 init 配置檔案，也可根據現有的檔案建立
cd $ORACLE_HOME/dbs
vi initgs30xa.ora
然後輸入如下內容：注意：不要有多餘的空格、換行符！db_name='gxa0'要先寫成生產的sid，等修改好控制檔案後再改成開發測試庫的sid！！
###########################################################################
*._log_archive_callout='local_first=true'
*._trace_files_public=TRUE
*.aq_tm_processes=0
*.archive_lag_target=3600
*.audit_file_dest='/paic/stg/oracle/10g/app/oracle/admin/gs30xa/adump'
*.audit_trail='NONE'
*.background_dump_dest='/paic/stg/oracle/10g/app/oracle/admin/gs30xa/bdump'
*.compatible='10.2.0.3'
*.control_files='/paic/s2cs5020/stg/cx2/oradata/gs30xa/control01.ctl','/paic/s2cs5020/stg/cx2/oradata/gs30xa/control02.ctl','/paic/s2cs5020/stg/cx2/oradata/gs30xa/control03.ctl'
*.core_dump_dest='/paic/stg/oracle/10g/app/oracle/admin/gs30xa/cdump'
*.db_block_size=8192
*.db_cache_size=82880000
*.db_domain='world'
*.db_name='gxa0'
*.java_pool_size=50000000
*.job_queue_processes=0
*.large_pool_size=60663296
*.log_archive_max_processes=10
*.log_buffer=10485760
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.open_cursors=600
*.open_links=10
*.os_authent_prefix=''
*.pga_aggregate_target=60M
*.processes=320
*.resource_limit=TRUE
*.sga_target=500M
*.shared_pool_size=250000000
*.undo_management='AUTO'
*.undo_retention=86400
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/paic/stg/oracle/10g/app/oracle/admin/gs30xa/udump'
*.utl_file_dir=''
#######################################################################
4.應用prof_gs30xa檔案：
cd
. ./prof_gs30xa
sqlplus / as sysdba
startup mount
5.修改controlfile：
exit
cd $ORACLE_BASE/admin/gs30xa/udump
sqlplus / as sysdba
alter database backup controlfile to trace;
shutdown immediate;
exit
ls -ltr
(然後複製最下面即最新時間點產生的trace檔名，如gs30xa_ora_10564.trc)
cp gs30xa_ora_10564.trc control.sql (利用trace檔案生成重建controlfile的指令碼)
vi control.sql
只抽取如下部分，其餘行都刪掉：
##STARTUP NOMOUNT
##CREATE CONTROLFILE set DATABASE "GS30XA" RESETLOGS FORCE LOGGING NOARCHIVELOG
## MAXLOGFILES 32
## MAXLOGMEMBERS 3
## MAXDATAFILES 500
## MAXINSTANCES 8
## MAXLOGHISTORY 2336
##LOGFILE
## GROUP 1 '/paic/xa/gbs/redo/oradata/gxa0/redo01.log' SIZE 50M,
## GROUP 2 '/paic/xa/gbs/redo/oradata/gxa0/redo02.log' SIZE 50M,
## GROUP 3 '/paic/xa/gbs/redo/oradata/gxa0/redo03.log' SIZE 50M,
## GROUP 4 '/paic/xa/gbs/redo/oradata/gxa0/redo04.log' SIZE 50M,
## GROUP 5 '/paic/xa/gbs/redo/oradata/gxa0/redo05.log' SIZE 50M,
## GROUP 6 '/paic/xa/gbs/redo/oradata/gxa0/redo06.log' SIZE 50M,
## GROUP 7 '/paic/xa/gbs/redo/oradata/gxa0/redo07.log' SIZE 50M,
## GROUP 8 '/paic/xa/gbs/redo/oradata/gxa0/redo08.log' SIZE 50M,
## GROUP 9 '/paic/xa/gbs/redo/oradata/gxa0/redo09.log' SIZE 50M,
## GROUP 10 '/paic/xa/gbs/redo/oradata/gxa0/redo10.log' SIZE 50M,
## GROUP 11 '/paic/xa/gbs/redo/oradata/gxa0/redo11.log' SIZE 50M,
## GROUP 12 '/paic/xa/gbs/redo/oradata/gxa0/redo12.log' SIZE 50M
##-- STANDBY LOGFILE
##DATAFILE
## '/paic/xa/gbs/data/oradata/gxa0/system01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/undotbs01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/sysaux01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/users01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/auddata01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/abbsdata01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/abbsindx01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/dbadata01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/dmlbakdata01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/i3_orcl01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbs01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbs02.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbs03.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbs04.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbsbak01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbsbase01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbsindex01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbsindex02.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbslogtmpdata01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbslogtmpidx01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbsrep01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbsrept01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbsusers01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gresv01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gresvdata01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gresvidx01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/tools01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbsindex03.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbsmisindex01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbsmis01.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/gbsindex04.dbf',
## '/paic/xa/gbs/data/oradata/gxa0/dmlbakdata02.dbf'
##CHARACTER SET ZHS16GBK
##;
需要修改的地方有:
(1)CREATE CONTROLFILE set DATABASE "GS30XA" RESETLOGS FORCE LOGGING NOARCHIVELOG
注意：：：
將old“ REUSE DATABASE "GXA0"” 改為new“ SET DATABASE "GS30XA"”
將old“ NORESETLOGS NOARCHIVELOG” 改為new“ RESETLOGS FORCE LOGGING NOARCHIVELOG”
(2)將路徑都改為新datafile的路徑，最好拷到UE中改，改好後重新拷到檔案中
old LOGFILE:
GROUP 1 '/paic/xa/gbs/redo/oradata/gxa0/redo01.log' SIZE 50M,
GROUP 2 '/paic/xa/gbs/redo/oradata/gxa0/redo02.log' SIZE 50M,
GROUP 3 '/paic/xa/gbs/redo/oradata/gxa0/redo03.log' SIZE 50M,
GROUP 4 '/paic/xa/gbs/redo/oradata/gxa0/redo04.log' SIZE 50M,
GROUP 5 '/paic/xa/gbs/redo/oradata/gxa0/redo05.log' SIZE 50M,
GROUP 6 '/paic/xa/gbs/redo/oradata/gxa0/redo06.log' SIZE 50M,
GROUP 7 '/paic/xa/gbs/redo/oradata/gxa0/redo07.log' SIZE 50M,
GROUP 8 '/paic/xa/gbs/redo/oradata/gxa0/redo08.log' SIZE 50M,
GROUP 9 '/paic/xa/gbs/redo/oradata/gxa0/redo09.log' SIZE 50M,
GROUP 10 '/paic/xa/gbs/redo/oradata/gxa0/redo10.log' SIZE 50M,
GROUP 11 '/paic/xa/gbs/redo/oradata/gxa0/redo11.log' SIZE 50M,
GROUP 12 '/paic/xa/gbs/redo/oradata/gxa0/redo12.log' SIZE 50M
new LOGFILE：
GROUP 1 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo01.log' SIZE 50M,
GROUP 2 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo02.log' SIZE 50M,
GROUP 3 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo03.log' SIZE 50M,
GROUP 4 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo04.log' SIZE 50M,
GROUP 5 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo05.log' SIZE 50M,
GROUP 6 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo06.log' SIZE 50M,
GROUP 7 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo07.log' SIZE 50M,
GROUP 8 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo08.log' SIZE 50M,
GROUP 9 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo09.log' SIZE 50M,
GROUP 10 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo10.log' SIZE 50M,
GROUP 11 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo11.log' SIZE 50M,
GROUP 12 '/paic/s2cs5020/stg/cx2/oradata/gs30xa/redo12.log' SIZE 50M
old DATAFILE:
'/paic/xa/gbs/data/oradata/gxa0/system01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/undotbs01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/sysaux01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/users01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/auddata01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/abbsdata01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/abbsindx01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/dbadata01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/dmlbakdata01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/i3_orcl01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbs01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbs02.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbs03.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbs04.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbsbak01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbsbase01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbsindex01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbsindex02.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbslogtmpdata01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbslogtmpidx01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbsrep01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbsrept01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbsusers01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gresv01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gresvdata01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gresvidx01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/tools01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbsindex03.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbsmisindex01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbsmis01.dbf',
'/paic/xa/gbs/data/oradata/gxa0/gbsindex04.dbf',
'/paic/xa/gbs/data/oradata/gxa0/dmlbakdata02.dbf'
new DATAFILE:
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/system01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/undotbs01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/sysaux01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/users01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/auddata01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/abbsdata01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/abbsindx01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/dbadata01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/dmlbakdata01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/i3_orcl01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbs01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbs02.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbs03.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbs04.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbsbak01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbsbase01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbsindex01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbsindex02.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbslogtmpdata01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbslogtmpidx01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbsrep01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbsrept01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbsusers01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gresv01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gresvdata01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gresvidx01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/tools01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbsindex03.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbsmisindex01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbsmis01.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/gbsindex04.dbf',
'/paic/s2cs5020/stg/cx2/oradata/gs30xa/dmlbakdata02.dbf'
修改完後儲存退出 :wq!
6.修改init檔案
cd $ORACLE_HOME/dbs
vi initgs30xa.ora
然後將db_name修改成開發測試庫的sid：*.db_name='gs30xa'
7.將原來的control檔案備份成成其他名字，然後刪除
cd /paic/s2cs5020/stg/cx2/oradata/gs30xa
s2cs5020:10g:gs30xa > rm control01.ctl
s2cs5020:10g:gs30xa > rm control02.ctl
s2cs5020:10g:gs30xa > rm control03.ctl
rm control01.ctl
rm control02.ctl
rm control03.ctl
7.執行control.sql
cd $ORACLE_BASE/admin/gs30xa/udump
sqlplus / as sysdba
SQL> @control.sql
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 2178744 bytes
Variable Size 372883784 bytes
Database Buffers 138412032 bytes
Redo Buffers 10813440 bytes
Control file created.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from v$database;
8.
8i的temp表空間是datafile，不用再重建。
9i以及10g的temp表空間是tempfile，需要重建。
SQL> alter tablespace TEMP add tempfile '/paic/s2cs5020/stg/cx2/oradata/gs30xa/temp01.dbf' size 500m reuse;
alter tablespace TEMP add tempfile '/paic/s2cs5020/stg/cx2/oradata/gs30cs/temp01.dbf' size 500m reuse;
Tablespace altered.
SQL> alter tablespace I3_ORCL_TMP add tempfile '/paic/s2cs5020/stg/cx2/oradata/gs30xa/i3_orcl_tmp01.dbf' size 100m reuse;
alter tablespace I3_ORCL_TMP add tempfile '/paic/s2cs5020/stg/cx2/oradata/gs30cs/i3_orcl_tmp01.dbf' size 100m reuse;
9.生成spfile，並重啟資料庫保證引數調整生效。
SQL> Shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;
File created.
SQL> exit
10.配置監聽和連線串
s2cs5020:10g:gs30xa > cd $ORACLE_HOME/network/admin/
s2cs5020:10g:gs30xa > ls
listener.ora listener.ora.bak samples shrept.lst tnsnames.ora
s2cs5020:10g:gs30xa > vi listener.ora
增加監聽（說明：埠隨意指定，只要沒有被佔用就行）：
gs30xa =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.31.9.62)(PORT = 1558))
)
)
)
SID_LIST_gs30xa =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = gs30xa.world)
(ORACLE_HOME = /paic/stg/oracle/10g/app/oracle/product/10.2.0)
(SID_NAME = gs30xa)
)
)
配置tnsname.ora，增加連線串：
GS30XA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.31.9.62)(PORT = 1558))
(connect_data =
(sid = GS30XA)
)
)
啟動監聽：
s2cs5020:10g:gs30xa > lsnrctl start gs30xa(gs30xa替換成新建庫的sid)
Service "gs30xa.world" has 1 instance(s).
Instance "gs30xa", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
啟動成功
11.刪除連線導生產的db_link
cd $ORACLE_BASE/admin/gs30xa0/udump
sqlplus / as sysdba
SQL> startup mount
SQL> alter database open;
SQL> set heading off
SQL> set feedback off
SQL> select distinct 'alter user '||owner||' profile default identified by Paic1234 account unlock;' from dba_db_links;
執行產生的sql，修改使用者密碼
SQL> spool $sid
SQL> SELECT 'CONN DATABASE LINK '||DB_LINK||';' FROM DBA_DB_LINKS;注意要改語句中的sid！
SQL> spool off
SQL> host
s2cs5020:10g:gs30xa >vi xa.sql---檢查一下指令碼內容
s2cs5020:10g:gs30xa > exit
SQL> @xa.sql
12.初始化所有使用者密碼
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11411056/viewspace-734388/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle DG資料庫切換步驟筆記Oracle資料庫筆記
- oracle dg庫資料檔案空間不足Oracle
- 拋開dbca，手動建庫步驟
- oracle資料庫使用者建立步驟Oracle資料庫
- 資料庫查詢步驟資料庫
- 複製資料庫步驟資料庫
- Oracle資料庫檔案遷移步驟Oracle資料庫
- DG備庫手動管理 新增資料檔案
- 開發和測試需要哪些步驟？
- 生產資料庫、開發資料庫、測試資料庫中的資料的區分資料庫
- Oracle DG備庫手動管理新增資料檔案Oracle
- Oracle資料庫啟動步驟Oracle資料庫
- JDBC連線資料庫步驟JDBC資料庫
- duplicate資料庫的操作步驟資料庫
- 資料庫優化的步驟資料庫優化
- 資料庫設計方法步驟資料庫
- 手動建立資料庫步驟資料庫
- java開發中JDBC連線資料庫程式碼和步驟JavaJDBC資料庫
- 關於資料庫丟失控制檔案的測試資料庫
- oracle10 data guard(dg)__主庫添刪表空間及資料檔案相關測試Oracle
- 修改歸檔模式的通用步驟(非RAC 資料庫)模式資料庫
- MySQL資料庫資料檔案路徑遷移步驟MySql資料庫
- MySQL資料庫安裝步驟-WindowsMySql資料庫Windows
- PHP連線資料庫的步驟PHP資料庫
- 資料庫設計的基本步驟資料庫
- 手工建立資料庫的完整步驟資料庫
- JDBC訪問資料庫的步驟JDBC資料庫
- informix資料庫擴容操作步驟ORM資料庫
- 資料庫建模步驟(power designer)資料庫
- 完整java開發中JDBC連線資料庫程式碼和步驟JavaJDBC資料庫
- Oracle 資料檔案移動步驟Oracle
- RAC資料庫啟用歸檔和閃回的步驟資料庫
- 建立資料庫檔案-日誌檔案-次要資料庫檔案資料庫
- 測試驅動開發（TDD）—— 資料庫查詢篇資料庫
- 軟體開發中的資料庫測試技術資料庫
- 資料庫測試指南資料庫
- python連線mysql資料庫步驟PythonMySql資料庫
- 【JDBC的實現步驟……MySQL資料庫】JDBCMySql資料庫