關於建立DataGuard Physical Standby資料庫

建立DataGuard Physical Standby資料庫

os：solaris 10
db：oracle9.2.0.6

1、開啟資料庫為Forced Logging模式：

SQL>alter database force logging;

強制日誌模式：

用於忽略nologging選項不記錄redo的請求，一般用於dataguard以及安全要求較高的資料庫，要求所有的事務都可以透過歸檔進行回滾。

2、開啟歸檔模式及定義本地歸檔目錄：

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=LOCATION=/oracle/app/archive' SCOPE=BOTH;

3、確定Primary 資料庫資料檔案：

SQL> select * from v$dbfile;

FILE# NAME
----------------------------------------------------------------
10 /oracle/app/oradata/test/xdb01.dbf
9 /oracle/app/oradata/test/users01.dbf
2 /oracle/app/oradata/test/undotbs01.dbf
........

4、複製主庫資料庫：

使用冷備份方式複製資料庫。

4.1、停止primary資料庫：

SQL> SHUTDOWN IMMEDIATE;

4.2、複製primary資料庫：

cp /oracle/app/oradata/test/system01.dbf
/oracle/app/oradata/test/standby/system01.dbf
........

4.3、重啟primary資料庫：
SQL> STARTUP;

5、建立Physical Standby資料庫控制檔案：

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/app/control01.ctl';

然後複製2份control01.ctl控制檔案分別為control02.ctl、control03.ctl。

6、建立Physical Standby資料庫用的pfile：

SQL> CREATE PFILE='/oracle/app/product/9.2.0/dbs/inittestdg.ora' FROM SPFILE;

：
7、從primary資料庫複製備份的檔案至standby 資料庫：

--備份的資料庫檔案（資料檔案、臨時檔案、日誌檔案）放到/oracle/app/oradata/testdg/
--備份的standby控制檔案放到 放到/oracle/app/oradata/testdg/
--備份的pfile初始化引數檔案 放到/oracle/app/product/9.2/dbs/

8、修改Physical Standby資料庫的pfile檔案：

新增如下內容
db_name=test
compatible=9.2.0.0.0
standby_archive_dest='/oracle/app/standby_archive/'
db_file_name_convert=('/oracle/app/oradata/test/','/oracle/app/oradata/testdg/')
log_file_name_convert=('/oracle/app/archive/','/oracle/app/standby_archive/')
log_archive_format=log%d_%t_%s.arc
log_archive_dest_1=('LOCATION=/oracle/app/standby_archive/')
standby_file_management=AUTO
remote_archive_enable=TRUE
instance_name=testdg

# The following parameter is required only if the primary and standby databases
# are located on the same system.
lock_name_space=testdg

9、建立Physical Standby資料庫各個日誌存放目錄及歸檔日誌目錄：

$mkdir /oracle/app/admin/testdg/bdump
$mkdir /oracle/app/admin/testdg/cdump
$mkdir /oracle/app/admin/testdg/udump
$mkdir /oracle/app/standby_archive

10、配置primary和standby資料庫的listener.ora與tnsnames.ora

primary資料庫的listener.ora新增如下：
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /oracle/app/product/9.2)
(SID_NAME = test)
)
(SID_DESC =
(GLOBAL_DBNAME = testdg)
(ORACLE_HOME = /oracle/app/product/9.2)
(SID_NAME = testdg)
)

primary資料庫的tnsnames.ora新增如下：
TESTDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sol2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdg)
)
)

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sol1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

standy資料庫的listener.ora新增如下：
(SID_DESC =

(GLOBAL_DBNAME = testdg)
(ORACLE_HOME = /oracle/app/product/9.2.0)
(SID_NAME = testdg)

)

standy資料庫的tnsnames.ora新增如下：
TESTDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sol2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdg)
)
)
TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sol1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

然後重啟primary、standy資料庫的監聽：
% lsnrctl stop
% lsnrctl start

11、開啟Physical Standby資料庫的Connection Detection模式：
修改SQLNET.ORA引數，新增
SQLNET.EXPIRE_TIME=2

12、建立Physical Standby資料庫的spfile
SQL> CREATE SPFILE FROM PFILE;

13、啟動Physical Standby資料庫：
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

14、開啟日誌應用服務：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
後臺程式服務名為ora_mrp0_testdg

15、啟動Physical Standby資料庫的歸檔：
15.1、設定primary資料庫的LOG_ARCHIVE_DEST_2引數
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=testdg' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

15.2、嘗試開始遠端歸檔：
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

16、驗證Physical Standby資料庫
16.1、在standby資料庫查詢v$archived_log檢視：

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME,name from V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME NAME
--------------------------------------------------------------------------------

5 01-AUG-11 01-AUG-11 /oracle/app/standby_archive/log2055914502_1_5.arc

16.2、在primary資料庫切換歸檔：
SQL> alter system archive log current;
System altered.

16.3、驗證歸檔日誌是否傳送到standby資料庫中：

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME,name from V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME NAME
--------------------------------------------------------------------------------

5 01-AUG-11 01-AUG-11 /oracle/app/standby_archive/log2055914502_1_5.arc
6 01-AUG-11 01-AUG-11 /oracle/app/standby_archive/log2055914502_1_6.arc

17、驗證新的歸檔日誌是否在standby資料庫被應用：

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP
---------- ---
5 YES
6 YES

-THE END-

[@more@]