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搭建 單點-單點

相關文章

dg搭建單點-單點