ITPub博客

首页 > 数据库 > Oracle > 搭建Data Guard的详细实验过程

搭建Data Guard的详细实验过程

原创 Oracle 作者:wenjunheyixiao 时间:2016-01-21 14:30:18 0 删除 编辑

 

1

主库:ENMO11G

备库:SBDB

查看两台机子的IP地址:

[root@enmo11g ~]# ifconfig

[root@sbdb ~]# ifconfig

 

[root@enmo11g ~]# vi /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.70.12    enmo11g

192.168.70.22    sbdb

~                                                                              

~

[root@sbdb ~]# vi /etc/hosts                 

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.70.12    enmo11g

192.168.70.22    sbdb

~                                                                              

~    

 

2.

创建监听:

 

[oracle@enmo11g ~]$ netca

 listener.ora

 tnsname.ora

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

备库同样的创建方法:在此就不截图了!

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

 

两台机子互相ping通,包括主机名

[oracle@sbdb ~]$ tnsping enmo11g

 

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2016 16:58:43

 

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

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = enmo11g)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = enmo11g)))

OK (0 msec)

[oracle@sbdb ~]$ tnsping sbdb

 

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2016 16:59:29

 

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

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sbdb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = sbdb)))

OK (10 msec)

[oracle@enmo11g Desktop]$ tnsping enmo11g

 

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2016 16:59:44

 

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

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = enmo11g)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = enmo11g)))

OK (0 msec)

[oracle@enmo11g Desktop]$ tnsping sbdb

 

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2016 16:59:50

 

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

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sbdb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = sbdb)))

OK (0 msec)

 

3

配置监听:

主库

[oracle@enmo11g product]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

[oracle@enmo11g admin]$ ls

listener1601144PM3155.bak  listener.ora  samples  shrept.lst  tnsnames.ora

[oracle@enmo11g admin]$ vi listener.ora

 

[oracle@enmo11g admin]$ vi tnsnames.ora

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

备库:

[oracle@sbdb Desktop]$  cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

[oracle@sbdb admin]$ ls

listener.ora  samples  shrept.lst  tnsnames.ora

[oracle@sbdb admin]$ vi listener.ora

 

[oracle@sbdb admin]$ vi tnsnames.ora

 

4.

查看监听的状态:

主库:

[oracle@enmo11g admin]$ lsnrctl start

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2016 18:43:08

 

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

 

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

 

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/enmo11g/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enmo11g)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

 

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

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                14-JAN-2016 18:43:10

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

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

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

Listening Endpoints Summary...

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

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

Services Summary...

Service "ENMO11G" has 1 instance(s).

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

The command completed successfully

[oracle@enmo11g admin]$ lsnrctl status

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2016 18:43:19

 

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

 

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

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                14-JAN-2016 18:43:10

Uptime                    0 days 0 hr. 0 min. 8 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

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

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

Listening Endpoints Summary...

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

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

Services Summary...

Service "ENMO11G" has 1 instance(s).

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

The command completed successfully

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

备库:

[oracle@sbdb admin]$ lsnrctl start

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2016 18:42:09

 

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

 

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

 

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/sbdb/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sbdb)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

 

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

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                14-JAN-2016 18:42:11

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

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

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

Listening Endpoints Summary...

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

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

Services Summary...

Service "SBDB" has 1 instance(s).

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

The command completed successfully

[oracle@sbdb admin]$ lsnrctl status

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2016 18:42:20

 

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

 

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

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                14-JAN-2016 18:42:11

Uptime                    0 days 0 hr. 0 min. 9 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

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

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

Listening Endpoints Summary...

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

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

Services Summary...

Service "SBDB" has 1 instance(s).

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

The command completed successfully

 

5

添加重做日志组:

SQL> select member from v$logfile;

 

MEMBER

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

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

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

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

 

SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/ENMO11G/standby_redo04.log' size 50M;

 

Database altered.

 

SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/ENMO11G/standby_redo05.log' size 50M;

 

Database altered.

 

SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/ENMO11G/standby_redo06.log' size 50M;

 

Database altered.

 

SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/ENMO11G/standby_redo07.log'  size 50M;

 

Database altered.

 

删除一个重做日志组:

SQL> alter database drop standby logfile group 8;

 

Database altered.

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

查看当前有多少个重做日志组:

SQL> select member from v$logfile;

 

MEMBER

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

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

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

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

/u01/app/oracle/oradata/ENMO11G/standby_redo04.log

