OCM_Session8_2_Step-by-Step Instructions for Creating a Physical Standby Database

  • 2.Step-by-Step Instructions for Creating a Physical Standby Database

准备工作：
创建转储目录

[oracle@ocm2 dbs]$ mkdir -p /u01/app/oracle/admin/PRODSTD/adump
[oracle@ocm2 dbs]$ mkdir -p /u01/app/oracle/admin/PRODSTD/bdump
[oracle@ocm2 dbs]$ mkdir -p /u01/app/oracle/admin/PRODSTD/cdump
[oracle@ocm2 dbs]$ mkdir -p /u01/app/oracle/admin/PRODSTD/udump  


  • 2.1.Create a Backup Copy of the Primary Database Datafiles
冷备份主库

SYS@PROD>!
[oracle@ocm1 ~]$ cd /u01/app/oracle/oradata/
[oracle@ocm1 oradata]$ ll
total 4
drwxr-xr-x 7 oracle oinstall 4096 Mar 19 13:16 PROD
[oracle@ocm1 oradata]$ tar -czvf PROD.tar.gz PROD
PROD/
PROD/Disk1/
PROD/Disk1/temp01.dbf
PROD/Disk1/standby07.log
PROD/Disk1/standby11.log
PROD/Disk1/redo03.log
PROD/Disk1/standby10.log
PROD/Disk1/standby09.log
PROD/Disk1/system01.dbf
PROD/Disk1/redo01.log
PROD/Disk1/standby08.log
PROD/Disk1/standby06.log
PROD/Disk1/control01.ctl
PROD/Disk1/users01.dbf
PROD/Disk1/sysaux01.dbf
PROD/Disk1/example01.dbf
PROD/Disk1/redo02.log
PROD/Disk1/redo05.log
PROD/Disk1/redo04.log
PROD/Disk1/undotbs01.dbf
PROD/Disk2/
PROD/Disk2/standby07_1.log
PROD/Disk2/standby06_1.log
PROD/Disk2/redo03_1.log
PROD/Disk2/standby09_1.log
PROD/Disk2/standby08_1.log
PROD/Disk2/control02.ctl
PROD/Disk2/redo05_1.log
PROD/Disk2/redo02_1.log
PROD/Disk2/redo01_1.log
PROD/Disk2/redo04_1.log
PROD/Disk2/standby10_1.log
PROD/Disk2/standby11_1.log
PROD/Disk2/arch/
PROD/Disk2/arch/arc_05p3jeqe_1_1.bak
PROD/Disk3/
PROD/Disk3/control03.ctl
PROD/Disk4/
PROD/Disk5/
PROD/Disk5/bak/
PROD/Disk5/bak/PROD_04p3jeq8_1_1.bak
PROD/Disk5/bak/PROD_03p3jemu_1_1.bak
[oracle@ocm1 oradata]$ exit
exit

  • 2.2.Create a Control File for the Standby Database
成备库的控制文件

SYS@PROD>startup mount
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  1220360 bytes
Variable Size             171966712 bytes
Database Buffers          348127232 bytes
Redo Buffers                2973696 bytes
Database mounted.
SYS@PROD>
SYS@PROD>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/control01.ctl'; 

Database altered.

SYS@PROD> ALTER DATABASE OPEN;

Database altered.

  • 2.3.Prepare an Initialization Parameter File for the Standby Database

准备备库的参数文件initPRODSTD.ora

①在主库中将主库的pfile文件传输到备库中
[oracle@ocm1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@ocm1 dbs]$ ll
total 6744
-rw-rw---- 1 oracle oinstall    1544 Mar 25 09:59 hc_PROD.dat
-rw-r----- 1 oracle oinstall   12920 May  3  2001 initdw.ora
-rw-r----- 1 oracle oinstall    8385 Sep 11  1998 init.ora
-rw-r--r-- 1 oracle oinstall    1827 Mar 25 09:39 initPROD.ora
-rw-rw---- 1 oracle oinstall      24 Mar 18 10:18 lkPROD
-rw-r----- 1 oracle oinstall    5120 Mar 18 09:54 orapwPROD
-rw-r--r-- 1 oracle oinstall     318 Mar 17 20:37 pfile.ora
-rw-r----- 1 oracle oinstall 6832128 Mar 19 19:51 snapcf_PROD.f
-rw-r----- 1 oracle oinstall    4608 Mar 25 09:57 spfilePROD.ora
[oracle@ocm1 dbs]$ scp initPROD.ora ocm2.localdomain:/u01/app/oracle/product/10.2.0/db_1/dbs
The authenticity of host 'ocm2.localdomain (192.168.1.156)' can't be established.
RSA key fingerprint is de:2a:4c:d0:b2:20:88:4c:a2:72:24:11:50:4b:d6:74.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ocm2.localdomain,192.168.1.156' (RSA) to the list of known hosts.
oracle@ocm2.localdomain's password: 
initPROD.ora                                                                                                                         100% 1827     1.8KB/s   00:00    
[oracle@ocm1 dbs]$ 

