【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（一）
【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（一）
之前釋出過一步一步搭建 oracle 11gR2 rac + dg，這裡的dg為物理dg，但是實際自己使用過程中發現需要開3個虛擬機器，機器特卡，所以決定在同一臺機器上再搭建一臺物理和邏輯dg。
一步一步搭建 oracle 11gR2 rac + dg 之前傳（一） http://blog.itpub.net/26736162/viewspace-1290405/
一步一步搭建oracle 11gR2 rac+dg之環境準備(二) http://blog.itpub.net/26736162/viewspace-1290416/
一步一步搭建oracle 11gR2 rac+dg之共享磁碟設定(三) http://blog.itpub.net/26736162/viewspace-1291144/
一步一步搭建 oracle 11gR2 rac+dg之grid安裝(四) http://blog.itpub.net/26736162/viewspace-1297101/
一步一步搭建oracle 11gR2 rac+dg之database安裝(五) http://blog.itpub.net/26736162/viewspace-1297113/
一步一步搭建11gR2 rac+dg之安裝rac出現問題解決(六) http://blog.itpub.net/26736162/viewspace-1297128/
一步一步搭建11gR2 rac+dg之DG 機器配置(七) http://blog.itpub.net/26736162/viewspace-1298733/
一步一步搭建11gR2 rac+dg之配置單例項的DG(八) http://blog.itpub.net/26736162/viewspace-1298735/
一步一步搭建11gR2 rac+dg之DG SWITCHOVER功能(九) http://blog.itpub.net/26736162/viewspace-1328050/
本篇blog結構圖：
-
先建立物理備庫
建立物理備庫的方法很多，對於Oracle 11g而言，可以直接從active database來建立，也可以基於10g 的RMAN使用duplicate方式來建立。
--演示環境
[root@rhel6_lhr ~]# su - oracle
[oracle@rhel6_lhr ~]$ cat /etc/issue
Red Hat Enterprise Linux Server release 6.5 (Santiago)
Kernel \r on an \m
[oracle@rhel6_lhr ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.3.0 Production
[oracle@rhel6_lhr ~]$
--主庫：ora11g
--備庫: ora11gdg
--配置物理standby，使用最佳效能模式
--主庫：配置歸檔且force logging
-
主庫前期準備
[oracle@rhel6_lhr oradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 4 10:20:43 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user lhr identified by lhr;
User created.
SQL> grant dba to lhr;
Grant succeeded.
SQL> create table lhr.test as select * from dba_tables;
Table created.
SQL> select count(1) from lhr.test;
COUNT(1)
----------
2834
SQL> alter database force logging;
Database altered.
SQL> set line 9999
SQL> select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from v$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------- ------------ --- ---------------- --------------------
LILOVE READ WRITE ARCHIVELOG YES PRIMARY NOT ALLOWED
SQL>
--為主庫新增standby redo log，簡要描述一下standby redo log的作用
--實際上就是與主庫接收到的重做日誌相對應，也就是說備庫呼叫RFS程式將從主庫接收到的重做日誌按順序寫入到standby logfile
--在主庫建立standby logfile是便於發生角色轉換後備用
--sandby redo log建立原則：
--a)、確保standby redo log的大小與主庫online redo log的大小一致
--b)、如主庫為單例項資料庫：standby redo log組數=主庫日誌組總數+1
--c)、如果主庫是RAC資料庫：standby redo log組數=(每執行緒的日誌組數+1)*最大執行緒數
--d)、不建議複用standby redo log，避免增加額外的I/O以及延緩重做傳輸
SQL> select * from v$standby_log;
no rows selected
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/ora11g/standby_redo/standby_redo01.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/ora11g/standby_redo/standby_redo02.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/ora11g/standby_redo/standby_redo03.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/ora11g/standby_redo/standby_redo04.log') size 50m;
Database altered.
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
4 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
5 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
6 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
7 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL>
-
修改主庫引數檔案
--使用下面的命令修改主庫引數(此時主庫應當使用spfile啟動引數)
SQL> edit a.sql
SQL> ! more a.sql
--Add below item when DB acts as primary role
alter system set db_unique_name='ora11g' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(ora11g,ora11gdg)';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=ora11g valid_for=(ALL_LOGFILES,ALL_ROLES)';
alter system set log_archive_dest_2='SERVICE=ora11gdg ASYNC db_unique_name=ora11gdg valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)';
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set log_archive_max_processes=4;
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
--Add below item when DB turn to standby role
alter system set db_file_name_convert='ora11gdg','ora11g' scope=spfile;
alter system set log_file_name_convert='ora11gdg','ora11g' scope=spfile;
alter system set standby_file_management='AUTO';
alter system set fal_server='ora11gdg';
alter system set fal_client='ora11g';
SQL> @a.sql;
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
System altered.
SQL>
-
配置主備庫監聽
--為主庫和備庫配置監聽，整個DG的redo傳輸服務，都依賴於Oracle Net，因此需要為主備庫配置監聽
--配置方法多種多樣，可用netmgr，netca，以及直接編輯listener.ora 與tnsnames.ora檔案
--下面是配置之後的listener.ora 與tnsnames.ora檔案內容
[oracle@rhel6_lhr ~]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora11g)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME= ora11g)
)
(SID_DESC =
(GLOBAL_DBNAME = ora11gdg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME= ora11gdg)
)
)
[oracle@rhel6_lhr admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ora11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11g)
)
)
ora11gdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11gdg)
)
)
[oracle@rhel6_lhr oradata]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-MAR-2015 10:17:45
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rhel6_lhr/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 04-MAR-2015 10:17:45
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rhel6_lhr/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1522)))
Services Summary...
Service "ora11g" has 1 instance(s).
Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service...
Service "ora11gdg" has 1 instance(s).
Instance "ora11gdg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
-
配置備庫密碼檔案及引數檔案
--由於要求主庫與備庫sys使用相同的密碼，在此處，我們直接複製了主庫的密碼檔案到備庫
[oracle@rhel6_lhr dbs]$ cp $ORACLE_HOME/dbs/orapwora11g $ORACLE_HOME/dbs/orapwora11gdg
[oracle@rhel6_lhr dbs]$ echo db_name=ora11g >$ORACLE_HOME/dbs/initora11gdg.ora
[oracle@rhel6_lhr dbs]$ ll $ORACLE_HOME/dbs/initora11gdg.ora
-rw-r--r-- 1 oracle oinstall 15 Mar 4 10:34 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initora11gdg.ora
[oracle@rhel6_lhr dbs]$
[oracle@rhel6_lhr oradata]$ mkdir -p /u01/app/oracle/admin/ora11gdg/adump
[oracle@rhel6_lhr oradata]$ mkdir -p /u01/app/oracle/oradata/ora11gdg/
[oracle@rhel6_lhr oradata]$ mkdir -p /u01/app/oracle/oradata/ora11gdg/standby_redo/
-
利用rman的duplicate複製主庫檔案到備庫
--對於從主庫克隆standby有多種方法，而且Oracle 11g支援從ative database直接克隆資料庫
--為主庫生成控制檔案,注，對於配置standby，不能直接使用copy方式複製控制檔案到備庫
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 4 10:40:55 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 250560512 bytes
Fixed Size 2227256 bytes
Variable Size 192938952 bytes
Database Buffers 50331648 bytes
Redo Buffers 5062656 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhel6_lhr ~]$ rman target sys/lhr@ora11g auxiliary sys/lhr@ora11gdg
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 4 10:52:09 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4269654224)
connected to auxiliary database: ORA11G (not mounted)
RMAN>
duplicate target database
for standby
from active database
DORECOVER
spfile
set db_unique_name='ora11gdg'
set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
set log_archive_dest_2='service=ora11g async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=ora11g'
set standby_file_management='AUTO'
set fal_server='ora11g'
set fal_client='ora11gdg'
set control_files='/u01/app/oracle/oradata/ora11gdg/crontal01.ctl','/u01/app/oracle/oradata/ora11gdg/control02.ctl'
set db_file_name_convert='ora11g','ora11gdg'
set log_file_name_convert='ora11g','ora11gdg'
set memory_target='400M'
16> ;
Starting Duplicate Db at 2015-03-04 10:52:23
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=171 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwora11g' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwora11gdg' targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11g.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11gdg.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11gdg.ora''";
}
executing Memory Script
Starting backup at 2015-03-04 10:52:24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
Finished backup at 2015-03-04 10:52:26
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11gdg.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''ora11gdg'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=USE_DB_RECOVERY_FILE_DEST'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=ora11g async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=ora11g'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''ora11g'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''ora11gdg'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/ora11gdg/crontal01.ctl'', ''/u01/app/oracle/oradata/ora11gdg/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''ora11g'', ''ora11gdg'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''ora11g'', ''ora11gdg'' comment=
'''' scope=spfile";
sql clone "alter system set memory_target =
400M comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''ora11gdg'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=USE_DB_RECOVERY_FILE_DEST'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=ora11g async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=ora11g'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''ora11g'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''ora11gdg'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/ora11gdg/crontal01.ctl'', ''/u01/app/oracle/oradata/ora11gdg/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''ora11g'', ''ora11gdg'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''ora11g'', ''ora11gdg'' comment= '''' scope=spfile
sql statement: alter system set memory_target = 400M comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 272633136 bytes
Database Buffers 134217728 bytes
Redo Buffers 8466432 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/ora11gdg/crontal01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/ora11gdg/control02.ctl' from
'/u01/app/oracle/oradata/ora11gdg/crontal01.ctl';
}
executing Memory Script
Starting backup at 2015-03-04 10:52:34
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ora11g.f tag=TAG20150304T105234 RECID=3 STAMP=873456755
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2015-03-04 10:52:36
Starting restore at 2015-03-04 10:52:36
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2015-03-04 10:52:37
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/app/oracle/oradata/ora11gdg/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/ora11gdg/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/ora11gdg/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/ora11gdg/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/ora11gdg/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/ora11gdg/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/ora11gdg/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/ora11gdg/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/ora11gdg/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/ora11gdg/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/ora11gdg/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/ora11gdg/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 2015-03-04 10:52:43
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ora11g/system01.dbf
output file name=/u01/app/oracle/oradata/ora11gdg/system01.dbf tag=TAG20150304T105243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf
output file name=/u01/app/oracle/oradata/ora11gdg/sysaux01.dbf tag=TAG20150304T105243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/ora11g/example01.dbf
output file name=/u01/app/oracle/oradata/ora11gdg/example01.dbf tag=TAG20150304T105243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf
output file name=/u01/app/oracle/oradata/ora11gdg/undotbs01.dbf tag=TAG20150304T105243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf
output file name=/u01/app/oracle/oradata/ora11gdg/users01.dbf tag=TAG20150304T105243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2015-03-04 10:54:37
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_03_04/o1_mf_1_10_bhdwvv9v_.arc" auxiliary format
"/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_%u_.arc" archivelog like
"/u01/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_03_04/o1_mf_1_11_bhdwzfd3_.arc" auxiliary format
"/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 2015-03-04 10:54:37
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=10 RECID=3 STAMP=873456763
output file name=/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_0dq0vp7d_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=11 RECID=4 STAMP=873456877
output file name=/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_0eq0vp7f_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2015-03-04 10:54:40
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_0eq0vp7f_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_0dq0vp7d_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_0eq0vp7f_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_0dq0vp7d_.arc
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=873456880 file name=/u01/app/oracle/oradata/ora11gdg/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=873456880 file name=/u01/app/oracle/oradata/ora11gdg/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=873456880 file name=/u01/app/oracle/oradata/ora11gdg/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=873456880 file name=/u01/app/oracle/oradata/ora11gdg/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=873456880 file name=/u01/app/oracle/oradata/ora11gdg/example01.dbf
contents of Memory Script:
{
set until scn 1111652;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2015-03-04 10:54:40
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_0dq0vp7d_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_0eq0vp7f_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_10_0dq0vp7d_.arc thread=1 sequence=10
archived log file name=/u01/app/oracle/flash_recovery_area/ORA11GDG/archivelog/2015_03_04/o1_mf_1_11_0eq0vp7f_.arc thread=1 sequence=11
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-03-04 10:54:43
Finished Duplicate Db at 2015-03-04 10:54:46
-
啟動備庫到mount狀態並校驗結果
[oracle@rhel6_lhr standby_redo]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 4 11:02:24 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 289410352 bytes
Database Buffers 117440512 bytes
Redo Buffers 8466432 bytes
Database mounted.
SQL> alter system set dg_broker_start=true;
System altered.
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string ora11gdg
SQL>
以只讀方式開啟資料庫，oracle知道我們在備用資料庫控制檔案中進行裝載，所以當開啟資料時，他將自動置於只讀模式。
SQL> alter database open;
Database altered.
SQL> select count(1) from lhr.test;
COUNT(1)
----------
2834
SQL> set line 9999
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
4269654224 ORA11G 1113620 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
SQL>
為了實時查詢，啟用管理恢復
SQL> alter database recover managed standby database using current logfile disconnect;
主庫切換日誌
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL>
--在備庫端啟動redo apply
SQL> select name,open_mode,database_role,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
LILOVE MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select sequence#, first_time, next_time,applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
31 03-MAR-15 03-MAR-15 NO
32 03-MAR-15 03-MAR-15 NO
33 03-MAR-15 03-MAR-15 NO
34 03-MAR-15 03-MAR-15 NO
35 03-MAR-15 03-MAR-15 NO
36 03-MAR-15 03-MAR-15 NO
6 rows selected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 35
Next log sequence to archive 0
Current log sequence 37
SQL>
至此，物理備庫搭建完成。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1448197/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（二）
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫 (三)
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫 (四)--新增一個物理dg節點
- 配置物理備庫+邏輯備庫
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（1）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（2）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（3）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（4）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（5）
- Oracle10G Dataguard 多個備庫 - 主庫和物理備庫的切換Oracle
- RMAN DUPLICATE建立DataGuard物理備庫
- 【DataGuard】使用GC建立的物理DataGuard主備庫pfile比較GC
- dataguard回顧之安裝——建立邏輯備庫
- dataguard-建立物理備庫全程解析
- ORACLE DATAGUARD 資料庫---建立物理備用資料庫Oracle資料庫
- 認識資料庫物理備份和邏輯備份區別資料庫
- dataguard之邏輯備庫表空間不足
- oracle 之dataguard主庫系統崩潰之物理備庫切主庫Oracle
- 一個備份集同時恢出dataguard的主庫&備庫
- 4節點RAC建立邏輯備庫
- rac與邏輯備庫不能自動建表空間，物理備庫正常
- 邏輯備庫Switchover
- 【DataGuard】手工冷備搭建 Oracle 11g DataGuard 物理備庫Oracle
- dataguard回顧之安裝———使用rman建立物理備庫
- dataguard之邏輯備庫移動資料檔案
- 邏輯Data Guard主備庫的轉換
- 一主一物一邏備庫
- 11 管理邏輯備庫
- RMAN備用資料庫(邏輯dataguard) --關於dorecover 的解釋 （2）資料庫
- 【DATAGUARD】DG系列之11g邏輯備庫的搭建
- dataguard 主備庫出現gap
- 邏輯DG主備庫轉換的failoverAI
- 【DataGuard】Oracle 11g DataGuard 角色轉換(一)物理備庫SwitchoverOracle
- oracle邏輯備用資料庫（一）Oracle資料庫
- [原創]RMAN備用資料庫(邏輯dataguard) --關於dorecover 的解釋資料庫
- 使用RMAN DUPLICATE...FROM ACTIVE DATABASE命令來建立DataGuard物理備庫Database
- 邏輯備份全庫或者邏輯備份多個使用者的資料
- DataGuard主備庫切換步驟