SQL> select log_mode from v$database;

LOG_MODE

------------

ARCHIVELOG

SQL> select force_logging from v$database;

FOR

---

NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging,log_mode from v$database;

FOR LOG_MODE

--- ------------

YES ARCHIVELOG

SQL> create pfile from spfile;

File created.

SQL> alter system set log_archive_config='DG_CONFIG=(primary,standby)'; --db_unique_name,主备库的DB_UNIQUE_NAME不要一样

System altered.

SQL> alter system set log_archive_dest_1='location=/u01/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';

System altered.

#ASYNC为异步同步 SYNC同步

SQL> alter system set log_file_name_convert='/u01/oracle/oradata/orcl/','/u01/oracle/oradata/orcl/' scope=spfile;

System altered.

SQL> alter system set db_file_name_convert='/u01/oracle/oradata/orcl/','/u01/oracle/oradata/orcl/' scope=spfile;

System altered.

SQL>

SQL> alter system set fal_server=standby; --备库链接字符串，多个从库用逗号分隔fal_server=standby1,standby2

System altered.

SQL> alter system set fal_client=primary; --主库链接字符串

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> !mkdir -p /u01/oracle/rman_bak --创建备份目录

#备份主数据

create pfile='/u01/oracle/rman_bak/init$ORACLE_SID.ora' from spfile;

rman target /

run {

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup incremental level 0 format '/u01/oracle/rman_bak/inr0_%U.bak' tag 'full_bak_for_standby' database plus archivelog;

release channel c1;

release channel c2;

}

backup format '/u01/oracle/rman_bak/control01.ctl' current controlfile for standby;

--拷贝备份到standby节点

scp -rp /u01/oracle/rman_bak/ 192.168.56.72:/u01/oracle/

#备库操作

修改备份库的初始化参数

*.db_unique_name='STANDBY'

control_files=("/opt/oracle/oradata/primary/control01.ctl")

log_archive_config='dg_config=(standby,primary)'

log_archive_dest_1='location=/opt/oracle/archivelog/ vaild_for=(all_logfiles,all_roles) db_unique_name=standby' --数据库唯一名

log_archive_dest_2='service=primary valid_for=(online_logfiles,primary_role) db_unique_name=primary' --数据库唯一名

db_file_name_convert=('/opt/oracle/oradata/primary/','/opt/oracle/oradata/primary/')

log_file_name_convert=('/opt/oracle/oradata/primary/','/opt/oracle/oradata/priamry/')

standby_file_management=auto

fal_server='primary' --字符串

fal_client='standby' --字符串

--创建参数文件(最好把主库的参数文件拷贝到备库)

cd $ORACLE_HOME/dbs/

orapwd file=orapw$ORACLE_SID password=oracle

--启动备库

SQL> startup nomount pfile=/u01/oracle/rman_bak/initorcl.ora

ORACLE instance started.

Total System Global Area 1269366784 bytes

Fixed Size 2252864 bytes

Variable Size 805310400 bytes

Database Buffers 452984832 bytes

Redo Buffers 8818688 bytes

--恢复备库

[oracle@db3 ~]$ rman target /

RMAN> restore controlfile from '/u01/oracle/rman_bak/control01.ctl';

Starting restore at 08-AUG-14

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/oracle/oradata/orcl/control01.ctl

output file name=/u01/oracle/fast_recovery_area/orcl/control02.ctl

Finished restore at 08-AUG-14

------

[oracle@standby rman_bak]$ rman target sys/oracle@primary auxiliary /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Aug 8 15:57:23 2014

connected to target database: ORCL (DBID=1382832428)

connected to auxiliary database: ORCL (not mounted)

RMAN> catalog start with 'backup_path/';

RMAN> duplicate target database for standby;--如果报错RMAN-05001执行：duplicate target database for standby nofilenamecheck;

--duplicate target database for standby from active database;

Starting Duplicate Db at 10-AUG-16

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=1 device type=DISK

contents of Memory Script:

{

restore clone standby controlfile;

}

executing Memory Script

Starting restore at 10-AUG-16

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/oracle/rman_bak/control01.ctl

channel ORA_AUX_DISK_1: piece handle=/u01/oracle/rman_bak/control01.ctl tag=TAG20160331T154500

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/oracle/oradata/orcl/control01.ctl

output file name=/u01/oracle/fast_recovery_area/orcl/control02.ctl

Finished restore at 10-AUG-16

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/oracle/oradata/orcl/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u01/oracle/oradata/orcl/system01.dbf";

set newname for datafile 2 to

"/u01/oracle/oradata/orcl/sysaux01.dbf";

set newname for datafile 3 to

"/u01/oracle/oradata/orcl/undotbs01.dbf";

set newname for datafile 4 to

"/u01/oracle/oradata/orcl/users01.dbf";

set newname for datafile 5 to

"/u01/oracle/oradata/orcl/example01.dbf";

restore

clone database

;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oracle/oradata/orcl/temp01.dbf in control file

executing command: SET NEWNAME

Starting restore at 10-AUG-16

using channel ORA_AUX_DISK_1