②，在备库中修改pfile

[oracle@ocm2 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@ocm2 dbs]$ ll
total 48
-rw-rw---- 1 oracle oinstall  1544 Mar 24 14:10 hc_EMREP.dat
-rw-r----- 1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-r----- 1 oracle oinstall  8385 Sep 11  1998 init.ora
-rw-r--r-- 1 oracle oinstall  1827 Mar 25 10:08 initPROD.ora
-rw-rw---- 1 oracle oinstall    24 Mar 20 15:14 lkEMREP
-rw-r----- 1 oracle oinstall  1536 Mar 20 16:16 orapwEMREP
-rw-r----- 1 oracle oinstall  2560 Mar 24 14:10 spfileEMREP.ora
[oracle@ocm2 dbs]$ mv initPROD.ora initPRODSTD.ora
[oracle@ocm2 dbs]$ vi initPRODSTD.ora

修改内容如下：

PRODSTD.__db_cache_size=331350016
PRODSTD.__java_pool_size=4194304
PRODSTD.__large_pool_size=4194304
PRODSTD.__shared_pool_size=180355072
PRODSTD.__streams_pool_size=0
*.BACKGROUND_DUMP_DEST='/u01/app/oracle/admin/PRODSTD/bdump'
*.control_files='/u01/app/oracle/oradata/PRODSTD/Disk1/control01.ctl','/u01/app/oracle/oradata/PRODSTD/Disk2/control02.ctl','/u01/app/oracle/oradata/PRODSTD/Disk3/control03.ctl'
*.CORE_DUMP_DEST='/u01/app/oracle/admin/PRODSTD/cdump'
*.DB_BLOCK_SIZE=8192
*.DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/PRODSTD/Disk1'
*.DB_CREATE_ONLINE_LOG_DEST_1='/u01/app/oracle/oradata/PRODSTD/Disk1'
*.DB_NAME='PROD'
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest='/home/oracle/flash'
*.job_queue_processes=15
*.max_dispatchers=10
*.max_shared_servers=30
*.processes=135
*.sessions=300
*.SGA_TARGET=500M
*.shared_server_sessions=200
*.shared_servers=10
*.UNDO_MANAGEMENT='auto'
*.undo_retention=5400
*.UNDO_TABLESPACE='undotbs1'
*.USER_DUMP_DEST='/u01/app/oracle/admin/PRODSTD/udump'
*.utl_file_dir='/home/oracle','/home/oracle/temp','/home/oracle/scripts'

##parameter for Primary Database
DB_NAME=PROD 
DB_UNIQUE_NAME=PRODSTD
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PRODSTD)'
CONTROL_FILES='/u01/app/oracle/oradata/PRODSTD/Disk1/control01.ctl','/u01/app/oracle/oradata/PRODSTD/Disk2/control02.ctl','/u01/app/oracle/oradata/PRODSTD/Disk3/control03.ctl' 
LOG_ARCHIVE_DEST_1='location=/u01/app/oracle/oradata/PRODSTD/Disk2/arch  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODSTD'
LOG_ARCHIVE_DEST_2='SERVICE=PROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'
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
##parameter for Standby Database    
FAL_SERVER=PROD
FAL_CLIENT=PRODSTD
DB_FILE_NAME_CONVERT='PROD','PRODSTD'
LOG_FILE_NAME_CONVERT='PROD','PRODSTD'
STANDBY_FILE_MANAGEMENT=AUTO


Note：
此处需注意参数文件里的路径必须存在

[oracle@ocm2 dbs]$  mkdir /home/oracle/flash
[oracle@ocm2 dbs]$  mkdir /home/oracle/temp
[oracle@ocm2 dbs]$ mkdir /home/oracle/scripts

  • 2.4.Copy Files from the Primary System to the Standby System
从主库拷贝文件到备库

①password parameter file created in 1.2

[oracle@ocm2 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@ocm1 dbs]$ scp orapwPROD ocm2.localdomain:/u01/app/oracle/product/10.2.0/db_1/dbs
oracle@ocm2.localdomain's password: 
orapwPROD                                                                                                                            100% 5120     5.0KB/s   00:01    
[oracle@ocm1 dbs]$ 

