搭建DG:

1. 准备工作

主备	虚拟机名称	主机名	IP地址	db_name	db_unique_name	备注
主库	wxb	wang	192.168.10.2	ORA11GR2	ORA11GR2	主库
备库	www	bing	192.168.10.3	ORA11GR2	SBDB	备库，Oracle数据库只安装了软件

[oracle@wang ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 24 10:21:52 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORA11GR2>select flashback_on from v$database;

FLASHBACK_ON

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

NO

SYS@ORA11GR2>show parameter local_listener

NAME TYPE VALUE

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

local_listener string

SYS@ORA11GR2>

注:如果主库有外部表，必须将外部表删除；

2.主库数据库归档并强制生成日志

SYS@ORA11GR2>archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 12

Next log sequence to archive 14

Current log sequence 14

SYS@ORA11GR2>show parameter recover

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/FRA

db_recovery_file_dest_size big integer 3G

db_unrecoverable_scn_tracking boolean TRUE

recovery_parallelism integer 0

SYS@ORA11GR2>

SYS@ORA11GR2>alter database force logging;

Database altered.

SYS@ORA11GR2>select force_logging from v$database;

FOR

---

YES

3.主库创建standby logfile

SYS@ORA11GR2>select group#,member from v$logfile order by 1;

GROUP# MEMBER

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

1 /u01/app/oracle/oradata/ORA11GR2/redo01.log

2 /u01/app/oracle/oradata/ORA11GR2/redo02.log

3 /u01/app/oracle/oradata/ORA11GR2/redo03.log

SYS@ORA11GR2>select group#,bytes/1024/1024 m from v$log;

GROUP# M

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

1 50

2 50

3 50

SYS@ORA11GR2>alter database add standby logfile group 4 ('/u01/app/oracle/oradata/ORA11GR2/standby401.log','/u01/app/oracle/oradata/ORA11GR2/standby402.log') size 50m;

Database altered.

SYS@ORA11GR2>alter database add standby logfile group 5 ('/u01/app/oracle/oradata/ORA11GR2/standby501.log','/u01/app/oracle/oradata/ORA11GR2/standby502.log') size 50m;

Database altered.

SYS@ORA11GR2>alter database add standby logfile group 6 ('/u01/app/oracle/oradata/ORA11GR2/standby6501.log','/u01/app/oracle/oradata/ORA11GR2/standby602.log') size 50m;

Database altered.

SYS@ORA11GR2>alter database add standby logfile group 7('/u01/app/oracle/oradata/ORA11GR2/standby701.log','/u01/app/oracle/oradata/ORA11GR2/standby702.log') size 50m;

Database altered.

SYS@ORA11GR2>select group#,thread#,sequence#,bytes/1024/1024 m,status from v$standby_log;

GROUP# THREAD# SEQUENCE# M STATUS

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

4 0 0 50 UNASSIGNED

5 0 0 50 UNASSIGNED

6 0 0 50 UNASSIGNED

7 0 0 50 UNASSIGNED

SYS@ORA11GR2>col member for a55

SYS@ORA11GR2>col type for a10

SYS@ORA11GR2>select group#,type,member from v$logfile order by 1;

GROUP# TYPE MEMBER

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

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

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

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

4 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby401.log

4 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby402.log

5 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby501.log

5 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby502.log

6 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby6501.log

6 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby602.log

7 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby701.log

7 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby702.log

11 rows selected.

SYS@ORA11GR2>select group#,type,member from v$logfile order by 1;

GROUP# TYPE MEMBER

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

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

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

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

4 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby401.log

4 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby402.log

5 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby501.log

5 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby502.log

6 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby6501.log

6 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby602.log

7 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby701.log

7 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby702.log

11 rows selected.

4. 主库配置静态监听及作为客户端的tnsnames

——主库配置静态监听：

[oracle@wang ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

[oracle@wang admin]$ ls

listener.ora samples shrept.lst tnsnames.ora

[oracle@wang admin]$ vi listener.ora

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

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

sid_list_listener=

(sid_list=

(sid_desc=

(global_dbname=ORA11GR2)

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

(sid_name=ORA11GR2))

)

~

[oracle@wang admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:36:58

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

The command completed successfully

[oracle@wang admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:37:06

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

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 30-AUG-2016 20:38:58

Uptime 54 days 13 hr. 58 min. 9 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/wang/listener/alert/log.xml

Listening Endpoints Summary...

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

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

Services Summary...

Service "ORA11GR2" has 2 instance(s).

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

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

Service "ORA11GR2XDB" has 1 instance(s).

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

The command completed successfully

[oracle@wang admin]$

——主库配置tns：

[oracle@wang admin]$ vi 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.

ORA11GR2 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORA11GR2)

)

SBDB =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SBDB)

)

~

"tnsnames.ora" 20L, 507C written

[oracle@wang admin]$ tnsping sbdb

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:41:03

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SBDB)))

OK (0 msec)

[oracle@wang admin]$

5.备库配置静态监听及作为客户端的tnsnames

