ITPub博客

首页 > 数据库 > Oracle > 【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库 (三)

【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库 (三)

原创 Oracle 作者:lhrbest 时间:2015-04-01 17:46:20 0 删除 编辑

DATAGUARD】 基于同一个主机建立物理备库和逻辑备库 (三)

blog文档结构图:

wps3D8A.tmp 

 

 

  需求: 在同一台机器配置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