在备库中修改密码文件名称
[oracle@ocm2 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@ocm2 dbs]$ ll
total 56
-rw-rw---- 1 oracle oinstall  1544 Mar 24 14:10 hc_EMREP.dat
-rw-r----- 1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-r----- 1 oracle oinstall  8385 Sep 11  1998 init.ora
-rw-r--r-- 1 oracle oinstall  1960 Mar 25 10:13 initPRODSTD.ora
-rw-rw---- 1 oracle oinstall    24 Mar 20 15:14 lkEMREP
-rw-r----- 1 oracle oinstall  1536 Mar 20 16:16 orapwEMREP
-rw-r----- 1 oracle oinstall  5120 Mar 25 10:17 orapwPROD
-rw-r----- 1 oracle oinstall  2560 Mar 24 14:10 spfileEMREP.ora
[oracle@ocm2 dbs]$ mv orapwPROD orapwPRODSTD



②Backup datafiles created in Section 2.1 

[oracle@ocm1 dbs]$ scp /u01/app/oracle/oradata/PROD.tar.gz ocm2.localdomain:/u01/app/oracle/oradata/
oracle@ocm2.localdomain's password: 
PROD.tar.gz                                                                                                                          100%  225MB   2.8MB/s   01:20    
[oracle@ocm1 dbs]$ 

在备库中恢复

[oracle@ocm2 ~]$ cd /u01/app/oracle/oradata/
[oracle@ocm2 oradata]$ ll
total 230872
drwxr-x--- 2 oracle oinstall      4096 Mar 20 15:16 EMREP
-rw-r--r-- 1 oracle oinstall 236170241 Mar 25 10:21 PROD.tar.gz
[oracle@ocm2 oradata]$ tar -xzvf  PROD.tar.gz
PROD/
PROD/Disk1/
PROD/Disk1/temp01.dbf
PROD/Disk1/standby07.log
PROD/Disk1/standby11.log
PROD/Disk1/redo03.log
PROD/Disk1/standby10.log
PROD/Disk1/standby09.log
PROD/Disk1/system01.dbf
PROD/Disk1/redo01.log
PROD/Disk1/standby08.log
PROD/Disk1/standby06.log
PROD/Disk1/control01.ctl
PROD/Disk1/users01.dbf
PROD/Disk1/sysaux01.dbf
PROD/Disk1/example01.dbf
PROD/Disk1/redo02.log
PROD/Disk1/redo05.log
PROD/Disk1/redo04.log
PROD/Disk1/undotbs01.dbf
PROD/Disk2/
PROD/Disk2/standby07_1.log
PROD/Disk2/standby06_1.log
PROD/Disk2/redo03_1.log
PROD/Disk2/standby09_1.log
PROD/Disk2/standby08_1.log
PROD/Disk2/control02.ctl
PROD/Disk2/redo05_1.log
PROD/Disk2/redo02_1.log
PROD/Disk2/redo01_1.log
PROD/Disk2/redo04_1.log
PROD/Disk2/standby10_1.log
PROD/Disk2/standby11_1.log
PROD/Disk2/arch/
PROD/Disk2/arch/arc_05p3jeqe_1_1.bak
PROD/Disk3/
PROD/Disk3/control03.ctl
PROD/Disk4/
PROD/Disk5/
PROD/Disk5/bak/
PROD/Disk5/bak/PROD_04p3jeq8_1_1.bak
PROD/Disk5/bak/PROD_03p3jemu_1_1.bak
[oracle@ocm2 oradata]$ 
[oracle@ocm2 oradata]$ ll
total 230876
drwxr-x--- 2 oracle oinstall      4096 Mar 20 15:16 EMREP
drwxr-xr-x 7 oracle oinstall      4096 Mar 19 13:16 PROD
-rw-r--r-- 1 oracle oinstall 236170241 Mar 25 10:21 PROD.tar.gz
[oracle@ocm2 oradata]$ mv PROD PRODSTD
[oracle@ocm2 oradata]$ ll
total 230876
drwxr-x--- 2 oracle oinstall      4096 Mar 20 15:16 EMREP
drwxr-xr-x 7 oracle oinstall      4096 Mar 19 13:16 PRODSTD
-rw-r--r-- 1 oracle oinstall 236170241 Mar 25 10:21 PROD.tar.gz
[oracle@ocm2 oradata]$ rm -rf PROD.tar.gz 
[oracle@ocm2 oradata]$ ll
total 8
drwxr-x--- 2 oracle oinstall 4096 Mar 20 15:16 EMREP
drwxr-xr-x 7 oracle oinstall 4096 Mar 19 13:16 PRODSTD-路径和PROD的路径一致