——备库配置静态监听：

[oracle@bing ~]$ echo $ORACLE_SID

PROD

[oracle@bing ~]$ export ORACLE_SID=SBDB

[oracle@bing ~]$ echo $ORACLE_SID

SBDB

[oracle@bing ~]$

[oracle@bing ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

[oracle@bing admin]$ ls

listener.ora samples shrept.lst tnsnames.ora

[oracle@bing admin]$ vi listener.ora

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

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

sid_list_listener=

(sid_list=

(sid_desc=

(global_dbname=SBDB)

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

(sid_name=SBDB))

)

~

"listener.ora" 19L, 477C written

[oracle@bing admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:49:14

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

The command completed successfully

[oracle@bing admin]$

[oracle@bing admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:49:24

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

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 05-SEP-2016 16:44:16

Uptime 48 days 18 hr. 5 min. 8 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/bing/listener/alert/log.xml

Listening Endpoints Summary...

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

Services Summary...

Service "SBDB" has 1 instance(s).

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

The command completed successfully

[oracle@bing admin]$

——配置备库的tns：

[oracle@bing admin]$ vi 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.

12 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PROD)

)

ORA11GR2 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORA11GR2)

)

~

"tnsnames.ora" 21L, 518C written

[oracle@bing admin]$ tnsping ora11gr2

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:52:04

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA11GR2)))

OK (0 msec)

[oracle@bing admin]$

6.主库修改参数增加DG相关的属性参数：

——根据spfile创建pfile：

SYS@ORA11GR2>create pfile from spfile;

File created.

SYS@ORA11GR2>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@wang ~]$ cd $ORACLE_HOME/dbs

[oracle@wang dbs]$ ls

hc_ORA11GR2.dat initORA11GR2.ora orapwORA11GR2

init.ora lkORA11GR2 spfileORA11GR2.ora

——修改pfile，增加DG属性参数：

[oracle@wang dbs]$ vi initORA11GR2.ora

ORA11GR2.__db_cache_size=373293056

ORA11GR2.__java_pool_size=4194304

ORA11GR2.__large_pool_size=8388608

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

ORA11GR2.__pga_aggregate_target=289406976

ORA11GR2.__sga_target=545259520

ORA11GR2.__shared_io_pool_size=0

ORA11GR2.__shared_pool_size=146800640

ORA11GR2.__streams_pool_size=4194304

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

*.audit_trail='db'

*.compatible='11.2.0.4.0'

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

*.db_block_size=8192

*.db_domain=''

*.db_name='ORA11GR2'

*.db_recovery_file_dest_size=3221225472

*.db_recovery_file_dest='/u01/app/FRA'

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

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

*.memory_target=833617920

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME=ORA11GR2

LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11GR2,SBDB)'

LOG_ARCHIVE_DEST_1=

'LOCATION=/u01/arch1/ORA11GR2/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=ORA11GR2'

LOG_ARCHIVE_DEST_2=

'SERVICE=SBDB ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=SBDB'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=SBDB

DB_FILE_NAME_CONVERT='SBDB','ORA11GR2'

LOG_FILE_NAME_CONVERT='SBDB','ORA11GR2'

STANDBY_FILE_MANAGEMENT=AUTO

[oracle@wang dbs]$

——根据参数文件创建不存在的目录：

[oracle@wang dbs]$ mkdir -p /u01/arch1/ORA11GR2/

——通过pfile再重新生成spfile验证已经修改的参数文件：

