[轉帖]Oracle9i Standby (Dataguard) 建立

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> alter database force logging;

*.INSTANCE_NAME='primary'
*.DB_NAME='primary'

*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/ora9/oradata/archive_log mandatory'
*.LOG_ARCHIVE_DEST_2='SERVICE=standby OPTIONAL reopen=60 ARCH SYNC NOAFFIRM '--&gt(Maximum Performance 模式)
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%d_%t_%s.arc
*.REMOTE_ARCHIVE_ENABLE=TRUE

*.FAL_SERVER=standby
*.FAL_CLIENT=primary
*. STANDBY_ARCHIVE_DEST=/opt/ora9/oradata/archive_log
*.STANDBY_FILE_MANAGEMENT=AUTO
*._log_archive_callout='LOCAL_FIRST=TRUE'--&gt避免尖峰時間,switch log太快，系統hang住
*.archive_lag_target=1800

$ scp $ORACLE_HOME/dbs/initprimary.ora    oracle@192.168.217.23:$ORACLE_HOME/dbs/initstandby.ora
$ scp $ORACLE_HOME/dbs/orapwprimary    oracle@192.168.217.23:$ORACLE_HOME/dbs/orapwstandby

*.INSTANCE_NAME='standby'
*.DB_NAME='primary' --&gt需要跟primary db相同

*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/ora9/oradata/archive_log mandatory'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary OPTIONAL reopen=60 ARCH SYNC NOAFFIRM '--&gt(Maximum Performance 模式)
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=DEFER --&gt暫時關閉，等Switchover時才開啟。
*.LOG_ARCHIVE_FORMAT=%d_%t_%s.arc
*.REMOTE_ARCHIVE_ENABLE=TRUE

*.FAL_SERVER=primary
*.FAL_CLIENT=standby
*. STANDBY_ARCHIVE_DEST=/opt/ora9/oradata/archive_log
*.STANDBY_FILE_MANAGEMENT=AUTO
*._log_archive_callout='LOCAL_FIRST=TRUE'
*.archive_lag_target=1800

#su - oracle

$ mkdir -p $ORACLE_BASE/backup --&gt用於 Rman備份
$ mkdir -p $ORACLE_BASE/oradata/primary --&gt資料庫目錄
$ mkdir -p $ORACLE_BASE/oradata/archive_log
$ mkdir -p $ORACLE_BASE/oradata/standby_archive_log
$ mkdir -p $ORACLE_BASE/admin/primary/bdump
$ mkdir -p $ORACLE_BASE/admin/primary/cdump
$ mkdir -p $ORACLE_BASE/admin/primary/create
$ mkdir -p $ORACLE_BASE/admin/primary/pfile
$ mkdir -p $ORACLE_BASE/admin/primary/udump

LISTENER =
(DESCRIPTION_LIST =
　(DESCRIPTION =
　　(ADDRESS_LIST =
　　　(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
　　)
　　(ADDRESS_LIST =
　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521))
　　)
　)
)

SID_LIST_LISTENER =
(　　SID_LIST =
　　　(SID_DESC =
　　　　(SID_NAME = PLSExtProc)
　　　　(ORACLE_HOME = /opt/ora9/product/9.2)
　　　　(PROGRAM = extproc)
　　　)
　　　(SID_DESC =
　　　　(GLOBAL_DBNAME = primary)
　　　　(ORACLE_HOME = /opt/ora9/product/9.2)
　　　　(SID_NAME = primary)
　　　)
)

LISTENER =
(DESCRIPTION_LIST =
　(DESCRIPTION =
　　(ADDRESS_LIST =
　　　(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
　　)
　　(ADDRESS_LIST =
　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521))
　　)
　)
)

SID_LIST_LISTENER =
(　　SID_LIST =
　　　(SID_DESC =
　　　　(SID_NAME = PLSExtProc)
　　　　(ORACLE_HOME = /opt/ora9/product/9.2)
　　　　(PROGRAM = extproc)
　　　)
　　　(SID_DESC =
　　　　(GLOBAL_DBNAME = standby)
　　　　(ORACLE_HOME = /opt/ora9/product/9.2)
　　　　(SID_NAME = standby)
　　　)
)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME = 2

MASTER =
(DESCRIPTION =
　 (ADDRESS_LIST =
　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521))
　　)
　(CONNECT_DATA =
　　　　(SERVER = DEDICATED)
　　　　(SERVICE_NAME = master)
　)
)

STANDBY =
(DESCRIPTION =
　(ADDRESS_LIST =
　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521))
　)
　(CONNECT_DATA =
　　(SERVER = DEDICATED)
　　(SERVICE_NAME = standby)
　)
)

MASTER =
(DESCRIPTION =
　 (ADDRESS_LIST =
　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521))
　　)
　(CONNECT_DATA =
　　　　(SERVER = DEDICATED)
　　　　(SERVICE_NAME = master)
　)
)