删除PRODSTD中原主库控制文件
[oracle@ocm2 oradata]$ pwd
/u01/app/oracle/oradata
[oracle@ocm2 oradata]$ rm -rf PRODSTD/Disk1/control01.ctl 
[oracle@ocm2 oradata]$ rm -rf PRODSTD/Disk2/control02.ctl 
[oracle@ocm2 oradata]$ rm -rf PRODSTD/Disk3/control03.ctl 

③Standby control file created in Section2.2 

[oracle@ocm1 dbs]$  scp /home/oracle/control01.ctl ocm2.localdomain:/u01/app/oracle/oradata/PRODSTD/Disk1
oracle@ocm2.localdomain's password: 
control01.ctl                                                                                                                        100% 6672KB   3.3MB/s   00:02    
[oracle@ocm1 dbs]$ 


④Initialization parameter file created in Section2.3，已经拷贝

  • 2.5 Set Up the Environment to Support the Standby Database

Step 1   Create a Windows-based service.-linux操作下面不用操作这一步
Step 2   Create a password file. 2.4.1已经创建
Step 3   Configure listeners for the primary and standby databases.
Step 4   Create Oracle Net service names.


ocm1
[oracle@ocm1 dbs]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@ocm1 admin]$ ll
total 20
-rw-r--r-- 1 oracle oinstall  712 Mar 18 12:30 listener.ora
drwxr-x--- 2 oracle oinstall 4096 Mar 16 19:17 samples
-rw-r----- 1 oracle oinstall  172 Dec 26  2003 shrept.lst
-rw-r--r-- 1 oracle oinstall   41 Mar 18 13:38 sqlnet.ora
-rw-r--r-- 1 oracle oinstall  589 Mar 18 18:47 tnsnames.ora

[oracle@ocm1 admin]$ cat listener.ora 
LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.localdomain)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=PROD)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME=PROD))
    (SID_DESC=
      (GLOBAL_DBNAME=PRODSTD)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)----添加
      (SID_NAME=PRODSTD))
     (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM=extproc)))

LSNR2=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.localdomain)(PORT=1526))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc1))))
[oracle@ocm1 admin]$ cat tnsnames.ora 
prod=
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.localdomain)(PORT=1521))
  (CONNECT_DATA=
   (SERVICE_NAME=PROD)
   (SERVER=dedicated)))

PRODSTD=
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=ocm2.localdomain)(PORT=1521))-添加
  (CONNECT_DATA=
   (SERVICE_NAME=PRODSTD)
   (SERVER=dedicated)))
[oracle@ocm1 admin]$                                                                                                              

[oracle@ocm1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-MAR-2014 10:48:36

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

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

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                25-MAR-2014 10:48:38
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/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "EMREP" has 1 instance(s).
  Instance "EMREP", status UNKNOWN, has 1 handler(s) for this service...
Service "PROD" has 1 instance(s).
  Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
Service "PRODSTD" has 1 instance(s).
  Instance "PRODSTD", status UNKNOWN, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ocm1 admin]$ 



ocm2：

[oracle@ocm2 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC=
      (GLOBAL_DBNAME=PROD)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME=PROD))  
  (SID_DESC=
      (GLOBAL_DBNAME=PRODSTD)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1) ----------添加
      (SID_NAME=PRODSTD))
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocm2.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

