RMAN冷拷贝搭建dataguard物理备库

最新推荐文章于 2021-04-13 00:45:09 发布

清霄

最新推荐文章于 2021-04-13 00:45:09 发布

阅读量1.1k

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/cwg_1992/article/details/14518727

版权

Oracle 专栏收录该内容

57 篇文章 0 订阅

订阅专栏

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

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

转载请注明出处

清霄

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
RMAN冷拷贝搭建dataguard物理备库

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=
复制链接

扫一扫