[oracle@wang dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 24 11:07:27 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORA11GR2>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

SYS@ORA11GR2>create spfile from pfile;

File created.

SYS@ORA11GR2>startup

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 503319672 bytes

Database Buffers 322961408 bytes

Redo Buffers 2392064 bytes

Database mounted.

Database opened.

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter spfile

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfileORA11GR2.ora

7. 拷贝相关文件至备库（即参数文件、密码文件）

[oracle@wang dbs]$ pwd

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

[oracle@wang dbs]$ ls

hc_ORA11GR2.dat initORA11GR2.ora orapwORA11GR2

init.ora lkORA11GR2 spfileORA11GR2.ora

[oracle@wang dbs]$ scp initORA11GR2.ora 192.168.10.3:$ORACLE_HOME/dbs/initSBDB.ora

The authenticity of host '192.168.10.3 (192.168.10.3)' can't be established.

RSA key fingerprint is 46:2d:74:b7:54:f9:5f:4b:13:7d:e0:0f:5a:a4:92:2f.

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

Warning: Permanently added '192.168.10.3' (RSA) to the list of known hosts.

oracle@192.168.10.3's password:

initORA11GR2.ora 100% 1514 1.5KB/s 00:00

[oracle@wang dbs]$

[oracle@wang dbs]$ scp orapwORA11GR2 192.168.10.3:$ORACLE_HOME/dbs/orapwSBDB

oracle@192.168.10.3's password:

orapwORA11GR2 100% 1536 1.5KB/s 00:00

[oracle@wang dbs]$

8.备库参数修改增加DG相关的属性参数

[oracle@bing dbs]$ pwd

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

[oracle@bing dbs]$ ls initSBDB.ora orapwSBDB

initSBDB.ora orapwSBDB

[oracle@bing dbs]$

——修改备库参数文件：(全局修改，唯一不变的是db_name=ORA11GR2)

[oracle@bing dbs]$ cat initSBDB.ora

SBDB.__db_cache_size=373293056

SBDB.__java_pool_size=4194304

SBDB.__large_pool_size=8388608

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

SBDB.__pga_aggregate_target=289406976

SBDB.__sga_target=545259520

SBDB.__shared_io_pool_size=0

SBDB.__shared_pool_size=146800640

SBDB.__streams_pool_size=4194304

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

*.audit_trail='db'

*.compatible='11.2.0.4.0'

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

*.db_block_size=8192

*.db_domain=''

*.db_name='ORA11GR2'

*.db_recovery_file_dest_size=3221225472

*.db_recovery_file_dest='/u01/app/FRA'

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

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

*.memory_target=833617920

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME=SBDB

LOG_ARCHIVE_CONFIG='DG_CONFIG=(SBDB,ORA11GR2)'

LOG_ARCHIVE_DEST_1=

'LOCATION=/u01/arch1/SBDB/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=SBDB'

LOG_ARCHIVE_DEST_2=

'SERVICE=ORA11GR2 ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=ORA11GR2'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=ORA11GR2

DB_FILE_NAME_CONVERT='ORA11GR2','SBDB'

LOG_FILE_NAME_CONVERT='ORA11GR2','SBDB'

STANDBY_FILE_MANAGEMENT=AUTO

[oracle@bing dbs]$

——根据参数文件创建不存在的目录：

[oracle@bing dbs]$ mkdir -p /u01/app/oracle/admin/SBDB/adump

[oracle@bing dbs]$ mkdir -p /u01/app/oracle/oradata/SBDB/

[oracle@bing dbs]$ mkdir -p /u01/app/FRA

[oracle@bing dbs]$ mkdir -p /u01/arch1/SBDB/

——备库通过pfile生成spfile，并启动到nomount下，验证已经修改的参数文件：

[oracle@bing dbs]$ echo $ORACLE_SID

SBDB

[oracle@bing dbs]$

[oracle@bing dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 24 11:32:23 2016

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 503319672 bytes

Database Buffers 322961408 bytes

Redo Buffers 2392064 bytes

SQL>

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfileSBDB.ora

9. 使用RMAN auxiliary恢复数据库

主库为open状态，备库为nomount，监听为启动状态

[oracle@wang dbs]$ rman target / auxiliary sys/oracle@sbdb

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 24 11:34:57 2016

connected to target database: ORA11GR2 (DBID=237843809)

connected to auxiliary database: ORA11GR2 (not mounted)

RMAN>

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 24-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORA11GR2' auxiliary format

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

}

executing Memory Script

Starting backup at 24-OCT-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=79 device type=DISK

Finished backup at 24-OCT-16

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/SBDB/control01.ctl';

restore clone controlfile to '/u01/app/oracle/oradata/SBDB/control02.ctl' from

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

}

executing Memory Script

Starting backup at 24-OCT-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_ORA11GR2.f tag=TAG20161024T113659 RECID=2 STAMP=926077019

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

Finished backup at 24-OCT-16

Starting restore at 24-OCT-16

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 24-OCT-16

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for tempfile 1 to

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

switch clone tempfile all;

set newname for datafile 1 to

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

set newname for datafile 2 to

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

set newname for datafile 3 to

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

set newname for datafile 4 to

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

set newname for datafile 5 to

"/u01/app/oracle/oradata/SBDB/example01.dbf";

backup as copy reuse

datafile 1 auxiliary format

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

2 auxiliary format

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

3 auxiliary format

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

4 auxiliary format

"/u01/app/oracle/oradata/SBDB/users01.dbf" datafile

5 auxiliary format

"/u01/app/oracle/oradata/SBDB/example01.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/SBDB/temp01.dbf in control file

executing command: SET NEWNAME

Starting backup at 24-OCT-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

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

output file name=/u01/app/oracle/oradata/SBDB/system01.dbf tag=TAG20161024T113707

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

channel ORA_DISK_1: starting datafile copy

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

output file name=/u01/app/oracle/oradata/SBDB/sysaux01.dbf tag=TAG20161024T113707

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/ORA11GR2/example01.dbf

output file name=/u01/app/oracle/oradata/SBDB/example01.dbf tag=TAG20161024T113707

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

channel ORA_DISK_1: starting datafile copy

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

output file name=/u01/app/oracle/oradata/SBDB/undotbs01.dbf tag=TAG20161024T113707

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

channel ORA_DISK_1: starting datafile copy

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

output file name=/u01/app/oracle/oradata/SBDB/users01.dbf tag=TAG20161024T113707

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

