1.配置主库环境
启用归档
SQL> alter system set log_archive_dest_1='location=/oracle/archive_log/arch_%t_%s_%r.arc' scope=spfile;
系统已更改。
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='arch_%t_%s_%r.arc' scope=spfile;
系统已更改。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 2538741760 bytes
Fixed Size 2757504 bytes
Variable Size 738200704 bytes
Database Buffers 1778384896 bytes
Redo Buffers 19398656 bytes
数据库装载完毕。
SQL> alter database archivelog;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 /oracle/archive_log
最早的联机日志序列 10
下一个存档日志序列 12
当前日志序列 12
SQL> alter database force logging;
数据库已更改。
SQL> select force_logging from v$database;
FORCE_LOGGING
--------------------------------------------------------------------------------
YES
2.配置Oracle Net
主Oracle Net
SQL> !
oracle@Sol_ORA:~$ cd $ORACLE_HOME/network/admin
oracle@Sol_ORA:/oracle/12.1/network/admin$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/12.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TESTM = #主库的连接字符串
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.1.176)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTM)
)
)
LISTENER_TESTM =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.1.176)(PORT = 1521))
TESTS = #备库的链接字符串
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.3.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTM)
)
)
oracle@Sol_ORA:/oracle/12.1/network/admin$ cat 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))
)
)
备库的Oracle Net
[oracle@ora_slave admin]$ pwd
/oracle/product/12.1.0/network/admin
[oracle@ora_slave admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/12.1.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TESTS = #备库的连接字符串
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.3.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTM)
)
)
TESTM = #主库的连接字符串
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.1.176)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTM)
)
)
LISTENER_TESTM =
(ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.3.20)(PORT = 1521))
[oracle@ora_slave admin]$ cat 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))
)
)
注意:如果修改listener.ora 记得务必将lsnrctl reload一下
3.修改主库的pfile
oracle@Sol_ORA:~$ 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=TESTS reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=TEST_S'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='TESTS'
*.fal_client='TESTM'
TESTM.__data_transfer_cache_size=0
TESTM.__db_cache_size=1761607680
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=503316480
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=4800m
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTMXDB)'
*.local_listener='LISTENER_TESTM'
*.log_archive_format='arch_%t_%s_%r.arc'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=810m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2430m
*.undo_tablespace='UNDOTBS1'
4.修改备库的pfile
*.db_name=TESTM
*.db_unique_name='TEST_S'
*.log_archive_config='dg_config=(TEST_S,TEST_M)'
*.log_archive_dest_1='location=/oracle/archive valid_for=(all_logfiles,all_roles) db_unique_name=TEST_S'
*.log_archive_dest_2='service=TESTM reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=TEST_M'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='TESTM'
*.fal_client='TESTS'
*.log_file_name_convert='/oracle/archive_log','/oracle/archive'
*.db_file_name_convert='/oracle/archive_log','/oracle/archive'
TESTM.__data_transfer_cache_size=0
TESTM.__db_cache_size=264241152
TESTM.__java_pool_size=4194304
TESTM.__large_pool_size=8388608
TESTM.__oracle_base='/oracle'#ORACLE_BASE set from environment
TESTM.__pga_aggregate_target=301989888
TESTM.__sga_target=452984832
TESTM.__shared_io_pool_size=20971520
TESTM.__shared_pool_size=146800640
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=4800m
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTMXDB)'
*.local_listener='LISTENER_TESTM'
*.memory_target=720m
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
5.将备库启动到nomount状态
[oracle@ora_slave pfile]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on 星期四 11月 7 23:21:17 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup nomount pfile='/oracle/dataguard/pfile/tests.ora'
ORACLE 例程已经启动。
Total System Global Area 751595520 bytes
Fixed Size 2292912 bytes
Variable Size 557843280 bytes
Database Buffers 188743680 bytes
Redo Buffers 2715648 bytes
SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开
注意:将数据库nomount后,记得exit,断开所有与rman无关的连接
6.用rman将主库拷贝的备库
oracle@Sol_ORA:~$ rman target sys/test@TESTM auxiliary sys/test@TESTS
恢复管理器: Release 12.1.0.1.0 - Production on 星期五 11月 8 13:50:42 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: TESTM (DBID=1234276969)
已连接到辅助数据库: TESTM (未装载)
RMAN> duplicate target database for standby from active database nofilenamecheck;
启动 Duplicate Db 于 08-11月-13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: SID=21 设备类型=DISK
内存脚本的内容:
{
backup as copy reuse
targetfile '/oracle/12.1/dbs/orapwTESTM' auxiliary format
'/oracle/product/12.1.0/dbs/orapwTESTM' ;
}
正在执行内存脚本
启动 backup 于 08-11月-13
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=242 设备类型=DISK
完成 backup 于 08-11月-13
内存脚本的内容:
{
restore clone from service 'TESTM' standby controlfile;
}
正在执行内存脚本
启动 restore 于 08-11月-13
使用通道 ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集
通道 ORA_AUX_DISK_1: 正在使用来自服务 TESTM 的网络备份集
通道 ORA_AUX_DISK_1: 正在还原控制文件
通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:01
输出文件名=/oracle/oradata/TESTM/control01.ctl
输出文件名=/oracle/fast_recovery_area/TESTM/control02.ctl
完成 restore 于 08-11月-13
内存脚本的内容:
{
sql clone 'alter database mount standby database';
}
正在执行内存脚本
sql 语句: alter database mount standby database
将以前的重复文件 /oracle/oradata/TESTM/system01.dbf (用于数据文件 1) 与 2379041 的检查点 SCN 一起使用
将以前的重复文件 /oracle/oradata/TESTM/sysaux01.dbf (用于数据文件 3) 与 2379075 的检查点 SCN 一起使用
将以前的重复文件 /oracle/oradata/TESTM/undotbs01.dbf (用于数据文件 4) 与 2379105 的检查点 SCN 一起使用
将以前的重复文件 /oracle/oradata/TESTM/users01.dbf (用于数据文件 6) 与 2379111 的检查点 SCN 一起使用
内存脚本的内容:
{
set newname for tempfile 1 to
"/oracle/oradata/TESTM/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oracle/oradata/TESTM/system01.dbf";
set newname for datafile 3 to
"/oracle/oradata/TESTM/sysaux01.dbf";
set newname for datafile 4 to
"/oracle/oradata/TESTM/undotbs01.dbf";
set newname for datafile 6 to
"/oracle/oradata/TESTM/users01.dbf";
sql 'alter system archive log current';
}
正在执行内存脚本
正在执行命令: SET NEWNAME
临时文件 1 在控制文件中已重命名为 /oracle/oradata/TESTM/temp01.dbf
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
正在执行命令: SET NEWNAME
sql 语句: alter system archive log current
内存脚本的内容:
{
restore clone force from service 'TESTM'
archivelog from scn 2379041;
catalog clone datafilecopy "/oracle/oradata/TESTM/system01.dbf",
"/oracle/oradata/TESTM/sysaux01.dbf",
"/oracle/oradata/TESTM/undotbs01.dbf",
"/oracle/oradata/TESTM/users01.dbf";
switch clone datafile 1 to datafilecopy
"/oracle/oradata/TESTM/system01.dbf";
switch clone datafile 3 to datafilecopy
"/oracle/oradata/TESTM/sysaux01.dbf";
switch clone datafile 4 to datafilecopy
"/oracle/oradata/TESTM/undotbs01.dbf";
switch clone datafile 6 to datafilecopy
"/oracle/oradata/TESTM/users01.dbf";
}
正在执行内存脚本
启动 restore 于 08-11月-13
使用通道 ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: 正在开始将归档日志还原到默认目标
通道 ORA_AUX_DISK_1: 正在使用来自服务 TESTM 的网络备份集
通道 ORA_AUX_DISK_1: 正在还原归档日志
归档日志线程=1 序列=13
通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:01
通道 ORA_AUX_DISK_1: 正在开始将归档日志还原到默认目标
通道 ORA_AUX_DISK_1: 正在使用来自服务 TESTM 的网络备份集
通道 ORA_AUX_DISK_1: 正在还原归档日志
归档日志线程=1 序列=14
通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:01
通道 ORA_AUX_DISK_1: 正在开始将归档日志还原到默认目标
通道 ORA_AUX_DISK_1: 正在使用来自服务 TESTM 的网络备份集
通道 ORA_AUX_DISK_1: 正在还原归档日志
归档日志线程=1 序列=15
通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:01
完成 restore 于 08-11月-13
已将数据文件副本列入目录
数据文件副本文件名=/oracle/oradata/TESTM/system01.dbf RECID=1 STAMP=830910982
已将数据文件副本列入目录
数据文件副本文件名=/oracle/oradata/TESTM/sysaux01.dbf RECID=2 STAMP=830910982
已将数据文件副本列入目录
数据文件副本文件名=/oracle/oradata/TESTM/undotbs01.dbf RECID=3 STAMP=830910982
已将数据文件副本列入目录
数据文件副本文件名=/oracle/oradata/TESTM/users01.dbf RECID=4 STAMP=830910982
数据文件 1 已转换成数据文件副本
输入数据文件副本 RECID=1 STAMP=830910982 文件名=/oracle/oradata/TESTM/system01.dbf
数据文件 3 已转换成数据文件副本
输入数据文件副本 RECID=2 STAMP=830910982 文件名=/oracle/oradata/TESTM/sysaux01.dbf
数据文件 4 已转换成数据文件副本
输入数据文件副本 RECID=3 STAMP=830910982 文件名=/oracle/oradata/TESTM/undotbs01.dbf
数据文件 6 已转换成数据文件副本
输入数据文件副本 RECID=4 STAMP=830910982 文件名=/oracle/oradata/TESTM/users01.dbf
完成 Duplicate Db 于 08-11月-13
RMAN>
7.启动MRP
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
8.备库添加备库日志
$ tail -10f alert_TESTM.log
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process (PID:19646)
RFS[2]: No standby redo logfiles created //没有备库的日志
RFS[2]: Opened log for thread 1 sequence 18 dbid 1234276969 branch 830945706
停止MRP添加logfile 然后在启动MRP
SQL> alter database recover managed standby database cancel;
数据库已更改。
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/oracle/dataguard/redo/redo01_4.log','/oracle/dataguard/redo/redo02_4.log') size 50M;
数据库已更改。
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oracle/dataguard/redo/redo01_5.log','/oracle/dataguard/redo/redo02_5.log') size 50M;
数据库已更改。
SQL> alter database recover managed standby database disconnect from session;
$ tail -f alert_TESTM.log
MRP0: Background Managed Standby Recovery process started (TESTM)
Fri Nov 08 00:47:56 2013
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Fri Nov 08 00:47:56 2013
Waiting for all non-current ORLs to be archived...
Fri Nov 08 00:47:56 2013
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 18 (in transit)
Completed: alter database recover managed standby database disconnect from session
Fri Nov 08 00:50:59 2013
db_recovery_file_dest_size of 4800 MB is 0.00% used. This
9.验证
主
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
17
备
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
17
主
SQL> alter system switch logfile;
系统已更改。
备
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
18
SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开
[oracle@ora_slave trace]$ tail -60f alert_TESTM.log
......
Fri Nov 08 01:01:08 2013
Archived Log entry 7 added for thread 1 sequence 19 ID 0x4991ca69 dest 1:
Fri Nov 08 01:01:08 2013
RFS[2]: Selected log 5 for thread 1 sequence 20 dbid 1234276969 branch 830945706
Fri Nov 08 01:01:08 2013
Media Recovery Waiting for thread 1 sequence 20 (in transit)
Fri Nov 08 01:01:08 2013
Recovery of Online Redo Log: Thread 1 Group 5 Seq 20 Reading mem 0
Mem# 0: /oracle/dataguard/redo/redo01_5.log
Mem# 1: /oracle/dataguard/redo/redo02_5.log
......
备库查看MRP状态
SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM
V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0'; 2
CLIENT_PROCESS PROCESS THREAD# SEQUENCE# STATUS
------------------------ --------------------------- ---------- ---------- ------------------------------------
N/A MRP0 1 20 APPLYING_LOG //正在应用日志
LGWR RFS 1 20 IDLE
10.注意(摘自dave大师博客)
两点注意事项:
(1)如果使用的是非catalog,在rman 连接时,加上nocatalog关键字,如:
DG2:/home/oracle> rman targetsys/oracle@DG_PD auxiliarysys/oracle@DG_ST nocatalog
Recovery Manager: Release 11.2.0.3.0 -Production on Fri Oct 28 18:46:46 2011
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
connected to target database: DG(DBID=1679060044)
using target databasecontrol file instead of recovery catalog
connected to auxiliary database: DG (notmounted)
不然会报如下错误:
DBGSQL: TARGET> begin :fhdbi := dbms_rcvcat.getDbid; end;
DBGSQL: sqlcode = 6550
DBGSQL: B :fhdbi = 32767
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of Duplicate Db commandat 10/28/2011 17:53:04
RMAN-05501: aborting duplication of targetdatabase
RMAN-03015: error occurred in stored scriptMemory Script
ORA-06550: line 1, column 17:
PLS-00201: identifier'DBMS_RCVCAT.GETDBID' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
(2) 在执行duplicate的时候,如果源库和目标库目录相同,那么在duplicate 时,需要加上nofilenamecheck,如下:
RMAN> duplicate target database forstandby from active database dorecover nofilenamecheck;
我在上面的示例中,目录结构不同,所以没有用该参数,如果目录相同,而又没有加该参数,那么就会报如下错误:
RMAN> duplicate target database forstandby from active database dorecover nofilenamecheck;
RMAN-05501: aborting duplication of targetdatabase
RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/users01.dbf conflicts with a file used by the targetdatabase
RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/undotbs01.dbf conflicts with a file used by thetarget database
RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/sysaux01.dbf conflicts with a file used by thetarget database
RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/system01.dbf conflicts with a file used by thetarget database
附加:摘自dave大师博客
注意Data Guard 启动顺序:
启动顺序:先备,后主
关闭顺序:先主, 后备
主库进行日志切换:
SQL>Alter system switch logfile;
select max(sequence#) from v$archived_log;
select max(sequence#) from v$log_history;
select group#,sequence#,archived,status from v$log;
select name,sequence#,applied from v$archived_log;
select sequence#,applied from v$archived_log;
若不同步,
1) 看log日志, archive是否有丢失
2)可以在备库坐如下操作:
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;
################################################
迷途小运维学习笔记
作者:john
转载请注明出处