RMAN配置DataGuardfromactivedatabasewithfilesystem

一、环境

主机名

成都创新互联主要从事网页设计、PC网站建设（电脑版网站建设）、wap网站建设（手机版网站建设）、响应式网站、程序开发、网站优化、微网站、微信小程序开发等，凭借多年来在互联网的打拼，我们在互联网网站建设行业积累了丰富的成都网站制作、做网站、外贸营销网站建设、网站设计、网络营销经验，集策划、开发、设计、营销、管理等多方位专业化运作于一体。

数据库版本

dbname

db_unique_name

IP地址

系统版本

Jason1(主)

oracle11204

Jason

jason1

192.168.1.99

rhel6.6_x86_64

jason2(备)

jason2

192.168.1.10

二、主库配置

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

[oracle@jason1 ~]$ sqlplus /nolog

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

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

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 orapwJASON192.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. 创建对应目录

备库上创建相关目录

[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/admin/JASON2/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]$

8. 备库参数文件配置

备库上修改初始参数文件，配置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/JASON2/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 ASYNC VALID_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

9. 配置监听

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

[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)

)

10. 创建spfile文件

[oracle@jason2 dbs]$ sqlplus /nolog

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

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 92274688 bytes

Redo Buffers 4284416 bytes

SQL> create spfile from pfile;

File created.

11.创建备库

将备库启动到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-201623:04:56

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-201622: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 targetsys/system@JASON1 auxiliary sys/system@JASON2

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 21 00:05:082016

connected to target database: JASON (DBID=2141348976)

connected to auxiliary database: JASON (not mounted)

RMAN> duplicate target database for standby nofilenamecheck fromactive database;

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=19 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwJASON' auxiliaryformat

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwJASON' ;

}

executing Memory Script

Starting backup at 21-JUL-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=40 device type=DISK

Finished backup at 21-JUL-16

contents of Memory Script:

{

backup as copy currentcontrolfile for standby auxiliary format '/u01/app/oracle/oradata/JASON/control01.ctl';

restore clone controlfileto '/u01/app/oracle/oradata/JASON/control02.ctl' from

'/u01/app/oracle/oradata/JASON/control01.ctl';

restore clone controlfileto '/u01/app/oracle/oradata/JASON/control03.ctl'from

'/u01/app/oracle/oradata/JASON/control01.ctl';

}

executing Memory Script

Starting backup at 21-JUL-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_JASON.ftag=TAG20160721T000524 RECID=1 STAMP=917741125

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 21-JUL-16

Starting restore at 21-JUL-16

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 21-JUL-16

Starting restore at 21-JUL-16

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

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";

backup as copy reuse

datafile 1 auxiliary format

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

2 auxiliary format

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

3 auxiliary format

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

4 auxiliary format

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

sql 'alter system archive logcurrent';

}

executing Memory Script

executing command: SET NEWNAME

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

executing command: SET NEWNAME

Starting backup at 21-JUL-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001name=/u01/app/oracle/oradata/JASON/system01.dbf

output file name=/u01/app/oracle/oradata/JASON/system01.dbftag=TAG20160721T000536

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:38

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002name=/u01/app/oracle/oradata/JASON/sysaux01.dbf

output file name=/u01/app/oracle/oradata/JASON/sysaux01.dbf tag=TAG20160721T000536

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:27

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003name=/u01/app/oracle/oradata/JASON/undotbs01.dbf

output file name=/u01/app/oracle/oradata/JASON/undotbs01.dbftag=TAG20160721T000536

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004name=/u01/app/oracle/oradata/JASON/users01.dbf

output file name=/u01/app/oracle/oradata/JASON/users01.dbftag=TAG20160721T000536

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 21-JUL-16

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

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

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=917741211 file name=/u01/app/oracle/oradata/JASON/sysaux01.dbf

datafile 3 switched to datafile copy

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

datafile 4 switched to datafile copy

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

Finished Duplicate Db at 21-JUL-16

RMAN>

12.开启ADG

将备库置于active dataguard模式下。

[oracle@jason2 ~]$ sqlplus /nolog

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

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

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

四、测试

13.主备库查看日志

主机切换日志