Finished backup at 24-OCT-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=2 STAMP=926077069 file name=/u01/app/oracle/oradata/SBDB/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=3 STAMP=926077069 file name=/u01/app/oracle/oradata/SBDB/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=926077069 file name=/u01/app/oracle/oradata/SBDB/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=926077069 file name=/u01/app/oracle/oradata/SBDB/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=926077069 file name=/u01/app/oracle/oradata/SBDB/example01.dbf

Finished Duplicate Db at 24-OCT-16

完成dg搭建！！！！！！！！！！！

——查看主备状态：

SYS@ORA11GR2>select name,database_role,switchover_status,open_mode from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

ORA11GR2 PRIMARY FAILED DESTINATION READ WRITE

SQL> select name,database_role,switchover_status,open_mode from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

ORA11GR2 PHYSICAL STANDBY SESSIONS ACTIVE MOUNTED

SQL> alter database open;

Database altered.

SQL> select name,database_role,switchover_status,open_mode from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

ORA11GR2 PHYSICAL STANDBY NOT ALLOWED READ ONLY

——备库应用日志：

SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.

——过会再查看主备状态：

SYS@ORA11GR2>select name,database_role,switchover_status,open_mode from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

ORA11GR2 PRIMARY SESSIONS ACTIVE READ WRITE

SYS@ORA11GR2>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

19

SQL> select name,database_role,switchover_status,open_mode from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

ORA11GR2 PHYSICAL STANDBY NOT ALLOWED READ ONLY WITH APPLY

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

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

17 YES

18 YES

19 IN-MEMORY

10.切换保护模式

10.1最大性能转最大可用（默认dg搭建完是最大性能模式）

——确认主备数据库状态：

SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;

NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS

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

ORA11GR2 MAXIMUM PERFORMANCE PRIMARY SESSIONS ACTIVE

SQL> select name,protection_mode,database_role,switchover_status from v$database;

NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS

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

ORA11GR2 MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED

——主库重启到mount下，修改参数：

SYS@ORA11GR2>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

SYS@ORA11GR2>startup mount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 503319672 bytes

Database Buffers 322961408 bytes

Redo Buffers 2392064 bytes

Database mounted.

SYS@ORA11GR2>

SYS@ORA11GR2>alter system set LOG_ARCHIVE_DEST_2='SERVICE=SBDB LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB';

System altered.

——执行模式转换操作：

SYS@ORA11GR2>alter database set standby database to maximize availability;

Database altered.

——打开主库验证：

SYS@ORA11GR2>alter database open;

Database altered.

SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;

NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS

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

ORA11GR2 MAXIMUM AVAILABILITY PRIMARY SESSIONS ACTIVE

——备库确认:

SQL> select name,protection_mode,database_role,switchover_status from v$database;

NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS

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

ORA11GR2 MAXIMUM AVAILABILITY PHYSICAL STANDBY NOT ALLOWED

——最好把备库也改一下，以便在主备切换时，依旧是最大可用：

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORA11GR2 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA11GR2';

System altered.

SQL>

10.2最大可用模式转换为最大保护：

（因为最大可用模式和最大保护模式对应的参数都是LGWR和SYNC,所以不需要再改参数了）

——主库操作：

SYS@ORA11GR2>alter database set standby database to maximize protection;

Database altered.

SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;

NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS

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

ORA11GR2 MAXIMUM PROTECTION PRIMARY SESSIONS ACTIVE

——查看备库：

SQL> select name,protection_mode,database_role,switchover_status from v$database;

NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS

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

ORA11GR2 MAXIMUM PROTECTION PHYSICAL STANDBY NOT ALLOWED

注: 备库修改参数，与主库相同，上面已经修改过了；

10.3最大保护转最大性能：

（最大性能参数为LGWR、SYNC或者ASYNC或者ARCH、SYNC，因为上面已经改为LGWR、SYNC，所以不需要再设置了，直接转换）

——主库操作：

SYS@ORA11GR2>alter database set standby database to maximize performance;

Database altered.

SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;

NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS

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

ORA11GR2 MAXIMUM PERFORMANCE PRIMARY SESSIONS ACTIVE

——查看备库状态：

select name,protection_mode,database_role,switchover_status from v$database;

NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS

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

ORA11GR2 MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED

注：最好把备库也改一下，以便在主备切换时，依旧是最大性能，上面参数已经修改过了。

11. DG备库断档问题

1）前提备库停止应用日志：

SQL> recover managed standby database cancel;

Media recovery complete.

2）主库操作：

SYS@ORA11GR2>alter system switch logfile;

System altered.

SYS@ORA11GR2>/

System altered.

SYS@ORA11GR2>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

38

3）查看备库日志应用情况：

（因为备库停止了应用日志，所以35 、36 、37 、 38 号日志未应用）

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

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

17 YES

18 YES

19 YES

20 YES

21 YES

22 YES

23 YES

24 YES

25 YES

26 YES

27 YES

SEQUENCE# APPLIED

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

28 YES

29 YES

30 YES

31 YES

32 YES

33 YES

34 YES

