1、检查数据库是否支持Data Guard(企业版才支持),是否归档模式，Enable force logging
SQL> select * from v$option where parameter = 'Managed Standby';

确认主库处于归档模式
SQL> archive log list;
若显示为No Archive Mode
Database log mode No Archive Mode
Automatic archival Disabled
则需要转为Archive Mode模式
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/data/archive_dest/march' scope=spfile;
SQL> alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> archive log list;
SQL> alter database open;

进行日志切换，查看/data/archive_dets/march目录下是否有归档日志
SQL> alter system switch logfile;

将primary数据库置为FORCE LOGGING模式
SQL> alter database force logging;

2、如果主库没有密码文件则建立密码文件，从而可以OS验证的方式登陆
[oracle@oracle-db1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=5

3、配置standby redolog（最佳性能模式可以忽略，若变成备库且要转为其它两种模式则要建立）
SQL> alter database add standby logfile group 4 '/data/app/oracle/oradata/boss/sredo04.log' size 50M;
SQL> alter database add standby logfile group 5 '/data/app/oracle/oradata/boss/sredo05.log' size 50M;
(maximum number of logfiles for each thread + 1) * maximum number of threads
单机线程数为1，RAC一般为2。standby redolog的组成员数和大小也尽量和online redolog一样

4、设置主库初始化参数
DB_UNIQUE_NAME=boss
INSTANCE_NAME=boss
LOG_ARCHIVE_CONFIG='DG_CONFIG=(boss,bosstdy)'
LOG_ARCHIVE_DEST_1='LOCATION=/data/archive_dest/march VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boss'
LOG_ARCHIVE_DEST_2='SERVICE=bosstdy LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bosstdy'
LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
LOG_ARCHIVE_MAX_PROCESSES=4
FAL_SERVER=bosstdy
FAL_CLIENT=boss

若主备数据库之间数据目录不一致，则添加下面参数
#DB_FILE_NAME_CONVERT='/data/app/oracle/oradata/boss','/data/app/oracle/oradata/boss'
#LOG_FILE_NAME_CONVERT='/data/app/oracle/oradata/boss','/data/app/oracle/oradata/boss'
STANDBY_FILE_MANAGEMENT=AUTO

5、备份主库数据文件
RMAN> backup full database format '/data/xchtmp/backup/backup_%T_%s_%p.dbf';
RMAN> sql "alter system archive log current";
RMAN> backup archivelog all format '/data/xchtmp/backup/backup_%T_%s_%p.arc';

6、创建 standby 数据库控制文件
SQL> alter database create standby controlfile as '/data/xchtmp/backup/standby.ctl';

7、为备库准备init参数
DB_UNIQUE_NAME=bosstdy
INSTANCE_NAME=bosstdy
LOG_ARCHIVE_CONFIG='DG_CONFIG=(boss,bosstdy)'
LOG_ARCHIVE_DEST_1='LOCATION=/letv/archivelog_dest/march VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bosstdy'
LOG_ARCHIVE_DEST_2='SERVICE=boss LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boss'
LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
LOG_ARCHIVE_MAX_PROCESSES=4
FAL_SERVER=boss
FAL_CLIENT=bosstdy

若主备数据库之间数据目录不一致，则添加下面参数
#DB_FILE_NAME_CONVERT='oradata/boss','oradata/bosstdy'
#LOG_FILE_NAME_CONVERT='oradata/boss','oradata/bosstdy'
STANDBY_FILE_MANAGEMENT=AUTO

8、拷贝上面生成的文件backup_%T_%s_%p.dbf、standby.ctl、backup_%T_%s_%p.arc到备库所在主机

9、建立主库监听和主备库的网络服务名（必须是dedicated的），并启动监听
BOSS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-db1)(PORT = 1521))
(CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = boss)
)
)

BOSSTDY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-db2)(PORT = 1521))
(CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bosstdy)
)
)

分别启动主库和备库的监听
[oracle@oracle-db1~]$ lsnrctl start
[oracle@oracle-db2~]$ lsnrctl start

10、备库建立密码文件，从而可以OS验证的方式登陆，注意密码与主库相同
[oracle@oracle-db2~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle

11、备库利用参数文件启动到nomount状态
[oracle@oracle-db2 ~]$ sqlplus /nolog
SQL> conn /as sysdba
SQL> create spfile from pfile;
SQL> startup nomount;

12、利用RMAN恢复备库
[oracle@oracle-db1 ~]$ rman target sys/oracle@boss auxiliary sys/oracle@bosstdy

在主库上执行上面命令时报错
RMAN-04006: error from auxiliary database:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
是由于备库bosstdy只是启动到nomount状态，没有启动到mount状态，只是实例属于BLOCKED状态
Instance "bosstdy", status BLOCKED, has 1 handler(s) for this service...

换一种思维,在备库执行以上恢复
[oracle@oracle-db2 ~]$ rman target sys/oracle@boss auxiliary /
RMAN> duplicate target database for standby;
如执行以上duplicate命令报错时
RMAN-05001: auxiliary filename xxx conflicts with a file used by the target database
添加nofilenamecheck参数
RMAN> duplicate target database for standby nofilenamecheck;

或者监听注册为静态的

13、备库添加standby redolog（日志组号、成员个数、日志大小等规则均与主库相同）
SQL> alter database add standby logfile group 4 '/data/app/oracle/oradata/boss/sredo04.log' size 50M;
SQL> alter database add standby logfile group 5 '/data/app/oracle/oradata/boss/sredo05.log' size 50M;

14、启动redo应用
--启动redo应用
SQL> alter database recover managed standby database disconnect from session;

--启动实时redo应用
SQL> alter database recover managed standby database using current logfile disconnect from session;

--停止redo应用的命令
SQL> alter database recover managed standby database cancel;

15、查看同步情况
--primary database
SQL> show parameter instance_name;
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;

--standby database
SQL> show parameter instance_name;
SQL> select max(sequence#) from v$archived_log;

16、验证redo log是否传输到standby database
--primary database
SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;
SQL> alter system switch logfile;

--standby database
SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

--验证standby redo是否应用
SQL> select sequence#,applied from v$archived_log order by sequence#;

17、主备库关闭与启动顺序
启动顺序：先standby，后primary
关闭顺序：先primary，后standby

18、错误的查找
--primary database
SQL> col dest_name for a30
SQL> col error for a20
SQL> select dest_name,status,error,target,process from v$archive_dest where substr(dest_name,-1) in (1,2);

ITPub博客

Data Guard之Physical Standby Database

最新文章