/u01/app/oracle/oradata/ENMO11G/standby_redo05.log

/u01/app/oracle/oradata/ENMO11G/standby_redo06.log

/u01/app/oracle/oradata/ENMO11G/standby_redo07.log

 

7 rows selected.

 

 

6

以spfile来创建pfile

SQL> create pfile from spfile;

 

File created.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                    2257800 bytes

Variable Size                  536874104 bytes

Database Buffers          285212672 bytes

Redo Buffers                    6586368 bytes

Database mounted.

Database opened.

SQL> show parameter pfile

 

NAME                                     TYPE         VALUE

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

spfile                                     string

 

mv掉spfile,以验证pfile是否修改成功

mv spfileENMO11G.ora spfileENMO11G.ora.bak

7

修改主库参数文件

ENMO11G.__db_cache_size=348127232

ENMO11G.__java_pool_size=4194304

ENMO11G.__large_pool_size=8388608

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

ENMO11G.__pga_aggregate_target=335544320

ENMO11G.__sga_target=499122176

ENMO11G.__shared_io_pool_size=0

ENMO11G.__shared_pool_size=125829120

ENMO11G.__streams_pool_size=0

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

*.audit_trail='db'

*.compble='11.2.0.4.0'

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

*.db_block_size=8192

*.db_domain=''

*.db_name='ENMO11G'

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

*.db_recovery_file_dest_size=5218762752

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

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

*.memory_target=833617920

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

 

DB_UNIQUE_NAME=ENMO11G

LOG_ARCHIVE_CONFIG='DG_CONFIG=(ENMO11G,SBDB)'

LOG_ARCHIVE_DEST_1=

 'LOCATION=/u01/app/oracle/fast_recovery_area

  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

  DB_UNIQUE_NAME=ENMO11G'

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

 

FAL_SERVER=SBDB

DB_FILE_NAME_CONVERT='SBDB','ENMO11G'

LOG_FILE_NAME_CONVERT='SBDB','ENMO11G'

STANDBY_FILE_MANAGEMENT=AUTO

 

8

传输参数文件和口令文件(安全考虑,用oracle用户传输会需要验证密码)

[oracle@enmo11g dbs]$ scp initENMO11G.ora 192.168.70.22:/u01/app/oracle/product/11.2.0/db_1/dbs/initSBDB.ora

oracle@192.168.70.22's password:

initENMO11G.ora                               100% 1506     1.5KB/s   00:00   

[oracle@enmo11g dbs]$ scp initENMO11G.ora 192.168.70.22:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwSBDB.ora

oracle@192.168.70.22's password:

initENMO11G.ora                               100% 1506     1.5KB/s   00:00   

在备库中查看从主库中传过去的参数文件与口令文件:

[oracle@sbdb dbs]$ ls

hc_SBDB.dat  init.ora  initSBDB.ora  lkSBDB

[oracle@sbdb dbs]$ ls

hc_SBDB.dat  init.ora  initSBDB.ora  lkSBDB  orapwSBDB.ora

 

9

修改库参数文件

SBDB.__db_cache_size=348127232

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=335544320

SBDB.__sga_target=499122176

SBDB.__shared_io_pool_size=0

SBDB.__shared_pool_size=125829120

SBDB.__streams_pool_size=0

*.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/fast_recovery_area/SBDB/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ENMO11G'

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

*.db_recovery_file_dest_size=5218762752

*.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,ENMO11G)'

LOG_ARCHIVE_DEST_1=

 'LOCATION=/u01/app/oracle/fast_recovery_area

  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

  DB_UNIQUE_NAME=SBDB'

LOG_ARCHIVE_DEST_2=

 'SERVICE=ENMO11G ASYNC

  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

  DB_UNIQUE_NAME=ENMO11G'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

  

FAL_SERVER=ENMO11G

DB_FILE_NAME_CONVERT='ENMO11G','SBDB'

LOG_FILE_NAME_CONVERT='ENMO11G','SBDB'

STANDBY_FILE_MANAGEMENT=AUTO

           

 

10

 新建几个参数文件中的目录

mkdir -p /u01/app/oracle/admin/SBDB/adump

mkdir -p /u01/app/oracle/oradata/SBDB

mkdir -p /u01/app/oracle/fast_recovery_area/SBDB

 

11

备库启动数据库到nomount状态

startup nomount

12

 启动rman(主库)

使用duplicate命令生成standby备库

 

