【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫 (三)
【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫 (三)
blog文件結構圖：
需求： 在同一臺機器配置10g單例項+物理dg+邏輯dg，即一個主庫上掛2個備庫，一個物理備庫，一個邏輯備庫，總體思路為：先搭建2臺物理dg，然後將其中的一臺轉換為邏輯dg
之前釋出過一步一步搭建 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/
【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（一）： http://blog.itpub.net/26736162/viewspace-1448197/
【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（二): http://blog.itpub.net/26736162/viewspace-1448207/
主庫：
db_name = oradg10g
db_unique_name/ORACLE_SID = oradg10g
Net Service Name = oradg10g
物理備庫：
db_name = oradg10g
db_unique_name/ORACLE_SID = orawldg
Net Service Name = orawldg
邏輯備庫：
db_name = oraljdg --轉為邏輯備庫後的資料庫名，不能和主庫一致
db_unique_name/ORACLE_SID = oraljdg
Net Service Name = oraljdg
命令提示符：
# 表示以root使用者操作
$ 表示以oracle使用者操作
SQL> 表示登陸sqlplus操作
其它提示：
所有密碼都為lhr
1.1 先建立2個物理備庫
--演示環境
[oracle@rhel6_lhr ~]$ sqlplus -v
SQL*Plus: Release 10.2.0.4.0 - Production
[oracle@rhel6_lhr ~]$ cat /etc/issue
Red Hat Enterprise Linux Server release 6.5 (Santiago)
Kernel \r on an \m
1.1.1 先建立一個主庫oradg10g
軟體安裝過程省略。。。。如有不會的，可以私聊。。。。。
我們直接採用dbca的靜默安裝方式來建立一個純淨的庫，關於dbca的靜默安裝方式參考：http://blog.itpub.net/26736162/viewspace-1448220/
[oracle@rhel6_lhr ~]$ env | grep ORACLE
ORACLE_BASE=/u03/app/oracle
ORACLE_HOME=/u03/app/oracle/product/10.2.0/db_1
[oracle@rhel6_lhr ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname oradg10g -sid oradg10g -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u04/orada/ -recoveryAreaDestination /u03/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 20 -databaseType OLTP -emConfiguration NONE
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u03/app/oracle/product/10.2.0/db_1/cfgtoollogs/dbca/oradg10g/oradg10g.log" for further details.
[oracle@rhel6_lhr ~]$ ps -ef|grep ora_
oracle 4816 1 0 17:19 ? 00:00:00 ora_pmon_oradg10g
oracle 4818 1 0 17:19 ? 00:00:00 ora_psp0_oradg10g
oracle 4820 1 0 17:19 ? 00:00:00 ora_mman_oradg10g
oracle 4822 1 0 17:19 ? 00:00:00 ora_dbw0_oradg10g
oracle 4824 1 0 17:19 ? 00:00:00 ora_lgwr_oradg10g
oracle 4826 1 0 17:19 ? 00:00:00 ora_ckpt_oradg10g
oracle 4828 1 0 17:19 ? 00:00:00 ora_smon_oradg10g
oracle 4830 1 0 17:19 ? 00:00:00 ora_reco_oradg10g
oracle 4832 1 0 17:19 ? 00:00:00 ora_cjq0_oradg10g
oracle 4834 1 0 17:19 ? 00:00:00 ora_mmon_oradg10g
oracle 4836 1 0 17:19 ? 00:00:00 ora_mmnl_oradg10g
oracle 4838 1 0 17:19 ? 00:00:00 ora_d000_oradg10g
oracle 4840 1 0 17:19 ? 00:00:00 ora_s000_oradg10g
oracle 4850 1 0 17:19 ? 00:00:00 ora_qmnc_oradg10g
oracle 4856 1 0 17:19 ? 00:00:00 ora_p000_oradg10g
oracle 4858 1 0 17:19 ? 00:00:00 ora_p001_oradg10g
oracle 4860 1 0 17:19 ? 00:00:00 ora_p002_oradg10g
oracle 4862 1 0 17:19 ? 00:00:00 ora_p003_oradg10g
oracle 4864 1 0 17:19 ? 00:00:00 ora_p004_oradg10g
oracle 4866 1 0 17:19 ? 00:00:00 ora_p005_oradg10g
oracle 4868 1 0 17:19 ? 00:00:00 ora_p006_oradg10g
oracle 4870 1 0 17:19 ? 00:00:00 ora_p007_oradg10g
oracle 4879 1 0 17:20 ? 00:00:00 ora_q000_oradg10g
oracle 4922 1 0 17:20 ? 00:00:00 ora_q001_oradg10g
oracle 4930 3884 0 17:20 pts/2 00:00:00 grep ora_
[oracle@rhel6_lhr ~]$
[oracle@rhel6_lhr ~]$ ORACLE_SID=oradg10g
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 30 17:21:19 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
1.1.2 主庫前期準備
一、主庫：配置歸檔且force logging，新增測試使用者及測試表
[oracle@rhel6_lhr ~]$ ORACLE_SID=oradg10g
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 30 17:21:19 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string oradg10g
db_unique_name string oradg10g
global_names boolean FALSE
instance_name string oradg10g
lock_name_space string
log_file_name_convert string
service_names string oradg10g
SQL> set line 9999
SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- -------------------- ---------------- --- ---------- --------------------
1480747539 ORADG10G 640502 MAXIMUM PERFORMANCE UNPROTECTED PRIMARY NO READ WRITE NOT ALLOWED
SQL> alter database force logging;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 3
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 448790528 bytes
Fixed Size 2084616 bytes
Variable Size 130023672 bytes
Database Buffers 310378496 bytes
Redo Buffers 6303744 bytes
Database mounted.
SQL> show parameter format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
nls_date_format string
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
star_transformation_enabled string FALSE
SQL> alter database archivelog;
Database altered.
SQL> alter system set log_archive_dest_1='location=/u04/arch/oradg10g';
System altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u04/arch/oradg10g
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL> alter system switch logfile;
System altered.
SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- -------------------- ---------------- --- ---------- --------------------
1480747539 ORADG10G 641693 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY YES READ WRITE NOT ALLOWED
SQL> create user lhr identified by lhr;
User created.
SQL> grant dba to lhr;
Grant succeeded.
SQL> create table lhr.test as select * from scott.emp;
Table created.
SQL> set pagesize 9999
SQL> select * from lhr.test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
二、 為主庫新增standby redo log
--為主庫新增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以及延緩重做傳輸
alter database add standby logfile
group 4 ('/u04/oradata/oradg10g/standby_redo04.log') size 50m,
group 5 ('/u04/oradata/oradg10g/standby_redo05.log') size 50m,
group 6 ('/u04/oradata/oradg10g/standby_redo06.log') size 50m,
group 7 ('/u04/oradata/oradg10g/standby_redo07.log') size 50m;
SQL> select * from v$standby_log;
no rows selected
SQL> select group#,bytes/1024/1024 ||'M' from v$log ;
GROUP# BYTES/1024/1024||'M'
---------- -----------------------------------------
1 50M
2 50M
3 50M
SQL> select name from v$datafile;
NAME
-----------------------------------------------
/u04/oradata/oradg10g/system01.dbf
/u04/oradata/oradg10g/undotbs01.dbf
/u04/oradata/oradg10g/sysaux01.dbf
/u04/oradata/oradg10g/users01.dbf
/u04/oradata/oradg10g/example01.dbf
SQL> alter database add standby logfile
2 group 4 ('/u04/oradata/oradg10g/standby_redo04.log') size 50m,
3 group 5 ('/u04/oradata/oradg10g/standby_redo05.log') size 50m,
4 group 6 ('/u04/oradata/oradg10g/standby_redo06.log') size 50m,
5 group 7 ('/u04/oradata/oradg10g/standby_redo07.log') size 50m;
Database altered.
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
4 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
5 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
6 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
7 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
SQL>
1.1.3 配置主備庫監聽
--為主庫和備庫配置監聽，整個DG的redo傳輸服務，都依賴於Oracle Net，因此需要為主備庫配置監聽
--配置方法多種多樣，可用netmgr，netca，以及直接編輯listener.ora 與tnsnames.ora檔案
--下面是配置之後的listener.ora 與tnsnames.ora檔案內容
[oracle@rhel6_lhr admin]$ cd $ORACLE_HOME/network/admin
[oracle@rhel6_lhr admin]$ more listener.ora
# listener.ora Network Configuration File: /u03/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u03/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = oradg10g)
(ORACLE_HOME = /u03/app/oracle/product/10.2.0/db_1)
(SID_NAME= oradg10g)
)
(SID_DESC =
(GLOBAL_DBNAME = orawldg)
(ORACLE_HOME = /u03/app/oracle/product/10.2.0/db_1)
(SID_NAME= orawldg)
)
(SID_DESC =
(GLOBAL_DBNAME = oraljdg)
(ORACLE_HOME = /u03/app/oracle/product/10.2.0/db_1)
(SID_NAME= oraljdg)
)
)
[oracle@rhel6_lhr admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u03/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA1024G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora1024g)
)
)
oradg10g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradg10g)
)
)
orawldg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orawldg)
)
)
oraljdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oraljdg)
)
)
[oracle@rhel6_lhr admin]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 30-MAR-2015 18:04:14
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Starting /u03/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /u03/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u03/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 30-MAR-2015 18:04:16
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u03/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u03/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "oradg10g" has 1 instance(s).
Instance "oradg10g", status UNKNOWN, has 1 handler(s) for this service...
Service "oraljdg" has 1 instance(s).
Instance "oraljdg", status UNKNOWN, has 1 handler(s) for this service...
Service "orawldg" has 1 instance(s).
Instance "orawldg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rhel6_lhr admin]$
[oracle@rhel6_lhr admin]$ tnsping oradg10g
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 30-MAR-2015 18:06:45
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/u03/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradg10g)))
OK (0 msec)
[oracle@rhel6_lhr admin]$
[oracle@rhel6_lhr admin]$ tnsping oraljdg
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 30-MAR-2015 18:07:05
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/u03/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oraljdg)))
OK (0 msec)
[oracle@rhel6_lhr admin]$ tnsping orawldg
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 30-MAR-2015 18:07:10
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/u03/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orawldg)))
OK (0 msec)
[oracle@rhel6_lhr admin]$
1.1.4 利用rman對主庫備份並生成備庫控制檔案
RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
backup current controlfile for standby format='/u04/backup/control_%U';
BACKUP FORMAT '/u04/backup/oradg_%U_%T.bak' skip inaccessible filesperset 5 DATABASE ;
sql 'alter system archive log current';
BACKUP FORMAT '/u04/backup/arch_%U_%T.bak' skip inaccessible filesperset 5 ARCHIVELOG ALL;
sql "alter database create standby controlfile as ''/u04/backup/control01.ctl'' reuse";
release channel c2;
release channel c1;
}
[oracle@rhel6_lhr dbs]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Mar 31 11:21:27 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORADG10G (DBID=1480747539)
RMAN> RUN {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> sql 'alter system archive log current';
5> backup current controlfile for standby format='/u04/backup/control_%U';
6> BACKUP FORMAT '/u04/backup/oradg_%U_%T.bak' skip inaccessible filesperset 5 DATABASE ;
7> sql 'alter system archive log current';
8> BACKUP FORMAT '/u04/backup/arch_%U_%T.bak' skip inaccessible filesperset 5 ARCHIVELOG ALL;
9> sql "alter database create standby controlfile as ''/u04/backup/control01.ctl'' reuse";
10> release channel c2;
11> release channel c1;
12> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=147 devtype=DISK
allocated channel: c2
channel c2: sid=140 devtype=DISK
sql statement: alter system archive log current
Starting backup at 2015-03-31 11:21:30
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including standby control file in backupset
channel c1: starting piece 1 at 2015-03-31 11:21:30
channel c1: finished piece 1 at 2015-03-31 11:21:31
piece handle=/u04/backup/control_09q370tq_1_1 tag=TAG20150331T112130 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-03-31 11:21:31
Starting backup at 2015-03-31 11:21:31
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u04/oradata/oradg10g/system01.dbf
input datafile fno=00004 name=/u04/oradata/oradg10g/users01.dbf
channel c1: starting piece 1 at 2015-03-31 11:21:32
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00003 name=/u04/oradata/oradg10g/sysaux01.dbf
input datafile fno=00005 name=/u04/oradata/oradg10g/example01.dbf
input datafile fno=00002 name=/u04/oradata/oradg10g/undotbs01.dbf
channel c2: starting piece 1 at 2015-03-31 11:21:32
channel c2: finished piece 1 at 2015-03-31 11:22:27
piece handle=/u04/backup/oradg_0bq370ts_1_1_20150331.bak tag=TAG20150331T112131 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:55
channel c1: finished piece 1 at 2015-03-31 11:22:34
piece handle=/u04/backup/oradg_0aq370ts_1_1_20150331.bak tag=TAG20150331T112131 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:02
Finished backup at 2015-03-31 11:22:34
Starting Control File and SPFILE Autobackup at 2015-03-31 11:22:34
piece handle=/u03/app/oracle/flash_recovery_area/ORADG10G/autobackup/2015_03_31/o1_mf_s_875791354_bkn4qv0l_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2015-03-31 11:22:36
sql statement: alter system archive log current
Starting backup at 2015-03-31 11:22:38
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=1 stamp=875727158
input archive log thread=1 sequence=4 recid=2 stamp=875727778
input archive log thread=1 sequence=5 recid=3 stamp=875729865
channel c1: starting piece 1 at 2015-03-31 11:22:41
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=1 sequence=6 recid=4 stamp=875729921
input archive log thread=1 sequence=7 recid=5 stamp=875729921
input archive log thread=1 sequence=8 recid=6 stamp=875791290
input archive log thread=1 sequence=9 recid=7 stamp=875791358
channel c2: starting piece 1 at 2015-03-31 11:22:41
channel c1: finished piece 1 at 2015-03-31 11:22:42
piece handle=/u04/backup/arch_0dq37100_1_1_20150331.bak tag=TAG20150331T112238 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
channel c2: finished piece 1 at 2015-03-31 11:22:42
piece handle=/u04/backup/arch_0eq37100_1_1_20150331.bak tag=TAG20150331T112238 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:02
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=10 recid=8 stamp=875791358
channel c1: starting piece 1 at 2015-03-31 11:22:43
channel c1: finished piece 1 at 2015-03-31 11:22:44
piece handle=/u04/backup/arch_0fq37102_1_1_20150331.bak tag=TAG20150331T112238 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
Finished backup at 2015-03-31 11:22:44
Starting Control File and SPFILE Autobackup at 2015-03-31 11:22:44
piece handle=/u03/app/oracle/flash_recovery_area/ORADG10G/autobackup/2015_03_31/o1_mf_s_875791364_bkn4r4nw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2015-03-31 11:22:46
sql statement: alter database create standby controlfile as ''/u04/backup/control01.ctl'' reuse
released channel: c2
released channel: c1
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
9 Full 6.77M DISK 00:00:01 2015-03-31 11:21:31
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20150331T112130
Piece Name: /u04/backup/control_09q370tq_1_1
Standby Control File Included: Ckp SCN: 648541 Ckp time: 2015-03-31 11:21:30
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
10 Full 216.28M DISK 00:00:51 2015-03-31 11:22:23
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20150331T112131
Piece Name: /u04/backup/oradg_0bq370ts_1_1_20150331.bak
List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
2 Full 648547 2015-03-31 11:21:32 /u04/oradata/oradg10g/undotbs01.dbf
3 Full 648547 2015-03-31 11:21:32 /u04/oradata/oradg10g/sysaux01.dbf
5 Full 648547 2015-03-31 11:21:32 /u04/oradata/oradg10g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
11 Full 366.19M DISK 00:00:58 2015-03-31 11:22:30
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20150331T112131
Piece Name: /u04/backup/oradg_0aq370ts_1_1_20150331.bak
List of Datafiles in backup set 11
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 648546 2015-03-31 11:21:32 /u04/oradata/oradg10g/system01.dbf
4 Full 648546 2015-03-31 11:21:32 /u04/oradata/oradg10g/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
12 Full 6.80M DISK 00:00:01 2015-03-31 11:22:35
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20150331T112234
Piece Name: /u03/app/oracle/flash_recovery_area/ORADG10G/autobackup/2015_03_31/o1_mf_s_875791354_bkn4qv0l_.bkp
Control File Included: Ckp SCN: 648574 Ckp time: 2015-03-31 11:22:34
SPFILE Included: Modification time: 2015-03-30 17:30:55
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
13 5.71M DISK 00:00:02 2015-03-31 11:22:42
BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20150331T112238
Piece Name: /u04/backup/arch_0dq37100_1_1_20150331.bak
List of Archived Logs in backup set 13
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 3 638058 2015-03-30 17:19:27 641681 2015-03-30 17:32:38
1 4 641681 2015-03-30 17:32:38 641933 2015-03-30 17:42:58
1 5 641933 2015-03-30 17:42:58 643387 2015-03-30 18:17:45
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
14 5.05M DISK 00:00:02 2015-03-31 11:22:42
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20150331T112238
Piece Name: /u04/backup/arch_0eq37100_1_1_20150331.bak
List of Archived Logs in backup set 14
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 6 643387 2015-03-30 18:17:45 643429 2015-03-30 18:18:41
1 7 643429 2015-03-30 18:18:41 643434 2015-03-30 18:18:41
1 8 643434 2015-03-30 18:18:41 648536 2015-03-31 11:21:30
1 9 648536 2015-03-31 11:21:30 648585 2015-03-31 11:22:38
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
15 2.00K DISK 00:00:01 2015-03-31 11:22:43
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20150331T112238
Piece Name: /u04/backup/arch_0fq37102_1_1_20150331.bak
List of Archived Logs in backup set 15
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 10 648585 2015-03-31 11:22:38 648590 2015-03-31 11:22:38
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
16 Full 6.80M DISK 00:00:00 2015-03-31 11:22:44
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20150331T112244
Piece Name: /u03/app/oracle/flash_recovery_area/ORADG10G/autobackup/2015_03_31/o1_mf_s_875791364_bkn4r4nw_.bkp
Control File Included: Ckp SCN: 648597 Ckp time: 2015-03-31 11:22:44
SPFILE Included: Modification time: 2015-03-30 17:30:55
RMAN>
備份過程中的告警日誌：
Tue Mar 31 11:21:30 2015
ALTER SYSTEM ARCHIVE LOG
Tue Mar 31 11:21:30 2015
Thread 1 advanced to log sequence 9 (LGWR switch)
Current log# 3 seq# 9 mem# 0: /u04/oradata/oradg10g/redo03.log
Tue Mar 31 11:21:30 2015
Clearing standby activation ID 1480716819 (0x5841f213)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Tue Mar 31 11:22:34 2015
Starting control autobackup
Control autobackup written to DISK device
handle '/u03/app/oracle/flash_recovery_area/ORADG10G/autobackup/2015_03_31/o1_mf_s_875791354_bkn4qv0l_.bkp'
Tue Mar 31 11:22:38 2015
ALTER SYSTEM ARCHIVE LOG
Tue Mar 31 11:22:38 2015
Thread 1 advanced to log sequence 10 (LGWR switch)
Current log# 1 seq# 10 mem# 0: /u04/oradata/oradg10g/redo01.log
Tue Mar 31 11:22:38 2015
ALTER SYSTEM ARCHIVE LOG
Tue Mar 31 11:22:38 2015
Thread 1 advanced to log sequence 11 (LGWR switch)
Current log# 2 seq# 11 mem# 0: /u04/oradata/oradg10g/redo02.log
Tue Mar 31 11:22:44 2015
Starting control autobackup
Control autobackup written to DISK device
handle '/u03/app/oracle/flash_recovery_area/ORADG10G/autobackup/2015_03_31/o1_mf_s_875791364_bkn4r4nw_.bkp'
Tue Mar 31 11:22:47 2015
alter database create standby controlfile as '/u04/backup/control01.ctl' reuse
Tue Mar 31 11:22:47 2015
Clearing standby activation ID 1480716819 (0x5841f213)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Completed: alter database create standby controlfile as '/u04/backup/control01.ctl' reuse
1.1.5 修改主庫引數檔案
--使用下面的命令修改主庫引數(此時主庫應當使用spfile啟動引數)
--修改primary端引數，加入歸檔日誌傳輸、檔案自動管理和命名轉換引數
alter system set db_unique_name='oradg10g' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(oradg10g,orawldg,oraljdg)';
alter system set log_archive_dest_1='LOCATION=/u04/arch/oradg10g db_unique_name=oradg10g valid_for=(ALL_LOGFILES,ALL_ROLES)';
alter system set log_archive_dest_2='SERVICE=orawldg LGWR ASYNC db_unique_name=orawldg valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)';
alter system set log_archive_dest_3='SERVICE=oraljdg LGWR ASYNC db_unique_name=oraljdg 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_dest_state_3=enable;
alter system set log_archive_max_processes=4;
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
alter system set standby_file_management='AUTO';
alter system set db_file_name_convert='oradg10g','orawldg' scope=spfile;
alter system set log_file_name_convert='oradg10g','orawldg' scope=spfile;
alter system set fal_server='orawldg';
alter system set fal_client='oradg10g';
1.1.6 配置備庫密碼檔案及引數檔案
--由於要求主庫與備庫sys使用相同的密碼，在此處，我們直接複製了主庫的密碼檔案到備庫，而且db_name必須相同
[oracle@rhel6_lhr dbs]$ cp $ORACLE_HOME/dbs/orapworadg10g $ORACLE_HOME/dbs/orapworawldg
[oracle@rhel6_lhr dbs]$ cp $ORACLE_HOME/dbs/orapworadg10g $ORACLE_HOME/dbs/orapworaljdg
[oracle@rhel6_lhr dbs]$
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 30 18:31:27 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile='?/dbs/initorawldg.ora' from spfile;
File created.
SQL> create pfile='?/dbs/initoraljdg.ora' from spfile;
File created.
SQL>
[oracle@rhel6_lhr dbs]$ mkdir -p /u03/app/oracle/admin/orawldg/adump
[oracle@rhel6_lhr dbs]$ mkdir -p /u03/app/oracle/admin/orawldg/bdump
[oracle@rhel6_lhr dbs]$ mkdir -p /u03/app/oracle/admin/orawldg/cdump
[oracle@rhel6_lhr dbs]$ mkdir -p /u03/app/oracle/admin/orawldg/udump
[oracle@rhel6_lhr dbs]$
[oracle@rhel6_lhr dbs]$
[oracle@rhel6_lhr dbs]$ mkdir -p /u03/app/oracle/admin/oraljdg/adump
[oracle@rhel6_lhr dbs]$ mkdir -p /u03/app/oracle/admin/oraljdg/bdump
[oracle@rhel6_lhr dbs]$ mkdir -p /u03/app/oracle/admin/oraljdg/cdump
[oracle@rhel6_lhr dbs]$ mkdir -p /u03/app/oracle/admin/oraljdg/udump
[oracle@rhel6_lhr dbs]$
[oracle@rhel6_lhr dbs]$
一、 配置物理備庫引數檔案
--紅色字型是需要建立相關路徑或修改相關路徑
--黃色背景是需要注意的地方
[oracle@rhel6_lhr dbs]$ more initorawldg.ora
*.audit_file_dest='/u03/app/oracle/admin/orawldg/adump'
*.background_dump_dest='/u03/app/oracle/admin/orawldg/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u04/oradata/orawldg/control01.ctl','/u04/oradata/orawldg/control02.ctl','/u04/oradata/orawldg/control03.ctl'
*.core_dump_dest='/u03/app/oracle/admin/orawldg/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='oradg10g','orawldg'
*.db_name='oradg10g'
*.db_recovery_file_dest='/u03/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='orawldg'
*.fal_client='orawldg'
*.fal_server='oradg10g'
*.job_queue_processes=50
*.log_archive_config='DG_CONFIG=(oradg10g,orawldg,oraljdg)'
*.log_archive_dest_1='location=/u04/arch/orawldg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orawldg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='log_%t_%s_%r.arc'
*.log_file_name_convert='oradg10g','orawldg'
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=200M
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u03/app/oracle/admin/orawldg/udump'
二、 配置邏輯備庫引數檔案
[oracle@rhel6_lhr dbs]$ more initoraljdg.ora
*.audit_file_dest='/u03/app/oracle/admin/oraljdg/adump'
*.background_dump_dest='/u03/app/oracle/admin/oraljdg/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u04/oradata/oraljdg/control01.ctl','/u04/oradata/oraljdg/control02.ctl','/u04/oradata/oraljdg/control03.ctl'
*.core_dump_dest='/u03/app/oracle/admin/oraljdg/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='oradg10g','oraljdg'
*.db_name='oradg10g'
*.db_recovery_file_dest='/u03/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='oraljdg'
*.fal_client='oraljdg'
*.fal_server='oradg10g'
*.job_queue_processes=50
*.log_archive_config='DG_CONFIG=(oradg10g,orawldg,oraljdg)'
*.log_archive_dest_1='location=/u04/arch/oraljdg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oraljdg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='log_%t_%s_%r.arc'
*.log_file_name_convert='oradg10g','oraljdg'
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=200M
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u03/app/oracle/admin/oraljdg/udump'
三、 建立物理和邏輯備庫的相關路徑
---物理dg路徑
mkdir -p /u03/app/oracle/admin/orawldg/adump
mkdir -p /u03/app/oracle/admin/orawldg/bdump
mkdir -p /u03/app/oracle/admin/orawldg/cdump
mkdir -p /u03/app/oracle/admin/orawldg/udump
mkdir -p /u04/oradata/orawldg/
mkdir -p /u04/arch/orawldg
--邏輯dg路徑
mkdir -p /u03/app/oracle/admin/oraljdg/adump
mkdir -p /u03/app/oracle/admin/oraljdg/bdump
mkdir -p /u03/app/oracle/admin/oraljdg/cdump
mkdir -p /u03/app/oracle/admin/oraljdg/udump
mkdir -p /u04/oradata/oraljdg/
mkdir -p /u04/arch/oraljdg/
四、 控制檔案
按照引數檔案中的定義將控制檔案複製到相關路徑：
---複製物理備庫控制檔案
[oracle@rhel6_lhr backup]$ cp /u04/backup/control01.ctl /u04/oradata/orawldg/control01.ctl
[oracle@rhel6_lhr backup]$ cp /u04/backup/control01.ctl /u04/oradata/orawldg/control02.ctl
[oracle@rhel6_lhr backup]$ cp /u04/backup/control01.ctl /u04/oradata/orawldg/control03.ctl
[oracle@rhel6_lhr backup]$
--複製邏輯備庫控制檔案
[oracle@rhel6_lhr backup]$ cp /u04/backup/control01.ctl /u04/oradata/oraljdg/control01.ctl
[oracle@rhel6_lhr backup]$ cp /u04/backup/control01.ctl /u04/oradata/oraljdg/control02.ctl
[oracle@rhel6_lhr backup]$ cp /u04/backup/control01.ctl /u04/oradata/oraljdg/control03.ctl
[oracle@rhel6_lhr backup]$
1.1.7 搭建物理備庫一
[oracle@rhel6_lhr ~]$ ORACLE_SID=orawldg
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 31 12:57:38 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create spfile from pfile='?/dbs/initorawldg.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 159383552 bytes
Fixed Size 2082400 bytes
Variable Size 75499936 bytes
Database Buffers 75497472 bytes
Redo Buffers 6303744 bytes
SQL> alter database mount standby database;
Database altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhel6_lhr ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Mar 31 13:04:52 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORADG10G (DBID=1480747539, not open)
RMAN> restore database;
Starting restore at 2015-03-31 13:05:14
Starting implicit crosscheck backup at 2015-03-31 13:05:14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 2015-03-31 13:05:15
Starting implicit crosscheck copy at 2015-03-31 13:05:15
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 2015-03-31 13:05:15
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u04/oradata/orawldg/undotbs01.dbf
restoring datafile 00003 to /u04/oradata/orawldg/sysaux01.dbf
restoring datafile 00005 to /u04/oradata/orawldg/example01.dbf
channel ORA_DISK_1: reading from backup piece /u04/backup/oradg_0bq370ts_1_1_20150331.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/backup/oradg_0bq370ts_1_1_20150331.bak tag=TAG20150331T112131
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u04/oradata/orawldg/system01.dbf
restoring datafile 00004 to /u04/oradata/orawldg/users01.dbf
channel ORA_DISK_1: reading from backup piece /u04/backup/oradg_0aq370ts_1_1_20150331.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/backup/oradg_0aq370ts_1_1_20150331.bak tag=TAG20150331T112131
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
Finished restore at 2015-03-31 13:06:11
RMAN>
-----告警日誌
Tue Mar 31 13:04:23 2015
alter database mount standby database
Tue Mar 31 13:04:27 2015
Setting recovery target incarnation to 2
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=14, OS id=23316
Tue Mar 31 13:04:28 2015
ARC0: Archival started
ARC1: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Tue Mar 31 13:04:28 2015
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC0: Thread not mounted
ARC1 started with pid=15, OS id=23318
ARC1: Becoming the heartbeat ARCH
ARC1: Thread not mounted
Tue Mar 31 13:04:28 2015
Successful mount of redo thread 1, with mount id 1480784407
Tue Mar 31 13:04:28 2015
Physical Standby Database mounted.
Completed: alter database mount standby database
Tue Mar 31 13:05:18 2015
Full restore complete of datafile 2 /u04/oradata/orawldg/undotbs01.dbf. Elapsed time: 0:00:01
checkpoint is 648547
last deallocation scn is 647928
Full restore complete of datafile 5 /u04/oradata/orawldg/example01.dbf. Elapsed time: 0:00:02
checkpoint is 648547
last deallocation scn is 617217
Tue Mar 31 13:05:38 2015
Full restore complete of datafile 3 /u04/oradata/orawldg/sysaux01.dbf. Elapsed time: 0:00:19
checkpoint is 648547
last deallocation scn is 638875
Full restore complete of datafile 4 /u04/oradata/orawldg/users01.dbf. Elapsed time: 0:00:00
checkpoint is 648546
Tue Mar 31 13:06:08 2015
Full restore complete of datafile 1 /u04/oradata/orawldg/system01.dbf. Elapsed time: 0:00:22
checkpoint is 648546
last deallocation scn is 640406
檢視後續的告警日誌，如果standby logfile沒有的話就需要為物理備庫新增standby log，該步驟可選：
alter database add standby logfile
group 4 ('/u04/oradata/orawldg/standby_redo04.log') size 50m,
group 5 ('/u04/oradata/orawldg/standby_redo05.log') size 50m,
group 6 ('/u04/oradata/orawldg/standby_redo06.log') size 50m,
group 7 ('/u04/oradata/orawldg/standby_redo07.log') size 50m;
--告警日誌
Tue Mar 31 16:05:54 2015
Errors in file /u03/app/oracle/admin/oraljdg/udump/oraljdg_rfs_11868.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u04/oradata/oraljdg/standby_redo05.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RFS[4]: Unable to open standby log 5: 313
Tue Mar 31 16:05:54 2015
Errors in file /u03/app/oracle/admin/oraljdg/udump/oraljdg_rfs_11868.trc:
ORA-00313: open failed for members of log group 6 of thread 1
ORA-00312: online log 6 thread 1: '/u04/oradata/oraljdg/standby_redo06.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Mar 31 16:05:54 2015
Errors in file /u03/app/oracle/admin/oraljdg/udump/oraljdg_rfs_11868.trc:
ORA-00313: open failed for members of log group 6 of thread 1
ORA-00312: online log 6 thread 1: '/u04/oradata/oraljdg/standby_redo06.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RFS[4]: Unable to open standby log 6: 313
Tue Mar 31 16:05:54 2015
Errors in file /u03/app/oracle/admin/oraljdg/udump/oraljdg_rfs_11868.trc:
ORA-00313: open failed for members of log group 7 of thread 1
ORA-00312: online log 7 thread 1: '/u04/oradata/oraljdg/standby_redo07.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
一、 校驗結果
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string oradg10g, orawldg
db_name string oradg10g
db_unique_name string orawldg
global_names boolean FALSE
instance_name string orawldg
lock_name_space string
log_file_name_convert string oradg10g, orawldg
service_names string orawldg
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL>
以只讀方式開啟資料庫，oracle知道我們在備用資料庫控制檔案中進行裝載，所以當開啟資料時，他將自動置於只讀模式。
----告警日誌
Tue Mar 31 14:18:11 2015
alter database open
Tue Mar 31 14:18:11 2015
SMON: enabling cache recovery
Tue Mar 31 14:18:12 2015
Re-creating tempfile /u04/oradata/orawldg/temp01.dbf
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 2
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open
SQL> select * from lhr.test ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 52428800 1 YES CLEARING 651380 31-MAR-15
2 1 14 52428800 1 YES CLEARING 648590 31-MAR-15
3 1 15 52428800 1 YES CLEARING_CURRENT 672286 31-MAR-15
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
4 1480747539 1 23 52428800 39424 YES ACTIVE 676992 31-MAR-15 677115 31-MAR-15
5 UNASSIGNED 1 0 52428800 512 NO UNASSIGNED 0 0
6 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
7 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u04/arch/orawldg
Oldest online log sequence 21
Next log sequence to archive 0
Current log sequence 23
SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- -------------------------------------------------- ----------- -------------------- -------------------- ---------------- --- ---------- --------------------
1480747539 ORADG10G 672285 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED
SQL>
為了實時查詢，啟用管理恢復，在open狀態下執行後，資料庫將自動由open變為mount狀態
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- -------------------------------------------------- ----------- -------------------- -------------------- ---------------- --- ---------- --------------------
1480747539 ORADG10G 672285 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED SESSIONS ACTIVE
SQL>
---告警日誌
Tue Mar 31 14:23:07 2015
alter database recover managed standby database using current logfile disconnect
Tue Mar 31 14:23:07 2015
Stopping Job queue slave processes, flags = 27
Tue Mar 31 14:23:07 2015
Stopping background process CJQ0
Tue Mar 31 14:23:07 2015
Job queue slave processes stopped
Tue Mar 31 14:23:07 2015
SMON: disabling cache recovery
Tue Mar 31 14:23:07 2015
Attempt to start background Managed Standby Recovery process (orawldg)
MRP0 started with pid=10, OS id=6326
Tue Mar 31 14:23:07 2015
MRP0: Background Managed Standby Recovery process started (orawldg)
Managed Standby Recovery starting Real Time Apply
parallel recovery started with 2 processes
Tue Mar 31 14:23:12 2015
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 15
Tue Mar 31 14:23:13 2015
Completed: alter database recover managed standby database using current logfile disconnect
至此，物理備庫一搭建完成。
1.1.8 搭建物理備庫二
[oracle@rhel6_lhr dbs]$ ORACLE_SID=oraljdg
[oracle@rhel6_lhr dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 31 15:32:55 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create spfile from pfile='?/dbs/initoraljdg.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2082784 bytes
Variable Size 79693856 bytes
Database Buffers 121634816 bytes
Redo Buffers 6303744 bytes
SQL> alter database mount standby database;
Database altered.
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhel6_lhr dbs]$
[oracle@rhel6_lhr dbs]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Mar 31 15:39:16 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORADG10G (DBID=1480747539, not open)
RMAN> restore database;
Starting restore at 2015-03-31 15:39:22
Starting implicit crosscheck backup at 2015-03-31 15:39:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 2015-03-31 15:39:24
Starting implicit crosscheck copy at 2015-03-31 15:39:24
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 2015-03-31 15:39:24
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u04/oradata/oraljdg/undotbs01.dbf
restoring datafile 00003 to /u04/oradata/oraljdg/sysaux01.dbf
restoring datafile 00005 to /u04/oradata/oraljdg/example01.dbf
channel ORA_DISK_1: reading from backup piece /u04/backup/oradg_0bq370ts_1_1_20150331.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/backup/oradg_0bq370ts_1_1_20150331.bak tag=TAG20150331T112131
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u04/oradata/oraljdg/system01.dbf
restoring datafile 00004 to /u04/oradata/oraljdg/users01.dbf
channel ORA_DISK_1: reading from backup piece /u04/backup/oradg_0aq370ts_1_1_20150331.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/backup/oradg_0aq370ts_1_1_20150331.bak tag=TAG20150331T112131
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
Finished restore at 2015-03-31 15:40:18
RMAN>
-----告警日誌
Tue Mar 31 15:37:02 2015
Using STANDBY_ARCHIVE_DEST parameter default value as /u04/arch/oraljdg
destination database instance is 'started' not 'mounted'
Tue Mar 31 15:38:31 2015
alter database mount standby database
Tue Mar 31 15:38:35 2015
Setting recovery target incarnation to 2
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=14, OS id=10354
Tue Mar 31 15:38:35 2015
ARC0: Archival started
ARC1: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=15, OS id=10356
Tue Mar 31 15:38:35 2015
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC0: Thread not mounted
Tue Mar 31 15:38:35 2015
ARC1: Becoming the heartbeat ARCH
ARC1: Thread not mounted
Tue Mar 31 15:38:35 2015
Successful mount of redo thread 1, with mount id 1480822583
Tue Mar 31 15:38:35 2015
Physical Standby Database mounted.
Completed: alter database mount standby database
Tue Mar 31 15:39:28 2015
Full restore complete of datafile 2 /u04/oradata/oraljdg/undotbs01.dbf. Elapsed time: 0:00:01
checkpoint is 648547
last deallocation scn is 647928
Full restore complete of datafile 5 /u04/oradata/oraljdg/example01.dbf. Elapsed time: 0:00:06
checkpoint is 648547
last deallocation scn is 617217
Tue Mar 31 15:39:46 2015
Full restore complete of datafile 3 /u04/oradata/oraljdg/sysaux01.dbf. Elapsed time: 0:00:19
checkpoint is 648547
last deallocation scn is 638875
Full restore complete of datafile 4 /u04/oradata/oraljdg/users01.dbf. Elapsed time: 0:00:00
checkpoint is 648546
Tue Mar 31 15:40:13 2015
Full restore complete of datafile 1 /u04/oradata/oraljdg/system01.dbf. Elapsed time: 0:00:20
checkpoint is 648546
last deallocation scn is 640406
Tue Mar 31 15:42:04 2015
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 10560
RFS[1]: Identified database type as 'physical standby'
Tue Mar 31 15:42:04 2015
RFS LogMiner: Client disabled from further notification
Tue Mar 31 15:42:04 2015
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 10562
RFS[2]: Identified database type as 'physical standby'
Tue Mar 31 15:42:04 2015
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 10564
RFS[3]: Identified database type as 'physical standby'
Tue Mar 31 15:42:06 2015
RFS[1]: Archived Log: '/u04/arch/oraljdg/log_1_13_875726293.arc'
Tue Mar 31 15:42:06 2015
RFS[2]: Archived Log: '/u04/arch/oraljdg/log_1_12_875726293.arc'
Tue Mar 31 15:42:06 2015
RFS[3]: Archived Log: '/u04/arch/oraljdg/log_1_11_875726293.arc'
Tue Mar 31 15:42:06 2015
RFS[2]: Archived Log: '/u04/arch/oraljdg/log_1_16_875726293.arc'
Tue Mar 31 15:42:06 2015
RFS[3]: Archived Log: '/u04/arch/oraljdg/log_1_15_875726293.arc'
Tue Mar 31 15:42:06 2015
RFS[1]: Archived Log: '/u04/arch/oraljdg/log_1_14_875726293.arc'
Tue Mar 31 15:42:06 2015
RFS[2]: Archived Log: '/u04/arch/oraljdg/log_1_17_875726293.arc'
Tue Mar 31 15:42:06 2015
RFS[1]: Archived Log: '/u04/arch/oraljdg/log_1_18_875726293.arc'
Tue Mar 31 15:42:06 2015
RFS[3]: Archived Log: '/u04/arch/oraljdg/log_1_19_875726293.arc'
Tue Mar 31 15:42:06 2015
RFS[2]: Archived Log: '/u04/arch/oraljdg/log_1_20_875726293.arc'
檢視後續的告警日誌，如果standby logfile沒有的話就需要為物理備庫新增standby log，該步驟可選：
alter database add standby logfile
group 4 ('/u04/oradata/oraljdg/standby_redo04.log') size 50m,
group 5 ('/u04/oradata/oraljdg/standby_redo05.log') size 50m,
group 6 ('/u04/oradata/oraljdg/standby_redo06.log') size 50m,
group 7 ('/u04/oradata/oraljdg/standby_redo07.log') size 50m;
SQL> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2082784 bytes
Variable Size 79693856 bytes
Database Buffers 121634816 bytes
Redo Buffers 6303744 bytes
Database mounted.
SQL> alter database add standby logfile
2 group 4 ('/u04/oradata/oraljdg/standby_redo04.log') size 50m,
3 group 5 ('/u04/oradata/oraljdg/standby_redo05.log') size 50m,
4 group 6 ('/u04/oradata/oraljdg/standby_redo06.log') size 50m,
5 group 7 ('/u04/oradata/oraljdg/standby_redo07.log') size 50m;
Database altered.
SQL>
--告警日誌
Tue Mar 31 16:05:54 2015
Errors in file /u03/app/oracle/admin/oraljdg/udump/oraljdg_rfs_11868.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u04/oradata/oraljdg/standby_redo05.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RFS[4]: Unable to open standby log 5: 313
Tue Mar 31 16:05:54 2015
Errors in file /u03/app/oracle/admin/oraljdg/udump/oraljdg_rfs_11868.trc:
ORA-00313: open failed for members of log group 6 of thread 1
ORA-00312: online log 6 thread 1: '/u04/oradata/oraljdg/standby_redo06.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Mar 31 16:05:54 2015
Errors in file /u03/app/oracle/admin/oraljdg/udump/oraljdg_rfs_11868.trc:
ORA-00313: open failed for members of log group 6 of thread 1
ORA-00312: online log 6 thread 1: '/u04/oradata/oraljdg/standby_redo06.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RFS[4]: Unable to open standby log 6: 313
Tue Mar 31 16:05:54 2015
Errors in file /u03/app/oracle/admin/oraljdg/udump/oraljdg_rfs_11868.trc:
ORA-00313: open failed for members of log group 7 of thread 1
ORA-00312: online log 7 thread 1: '/u04/oradata/oraljdg/standby_redo07.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
一、 校驗結果
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string oradg10g, oraljdg
db_name string oradg10g
db_unique_name string oraljdg
global_names boolean FALSE
instance_name string oraljdg
lock_name_space string
log_file_name_convert string oradg10g, oraljdg
service_names string oraljdg
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL>
以只讀方式開啟資料庫，oracle知道我們在備用資料庫控制檔案中進行裝載，所以當開啟資料時，他將自動置於只讀模式。
----告警日誌
Tue Mar 31 14:18:11 2015
alter database open
Tue Mar 31 14:18:11 2015
SMON: enabling cache recovery
Tue Mar 31 14:18:12 2015
Re-creating tempfile /u04/oradata/orawldg/temp01.dbf
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 2
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open
SQL> select * from lhr.test ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 52428800 1 YES CLEARING 651380 31-MAR-15
2 1 14 52428800 1 YES CLEARING 648590 31-MAR-15
3 1 15 52428800 1 YES CLEARING_CURRENT 672286 31-MAR-15
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
4 1480747539 1 23 52428800 36352 YES ACTIVE 676992 31-MAR-15 677103 31-MAR-15
5 UNASSIGNED 1 0 52428800 512 NO UNASSIGNED 0 0
6 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
7 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u04/arch/orawldg
Oldest online log sequence 21
Next log sequence to archive 0
Current log sequence 23
SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- -------------------------------------------------- ----------- -------------------- -------------------- ---------------- --- ---------- --------------------
1480747539 ORADG10G 672285 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED
SQL>
為了實時查詢，啟用管理恢復，在open狀態下執行後，資料庫將自動由open變為mount狀態
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- -------------------------------------------------- ----------- -------------------- -------------------- ---------------- --- ---------- --------------------
1480747539 ORADG10G 672285 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED SESSIONS ACTIVE
SQL>
---告警日誌
Tue Mar 31 14:23:07 2015
alter database recover managed standby database using current logfile disconnect
Tue Mar 31 14:23:07 2015
Stopping Job queue slave processes, flags = 27
Tue Mar 31 14:23:07 2015
Stopping background process CJQ0
Tue Mar 31 14:23:07 2015
Job queue slave processes stopped
Tue Mar 31 14:23:07 2015
SMON: disabling cache recovery
Tue Mar 31 14:23:07 2015
Attempt to start background Managed Standby Recovery process (orawldg)
MRP0 started with pid=10, OS id=6326
Tue Mar 31 14:23:07 2015
MRP0: Background Managed Standby Recovery process started (orawldg)
Managed Standby Recovery starting Real Time Apply
parallel recovery started with 2 processes
Tue Mar 31 14:23:12 2015
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 15
Tue Mar 31 14:23:13 2015
Completed: alter database recover managed standby database using current logfile disconnect
至此，物理備庫二搭建完成。
1.2 把物理備庫二轉化為邏輯備庫
1.2.1 確定哪些表不支援
以下sql可以確定哪些表不支援(不被支援的表通常是由於列的定義包含了不支援的資料型別)：
SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
2 WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
3 AND BAD_COLUMN = 'Y';
OWNER TABLE_NAME
------------------------------ ------------------------------
TSMSYS SRS$
SQL>
檢視主庫是否啟用了supplemental logging特性，如果未啟動，需要開啟：
SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database;
SUP SUP
--- ---
NO NO
SQL> alter database add supplemental log data (PRIMARY KEY,unique INDEX) columns;
Database altered.
SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database;
SUP SUP
--- ---
YES YES
SQL>
1.2.2 停用備庫MRP程式
對於將物理備庫切換到邏輯備庫，我們需要在主庫構建LogMiner字典及啟用補充日誌，因此應先停用邏輯備庫的MRP程式，避免產生額外的redo apply, 邏輯備用資料庫在後臺使用LogMiner來提取生成SQL Apply事務必須的重做資料，在建立Log Miner字典之前，我們必須停止備用資料庫上的管理恢復，以確保我們只應用包含LogMiner字典的重做資料：
如果正在使用Broker管理現有的物理備庫，應先在Broker中禁用目標資料庫。
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string oradg10g, oraljdg
db_name string oradg10g
db_unique_name string oraljdg
global_names boolean FALSE
instance_name string oraljdg
lock_name_space string
log_file_name_convert string oradg10g, oraljdg
service_names string oraljdg
SQL> alter database recover managed standby database cancel;
Database altered.
1.2.3 主庫構建LogMiner字典
這裡字典表空間儘量大一點，不然邏輯dg搭建可能不能同步，build過程會等到所有事務都完成，這個步驟需要使用閃回查詢技術對資料字典資訊執行一致性讀，故undo_retention需要設定時間長一些，3600以上，不然可能碰到ora-1555。
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u04/oradata/oradg10g/system01.dbf
/u04/oradata/oradg10g/undotbs01.dbf
/u04/oradata/oradg10g/sysaux01.dbf
/u04/oradata/oradg10g/users01.dbf
/u04/oradata/oradg10g/example01.dbf
SQL> create tablespace logmnrtbs datafile '/u04/oradata/oradg10g/logmnrtbs1.dbf' size 100m autoextend on next 5m maxsize 2000m;
Tablespace created.
SQL> execute dbms_logmnr_d.set_tablespace('logmnrtbs');
PL/SQL procedure successfully completed.
SQL> exec dbms_logstdby.build;
PL/SQL procedure successfully completed.
SQL>
SQL> alter system archive log current;
System altered.
SQL>
1.2.4 把物理備庫二恢復為邏輯備庫
SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- -------------------------------------------------- ----------- -------------------- -------------------- ---------------- --- ---------- --------------------
1480747539 ORADG10G 677586 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES MOUNTED NOT ALLOWED
SQL> show paramter name
SP2-0158: unknown SHOW option "paramter"
SP2-0158: unknown SHOW option "name"
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string oradg10g, oraljdg
db_name string oradg10g
db_unique_name string oraljdg
global_names boolean FALSE
instance_name string oraljdg
lock_name_space string
log_file_name_convert string oradg10g, oraljdg
service_names string oraljdg
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY oraljdg parallel 10;
ALTER DATABASE RECOVER TO LOGICAL STANDBY oraljdg parallel 10
*
ERROR at line 1:
ORA-19953: database should not be open
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2082784 bytes
Variable Size 104859680 bytes
Database Buffers 96468992 bytes
Redo Buffers 6303744 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY oraljdg parallel 10;
Database altered.
SQL>
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2082784 bytes
Variable Size 104859680 bytes
Database Buffers 96468992 bytes
Redo Buffers 6303744 bytes
Database mounted.
SQL>
SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- -------------------------------------------------- ----------- -------------------- -------------------- ---------------- --- ---------- --------------------
4072027430 ORALJDG 0 MAXIMUM PERFORMANCE UNPROTECTED LOGICAL STANDBY YES MOUNTED NOT ALLOWED
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u04/arch/oraljdg
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string oradg10g, oraljdg
db_name string ORALJDG
db_unique_name string oraljdg
global_names boolean FALSE
instance_name string oraljdg
lock_name_space string
log_file_name_convert string oradg10g, oraljdg
service_names string oraljdg
SQL>
---告警日誌
Tue Mar 31 17:24:54 2015
ALTER DATABASE RECOVER TO LOGICAL STANDBY oraljdg parallel 10
*** DBNEWID utility started ***
DBID will be changed from 1480747539 to new DBID of 4072027430 for database ORADG10G
DBNAME will be changed from ORADG10G to new DBNAME of ORALJDG
Starting datafile conversion
Setting recovery target incarnation to 1
Datafile conversion complete
Database name changed to ORALJDG.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORALJDG changed to 4072027430.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open with RESETLOGS option.
Succesfully changed database name and ID.
*** DBNEWID utility finished succesfully ***
Completed: ALTER DATABASE RECOVER TO LOGICAL STANDBY oraljdg parallel 10
1.2.5 resetlogs開啟資料庫並啟用SQL應用
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL>
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL>
---告警日誌
Tue Mar 31 17:27:23 2015
Setting recovery target incarnation to 1
Tue Mar 31 17:27:23 2015
Successful mount of redo thread 1, with mount id 4071969207
Tue Mar 31 17:27:23 2015
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Tue Mar 31 17:28:33 2015
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 16192
RFS[1]: Identified database type as 'logical standby'
Tue Mar 31 17:28:33 2015
RFS LogMiner: Client enabled and ready for notification
Tue Mar 31 17:33:02 2015
ALTER DATABASE OPEN RESETLOGS
Tue Mar 31 17:33:02 2015
RESETLOGS after complete recovery through change 682393
Tue Mar 31 17:33:06 2015
Setting recovery target incarnation to 2
Tue Mar 31 17:33:06 2015
Assigning activation ID 4071969207 (0xf2b551b7)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=15, OS id=16448
Tue Mar 31 17:33:07 2015
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Tue Mar 31 17:33:07 2015
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC1 started with pid=16, OS id=16450
ARC1: Becoming the heartbeat ARCH
Tue Mar 31 17:33:08 2015
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u04/oradata/oraljdg/redo01.log
Successful open of redo thread 1
Tue Mar 31 17:33:08 2015
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Mar 31 17:33:08 2015
SMON: enabling cache recovery
Tue Mar 31 17:33:10 2015
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Starting control autobackup
db_recovery_file_dest_size of 2048 MB is 0.33% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Control autobackup written to DISK device
handle '/u03/app/oracle/flash_recovery_area/ORALJDG/autobackup/2015_03_31/o1_mf_s_875813590_bkntgq2r_.bkp'
Tue Mar 31 17:33:11 2015
SMON: enabling tx recovery
Tue Mar 31 17:33:11 2015
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 2
Threshold validation cannot be done before catproc is loaded.
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=17, OS id=16454
Tue Mar 31 17:33:23 2015
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation skipped -- detected logical instantiation
Tue Mar 31 17:33:23 2015
LOGSTDBY: Validation complete
Tue Mar 31 17:33:23 2015
LOGSTDBY: Attempting to pre-register on-disk logfiles
Global Name changed to ORALJDG
Starting control autobackup
Control autobackup written to DISK device
handle '/u03/app/oracle/flash_recovery_area/ORALJDG/autobackup/2015_03_31/o1_mf_s_875813611_bknthckm_.bkp'
Completed: ALTER DATABASE OPEN RESETLOGS
Tue Mar 31 17:33:34 2015
RFS LogMiner: RFS id [16192] assigned as thread [1] PING handler
SQL> alter database start logical standby apply immediate;
Database altered.
SQL>
SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- -------------------------------------------------- ----------- -------------------- -------------------- ---------------- --- ---------- --------------------
4072027430 ORALJDG 684039 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE LOGICAL STANDBY YES READ WRITE NOT ALLOWED
SQL>
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
4 1480747539 1 35 52428800 5938176 YES ACTIVE 683394 31-MAR-15 683592 31-MAR-15
5 UNASSIGNED 1 0 52428800 512 NO UNASSIGNED 0 0
6 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
7 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
至此邏輯備用資料庫以搭建完成。
1.2.6 測試同步
注意：logical standby 是不同步sys的資料的。
該步驟省略。。。。。
...........................................................................................................................................................................................
本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1481972/
QQ：642808185 註明：ITPUB的文章標題
<版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任!>
...........................................................................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28628435/viewspace-1984474/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【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
- 4節點RAC建立邏輯備庫
- rac與邏輯備庫不能自動建表空間，物理備庫正常
- 邏輯備庫Switchover
- 【DataGuard】手工冷備搭建 Oracle 11g DataGuard 物理備庫Oracle
- dataguard回顧之安裝———使用rman建立物理備庫
- dataguard之邏輯備庫移動資料檔案
- 邏輯Data Guard主備庫的轉換
- 一個備份集同時恢出dataguard的主庫&備庫
- 11 管理邏輯備庫
- RMAN備用資料庫(邏輯dataguard) --關於dorecover 的解釋 （2）資料庫
- dataguard 主備庫出現gap
- 【DATAGUARD】DG系列之11g邏輯備庫的搭建
- 邏輯DG主備庫轉換的failoverAI
- [原創]RMAN備用資料庫(邏輯dataguard) --關於dorecover 的解釋資料庫
- 使用RMAN DUPLICATE...FROM ACTIVE DATABASE命令來建立DataGuard物理備庫Database
- DataGuard主備庫切換步驟
- 邏輯備份全庫或者邏輯備份多個使用者的資料
- 【DATAGUARD】DG系列之RACtoONE物理備庫的搭建
- Oracle11g的Dataguard測試，建立物理備庫(Physical Standby Database)OracleDatabase
- 物理備庫互轉快照備庫