主:192.168.9.137 sid:orcl db_unique_name=pri
备:192.168.9.138 sid:orcl db_unique_name=sty
1.都关闭防火墙(主、备,root):
[root@testA ~]#
service iptables stop;chkconfig iptables off
[root@testA ~]#
service ip6tables stop;chkconfig ip6tables off
2.修改为归档模式(主,oracle)
SQL>
shutdown immediate
SQL>
startup mount
SQL>
archive log list;
3.修改为强制记录模式(主,oracle)
SQL> alter database force logging;
4.
配置standby logfile文件,当主库变成备库时,可以接收来自备库的日志
SQL> alter database add standby logfile group 4('/home/oracle/app/oracle/oradata/orcl/redo_dg_021.log') size 50m;
SQL> alter database add standby logfile group 5('/home/oracle/app/oracle/oradata/orcl/redo_dg_022.log') size 50m;
SQL> alter database add standby logfile group 6('/home/oracle/app/oracle/oradata/orcl/redo_dg_023.log') size 50m;
SQL> alter database add standby logfile group 7('/home/oracle/app/oracle/oradata/orcl/redo_dg_024.log') size 50m;
5.配置参数文件
(主,oracle)
SQL> create pfile from spfile;
[oracle@primary ~]$ cd $ORACLE_HOME/dbs
[oracle@primary dbs]$ vim initorcl.ora
#primary
#*.db_name='orcl'
*.db_unique_name=pri
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,sty)'
#*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=sty ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.remote_login_passwordfile='EXCLUSIVE'
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#standby role
*.FAL_SERVER=sty
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.STANDBY_FILE_MANAGEMENT=AUTO
#primary
#*.db_name='orcl'
*.db_unique_name=pri
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,sty)'
#*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=sty ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.remote_login_passwordfile='EXCLUSIVE'
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#standby role
*.FAL_SERVER=sty
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.STANDBY_FILE_MANAGEMENT=AUTO
其中路径部分可以根据自己机器中的路径修改
SQL> shutdown immediate
SQL> create spfile from pfile;
6.
主库上配置listener.ora 和tnsnames.ora(主,oracle)
[oracle@primary admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.137)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
主库上的tnsnames.ora:
[oracle@primary admin]$ cat tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.PRI =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.137)(PORT = 1521)))(CONNECT_DATA =(SID = orcl)))STY =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.138)(PORT = 1521)))(CONNECT_DATA =(SID = orcl)))
修改完成之后要重启监听。
7.备份数据库和控制文件(主,oracle)
[oracle@primary admin]$ rman target sys/asd@pri
RMAN> backup database format '/u01/app/oracle/backup/full_db_%U';
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/backup/sty.ctl';
8.
将主库上的参数文件、密码文件拷贝到备库上;
复制的方法就不演示了,多种多样。
9.(备,oracle)修改参数文件:
vim /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora*.db_name='orcl'*.DB_UNIQUE_NAME=sty*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'*.LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty'*.LOG_ARCHIVE_DEST_2='SERVICE=pri ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri'*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE*.STANDBY_FILE_MANAGEMENT=AUTO*.FAL_SERVER=pri
标红的是修改的部分
密码文件说明:主库上有授权或者收回sysdba时,要更新备库上的密码文件。
10.(备,oracle)将主库上的listener.ora 和tnsnames.ora复制到备库上,修改listener.ora文件
[oracle@standby ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/[oracle@standby admin]$ vim listener.ora将host中的201 改成202,也就是将host改成备库机器上的IP,tnsnames.ora不用修改。修改完成之后要重启监听
11.将备份复制到备库上;
12.在备库上创建对应的文件夹
[oracle@standby oracle]$ cd $ORACLE_BASE[oracle@standby oracle]$ lsarch backup checkpoints database product[oracle@standby oracle]$ mkdir -p oradata/orcl[oracle@standby oracle]$ mkdir -p admin/orcl/adump[oracle@standby oracle]$ mkdir -p admin/orcl/dpdump[oracle@standby oracle]$ mkdir -p admin/orcl/pfile
13.启动备库到nomount模式
SQL>create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora';SQL> startup nomount;
14.
在备库上使用RMAN恢复数据库
方法一:
rman>run {
allocate channel ch1 device type disk;allocate channel ch2 device type disk;allocate channel ch3 device type disk;allocate auxiliary channel ch4 device type disk;allocate auxiliary channel ch5 device type disk;allocate auxiliary channel ch6 device type disk;duplicate target database for standby nofilenamecheck from active database;release channel ch1;release channel ch2;release channel ch3;release channel ch4;release channel ch5;release channel ch6;}
方法二:
rman> connect target sys@softstd
rman> set DBID 402771454rman> restore controlfile from '/oradata/backup/full_SOFTDB_863944285_11.bak';rman> alter database mount;rman> restore database;rman> recover database;方法二恢复db可能会报错(SCN相关),可以不理会
如果用方法一,则直接启动standby db为恢复模式。
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
如果用方法二,则恢复后,需要用上面之前备份的standby controlfile将db启动至mount状态;
恢复db后,此时应该是mount状态SQL>SHUTDOWN IMMEDIATESQL>STARTUP NOMOUNT;rman> restore controlfile from '/u01/app/oracle/backup/sty.ctl';
然后再启动standby db为恢复模式SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
15.确保standby库正常接收redolog和Applying
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------------- ------------------- ------------------
184 2014-11-17 22:00:31 2014-11-18 00:12:53 YES
185 2014-11-18 00:12:53 2014-11-18 06:55:59 YES
186 2014-11-18 06:55:59 2014-11-18 08:30:56 YES
187 2014-11-18 08:30:56 2014-11-18 08:31:27 YES
188 2014-11-18 08:31:27 2014-11-18 13:58:10 YES
16.在主库执行日志切换,看是否应用到standby db
SQL>ALTER SYSTEM SWITCH LOGFILE;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------------- ------------------- ------------------
184 2014-11-17 22:00:31 2014-11-18 00:12:53 YES
185 2014-11-18 00:12:53 2014-11-18 06:55:59 YES
186 2014-11-18 06:55:59 2014-11-18 08:30:56 YES
187 2014-11-18 08:30:56 2014-11-18 08:31:27 YES
188 2014-11-18 08:31:27 2014-11-18 13:58:10 YES
189 2014-11-18 13:58:10 2014-11-18 14:08:33 IN-MEMORY
主:192.168.9.137 sid:orcl db_unique_name=pri
备:192.168.9.138 sid:orcl db_unique_name=sty
1.都关闭防火墙(主、备,root):
[root@testA ~]#
service iptables stop;chkconfig iptables off
[root@testA ~]#
service ip6tables stop;chkconfig ip6tables off
2.修改为归档模式(主,oracle)
SQL>
shutdown immediate
SQL>
startup mount
SQL>
archive log list;
3.修改为强制记录模式(主,oracle)
SQL> alter database force logging;
4.
配置standby logfile文件,当主库变成备库时,可以接收来自备库的日志
SQL> alter database add standby logfile group 4('/home/oracle/app/oracle/oradata/orcl/redo_dg_021.log') size 50m;
SQL> alter database add standby logfile group 5('/home/oracle/app/oracle/oradata/orcl/redo_dg_022.log') size 50m;
SQL> alter database add standby logfile group 6('/home/oracle/app/oracle/oradata/orcl/redo_dg_023.log') size 50m;
SQL> alter database add standby logfile group 7('/home/oracle/app/oracle/oradata/orcl/redo_dg_024.log') size 50m;
5.配置参数文件
(主,oracle)
SQL> create pfile from spfile;
[oracle@primary ~]$ cd $ORACLE_HOME/dbs
[oracle@primary dbs]$ vim initorcl.ora
#primary
#*.db_name='orcl'
*.db_unique_name=pri
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,sty)'
#*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=sty ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.remote_login_passwordfile='EXCLUSIVE'
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#standby role
*.FAL_SERVER=sty
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.STANDBY_FILE_MANAGEMENT=AUTO
#primary
#*.db_name='orcl'
*.db_unique_name=pri
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,sty)'
#*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=sty ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.remote_login_passwordfile='EXCLUSIVE'
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#standby role
*.FAL_SERVER=sty
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.STANDBY_FILE_MANAGEMENT=AUTO
其中路径部分可以根据自己机器中的路径修改
SQL> shutdown immediate
SQL> create spfile from pfile;
6.
主库上配置listener.ora 和tnsnames.ora(主,oracle)
[oracle@primary admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.137)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
主库上的tnsnames.ora:
[oracle@primary admin]$ cat tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.PRI =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.137)(PORT = 1521)))(CONNECT_DATA =(SID = orcl)))STY =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.138)(PORT = 1521)))(CONNECT_DATA =(SID = orcl)))
修改完成之后要重启监听。
7.备份数据库和控制文件(主,oracle)
[oracle@primary admin]$ rman target sys/asd@pri
RMAN> backup database format '/u01/app/oracle/backup/full_db_%U';
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/backup/sty.ctl';
8.
将主库上的参数文件、密码文件拷贝到备库上;
复制的方法就不演示了,多种多样。
9.(备,oracle)修改参数文件:
vim /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora*.db_name='orcl'*.DB_UNIQUE_NAME=sty*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'*.LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty'*.LOG_ARCHIVE_DEST_2='SERVICE=pri ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri'*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE*.STANDBY_FILE_MANAGEMENT=AUTO*.FAL_SERVER=pri
标红的是修改的部分
密码文件说明:主库上有授权或者收回sysdba时,要更新备库上的密码文件。
10.(备,oracle)将主库上的listener.ora 和tnsnames.ora复制到备库上,修改listener.ora文件
[oracle@standby ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/[oracle@standby admin]$ vim listener.ora将host中的201 改成202,也就是将host改成备库机器上的IP,tnsnames.ora不用修改。修改完成之后要重启监听
11.将备份复制到备库上;
12.在备库上创建对应的文件夹
[oracle@standby oracle]$ cd $ORACLE_BASE[oracle@standby oracle]$ lsarch backup checkpoints database product[oracle@standby oracle]$ mkdir -p oradata/orcl[oracle@standby oracle]$ mkdir -p admin/orcl/adump[oracle@standby oracle]$ mkdir -p admin/orcl/dpdump[oracle@standby oracle]$ mkdir -p admin/orcl/pfile
13.启动备库到nomount模式
SQL>create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora';SQL> startup nomount;
14.
在备库上使用RMAN恢复数据库
方法一:
rman>run {
allocate channel ch1 device type disk;allocate channel ch2 device type disk;allocate channel ch3 device type disk;allocate auxiliary channel ch4 device type disk;allocate auxiliary channel ch5 device type disk;allocate auxiliary channel ch6 device type disk;duplicate target database for standby nofilenamecheck from active database;release channel ch1;release channel ch2;release channel ch3;release channel ch4;release channel ch5;release channel ch6;}
方法二:
rman> connect target sys@softstd
rman> set DBID 402771454rman> restore controlfile from '/oradata/backup/full_SOFTDB_863944285_11.bak';rman> alter database mount;rman> restore database;rman> recover database;方法二恢复db可能会报错(SCN相关),可以不理会
如果用方法一,则直接启动standby db为恢复模式。
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
如果用方法二,则恢复后,需要用上面之前备份的standby controlfile将db启动至mount状态;
恢复db后,此时应该是mount状态SQL>SHUTDOWN IMMEDIATESQL>STARTUP NOMOUNT;rman> restore controlfile from '/u01/app/oracle/backup/sty.ctl';
然后再启动standby db为恢复模式SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
15.确保standby库正常接收redolog和Applying
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------------- ------------------- ------------------
184 2014-11-17 22:00:31 2014-11-18 00:12:53 YES
185 2014-11-18 00:12:53 2014-11-18 06:55:59 YES
186 2014-11-18 06:55:59 2014-11-18 08:30:56 YES
187 2014-11-18 08:30:56 2014-11-18 08:31:27 YES
188 2014-11-18 08:31:27 2014-11-18 13:58:10 YES
16.在主库执行日志切换,看是否应用到standby db
SQL>ALTER SYSTEM SWITCH LOGFILE;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------------- ------------------- ------------------
184 2014-11-17 22:00:31 2014-11-18 00:12:53 YES
185 2014-11-18 00:12:53 2014-11-18 06:55:59 YES
186 2014-11-18 06:55:59 2014-11-18 08:30:56 YES
187 2014-11-18 08:30:56 2014-11-18 08:31:27 YES
188 2014-11-18 08:31:27 2014-11-18 13:58:10 YES
189 2014-11-18 13:58:10 2014-11-18 14:08:33 IN-MEMORY