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);