35 NO

36 NO

37 NO

38 NO

SEQUENCE# APPLIED

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

39 NO

23 rows selected.

4）模拟故障：

——主库操作：

[oracle@wang dbs]$ cd /u01/arch1/ORA11GR2/

[oracle@wang ORA11GR2]$ ls

1_15_921272292.arc 1_25_921272292.arc 1_35_921272292.arc

1_16_921272292.arc 1_26_921272292.arc 1_36_921272292.arc

1_17_921272292.arc 1_27_921272292.arc 1_37_921272292.arc

1_18_921272292.arc 1_28_921272292.arc 1_38_921272292.arc

1_19_921272292.arc 1_29_921272292.arc 1_39_921272292.arc

1_20_921272292.arc 1_30_921272292.arc 1_40_921272292.arc

1_21_921272292.arc 1_31_921272292.arc 1_41_921272292.arc

1_22_921272292.arc 1_32_921272292.arc 1_42_921272292.arc

1_23_921272292.arc 1_33_921272292.arc

1_24_921272292.arc 1_34_921272292.arc

[oracle@wang ORA11GR2]$ mv 1_35_921272292.arc 1_35_921272292.arc.bak

[oracle@wang ORA11GR2]$ ls

1_15_921272292.arc 1_25_921272292.arc 1_35_921272292.arc.bak

1_16_921272292.arc 1_26_921272292.arc 1_36_921272292.arc

1_17_921272292.arc 1_27_921272292.arc 1_37_921272292.arc

1_18_921272292.arc 1_28_921272292.arc 1_38_921272292.arc

1_19_921272292.arc 1_29_921272292.arc 1_39_921272292.arc

1_20_921272292.arc 1_30_921272292.arc 1_40_921272292.arc

1_21_921272292.arc 1_31_921272292.arc 1_41_921272292.arc

1_22_921272292.arc 1_32_921272292.arc 1_42_921272292.arc

1_23_921272292.arc 1_33_921272292.arc 1_43_921272292.arc

1_24_921272292.arc 1_34_921272292.arc

——备库操作：

[oracle@bing ~]$ cd /u01/arch1/SBDB/

[oracle@bing SBDB]$ ls

1_17_921272292.arc 1_27_921272292.arc 1_37_921272292.arc

1_18_921272292.arc 1_28_921272292.arc 1_38_921272292.arc

1_19_921272292.arc 1_29_921272292.arc 1_39_921272292.arc

1_20_921272292.arc 1_30_921272292.arc 1_40_921272292.arc

1_21_921272292.arc 1_31_921272292.arc 1_41_921272292.arc

1_22_921272292.arc 1_32_921272292.arc 1_42_921272292.arc

1_23_921272292.arc 1_33_921272292.arc 1_43_921272292.arc

1_24_921272292.arc 1_34_921272292.arc 1_44_921272292.arc

1_25_921272292.arc 1_35_921272292.arc

1_26_921272292.arc 1_36_921272292.arc

[oracle@bing SBDB]$ mv 1_35_921272292.arc 1_35_921272292.arc.bak

[oracle@bing SBDB]$ ls

1_17_921272292.arc 1_27_921272292.arc 1_37_921272292.arc

1_18_921272292.arc 1_28_921272292.arc 1_38_921272292.arc

1_19_921272292.arc 1_29_921272292.arc 1_39_921272292.arc

1_20_921272292.arc 1_30_921272292.arc 1_40_921272292.arc

1_21_921272292.arc 1_31_921272292.arc 1_41_921272292.arc

1_22_921272292.arc 1_32_921272292.arc 1_42_921272292.arc

1_23_921272292.arc 1_33_921272292.arc 1_43_921272292.arc

1_24_921272292.arc 1_34_921272292.arc 1_44_921272292.arc

1_25_921272292.arc 1_35_921272292.arc.bak 1_45_921272292.arc

1_26_921272292.arc 1_36_921272292.arc

[oracle@bing SBDB]$

5）再次在备库操作，即在备库应用日志，发生断档问题了：

SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.

SQL>

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

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

17 YES

18 YES

19 YES

20 YES

21 YES

22 YES

23 YES

24 YES

25 YES

26 YES

27 YES

SEQUENCE# APPLIED

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

28 YES

29 YES

30 YES

31 YES

32 YES

33 YES

34 YES

35 NO

36 NO

37 NO

38 NO

SEQUENCE# APPLIED

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

39 NO

40 NO

41 NO

42 NO

43 NO

44 NO

45 NO

46 NO

47 NO

31 rows selected.

6）解决断档：（备库操作）

[oracle@bing SBDB]$ pwd

/u01/arch1/SBDB

[oracle@bing SBDB]$ ls

1_17_921272292.arc 1_28_921272292.arc 1_39_921272292.arc

1_18_921272292.arc 1_29_921272292.arc 1_40_921272292.arc

1_19_921272292.arc 1_30_921272292.arc 1_41_921272292.arc

1_20_921272292.arc 1_31_921272292.arc 1_42_921272292.arc

