Rman配置DataGuardusingBackup-basedduplicationwithatargetconnectionwithfilesystem

一、  环境

主机名

在勐腊等地区,都构建了全面的区域性战略布局,加强发展的系统性、市场前瞻性、产品创新能力,以专注、极致的服务理念,为客户提供网站制作、成都做网站 网站设计制作按需定制,公司网站建设,企业网站建设,品牌网站制作,成都全网营销,外贸网站制作,勐腊网站建设费用合理。

数据库版本

dbname

db_unique_name

IP地址

系统版本

Jason1(主)

 

oracle11204

 

Jason

 

jason1

192.168.1.99

 

rhel6.6_x86_64

jason2(备)

jason2

192.168.1.100

 

二、  主库配置

1. 确定主数据库开启强制LOGGING模式

[oracle@jason1 ~]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 14 20:45:33 2016

 

Copyright (c) 1982, 2013, Oracle. All rights reserved.

 

SQL> conn /as sysdba

Connected.

 

SQL> ALTER DATABASE FORCE LOGGING;

 

Database altered.

 

SQL> select force_logging from v$database;

 

FOR

---

YES

2. 开启归档

SQL> archive log list;

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

SQL> select group#,bytes/1024/1024 from v$log;

 

    GROUP# BYTES/1024/1024

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

         1              50

         3              50

         2              50

3. 创建standby日志组

1).standby redo log的文件大小与primary 数据库online redo log 文件大小相同

2).standby redo log日志文件组的个数依照下面的原则进行计算

    Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数

   例如在我的环境中,只有一个节点,这个节点有三组redo,所以

    Standby redo log组数公式>=(3+1)*1  == 4

   所以需要创建4组Standby redo log

3).每一日志组为了安全起见,可以包含多个成员文件。

查看主数据库的日志组个数与大小,创建standy日志组,大小不能小于在线日志大小。

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/JASON/redo03.log

/u01/app/oracle/oradata/JASON/redo02.log

/u01/app/oracle/oradata/JASON/redo01.log

 

在主数据库创建standby日志组,位置与原日志组相同的路径。

SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/JASON/standby01.log') SIZE 50M;

 

Database altered.

 

SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/JASON/standby02.log') SIZE 50M;

 

Database altered.

 

SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/JASON/standby03.log') SIZE 50M;

 

Database altered.

 

SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/JASON/standby04.log') SIZE 50M;

 

Database altered.

SQL> select group#,status,type,member from v$logfile;

 

    GROUP# STATUS  TYPE   MEMBER

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

         3         ONLINE /u01/app/oracle/oradata/JASON/redo03.log

         2         ONLINE /u01/app/oracle/oradata/JASON/redo02.log

         1         ONLINE /u01/app/oracle/oradata/JASON/redo01.log

         4         STANDBY/u01/app/oracle/oradata/JASON/standby01.log

         5         STANDBY/u01/app/oracle/oradata/JASON/standby02.log

         6         STANDBY /u01/app/oracle/oradata/JASON/standby03.log

         7         STANDBY/u01/app/oracle/oradata/JASON/standby04.log

7 rows selected.

4. 主库参数文件配置

在主库上修改dataguard配置相关的各个参数,各参数的具体含义可以参考oracle在线文档。

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(JASON1,JASON2)'SCOPE=SPFILE;

 

System altered.

 

SQL> alter system set DB_UNIQUE_NAME='JASON1' SCOPE=SPFILE;

 

System altered.

 

SQL> alter system set STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=SPFILE;

 

System altered.

 

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=JASON1' scope=spfile;

 

System altered.

 

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=JASON2 ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=JASON2' scope=spfile;

 

System altered.

 

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=spfile;

 

System altered.

 

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=spfile;

 

System altered.

SQL> alter system  setLOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/JASON','/u01/app/oracle/oradata/JASON' scope=spfile;

 

System altered.

SQL> alter system set FAL_SERVER='JASON2' scope=spfile;

 

System altered.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area 409194496 bytes

Fixed Size                 2253744 bytes

Variable Size            310381648 bytes