STANDBY =
(DESCRIPTION =
　(ADDRESS_LIST =
　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521))
　)
　(CONNECT_DATA =
　　(SERVER = DEDICATED)
　　(SERVICE_NAME = standby)
　)
)

#su - oracle
$ lsnrctl stop
$ lsnrctl start
$ lsnrctl status

[oracle@standby dbs]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 4月 1 23:41:33 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 235999648 bytes
Fixed Size 450976 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
SQL> exit

[oracle@primary ora9]$ rman target / nocatalog

Recovery Manager: Release 9.2.0.8.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: MASTER (DBID=2710930473)
using target database controlfile instead of recovery catalog

RMAN> backup filesperset 5 database format '/opt/ora9/backup/%d_t%t_s%s_p%p' include current controlfile for standby;

Starting backup at 02-4月 -08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=18 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00013 name=/opt/ora9/oradata/primary/DBS3.dbf
input datafile fno=00006 name=/opt/ora9/oradata/primary/indx01.dbf
input datafile fno=00009 name=/opt/ora9/oradata/primary/users01.dbf
input datafile fno=00004 name=/opt/ora9/oradata/primary/drsys01.dbf
input datafile fno=00008 name=/opt/ora9/oradata/primary/tools01.dbf
...... including current SPFILE in backupset
including standby controlfile in backupset
......

RMAN> sql 'alter system archive log current';

sql statement: alter system archive log current

RMAN> backup archivelog all ;

Starting backup at 02-4月 -08
current log archived
using channel ORA_DISK_1
.....

$ scp $ORACLE_BASE/backup/* oracle@192.168.217.23:/$ORACLE_BASE/backup

RMAN> connect auxiliary sys@standby;

auxiliary database Password:
connected to auxiliary database: master (not mounted)

RMAN> duplicate target database for standby nofilenamecheck dorecover;

Starting Duplicate Db at 02-4月 -08
using channel ORA_AUX_DISK_1

printing stored script. Memory Script
{
restore clone standby controlfile to clone_cf; --&gt恢復Standby control file
replicate clone controlfile from clone_cf;
sql clone 'alter database mount standby database'; --&gt原本Standby 開啟於nomount模式'，Rman將其轉變成mount 模式
}
executing script. Memory Script

Starting restore at 02-4月 -08

using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring controlfile
output filename=/opt/ora9/oradata/master/control_sdby01.ctl
channel ORA_AUX_DISK_1: restored backup piece 1
.....

[oracle@baksrv dbs]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on 星期三 4月 2 11:41:05 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile; --&gt做出新的spfile

File created.

SQL> startup;
ORACLE instance started.

Total System Global Area 235999648 bytes
Fixed Size 450976 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;--&gtswitch log一次
System altered.
SQL> select * from v$log;　--&gt目前的log sequence#　為148,最新的archived log為147

GROUP# 　THREAD# 　SEQUENCE#　 BYTES 　MEMBERS　 ARC　 STATUS 　FIRST_CHANGE# 　　FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1　　　　 1 　　　　　146　　　 　　104857600 　1 　　　YES 　　INACTIVE　 2178807 02-4月 -08
2 　　　　1 　　　　　147 　　　　　104857600 　1 　　　YES 　　ACTIVE 　　2178808 02-4月 -08
3 　　　　1 　　　　　148 　　　　　104857600 　1 　　　NO 　　CURRENT 　2179548 02-4月 -08
8 　　　　1 　　　　　145 　　　　　104857600 　1 　　　YES 　　INACTIVE　 2176247 02-4月 -08

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
146 NO
145 NO
147 NO --&gt已經接收到147...Ｄata Guard已經大致上完成了^_^

SQL> alter database recover managed standby database disconnect from session; --&gt開始做自動 standby recover

Database altered.

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
146 YES
145 YES
147 YES --&gt原本NO的狀態，已經變成YES

SQL> alter database recover managed standby database cancel;

SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo01.log' size 100M;
SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo02.log' size 100M;
SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo03.log' size 100M;

SQL> alter database recover managed standby database disconnect from session;

SQL> select process,status,client_process,sequence#,block# from v$managed_standby;

SQL> select * from v$standby_log; --&gt檢查Standby　Redo log是否啟用。目前log sequence為150

GROUP#　　 THREAD# 　　　SEQUENCE#　　 BYTES 　USED　 ARC 　STATUS FIRST_CHANGE# 　FIRST_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------- ------------ ----------
4 　　　1 　　　150 　　　　104857600　　 0 　　　　　YES 　　　ACTIVE　　　　 2180477 02-4月 -08 0
5　　　 0　　　 0 　　　　　104857600 　　1024 　　　　YES　　　 UNASSIGNED　 0 0
6　　　 0　　　 0 　　　　　104857600　　 1024　　　　 YES 　　　UNASSIGNED　 0 0

SQL>