[oracle@enmoD11g admin]$ rman target / auxiliary sys/oracle@SBDB

 

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 15 01:20:44 2016

 

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

 

connected to target database: ENMO11G (DBID=1575790731, not open)

connected to auxiliary database: ENMO11G (not mounted)

 

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

RMAN> duplicate target database for standby from active database;

 

 

Starting Duplicate Db at 2016-01-15 01:21:07

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=133 device type=DISK

 

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwENMO11G' auxiliary format

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

}

executing Memory Script

 

Starting backup at 2016-01-15 01:21:07

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=10 device type=DISK

Finished backup at 2016-01-15 01:21:08

 

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/fast_recovery_area/SBDB/control02.ctl' from

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

}

executing Memory Script

 

Starting backup at 2016-01-15 01:21:09

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/db_1/dbs/snapcf_ENMO11G.f tag=TAG20160115T012109 RECID=1 STAMP=901156870

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

Finished backup at 2016-01-15 01:21:12

 

Starting restore at 2016-01-15 01:21:12

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 2016-01-15 01:21:13

 

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

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

}

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

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting backup at 2016-01-15 01:21:20

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

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

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

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

channel ORA_DISK_1: starting datafile copy

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

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

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

channel ORA_DISK_1: starting datafile copy

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

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

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

channel ORA_DISK_1: starting datafile copy

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

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

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

Finished backup at 2016-01-15 01:26:07

 

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

 

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=901157168 file name=/u01/app/oracle/oradata/SBDB/system01.dbf

datafile 2 switched to datafile copy

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

datafile 3 switched to datafile copy

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

datafile 4 switched to datafile copy

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

Finished Duplicate Db at 2016-01-15 01:26:19

 

 

13

[oracle@sbdb dbs]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 14 17:56:17 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  830930944 bytes

Fixed Size                    2257800 bytes

Variable Size                  536874104 bytes

Database Buffers          285212672 bytes

Redo Buffers                    6586368 bytes
 

SQL> alter database mount;

 

Database altered.

SQL> select status from v$instance;

 

STATUS

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

MOUNTED

14

查看此时的状态:
主库

SQL> select database_role from v$database;

 

DATABASE_ROLE

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

PRIMARY

 

SQL> alter database open;

 

Database altered.

 

 

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ WRITE

 

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

备库

SQL> select database_role from v$database;

 

DATABASE_ROLE

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

PHYSICAL STANDBY


 

SQL> alter database open;

 

Database altered.

 

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ ONLY


 

启动实时应用

首先需要做的是查看一下DG环境中的启动模式,SQL实时应用支持在最高性能和最大可用模式,不支持在最大保护模式的环境中;因为最大保护模式为确保不丢失任何数据,primary上的事务在commit前必须确认REDO已经传递到至少一个standby上。

SQL> set sqlprompt 'PRIMARY>'

PRIMARY>select name,database_role,protection_mode,protection_level from v$database;

 

NAME          DATABASE_ROLE    PROTECTION_MODE        PROTECTION_LEVEL

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

ENMO11G   PRIMARY           MAXIMUM PERFORMANCE        MAXIMUM PERFORMANCE

 

当前的为模式MAXIMUM AVAILABILITY(最大可用模式),无需修改,若发现为MAXIMIZE PROTECTION(最大保护模式),需启动数据库到mount状态修改一下数据库的模式:

 

PRIMARY > alter database set standby database to maximize availability;

Database altered.

最大性能模式对应的是maximize performance

查看日志传输方式:确保由LGWR传输日志至STANDBY

PRIMARY > select DEST_NAME,STATUS,TYPE,ARCHIVER from v$archive_dest;

SQL> select dest_name,status,type,archiver from v$archive_dest;

 

DEST_NAME

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

STATUS          TYPE          ARCHIVER

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

LOG_ARCHIVE_DEST_1

VALID          PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_2

VALID          PUBLIC  LGWR

 

LOG_ARCHIVE_DEST_3

INACTIVE  PUBLIC  FOREGROUND

 

 

DEST_NAME

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

STATUS          TYPE          ARCHIVER

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

LOG_ARCHIVE_DEST_4

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_5

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_6

INACTIVE  PUBLIC  FOREGROUND

 

 

DEST_NAME

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

STATUS          TYPE          ARCHIVER

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

LOG_ARCHIVE_DEST_7

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_8

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_9

INACTIVE  PUBLIC  FOREGROUND

 

 