Database Buffers          92274688 bytes

Redo Buffers               4284416 bytes

Database mounted.

Database opened.

5. 配置监听及tnsname

创建监听及tnsname.ora,备库监听必须使用静态监听,如下:

[oracle@jason1 admin]$ cat listener.ora

# listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = JASON1)

      (ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = JASON)

    )

  )

 

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.99)(PORT = 1521))

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

 

[oracle@jason1 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

JASON1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.99)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = JASON1)

    )

  )

JASON2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.100)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = JASON2)

    )

  )

[oracle@jason1 admin]$lsnrctl status

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUL-201623:06:17

 

Copyright (c) 1991, 2013, Oracle. All rights reserved.

 

Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jason1)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNRfor Linux: Version 11.2.0.4.0 - Production

Start Date               20-JUL-2016 22:50:04

Uptime                    0 days 0hr. 16 min. 13 sec

Trace Level               off

Security                  ON:Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File        /u01/app/oracle/diag/tnslsnr/jason1/listener/alert/log.xml

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jason1)(PORT=1521)))

Services Summary...

Service "JASON1" has 2 instance(s).

  Instance "JASON",status UNKNOWN, has 1 handler(s) for this service...

  Instance "JASON",status READY, has 1 handler(s) for this service...

Service "JASONXDB" has 1 instance(s).

  Instance "JASON",status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@jason1 admin]$

6. 生成pfile文件,同步相应文件至备库

在主数据库生成pfile文件。

SQL> create pfile from spfile;

File created.

把dbs下的内容同步到standby主机上面,

[oracle@jason1 dbs]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@jason1 dbs]$ scp initJASON.ora orapwJASON 192.168.1.100:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

The authenticity of host '192.168.1.100 (192.168.1.100)' can't beestablished.

RSA key fingerprint is 25:ca:65:90:d3:30:fa:68:ed:11:64:b2:0e:b0:39:a7.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.1.100' (RSA) to the list of knownhosts.

oracle@192.168.1.100's password:   

initJASON.ora                                                                                       100%1415     1.4KB/s   00:00       

orapwJASON                                                                                          100% 1536     1.5KB/s   00:00   

[oracle@jason1 dbs]

7. 备份主库

创建备份存放目录,使用如下脚本备份主库。

mkdir  /data

chown oracle:oinstall /data

 

run

{

backup database include current controlfile

format '/data/fulldb_%U.bak'

plus archivelog

format '/data/arch_%U.bak';

}

三、  备库配置

8. 拷贝数据库备份

[oracle@jason2 oracle]#mkdir  /data

[oracle@jason2 oracle]#chownoracle:oinstall /data

在节点jason1上拷贝数据库备份至备机。

[oracle@jason1 data]$ scp * 192.168.1.100:/data

oracle@192.168.1.100's password:

arch_01rbevvh_1_1.bak                                                                               100%   96MB  23.9MB/s  00:04   

arch_04rbf01l_1_1.bak                                                                               100%   31KB  31.0KB/s  00:00   

fulldb_02rbevvp_1_1.bak                                                                             100% 1035MB  16.4MB/s  01:03   

fulldb_03rbf01i_1_1.bak                                                                             100% 9600KB   9.4MB/s   00:00   

[oracle@jason1 data]$

9. 创建对应目录

备库上创建相关目录

