1.环境
主库环境:
系统:Solaris11_AMD64
ORA:12cR1
IP:188.188.1.176
SID:TESTM
备库环境:
系统:CentOS6.4_AMD64
ORA:12cR1
IP:188.188.3.20
SID:TESTM
2.主库配置
启用强制归档和查看归档状态
SQL> alter database force logging;
数据库已更改。
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用 --归档启用了,此处不再多述归档
存档终点 /oracle/archive_log --归档路径
最早的联机日志序列 124
下一个存档日志序列 126
当前日志序列 126
3.创建备库日志组
查看主库现有的日志组:
SQL> select GROUP#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,status from v$log;
GROUP# SEQUENCE# MEMBERS BYTES ARCHIVED STATUS
---------- ---------- ---------- ---------- --------- ------------------------------------------------
1 124 1 52428800 YES INACTIVE
2 125 1 52428800 YES INACTIVE
3 126 1 52428800 NO CURRENT
日志组别和现有的重复
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/oracle/dataguard/redo01_4.log','/oracle/dataguard/redo02_4.log') size 50M;
数据库已更改。
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oracle/dataguard/redo01_5.log','/oracle/dataguard/redo02_5.log') size 50M;
数据库已更改。
4.配置Oracle Net
主库listener.ora配置如下
oracle@Sol_ORA:~$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /oracle/12.1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =(SID_LIST=(SID_DESC=(SID_NAME=TESTM)(ORACLE_HOME=/oracle/12.1))) --添加这一条,静态注册监听
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.1.176)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
主库tnsnames.ora配置如下
oracle@Sol_ORA:~$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/12.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST_M =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.1.176)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTM)
)
)
TEST_S = //加上备库的链接字符串
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.3.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTM)
)
)
LISTENER_TESTM =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.1.176)(PORT = 1521))
备库的listener.ora配置如下
[oracle@ora_slave admin]$ cat $ORACLE_HOME/network/admin/listener.ora
#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) )
# )
SID_LIST_LISTENER =(SID_LIST=(SID_DESC=(SID_NAME=TESTM)(ORACLE_HOME=/oracle/product/12.1.0)))
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.3.20)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
备库tnsnames.ora配置如下
[oracle@ora_slave admin]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/12.1.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST_M =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.1.176)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTM)
)
)
TEST_S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.3.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTM)
)
)
LISTENER_TESTM =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.3.20)(PORT = 1521))
5.创建密码文件和控制文件(在主库)
SQL> alter database create standby controlfile as '/oracle/dataguard/ctlfile/testM.ctl'; --创建备库的控制文件 由这个控制文件区分主备关系
数据库已更改。
SQL> !
oracle@Sol_ORA:~$ ls $ORACLE_HOME/dbs/orapw*
/oracle/12.1/dbs/orapwDBUA3028740 /oracle/12.1/dbs/orapwTEST --存在就不用创建了,如果不存在需要创建之
创建命令:
orapwd file=$OEACLE_HOME/dbs/orapw${ORACLE_SID} password=admin
6.创建和修改初始化参数文件
创建主库初始化参数文件
修改SQL> create pfile='/oracle/dataguard/pfile/testm.ora' from spfile; 文件已创建。
SQL> hosts cp /oracle/dataguard/pfile/testm.ora /oracle/dataguard/pfile/tests.ora
oracle@Sol_ORA:/tmp$ cat /oracle/dataguard/pfile/testm.ora
###########################下边新加内容#############################
DB_NAME ='TESTM'; //主库数据库名
DB_UNIQUE_NAME='TEST_M' //主库唯一标识
LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST_M,TEST_S)'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/archive_log VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TEST_M'
LOG_ARCHIVE_DEST_2='service=TEST_S DB_UNIQUE_NAME=TEST_S'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
############################上边新加内容#############################
TESTM.__data_transfer_cache_size=0
TESTM.__db_cache_size=1778384896
TESTM.__java_pool_size=33554432
TESTM.__large_pool_size=83886080
TESTM.__oracle_base='/oracle'#ORACLE_BASE set from environment
TESTM.__pga_aggregate_target=855638016
TESTM.__sga_target=2550136832
TESTM.__shared_io_pool_size=134217728
TESTM.__shared_pool_size=486539264
TESTM.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/TESTM/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/oracle/oradata/TESTM/control01.ctl','/oracle/fast_recovery_area/TESTM/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='TESTM'
*.db_recovery_file_dest='/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4815m
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTMXDB)'
*.local_listener='LISTENER_TESTM'
*.log_archive_dest_1='location=/oracle/archive_log'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=810m
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_target=2430m
*.undo_tablespace='UNDOTBS1'
7.用修改完的初始化参数启动主库实例并创建spfile
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile='/oracle/dataguard/pfile/testm.ora'
ORACLE 例程已经启动。
Total System Global Area 2538741760 bytes
Fixed Size 2757504 bytes
Variable Size 1124076672 bytes
Database Buffers 1392508928 bytes
Redo Buffers 19398656 bytes
数据库装载完毕。
数据库已经打开。
SQL> create spfile from pfile='/oracle/dataguard/pfile/testm.ora';
文件已创建。
8.配置备库初始化文件
配置备库pfile
[oracle@ora_slave pfile]$ more tests.ora
#############################如下添加###################################
DB_NAME=TESTS
DB_UNIQUE_NAME=TEST_S
LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST_M,TEST_S)'
CONTROL_FILES='/oracle/oradata/TESTM/control01.ctl', '/oracle/oradata/TESTM/control02.ctl'
DB_FILE_NAME_CONVERT='TESTM','TESTS'
LOG_FILE_NAME_CONVERT='/oracle/archive_log/','/oracle/dataguard/archivelog/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TEST_S'
LOG_ARCHIVE_DEST_2='SERVICE=TEST_M ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_M'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=TEST_M
###############################如上添加##################################
TESTS.__data_transfer_cache_size=0
TESTS.__db_cache_size=218103808
TESTS.__java_pool_size=4194304
TESTS.__large_pool_size=8388608
TESTS.__oracle_base='/oracle'#ORACLE_BASE set from environment
TESTS.__pga_aggregate_target=260046848
TESTS.__sga_target=494927872
TESTS.__shared_io_pool_size=12582912
TESTS.__shared_pool_size=243269632
TESTS.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/TESTS/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
#*.control_files='/oracle/oradata/TESTS/control01.ctl','/oracle/fast_recovery_area/TESTS/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='TESTS'
*.db_recovery_file_dest='/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4815m
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTSXDB)'
*.memory_target=720m
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=450
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=500
*.undo_tablespace='UNDOTBS1'
9.准备和拷贝备库的数据文件和控制文件
因为我们要用主库的控制文件,控制文件中的数据文件的位置是一定的,所以我们需要在备库中创建主库的文件路径,并吧数据拷贝过去
1.shutdown主库,拷贝数据文件,down了拷贝就不许要recover介质了
主库 在备库创建相应的数据目录 [oracle@ora_slave dataguard]$ mkdir /oracle/oradata/TESTM 在主库执行如下命令,拷贝控制文件和数据文件包括归档 如果没有目录直接创建之 oracle@Sol_ORA:/tmp$ cd /oracle/oradata/TESTM/ oracle@Sol_ORA:/oracle/oradata/TESTM$ ls control01.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf oracle@Sol_ORA:/oracle/oradata/TESTM$ scp * oracle@188.188.3.20:/oracle/oradata/TESTM oracle@188.188.3.20's password: control01.ctl 100% |***********************************************************************************| 9808 KB 00:00 redo01.log 100% |***********************************************************************************| 51200 KB 00:02 redo02.log 100% |***********************************************************************************| 51200 KB 00:01 redo03.log 100% |***********************************************************************************| 51200 KB 00:02 sysaux01.dbf 100% |***********************************************************************************| 880 MB 00:51 system01.dbf 100% |***********************************************************************************| 780 MB 00:41 temp01.dbf 100% |***********************************************************************************| 68616 KB 00:03 undotbs01.dbf 100% |***********************************************************************************| 1070 MB 00:53 users01.dbf 100% |***********************************************************************************| 5128 KB 00:00 oracle@Sol_ORA:/oracle/oradata/TESTM$ scp $ORACLE_HOME/dbs/orapwTESTM oracle@188.188.3.20:/oracle/product/12.1.0/dbs/ oracle@188.188.3.20's password: orapwTESTM 100% |***********************************************************************************| 7680 00:00
oracle@Sol_ORA:/oracle/oradata/TESTM$ scp /oracle/dataguard/ctlfile/testM.ctl oracle@188.188.3.20:/oracle/dataguard/ctlfile/ oracle@188.188.3.20's password: testM.crl 100% |***********************************************************************************| 7680 00:00
在备库
参考参数文件中的路径和文件必须存在且放到所位置上,如果没有的目录创建之。
按参数文件中的控制文件指向将/oracle/dataguard/ctlfile/testM.ctl分别拷贝到指定的位置改名为指定的名称
2.在备库启动实例
[oracle@ora_slave pfile]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on 星期二 10月 29 22:49:38 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup nomount pfile='/oracle/dataguard/pfile/oraTESTS.ora'
ORACLE 例程已经启动。
Total System Global Area 751595520 bytes
Fixed Size 2292912 bytes
Variable Size 595592016 bytes
Database Buffers 150994944 bytes
Redo Buffers 2715648 bytes
SQL> alter database mount standby database ;
数据库已更改。
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
10.重新启动主库
主库启动
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 751595520 bytes
Fixed Size 2292912 bytes
Variable Size 595592016 bytes
Database Buffers 150994944 bytes
Redo Buffers 2715648 bytes
数据库装载完毕。
数据库已经打开。
SQL> SELECT STATUS, ERROR FROM V$ARCHIVE_DEST;
STATUS ERROR --这个错误列不出错正常 如果出错解决相应的错误即可
--------------------------- -----------------------------------------------------------------
VALID
VALID
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
已选择 31 行。
备库查看状态
SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
CLIENT_PROCESS PROCESS THREAD# SEQUENCE# STATUS
------------------------ --------------------------- ---------- ---------- ------------------------------------
N/A MRP0 1 18 WAIT_FOR_LOG --正在等待主库的log 如果是应用日志此处应该是APPLYING_LOG
LGWR RFS 1 18 IDLE
SQL> SELECT STATUS, ERROR FROM V$ARCHIVE_DEST;
STATUS ERROR --此处的状态和主库一样如果有错误则解决之
--------------------------- -----------------------------------------------------------------
VALID
VALID
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
VALID
已选择 32 行。
11.验证
主库
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
25
备库
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
25
主库
SQL> alter system switch logfile;
系统已更改。
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
26
备库
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
26
就说明归档日志已经同步了!
12.错误汇总
SQL> SELECT STATUS, ERROR FROM V$ARCHIVE_DEST; --主库查询
STATUS ERROR
--------------------------- -----------------------------------------------------------------
VALID
ERROR ORA-01033: ORACLE 正在初始化或关闭 --没有启动备库实例
SQL> SELECT STATUS, ERROR FROM V$ARCHIVE_DEST; --主库查询
STATUS ERROR
--------------------------- -----------------------------------------------------------------
VALID
ERROR ORA-16191: 主日志传送客户机没有登录到备用数据库 --备库的密码文件必须是备库的orapw${ORACLE_SID}命名
################
本文由笔者原创
作者:john
转载请注明出处