1_21_921272292.arc 1_32_921272292.arc 1_43_921272292.arc

1_22_921272292.arc 1_33_921272292.arc 1_44_921272292.arc

1_23_921272292.arc 1_34_921272292.arc 1_45_921272292.arc

1_24_921272292.arc 1_35_921272292.arc.bak 1_46_921272292.arc

1_25_921272292.arc 1_36_921272292.arc 1_47_921272292.arc

1_26_921272292.arc 1_37_921272292.arc 1_48_921272292.arc

1_27_921272292.arc 1_38_921272292.arc 1_49_921272292.arc

[oracle@bing SBDB]$ mv 1_35_921272292.arc.bak 1_35_921272292.arc

[oracle@bing SBDB]$ ls

1_17_921272292.arc 1_28_921272292.arc 1_39_921272292.arc

1_18_921272292.arc 1_29_921272292.arc 1_40_921272292.arc

1_19_921272292.arc 1_30_921272292.arc 1_41_921272292.arc

1_20_921272292.arc 1_31_921272292.arc 1_42_921272292.arc

1_21_921272292.arc 1_32_921272292.arc 1_43_921272292.arc

1_22_921272292.arc 1_33_921272292.arc 1_44_921272292.arc

1_23_921272292.arc 1_34_921272292.arc 1_45_921272292.arc

1_24_921272292.arc 1_35_921272292.arc 1_46_921272292.arc

1_25_921272292.arc 1_36_921272292.arc 1_47_921272292.arc

1_26_921272292.arc 1_37_921272292.arc 1_48_921272292.arc

1_27_921272292.arc 1_38_921272292.arc 1_49_921272292.arc

7）.备库操作重新注册一下刚刚找回来的归档日志：

SQL> alter database register physical logfile '/u01/arch1/SBDB/1_35_921272292.arc';

Database altered.

8）.再次在备库重新应用日志：（因为前面操作，应用日志没有关闭，过会再次查看日志应用状况）

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APPLIED

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

17 YES

18 YES

19 YES

20 YES

21 YES

22 YES

23 YES

24 YES

25 YES

26 YES

27 YES

SEQUENCE# APPLIED

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

28 YES

29 YES

30 YES

31 YES

32 YES

33 YES

34 YES

35 YES

36 YES

37 YES

38 YES

SEQUENCE# APPLIED

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

39 YES

40 YES

41 YES

42 YES

43 YES

44 YES

45 YES

46 YES

47 YES

48 YES

49 YES

SEQUENCE# APPLIED

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

50 YES

51 YES

52 YES

35 YES

53 YES

54 YES

55 YES

56 IN-MEMORY

41 rows selected.

断档问题解决了！！！！！！！！！！！！！！！

12.Switchover主备切换操作

12.1（主切备，备切主）

1）主切备：

SYS@ORA11GR2>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PRIMARY SESSIONS ACTIVE

——执行切换操作（主切备后，后台进程关闭了数据库）

SYS@ORA11GR2>alter database commit to switchover to physical standby with session shutdown;

ERROR:

ORA-01034: ORACLE not available

Process ID: 6114

Session ID: 1 Serial number: 5

Database altered.

SYS@ORA11GR2>conn / as sysdba

Connected to an idle instance.

SYS@ORA11GR2>startup mount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 503319672 bytes

Database Buffers 322961408 bytes

Redo Buffers 2392064 bytes

Database mounted.

——再次查看状态（需进行应用日志操作）

SYS@ORA11GR2>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY RECOVERY NEEDED

SYS@ORA11GR2>recover managed standby database using current logfile disconnect from session;

Media recovery complete.

SYS@ORA11GR2>

SYS@ORA11GR2>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

——启库：

SYS@ORA11GR2>alter database open;

alter database open

*

ERROR at line 1:

ORA-10456: cannot open standby database; media recovery session may be in

Progress

——关闭日志应用：

SYS@ORA11GR2>recover managed standby database cancel;

Media recovery complete.

SYS@ORA11GR2>alter database open;

Database altered.

——查看状态：

SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY SESSIONS ACTIVE READ ONLY

2）备切主：（TO PRIMARY 状态，为可以切换为主库的正常状态）

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY TO PRIMARY READ ONLY WITH APPLY

——进行备切主操作：（备切主后，后台进程直接关库，再将库启动到mount下）

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PRIMARY NOT ALLOWED MOUNTED

——启库，再查看数据库状态：

SQL> alter database open;

Database altered.

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PRIMARY SESSIONS ACTIVE READ WRITE

3）再次查看转换后的备库状态：

SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY RECOVERY NEEDED READ ONLY

——应用日志：

SYS@ORA11GR2>recover managed standby database using current logfile disconnect from session;

Media recovery complete.

SYS@ORA11GR2>

SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY NOT ALLOWED READ ONLY WITH APPLY

12.2反切（备切主、主切备，即将切换后的备库切换回主库，切换后的主库切换回备库）

1）主库操作：

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PRIMARY SESSIONS ACTIVE READ WRITE