[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/admin/JASON/adump

[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/admin/JASON/dpdump

[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/admin/JASON/pfile

[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/archivelog

[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/oradata/JASON

[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/fast_recovery_area

[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/cfgtoollogs/catbundle

[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/cfgtoollogs/dbca/JASON

[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/cfgtoollogs/emca

[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/cfgtoollogs/netca

[oracle@jason2 oracle]$ ll

total 32

drwxr-xr-x  3 oracle oinstall 4096Jul 14 22:27 admin

drwxr-xr-x  2 oracle oinstall 4096Jul 14 22:28 archivelog

drwxr-xr-x  6 oracle oinstall 4096Jul 14 22:32 cfgtoollogs

drwxr-xr-x  2 oracle oinstall 4096Jul 13 23:32 checkpoints

drwxrwxr-x 11 oracle oinstall 4096 Jul 13 23:06 diag

drwxr-xr-x  2 oracle oinstall 4096Jul 14 22:30 fast_recovery_area

drwxr-xr-x  3 oracle oinstall 4096Jul 14 22:28 oradata

drwxr-xr-x  3 oracle oinstall 4096Jul 13 21:37 product

[oracle@jason2 oracle]$

10.备库参数文件配置

备库上修改初始参数文件,配置DG所需参数如下。

JASON.__db_cache_size=75497472

JASON.__java_pool_size=4194304

JASON.__large_pool_size=71303168

JASON.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

JASON.__pga_aggregate_target=155189248

JASON.__sga_target=255852544

JASON.__shared_io_pool_size=0

JASON.__shared_pool_size=96468992

JASON.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/JASON/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/JASON/control01.ctl','/u01/app/oracle/oradata/JASON/control02.ctl','/u01/app/oracle/oradata/JASON/control03.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='JASON'

*.db_recovery_file_dest_size=4385144832

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.log_file_name_convert='/u01/app/oracle/oradata/JASON','/u01/app/oracle/oradata/JASON'

*.db_unique_name='JASON2'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=JASONXDB)'

*.fal_server='JASON1'

*.log_archive_config='DG_CONFIG=(JASON1,JASON2)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=JASON2'

*.log_archive_dest_2='SERVICE=JASON1 ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=JASON1'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=411041792

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1

11.配置监听

备库监听必须设置为静态监听

[oracle@jason2 admin]$ cat listener.ora

# listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = JASON2)

      (ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = JASON)

    )

  )

 

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.100)(PORT = 1521))

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

 

[oracle@jason2 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

JASON1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.99)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = JASON1)

    )

  )

JASON2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.100)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = JASON2)

    )

  )

12.             创建spfile文件                                                                                

[oracle@jason2 dbs]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 14 23:07:22 2016

 

Copyright (c) 1982, 2013, Oracle. All rights reserved.

 

SQL> conn /as sysdba

Connected to an idle instance.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area 409194496 bytes

Fixed Size                 2253744 bytes

Variable Size            310381648 bytes

Database Buffers           92274688bytes

Redo Buffers               4284416 bytes

SQL> create spfile from pfile;

 

File created.

13.创建备库

将备库启动到nomount状态,然后在备机连接主库进行duplicate操作。

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

 

Total System Global Area 409194496 bytes

Fixed Size                 2253744 bytes

Variable Size            310381648 bytes

Database Buffers          92274688 bytes

Redo Buffers               4284416 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testingoptions

[oracle@jason2 ~]$lsnrctl status

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUL-2016 23:04:56

 

Copyright (c) 1991, 2013, Oracle. All rights reserved.

 

Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jason2)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNRfor Linux: Version 11.2.0.4.0 - Production

Start Date               20-JUL-2016 22:50:42

Uptime                    0 days 0hr. 14 min. 14 sec

Trace Level               off

Security                  ON:Local OS Authentication

SNMP                      OFF

Listener Parameter File  /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File        /u01/app/oracle/diag/tnslsnr/jason2/listener/alert/log.xml

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jason2)(PORT=1521)))

Services Summary...

Service "JASON2" has 2 instance(s).

  Instance "JASON",status UNKNOWN, has 1 handler(s) for this service...

  Instance "JASON",status BLOCKED, has 1 handler(s) for this service...

The command completed successfully

[oracle@jason2 ~]$ rman target sys/system@JASON1 auxiliarysys/system@JASON2

 

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 21 22:48:222016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: JASON (DBID=2141348976)

connected to auxiliary database: JASON (not mounted)

 

RMAN> duplicate target database for standby nofilenamecheck;

 

Starting Duplicate Db at 21-JUL-16

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

 

contents of Memory Script:

{

   restore clone standbycontrolfile;

}

executing Memory Script

 

Starting restore at 21-JUL-16

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece/data/fulldb_03rb9mk0_1_1.bak

