Oracle DataGuard建库

本项目安装一台primary，一台standby，所以要准备两台机器，命名为DG1、DG2 1、安装数据库软件

此处安装的是oracle10.2.0.1，安装过程我们基本上已经熟练了解，在此不细说。记住只安装数据库软件哦（这样可以简化后面的升级过程）

2、如果直接用10.2.0.1做dataguard，可能会出现一些bug，为了避免这些bug，我们要将数据库软件升级到10.2.0.5

3、建库：在DG1上用DBCA建库，DG2上面暂不建库 4、primary端设置

4.1、主库设置为force logging模式 1. SQL > alter database force logging; 2. create a Password file

[oracle@localhost dbs]$ orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl password=oracle 4.2、主库设置为归档模式 SQL > shutdown immediate; SQL > startup mount;

SQL > alter database archivelog; SQL > archive log list; 4.3、添加redo log file

1）添加新的standby redolog组，并为该组指定成员。

SQL > alter database add standby logfile group 4 （‘/u01/app/oracle/oradata/oradg/redo04.log’）size 50M; SQL > alter database add standby logfile group 5 （‘/u01/app/oracle/oradata/oradg/redo05.log’） size 50M; SQL > alter database add standby logfile group 6 （‘/u01/app/oracle/oradata/oradg/redo06.log’） size 50M; SQL > alter database add standby logfile group 7 （‘/u01/app/oracle/oradata/oradg/redo07.log’） size 50M; 2）确定standby redo log file groups已经创建：

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

4.4 、初始化参数 DB_NAME=testdb

DB_UNIQUE_NAME=testdb

LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb,testdbst)' LOG_ARCHIVE_DEST_1=

'LOCATION=/u01/oradata/arch/testdb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb’ LOG_ARCHIVE_DEST_2= 'SERVICE=boston LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbst’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=30 FAL_SERVER=testdbst FAL_CLIENT=testdb

STANDBY_FILE_MANAGEMENT=AUTO 二、创建物理备库步骤 1、创建主库数据文件的备份 使用rman备份主库

在物理备库使用rman执行恢复

第一次restore后，由于备库需要resetlogs，所以，需要再做一次restore恢复操作，保证和主库同步 2、创建standby database 的控制文件 SQL> startup mount

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/testdb.ctl'; SQL> alter database open;

3 、修改standby database的初始化参数文件 DB_NAME=testdb

DB_UNIQUE_NAME=testdbst

LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb,testdbst)' LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc LOG_ARCHIVE_DEST_1=

'LOCATION=/u01/oradata/arch/testdb/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdbst' LOG_ARCHIVE_DEST_2= 'SERVICE=chicago LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb'

LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE STANDBY_FILE_MANAGEMENT=AUTO FAL_SERVER=testdb FAL_CLIENT=testdbst 4、建立环境变量支持 5、 配置监听

SID_LIST_LISTENER = (SID_LIST = (SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc) )

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = orcl) ) )

LISTENER =

(DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )

6 、配置本地命名方式 Standby Listener.ora LISTENER =

(DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.149.3)(PORT = 1521)) ) )

SID_LIST_LISTENER = (SID_LIST = (SID_DESC =

(ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/db_1) (GLOBAL_DBNAME = testdb) (SID_NAME = testdb) )

(SID_DESC =

(ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/db_1) (GLOBAL_DBNAME = sales) (SID_NAME = sales) ) )

Tnsname.ora testdbst = (DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.149.3)(PORT = 1521)) )

(CONNECT_DATA = (SERVICE_NAME = testdb) ) ) testdb =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.149.4)(PORT = 1521)) )

(CONNECT_DATA = (SERVICE_NAME = testdb) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) )

(CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) Primary Listener.ora LISTENER =

(DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.149.4)(PORT = 1521)) ) )

SID_LIST_LISTENER = (SID_LIST = (SID_DESC =

(ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/db_1) (GLOBAL_DBNAME = testdb) (SID_NAME = testdb) )

(SID_DESC =

(ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/db_1) (GLOBAL_DBNAME = sales) (SID_NAME = sales) ) )

Tnsname.ora testdbst = (DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.149.3)(PORT = 1521)) )

(CONNECT_DATA = (SERVICE_NAME = testdb) ) ) testdb =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.149.4)(PORT = 1521)) )

(CONNECT_DATA = (SERVICE_NAME = testdb) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) )

(CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

7、 启动物理备库

8 、检查物理备库的执行属性 这样大功告成