——执行切换（后台自动关库）

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 503319672 bytes

Database Buffers 322961408 bytes

Redo Buffers 2392064 bytes

Database mounted.

——查询状态：（需应用日志）

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY RECOVERY NEEDED MOUNTED

——应用日志：

SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.

——再次查看数据库状态：

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY TO PRIMARY MOUNTED

——结束日志应用，然后启库：

SQL> recover managed standby database cancel;

Media recovery complete.

SQL>

SQL> alter database open;

Database altered.

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY TO PRIMARY READ ONLY

——再次应用日志：

SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY TO PRIMARY READ ONLY WITH APPLY

2）备库操作：

SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY SESSIONS ACTIVE READ ONLY WITH APPLY

——切换操作（备切主后，后台进程直接关库，再将库启动到mount下）

SYS@ORA11GR2>alter database commit to switchover to primary with session shutdown;

Database altered.

SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PRIMARY NOT ALLOWED MOUNTED

SYS@ORA11GR2>alter database open;

Database altered.

SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PRIMARY SESSIONS ACTIVE READ WRITE

3）再次查看切换后的备库状态：（not allowed为正常状态，如果为to primary则为不正常状态，因为已经有了主库）

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY NOT ALLOWED READ ONLY WITH APPLY

13. 开启dg broker

前提：1.很重要，搭建dg broker 之前一定一定要使备库日志应用与主库一致；

2.在搭建dg broker之前，一定一定要将主备库的tnsnames.ora里的连接字符串改成与db_unique_name的名字一样，同时修改参数文件里的log_archive_dest_2='service的值；

搭建dg broker 需在主备库上都进行设置，步骤如下：

1. 在监听文件listener.ora中的静态监听内的global_dbname参数值加_DGMGRL。

2. Reload 监听；

3. 查参数dg_broker_start,默认是false。修改：alter system set dg_broker_start = true;

1)配置主库：

[oracle@wang ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

[oracle@wang admin]$ ls

listener.ora samples shrept.lst tnsnames.ora

[oracle@wang admin]$ vi listener.ora

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

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

sid_list_listener=

(sid_list=

(sid_desc=

(global_dbname=ORA11GR2_DGMGRL)

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

(sid_name=ORA11GR2))

)

~

"listener.ora" 19L, 460C written

[oracle@wang admin]$

[oracle@wang admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 20:20:15

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

The command completed successfully

[oracle@wang admin]$

[oracle@wang admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 20:20:30

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

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 30-AUG-2016 20:38:58

Uptime 54 days 23 hr. 41 min. 32 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/wang/listener/alert/log.xml

Listening Endpoints Summary...

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

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

Services Summary...

Service "ORA11GR2" has 1 instance(s).

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

Service "ORA11GR2XDB" has 1 instance(s).

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

Service "ORA11GR2_DGMGRL" has 1 instance(s).

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

The command completed successfully

[oracle@wang admin]$

SYS@ORA11GR2>alter system set dg_broker_start=true;

System altered.

SYS@ORA11GR2>show parameter dg_broker_start

NAME TYPE VALUE

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

dg_broker_start boolean TRUE

2)配置备库：

[oracle@bing ~]$ export ORACLE_SID=SBDB

[oracle@bing ~]$ echo $ORACLE_SID

SBDB

[oracle@bing ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

[oracle@bing admin]$ ls

listener.ora samples shrept.lst tnsnames.ora

[oracle@bing admin]$ vi listener.ora

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

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

sid_list_listener=

(sid_list=

(sid_desc=

(global_dbname=SBDB_DGMGRL)

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

(sid_name=SBDB))

)

~

"listener.ora" 19L, 484C written

[oracle@bing admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 20:26:16

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

The command completed successfully

[oracle@bing admin]$

[oracle@bing admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 20:26:26

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

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 05-SEP-2016 16:44:16

Uptime 49 days 3 hr. 42 min. 10 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/bing/listener/alert/log.xml

Listening Endpoints Summary...

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

Services Summary...

Service "SBDB" has 1 instance(s).

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

Service "SBDBXDB" has 1 instance(s).

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

Service "SBDB_DGMGRL" has 1 instance(s).

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

The command completed successfully

[oracle@bing admin]$

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter dg_broker_start

NAME TYPE VALUE

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

dg_broker_start boolean TRUE

14. dg broker维护DG

1）配置dg broker:(在主库操作)

[oracle@wang admin]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle

Connected.

DGMGRL> create configuration 'ORA11GR2' as primary database is 'ORA11GR2' connect identifier is ORA11GR2;

Configuration "ORA11GR2" created with primary database "ORA11GR2"

DGMGRL>

DGMGRL> show configuration;

Configuration - ORA11GR2

Protection Mode: MaxPerformance

Databases:

ORA11GR2 - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> add database 'SBDB' as connect identifier is SBDB;

Database "SBDB" added

DGMGRL>

DGMGRL> show configuration;

Configuration - ORA11GR2

Protection Mode: MaxPerformance

Databases:

ORA11GR2 - Primary database

SBDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> show database verbose 'SBDB';

Database - SBDB

Role: PHYSICAL STANDBY

Intended State: OFFLINE

Transport Lag: (unknown)

Apply Lag: (unknown)

Apply Rate: (unknown)

Real Time Query: OFF

Instance(s):

SBDB

Properties:

DGConnectIdentifier = 'sbdb'

ObserverConnectIdentifier = ''

LogXptMode = 'SYNC'

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '30'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = 'ORA11GR2, SBDB'

LogFileNameConvert = 'ORA11GR2, SBDB'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

ApplyLagThreshold = '0'

TransportLagThreshold = '0'

TransportDisconnectedThreshold = '30'

SidName = 'SBDB'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bing)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SBDB_DGMGRL)(INSTANCE_NAME=SBDB)(SERVER=DEDICATED)))'

StandbyArchiveLocation = '/u01/arch1/SBDB/'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.arc'

TopWaitEvents = '(monitor)'

Database Status:

DISABLED

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration

Configuration - ORA11GR2

Protection Mode: MaxPerformance

Databases:

ORA11GR2 - Primary database

SBDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> enable database 'SBDB';

Enabled.

DGMGRL> show database 'SBDB';

Database - SBDB

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds (computed 1 second ago)

Apply Lag: 0 seconds (computed 1 second ago)

Apply Rate: 0 Byte/s

Real Time Query: ON

Instance(s):

SBDB

Database Status:

SUCCESS

2). fast start failover功能开启:

