关于创建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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7417660/viewspace-1053294/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7417660/viewspace-1053294/