DEST_NAME

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

STATUS          TYPE          ARCHIVER

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

LOG_ARCHIVE_DEST_10

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_11

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_12

INACTIVE  PUBLIC  FOREGROUND

 

 

DEST_NAME

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

STATUS          TYPE          ARCHIVER

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

LOG_ARCHIVE_DEST_13

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_14

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_15

INACTIVE  PUBLIC  FOREGROUND

 

 

DEST_NAME

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

STATUS          TYPE          ARCHIVER

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

LOG_ARCHIVE_DEST_16

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_17

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_18

INACTIVE  PUBLIC  FOREGROUND

 

 

DEST_NAME

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

STATUS          TYPE          ARCHIVER

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

LOG_ARCHIVE_DEST_19

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_20

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_21

INACTIVE  PUBLIC  FOREGROUND

 

 

DEST_NAME

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

STATUS          TYPE          ARCHIVER

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

LOG_ARCHIVE_DEST_22

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_23

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_24

INACTIVE  PUBLIC  FOREGROUND

 

 

DEST_NAME

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

STATUS          TYPE          ARCHIVER

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

LOG_ARCHIVE_DEST_25

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_26

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_27

INACTIVE  PUBLIC  FOREGROUND

 

 

DEST_NAME

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

STATUS          TYPE          ARCHIVER

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

LOG_ARCHIVE_DEST_28

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_29

INACTIVE  PUBLIC  FOREGROUND

 

LOG_ARCHIVE_DEST_30

INACTIVE  PUBLIC  FOREGROUND

 

 

DEST_NAME

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

STATUS          TYPE          ARCHIVER

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

LOG_ARCHIVE_DEST_31

INACTIVE  PUBLIC  FOREGROUND

 

 

31 rows selected.

如发现不是LGWR,而是ARCH传输,需修改一下日志传输方式:

 

SQL> alter system set log_archive_dest_2='service=orcl_s_tns lgwr valid_for=(online_logfiles,primary_role) db_unique_name=enmo11g';

 

System altered.

 

 

启动实时应用:(备库)----alter database mount;

SQL> alter database recover managed standby database using current logfile disconnect from session;(从redo log发送的)

Database altered.

 

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

 

 SEQUENCE# APPLIED

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

76 YES

77 YES

78 IN-MEMORY

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

Open physical standby 数据库时,不能看到primay数据库上的修改!

如果想看到primary数据库上的修改,必须得启动redo应用.

启动redo应用:

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

 

暂停redo应用

 

此时只是暂时redo应用,并不是停止物理standby数据库,standby仍会保持收,只不过不会再应用接收到的归档,知道你再次启动redo应用为止。

SQL>alter database recover managed standby database cancel;

例子:我在主库中创建了一个表t3:

SQL> create table t3 as select * from dba_objects;

 

Table created.

而此时在备库中查看t3的信息会跟主库完全一样!

SQL> select  * from t3;

15

启用Switchover角色互换  

 

主库:

SQL> select switchover_status from v$database;     

 

SWITCHOVER_STATUS

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

TO STANDBY

 

SQL> alter database commit to switchover to PHYSICAL STANDBY;

 

Database altered.

[oracle@enmo11g Desktop]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 15 12:20:26 2016

 

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

 

Connected to an idle instance.

 

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                    2257800 bytes

Variable Size                  536874104 bytes

Database Buffers          285212672 bytes

Redo Buffers                    6586368 bytes

Database mounted.

SQL> select database_role from v$database;

 

DATABASE_ROLE

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

PHYSICAL STANDBY

 

 

备库:

SQL> select switchover_status from v$database;

 

SWITCHOVER_STATUS

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

NOT ALLOWED

 

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

 

Database altered.

 

SQL> select switchover_status from v$database;

 

SWITCHOVER_STATUS

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

TO PRIMARY

 

SQL> alter database commit to switchover to PRIMARY;

 

Database altered.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> statup mount

SP2-0734: unknown command beginning "statup mou..." - rest of line ignored.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                    2257800 bytes

Variable Size                  536874104 bytes

Database Buffers          285212672 bytes

Redo Buffers                    6586368 bytes

Database mounted.

SQL> select database_role from v$database;

 

DATABASE_ROLE

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

PRIMARY


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30606702/viewspace-1980182/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Data Guard介绍
请登录后发表评论 登录
全部评论

注册时间:2015-12-02

  • 博文量
    117
  • 访问量
    160320