主库与备库执行如下命令并并且在备库开启应用日志进程：

——主库：

SYS@ORA11GR2>alter system set undo_retention=3600;

System altered.

SYS@ORA11GR2>alter system set db_flashback_retention_target=4320;

System altered.

SYS@ORA11GR2>alter database flashback on;

Database altered.

当然，还需要快速恢复区大小及位置，本实验先前已经设置好了快速恢复区

——备库：

SQL> alter system set undo_retention=3600;

System altered.

SQL> alter system set db_flashback_retention_target=4320;

System altered.

SQL> alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-01153: an incompatible media recovery is active

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> alter database flashback on;

Database altered.

SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.

(当然，还需要快速恢复区大小及位置，本实验先前已经设置好了快速恢复区)

——主库在DGMGRL工具下操作：

DGMGRL> edit database 'ORA11GR2' set property faststartfailovertarget='SBDB';

Property "faststartfailovertarget" updated

DGMGRL>

DGMGRL> edit database 'SBDB' set property faststartfailovertarget='ORA11GR2';

Property "faststartfailovertarget" updated

DGMGRL>

DGMGRL> start observer;

Observer started

[oracle@wang ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle

Connected.

DGMGRL>

DGMGRL> enable fast_start failover;

Error: ORA-16651: requirements not met for enabling fast-start failover

Failed.

——查看报警日志信息：

[oracle@wang trace]$tail -100f drcORA11GR2.log

=========================================================================

ENABLE FAST_START FAILOVER

ORA-16651, MaxPerformance FSFO requires LogXptMode=ASYNC for primary and target standby

ORA-16651, failed to enable FSFO

Command ENABLE FAST_START FAILOVER completed

=============================================================================

——解决：

DGMGRL> edit database 'SBDB' set property 'LogXptMode'='SYNC';

Property "LogXptMode" updated

DGMGRL> edit database 'ORA11GR2' set property 'LogXptMode'='SYNC';

Property "LogXptMode" updated

——转换到最大可用模式：

DGMGRL> edit configuration set protection mode as maxavailability;

Succeeded.

DGMGRL> show configuration;

Configuration - ORA11GR2

Protection Mode: MaxAvailability

Databases:

ORA11GR2 - Primary database

SBDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

——使fast_start failover功能生效：

DGMGRL> enable fast_start failover;

Enabled.

——验证：

DGMGRL> show configuration;

Configuration - ORA11GR2

Protection Mode: MaxAvailability

Databases:

ORA11GR2 - Primary database

SBDB - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS

——验证dg broker搭建成功：

SYS@ORA11GR2>col FS_FAILOVER_OBSERVER_HOST for a20

SYS@ORA11GR2>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;

FS_FAIL FS_FAILOVER_OBSERVER FS_FAILOVER_THRESHOLD

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

YES wang 30

【DG】搭建（二）及相关测试

搭建DG:

1. 准备工作

2.主库数据库归档并强制生成日志

3.主库创建standby logfile

4. 主库配置静态监听及作为客户端的tnsnames

5.备库配置静态监听及作为客户端的tnsnames

6.主库修改参数增加DG相关的属性参数：

7. 拷贝相关文件至备库（即参数文件、密码文件）

8.备库参数修改增加DG相关的属性参数

9. 使用RMAN auxiliary恢复数据库

10.切换保护模式

11. DG备库断档问题

12.Switchover主备切换操作

13. 开启dg broker

14. dg broker维护DG