channel ORA_AUX_DISK_1: piece handle=/data/fulldb_03rb9mk0_1_1.baktag=TAG20160721T223334

channel ORA_AUX_DISK_1: restored backup piece 1

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

output file name=/u01/app/oracle/oradata/JASON/control01.ctl

output file name=/u01/app/oracle/oradata/JASON/control02.ctl

output file name=/u01/app/oracle/oradata/JASON/control03.ctl

Finished restore at 21-JUL-16

 

contents of Memory Script:

{

   sql clone 'alter database mountstandby database';

}

executing Memory Script

 

sql statement: alter database mount standby database

 

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/JASON/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/JASON/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/JASON/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/JASON/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/JASON/users01.dbf";

   restore

   clone database

   ;

}

executing Memory Script

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /u01/app/oracle/oradata/JASON/temp01.dbf incontrol file

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 21-JUL-16

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backupset

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

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

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

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

channel ORA_AUX_DISK_1: reading from backup piece/data/fulldb_02rb9mhu_1_1.bak

channel ORA_AUX_DISK_1: piece handle=/data/fulldb_02rb9mhu_1_1.baktag=TAG20160721T223334

channel ORA_AUX_DISK_1: restored backup piece 1

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

Finished restore at 21-JUL-16

 

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

 

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=917822987 filename=/u01/app/oracle/oradata/JASON/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=917822987 filename=/u01/app/oracle/oradata/JASON/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=917822987 filename=/u01/app/oracle/oradata/JASON/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=917822987 filename=/u01/app/oracle/oradata/JASON/users01.dbf

Finished Duplicate Db at 21-JUL-16

 

RMAN> exit

Recovery Manager complete.

[oracle@jason2 ~]$

 

14.开启ADG

将备库置于active dataguard模式下。