[oracle@ocm2 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
PROD =
  (description =
    (address=(protocol=tcp)(host=ocm1.localdomain)(port=1521))
    (connect_data=
        (server = dedicated)
        (service_name = PROD)
    )
  )

PRODSTD =
  (description =
    (address=(protocol=tcp)(host=ocm2.localdomain)(port=1521)) --添加
    (connect_data=
        (server = dedicated)
        (service_name = PRODSTD)
    )
  )

[oracle@ocm2 admin]$ 

[oracle@ocm2 admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-MAR-2014 11:03:00

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

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

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm2.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ocm2.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                25-MAR-2014 11:03:02
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/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm2.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PROD" has 1 instance(s).
  Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
Service "PRODSTD" has 1 instance(s).
  Instance "PRODSTD", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ocm2 admin]$ 

Step 5   Create a server parameter file for the standby database.

[oracle@ocm2 ~]$ export ORACLE_SID=PRODSTD
[oracle@ocm2 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 25 11:06:14 2014

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

Connected to an idle instance.

SYS@PRODSTD>create spfile from pfile;

File created.


  • 2.6.Start the Physical Standby Database
启动备库

Step 1   Start the physical standby database.

SYS@PRODSTD>startup mount
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  1220360 bytes
Variable Size             188743928 bytes
Database Buffers          331350016 bytes
Redo Buffers                2973696 bytes
Database mounted.
SYS@PRODSTD>
备库
SYS@PRODSTD>select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED
主库
SYS@PROD>select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          SESSIONS ACTIVE

Step 2   Start Redo Apply.

备库只有在mount状态的时候可以应用日志，read only状态不能应用日志
应用日志调用2个MRP进程启动LEO3库日志应用并后台运行
alter database recover managed standby database disconnect from session parallel 2;

备库
SYS@PRODSTD>alter database recover managed standby database disconnect from session parallel 2;

Database altered.

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

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE

主库
SYS@PROD>select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          SESSIONS ACTIVE

Step 3   Test archival operations to the physical standby database.

SYS@PROD>alter system switch logfile;

System altered.


tail -f alert_PROD.log

LNS1 started with pid=59, OS id=9381
Tue Mar 25 11:23:53 2014
Thread 1 advanced to log sequence 13
  Current log# 5 seq# 13 mem# 0: /u01/app/oracle/oradata/PROD/Disk1/redo05.log
  Current log# 5 seq# 13 mem# 1: /u01/app/oracle/oradata/PROD/Disk2/redo05_1.log
Tue Mar 25 11:23:54 2014
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 13 for destination LOG_ARCHIVE_DEST_2
Tue Mar 25 11:23:55 2014
ARC4: Standby redo logfile selected for thread 1 sequence 12 for destination LOG_ARCHIVE_DEST_2

tail -f alert_PRODSTD.log

Tue Mar 25 11:23:54 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 4259
RFS[2]: Identified database type as 'physical standby'
Tue Mar 25 11:23:54 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 4257
RFS[3]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Successfully opened standby log 6: '/u01/app/oracle/oradata/PRODSTD/Disk1/standby06.log'
Tue Mar 25 11:23:55 2014
RFS[2]: Successfully opened standby log 7: '/u01/app/oracle/oradata/PRODSTD/Disk1/standby07.log'
Tue Mar 25 11:23:58 2014
Media Recovery Log /u01/app/oracle/oradata/PRODSTD/Disk2/arch/1_12_842523531.arc
Media Recovery Waiting for thread 1 sequence 13 (in transit)
Tue Mar 25 11:24:30 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 4262
RFS[4]: Identified database type as 'physical standby'

  • 2.7 Verify the Physical Standby Database Is Performing Properly

Step 1   Identify the existing archived redo log files.

SYS@PRODSTD>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied from v$archived_log;

 SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- --------- --------- ---
        11 25-MAR-14 25-MAR-14 YES
        12 25-MAR-14 25-MAR-14 YES


Step 2   Force a log switch to archive the current online redo log file.

SYS@PROD>ALTER SYSTEM SWITCH LOGFILE;

System altered.

tail -f alert_PROD.log
Tue Mar 25 11:32:49 2014
Thread 1 advanced to log sequence 14
  Current log# 1 seq# 14 mem# 0: /u01/app/oracle/oradata/PROD/Disk1/redo01.log
  Current log# 1 seq# 14 mem# 1: /u01/app/oracle/oradata/PROD/Disk2/redo01_1.log
Tue Mar 25 11:32:50 2014
LNS: Standby redo logfile selected for thread 1 sequence 14 for destination LOG_ARCHIVE_DEST_2

tail -f alert_PRODSTD.log
Tue Mar 25 11:32:50 2014
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Successfully opened standby log 7: '/u01/app/oracle/oradata/PRODSTD/Disk1/standby07.log'
Tue Mar 25 11:32:51 2014
Media Recovery Log /u01/app/oracle/oradata/PRODSTD/Disk2/arch/1_13_842523531.arc
Media Recovery Waiting for thread 1 sequence 14 (in transit)


Step 3   Verify the new redo data was archived on the standby database.
Step 4   Verify new archived redo log files were applied.

SYS@PRODSTD>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied from v$archived_log;

 SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- --------- --------- ---
        11 25-MAR-14 25-MAR-14 YES
        12 25-MAR-14 25-MAR-14 YES
        13 25-MAR-14 25-MAR-14 YES
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/hzcya1995/p/13315870.html