the file name for datafile 5 is missing in the control file

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/oracle/oradata/orcl/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/orcl/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/oracle/rman_bak/inr0_0dr1sanu_1_1.bak

channel ORA_AUX_DISK_1: piece handle=/u01/oracle/rman_bak/inr0_0dr1sanu_1_1.bak tag=FULL_BAK_FOR_STANDBY

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/orcl/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/orcl/undotbs01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/oracle/rman_bak/inr0_0er1sanv_1_1.bak

……...

select name,database_role from gv$database;

alter database recover managed standby database disconnect from session;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

col name for a50

select SEQUENCE#,first_time,next_time,APPLIED, THREAD# from v$archived_log order by SEQUENCE#,THREAD#;

#检查主备状态

主库:

select LOG_MODE,CONTROLFILE_TYPE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

alter system switch logfile;

select GROUP#,BYTES/1024/1024,STATUS from v$log;

备库:

select LOG_MODE,CONTROLFILE_TYPE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

select dest_id,sequence#,name,applied from v$archived_log order by sequence#;

col name for a50

select SEQUENCE#,first_time,next_time,APPLIED, THREAD# from v$archived_log order by SEQUENCE#,THREAD#;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

select GROUP#,BYTES/1024/1024,STATUS from v$log;

主库添加standby 日志组

SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/orcl/redo04.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 5 '/u01/oracle/oradata/orcl/redo05.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 6 '/u01/oracle/oradata/orcl/redo06.log' size 50M;

Database altered.

SQL> select GROUP#,BYTES/1024/1024,STATUS from v$standby_log;

GROUP# BYTES/1024/1024 STATUS

------ --------------- ----------

4 50 UNASSIGNED

5 50 UNASSIGNED

6 50 UNASSIGNED

备库添加standby 日志组

SQL> alter system set standby_file_management=manual;

System altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/orcl/redo04.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 5 '/u01/oracle/oradata/orcl/redo05.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 6 '/u01/oracle/oradata/orcl/redo06.log' size 50M;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL>

如果有归档没有传送到备库:

select * from v$archive_gap;

thread# low_sequence# high_sequence#

-------- -------------- --------------

1 1045 1060

2 1089 1104

主库检查归档是否存在

select * from v$archive_log where sequebce#=1045;(检查1045-1160；1089-1104)

拷贝归档日志到备库

alter database recover managed standby database cancel;

alter database register logfile '/tmp/log/thread_1_seq_1045';

--备库长时间不被激活，主库操作

SQL> ALTER SYSTEM SET log_archive_dest_state_3='DEFER' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_3='enable' SCOPE=BOTH;

System altered.

SQL> alter system switch logfile;

--主备库

rman target /

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

---切换

select name,db_unique_name,open_mode,database_role,switchover_status from gv$database;

select name,INST_ID,db_unique_name,open_mode,database_role,switchover_status from gv$database;

--原备库

alter database commit to switchover to primary;

alter database open;

select LOG_MODE,CONTROLFILE_TYPE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

col name for a50

select SEQUENCE#,first_time,next_time,APPLIED, THREAD# from v$archived_log order by SEQUENCE#,THREAD#;

--原主库

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> startup mount;

SQL> alter database recover managed standby database disconnect from session;

select LOG_MODE,CONTROLFILE_TYPE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

col name for a50

select SEQUENCE#,first_time,next_time,APPLIED, THREAD# from v$archived_log order by SEQUENCE#,THREAD#;

--主机宕机，备库切换

#备库

QL> alter database recover managed standby database finish;

Database altered.

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY NOT ALLOWED

SQL> alter database open;

Database altered.

#

--备库强制切换为主

SQL> alter database recover managed standby database finish force;

Database altered.

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate

SQL> startup

--切换adg

SQL> recover managed standby database using current logfile disconnect from session;

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

-------------------- ---------------- ------------------------------

READ ONLY WITH APPLY PHYSICAL STANDBY 137

其中disconnect from session表示后台执行，类似于shell的&。可以看出现在备库处于READ ONLY状态，不再是处于MOUNT不可使用的状态，即现在可以执行只读操作了。

错误解决：

RMAN-05535: WARNING: All redo log files were not defined properly.

Finished Duplicate Db at 30-NOV-15

解决方法：

在主备数据库设置LOG_FILE_NAME_CONVERT参数，即使参数的两个值相同也需要设置。例如：

LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'，该参数需要重启实例才能生效，之后清除备用数据库，在主数据库重新执行duplicate操作即可。

alter system set LOG_FILE_NAME_CONVERT=('/u01/oracle/oradata/orcl/','/u01/oracle/oradata/orcl/') scope=spfile;

Error 16047 for archive log file 1 to '68'

检查配置

主备库

show parameter log_archive_config;

SQL> show parameter log_archive_config;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_config string DG_CONFIG=(PRIMARY,STANDBY)

SQL> show parameter log_archive_config;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_config string DG_CONFIG=(standby,primary)

#恢复从库数据

recover automatic standby database ;

#从库启动

startup nomount;

alter database mount standby database;

dg搭建 单点-单点

dg搭建单点-单点