[oracle@jason2 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 14 23:42:40 2016

 

Copyright (c) 1982, 2013, Oracle. All rights reserved.

 

SQL> conn /as sysdba

Connected.

SQL> alter database open;

 

Database altered.

 

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

 

Database altered.

 

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

 

OPEN_MODE           DATABASE_ROLE    DB_UNIQUE_NAME

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

READ ONLY WITH APPLY PHYSICAL STANDBY JASON2

 

SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE     PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE  MAXIMUMPERFORMANCE

SQL> select status from v$standby_log;

 

STATUS

----------

ACTIVE

UNASSIGNED

UNASSIGNED

UNASSIGNED

SQL> select group#,status,type,member from v$logfile;

  GROUP# STATUS  TYPE   MEMBER

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

         3         ONLINE /u01/app/oracle/oradata/JASON/redo03.log

         2         ONLINE /u01/app/oracle/oradata/JASON/redo02.log

         1         ONLINE /u01/app/oracle/oradata/JASON/redo01.log

         4         STANDBY/u01/app/oracle/oradata/JASON/standby01.log

         5         STANDBY/u01/app/oracle/oradata/JASON/standby02.log

         6         STANDBY/u01/app/oracle/oradata/JASON/standby03.log

         7         STANDBY/u01/app/oracle/oradata/JASON/standby04.log

 

7 rows selected.

SQL>

查看备库数据文件,如下:

[root@jason2 JASON]# ll

total 1744852

-rw-r----- 1 oracle oinstall  9748480 Jul 21 00:11 control01.ctl

-rw-r----- 1 oracle oinstall  9748480 Jul 21 00:11 control02.ctl

-rw-r----- 1 oracle oinstall  9748480 Jul 21 00:11 control03.ctl

-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:06 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:06 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:06 redo03.log

-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:11 standby01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:09 standby02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:07 standby03.log

-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:07 standby04.log

-rw-r----- 1 oracle oinstall 534781952 Jul 21 00:09 sysaux01.dbf

-rw-r----- 1 oracle oinstall 775954432 Jul 21 00:09 system01.dbf

-rw-r----- 1 oracle oinstall 30416896 Jul 21 00:09 temp01.dbf

-rw-r----- 1 oracle oinstall 73408512 Jul 21 00:09 undotbs01.dbf

-rw-r----- 1 oracle oinstall  5251072 Jul 21 00:09 users01.dbf

[root@jason2 JASON]#

主库查看数据库状态

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

 

OPEN_MODE           DATABASE_ROLE    DB_UNIQUE_NAME

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

READ WRITE           PRIMARY          JASON1

 

SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE     PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE  MAXIMUMPERFORMANCE

四、  测试

15.switch_over测试

主库切换

SQL> SELECTSWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

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

TO STANDBY

 

SQL> ALTER DATABASECOMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

 

Database altered.

 

SQL> SELECTSWITCHOVER_STATUS FROM V$DATABASE;

SELECTSWITCHOVER_STATUS FROM V$DATABASE

*

ERROR at line 1:

ORA-01034: ORACLEnot available

Process ID: 2849

Session ID: 44Serial number: 27

 

SQL> startup

ORACLE instancestarted.

 

Total System GlobalArea  409194496 bytes

Fixed Size                  2253744 bytes

Variable Size             322964560 bytes

DatabaseBuffers           79691776 bytes

Redo Buffers                4284416 bytes

Database mounted.

Database opened.

SQL> selectopen_mode,database_role,db_unique_name from v$database;

 

OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME

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

READ ONLY            PHYSICAL STANDBY JASON1

 

 

SQL> ALTERDATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROMSESSION;

 

Database altered.

 

SQL>                   

 

备库切换

SQL> SELECTSWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

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

TO PRIMARY

 

SQL> ALTERDATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

 

Database altered.

 

SQL> selectopen_mode,database_role,db_unique_name from v$database;

 

OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME

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

MOUNTED              PRIMARY          JASON2

 

SQL> alterdatabase open;

 

Database altered.

 

SQL> selectopen_mode,database_role,db_unique_name from v$database;

 

OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME

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

READ WRITE           PRIMARY          JASON2

 

SQL>

五、  报错处理

16.报错

在备库服务器, 添加静态注册信息到 $GRID_HOME/network/listener.ora文件,

这主要是由于AUXILIARY实例启动到nomount状态时,listener无法注册AUXILIARY实例,listener会标志Auxiliary实例为'blocked'状态,因此duplicate命令就无法通过TNS的方式连接到Auxiliary实例,为了解决这个问题,需要先手动静态注册数据库实例到listener上。当Data Guard配置完成后,就可以删除静态注册的配置信息

[oracle@jason2 dbs]$ rman target sys/system@JASON_PD auxiliarysys/system@JASON_SD

 

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 14 23:15:312016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: JASON (DBID=2141348976)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: allappropriate instances are blocking new connections

设置静态监听后状态

[oracle@jason2 dbs]$ lsnrctl status

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JUL-201623:15:50

 

Copyright (c) 1991, 2013, Oracle. All rights reserved.

 

Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNRfor Linux: Version 11.2.0.4.0 - Production

Start Date               14-JUL-2016 22:41:05

Uptime                    0 days 0hr. 34 min. 44 sec

Trace Level               off

Security                  ON:Local OS Authentication

SNMP                      OFF

Listener Parameter File  /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File        /u01/app/oracle/diag/tnslsnr/jason2/listener/alert/log.xml

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))

 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "JASON2" has 1 instance(s).

  Instance "JASON",status BLOCKED, has 1 handler(s) for this service...

The command completed successfully

[oracle@jason2 dbs]$

六、    创建备库时主备库变化

本次实验中使用主库配置后的rman备份进行创建DG。数据库备份结束日志序号为17.然后通过手工切换模拟备份后一天产生的归档日志,最终日志序号为27。以下为主库日志查询结果。

SQL> SELECTSEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

         7 NO

         8 NO

         9 NO

        10 NO

        11 NO

        12 NO

        13 NO

        14 NO

 

 SEQUENCE# APPLIED

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

        15 NO

        16 NO

        17 NO

        18 NO

        19 NO

        20 NO

        21 NO

        22 NO

        23 NO

        24 NO

        25 NO

 

 SEQUENCE# APPLIED

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

        26 NO

        27 NO