[oracle@jason1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 2123:33:08 2016

SQL> conn /as sysdba

Connected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROMV$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE#FIRST_TIME NEXT_TIME

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

413-JUL-16 14-JUL-16

514-JUL-16 14-JUL-16

614-JUL-16 14-JUL-16

714-JUL-16 20-JUL-16

820-JUL-16 20-JUL-16

920-JUL-16 20-JUL-16

1020-JUL-16 20-JUL-16

1120-JUL-16 20-JUL-16

1220-JUL-16 21-JUL-16

1321-JUL-16 21-JUL-16

1421-JUL-16 21-JUL-16

SEQUENCE#FIRST_TIME NEXT_TIME

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

1421-JUL-16 21-JUL-16

1521-JUL-16 21-JUL-16

1621-JUL-16 21-JUL-16

1721-JUL-16 21-JUL-16

1821-JUL-16 21-JUL-16

18 21-JUL-16 21-JUL-16

20 rows selected.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOGORDER BY SEQUENCE#;

SEQUENCE# APPLIED

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

4 NO

5 NO

6 NO

7 NO

8 NO

9 NO

10 NO

11 NO

12 NO

13 NO

14 NO

SEQUENCE# APPLIED

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

14 YES

15 NO

15 YES

16 NO

16 YES

17 NO

17 YES

18 NO

20 rows selected.

SQL>

备机查看

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOGORDER BY SEQUENCE#;

SEQUENCE#FIRST_TIME NEXT_TIME

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

1421-JUL-16 21-JUL-16

1521-JUL-16 21-JUL-16

1621-JUL-16 21-JUL-16

1721-JUL-16 21-JUL-16

1821-JUL-16 21-JUL-16

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOGORDER BY SEQUENCE#;

SEQUENCE# APPLIED

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

14 YES

15 YES

16 YES

17 YES

18IN-MEMORY

SQL>

14.switch_over测试

主库切换

SQL> SELECTSWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO STANDBY

SQL> ALTERDATABASE COMMIT 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>

主机（原备机）查看日志并切换

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOGORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME

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

14 21-JUL-16 21-JUL-16

15 21-JUL-16 21-JUL-16

16 21-JUL-16 21-JUL-16

17 21-JUL-16 21-JUL-16

18 21-JUL-16 21-JUL-16

19 21-JUL-16 21-JUL-16

20 21-JUL-16 21-JUL-16

21 21-JUL-16 21-JUL-16

10 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOGORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME

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

14 21-JUL-16 21-JUL-16

15 21-JUL-16 21-JUL-16

16 21-JUL-16 21-JUL-16

17 21-JUL-16 21-JUL-16

18 21-JUL-16 21-JUL-16

19 21-JUL-16 21-JUL-16

20 21-JUL-16 21-JUL-16

21 21-JUL-16 21-JUL-16

22 21-JUL-16 21-JUL-16

SEQUENCE# FIRST_TIME NEXT_TIME

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

22 21-JUL-16 21-JUL-16

23 21-JUL-16 21-JUL-16

14 rows selected.

SQL>

备库（原主机）上查看日志

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOGORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME

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

4 13-JUL-16 14-JUL-16

5 14-JUL-16 14-JUL-16

6 14-JUL-16 14-JUL-16

7 14-JUL-16 20-JUL-16

8 20-JUL-16 20-JUL-16

9 20-JUL-16 20-JUL-16

10 20-JUL-16 20-JUL-16

11 20-JUL-16 20-JUL-16

12 20-JUL-16 21-JUL-16

13 21-JUL-16 21-JUL-16

14 21-JUL-16 21-JUL-16

SEQUENCE# FIRST_TIME NEXT_TIME

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

14 21-JUL-16 21-JUL-16

15 21-JUL-16 21-JUL-16

16 21-JUL-16 21-JUL-16

17 21-JUL-16 21-JUL-16

18 21-JUL-16 21-JUL-16

19 21-JUL-16 21-JUL-16

SEQUENCE# FIRST_TIME NEXT_TIME

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

20 21-JUL-16 21-JUL-16

21 21-JUL-16 21-JUL-16

22 21-JUL-16 21-JUL-16

23 21-JUL-16 21-JUL-16

26 rows selected.

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

SEQUENCE# APPLIED

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

4 YES

5 YES

6 YES

7 YES

8 YES

9 YES

10 YES

11 YES

12 YES

13 YES

14 YES

SEQUENCE# APPLIED

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

14 YES

15 YES

16 YES

17 YES

18 YES

18 NO

19 YES

19 NO

SEQUENCE# APPLIED

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

20 YES

21 YES

22 YES

23 IN-MEMORY

26 rows selected.

SQL>

五、报错处理

15.报错1

当前名称：RMAN配置DataGuardfromactivedatabasewithfilesystem
文章起源：http://6mz.cn/article/ggcjpj.html

网站建设知识

RMAN配置DataGuardfromactivedatabasewithfilesystem

一、环境

二、主库配置

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

2. 开启归档

3. 创建standby日志组

4. 主库参数文件配置

5. 配置监听及tnsname

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

三、备库配置

7. 创建对应目录

8. 备库参数文件配置

9. 配置监听

10. 创建spfile文件

11.创建备库

12.开启ADG

四、测试

13.主备库查看日志

14.switch_over测试

五、报错处理

15.报错1

其他资讯

网站建设知识

RMAN配置DataGuardfromactivedatabasewithfilesystem

一、 环境

二、 主库配置

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

2. 开启归档

3. 创建standby日志组

4. 主库参数文件配置

5. 配置监听及tnsname

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

三、 备库配置

7. 创建对应目录

8. 备库参数文件配置

9. 配置监听

10. 创建spfile文件

11.创建备库

12.开启ADG

四、 测试

13.主备库查看日志

14.switch_over测试

五、 报错处理

15.报错1

其他资讯

一、环境

二、主库配置

三、备库配置

四、测试

五、报错处理