[Oracle] oracle 11g dataguard (one instance)

oracle 11g standby (one instance)

primary xx.xx.xx.100   (node2)
standby xx.xx.xx.35    (oracle)

1.standby server add listener and tnsnames
[oracle@oracle6 admin]$ more listener.ora
SID_LIST_LISTENER2 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = node2)
      (ORACLE_HOME = /u01/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = node2)
    )
  )

LISTENER2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.35)(PORT = 1522))
  )

ADR_BASE_LISTENER = /u01

[oracle@oracle6 admin]$ more tnsnames.ora
node2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.100) (PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = node2)
    )
  )
可以使用tnsping 測試是否連通
 
2.primary server add tnsnames
[oracle@node2 admin]$ more tnsnames.ora
node4 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.35)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = node2)
    )
  )
可以使用tnsping 測試是否連通

3.將primary server上的password file copy到standby server上
[oracle@node2 admin]$ scp orapwnode2 oracle@172.16.99.35:/u01/oracle/product/11.2.0/dbhome_1/dbs

4.primary server backup database and copy to standby server
RMAN>run{
backup current controlfile format '/oradata/rmanbackup/control.ctl' for standby
backup as compressed backupset database format '/oradata/rmanbackup/%U'
}

[oracle@node2 rmanbackup]$ scp * oracle@172.16.99.35:/home/oracle/oradata/rmanbackup
oracle@172.16.99.35's password:
02r7g1f7_1_1                                                                                                                                                  100%  262MB 130.9MB/s   00:02    
03r7g1gk_1_1                                                                                                                                                  100% 1072KB   1.1MB/s   00:00    
control.ctl                                                                                                                                                   100% 9568KB   9.3MB/s   00:01

5.standby server上edit pfile
先在primary server上create pfile將檔案複製到standby server上
[oracle@oracle6 dbs]$ more initnode2.ora
*.audit_file_dest='/u01/oracle/admin/node2/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/oradata/oradata/control01.ctl','/home/oracle/oradata/oradata/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='node2'
*.diagnostic_dest='/u01/oracle'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
db_file_name_convert='/oradata/node2','/home/oracle/oradata/oradata'
log_file_name_convert='/oradata/node2','/home/oracle/oradata/oradata'

6.standby server restore database
[oracle@oracle6 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jun 6 19:39:31 2016

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes
Variable Size                159387560 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5226496 bytes

RMAN> restore standby controlfile from '/home/oracle/oradata/rmanbackup/control.ctl';

Starting restore at 06-JUN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/oradata/oradata/control01.ctl
output file name=/home/oracle/oradata/oradata/control02.ctl
Finished restore at 06-JUN-16

RMAN> catalog backuppiece '/home/oracle/oradata/rmanbackup/02r7g1f7_1_1';

cataloged backup piece
backup piece handle=/home/oracle/oradata/rmanbackup/02r7g1f7_1_1 RECID=1 STAMP=913837612

RMAN> catalog backuppiece '/home/oracle/oradata/rmanbackup/03r7g1gk_1_1';

cataloged backup piece
backup piece handle=/home/oracle/oradata/rmanbackup/03r7g1gk_1_1 RECID=2 STAMP=913837625

RMAN> restore database;

Starting restore at 06-JUN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

skipping datafile 1; already restored to file /home/oracle/oradata/oradata/system01.dbf
skipping datafile 3; already restored to file /home/oracle/oradata/oradata/undotbs01.dbf
skipping datafile 4; already restored to file /home/oracle/oradata/oradata/users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oradata/oradata/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/oradata/rmanbackup/02r7g1f7_1_1
channel ORA_DISK_1: piece handle=/home/oracle/oradata/rmanbackup/02r7g1f7_1_1 tag=TAG20160606T191135
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 06-JUN-16

SQL> alter database add standby logfile '/home/oracle/oradata/oradata/st_redo01.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/home/oracle/oradata/oradata/st_redo02.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/home/oracle/oradata/oradata/st_redo03.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/home/oracle/oradata/oradata/st_redo04.log' size 50m;

Database altered.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

SQL> alter system set log_archive_dest_1='location="/home/oracle/oradata/arch"';

System altered.


PS
1.搭建之前需要在primary database上執行 alter database force logging
2.restore standby controlfile from '/home/oracle/oradata/rmanbackup/control.ctl';
3.其中遇到問題,standby_file_management=MANUAL的時候，create tablespace的sql不會傳輸到standby server上。
需要做如下動作。
SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
SQL> alter system set standby_file_management=auto;

System altered.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active

SQL> alter database create datafile '/u01/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00005' as '/home/oracle/oradata/oradata/mon.dbf';
alter database create datafile '/u01/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00005' as '/home/oracle/oradata/oradata/mon.dbf'
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management
is automatic.


SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL> alter database create datafile '/u01/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00005' as '/home/oracle/oradata/oradata/mon.dbf';

Database altered.

SQL> alter system set standby_file_management=auto;

System altered.