24 rows selected.

SQL>

在主库传送rman备份至备库中,备份位置必须与主库相同。然后在备库进行恢复数据库。恢复结束后,执行alter database open命令打开备库。这时从备库日志中可以发现在备库打开时,自备份结束后产生的所有归档日志文件自动传送到备库中。这时再主库再次查看日志会发现日志中显示归档日志已传送,但未应用。如下

SQL> /

 

 SEQUENCE# APPLIED

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

         7 NO

         8 NO

         9 NO

        10 NO

        11 NO

        12 NO

        13 NO

        14 NO

        15 NO

        16 NO

        17 NO

 

 SEQUENCE# APPLIED

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

        17 NO

        18 NO

        18 NO

        19 NO

        19 NO

        20 NO

        20 NO

        21 NO

        21 NO

        22 NO

        22 NO

 

 SEQUENCE# APPLIED

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

        23 NO

        23 NO

        24 NO

        24 NO

        25 NO

        25 NO

        26 NO

        26 NO

        27 NO

        27 NO

        28 NO

 

 SEQUENCE# APPLIED

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

        28 NO

        29 NO

        29 NO

36 rows selected.

 

SQL>

接着在备库开启日志实时应用,执行alter database recover managed standby database using current logfiledisconnect from session;

该命令执行时,在备库日志中可以发现MRP0开始应用所有归档日志进行数据恢复,直到最新日志。具体变化可以查看如下主库与备库的日志。

SQL> /

 

 SEQUENCE# APPLIED

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

         7 NO

         8 NO

         9 NO

        10 NO

        11 NO

        12 NO

        13 NO

        14 NO

        15 NO

        16 NO

        17 NO

 

 SEQUENCE# APPLIED

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

        17 YES

        18 NO

        18 YES

        19 YES

        19 NO

        20 NO

        20 YES

        21 NO

        21 YES

        22 NO

        22 YES

 

 SEQUENCE# APPLIED

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

        23 NO

        23 YES

        24 NO

        24 YES

        25 YES

        25 NO

        26 NO

        26 YES

        27 NO

        27 YES

        28 NO

 

 SEQUENCE# APPLIED

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

        28 YES

        29 NO

        29 NO

 

36 rows selected.

SQL>

主库日志

[root@jason1 trace]# tail -f alert_JASON.log

Thu Jul 21 22:33:24 2016

ALTER SYSTEM ARCHIVE LOG

Thu Jul 21 22:33:24 2016

Thread 1 advanced to log sequence 17 (LGWR switch)

  Current log# 2 seq# 17 mem# 0: /u01/app/oracle/oradata/JASON/redo02.log

Archived Log entry 13 added for thread 1 sequence 16 ID 0x7fa28a70 dest1:

Thu Jul 21 22:33:42 2016

Warning: VKTM detected a time drift.

Time drifts can result in an unexpected behavior such as time-outs.Please check trace file for more details.

Thu Jul 21 22:34:42 2016

ALTER SYSTEM ARCHIVE LOG

Thu Jul 21 22:34:42 2016

Thread 1 advanced to log sequence 18 (LGWR switch)

  Current log# 3 seq# 18 mem# 0:/u01/app/oracle/oradata/JASON/redo03.log

Archived Log entry 14 added for thread 1 sequence 17 ID 0x7fa28a70 dest1:

Thu Jul 21 22:36:46 2016

Starting background process SMCO

Thu Jul 21 22:36:47 2016

SMCO started with pid=33, OS id=2861

Thu Jul 21 22:37:45 2016

Error 1034 received logging on to the standby

PING[ARC2]: Heartbeat failed to connect to standby 'JASON2'. Error is1034.

Thu Jul 21 22:38:45 2016

Error 1034 received logging on to the standby

PING[ARC2]: Heartbeat failed to connect to standby 'JASON2'. Error is1034.


网页标题:Rman配置DataGuardusingBackup-basedduplicationwithatargetconnectionwithfilesystem
地址分享:http://scyanting.com/article/iegopg.html