DataGuard配置全过程

1、准备
1）检查归档
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

2）检查数据库force logging
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR
—
NO

SQL> alter database force logging;

Database altered.

(在DataGuard环境下,如果主库不加alter database force logging .
 主库用工具做大量数据插入而不到日志中去,
 此时插入的数据不会同步到DataGuard数据库。)
2、dg目标库初始化
1）生产库备份
RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> backup database format ‘/u01/rmanbak/full_%d_%T_%s_%p.bak’;  
6> }
然后backup 归档.(备份归档之前先切几次redo）

allocated channel: c1
channel c1: sid=142 devtype=DISK

allocated channel: c2
channel c2: sid=141 devtype=DISK

Starting backup at 11-APR-14
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/ggdg/system01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/ggdg/users01.dbf
channel c1: starting piece 1 at 11-APR-14
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00003 name=/u01/app/oracle/oradata/ggdg/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/ggdg/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/ggdg/undotbs01.dbf
channel c2: starting piece 1 at 11-APR-14
channel c1: finished piece 1 at 11-APR-14
piece handle=/u01/rmanbak/full_GGDG_20140411_6_1.bak tag=TAG20140411T170922 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:56
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
channel c1: starting piece 1 at 11-APR-14
channel c1: finished piece 1 at 11-APR-14
piece handle=/u01/rmanbak/full_GGDG_20140411_8_1.bak tag=TAG20140411T170922 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:06
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 11-APR-14
channel c1: finished piece 1 at 11-APR-14
piece handle=/u01/rmanbak/full_GGDG_20140411_9_1.bak tag=TAG20140411T170922 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:16
channel c2: finished piece 1 at 11-APR-14
piece handle=/u01/rmanbak/full_GGDG_20140411_7_1.bak tag=TAG20140411T170922 comment=NONE
channel c2: backup set complete, elapsed time: 00:01:19
Finished backup at 11-APR-14
released channel: c1
released channel: c2

2）创建standby控制文件
RMAN> backup current controlfile for standby format ‘/u01/rmanbak/ctl_std’;

Starting backup at 11-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 11-APR-14
channel ORA_DISK_1: finished piece 1 at 11-APR-14
piece handle=/u01/rmanbak/ctl_std tag=TAG20140411T171121 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-APR-14

3）创建参数文件

SQL> create pfile=’/u01/rmanbak/t.txt’ from spfile;

File created.

4）把文件传到目标端
[oracle@dg1 rmanbak]$ scp * 172.168.56.174:/u01/rmanbak
oracle@172.168.56.174’s password: 
ctl_std                                       100% 6944KB   6.8MB/s   00:00    
full_GGDG_20140411_6_1.bak                    100%  366MB  15.9MB/s   00:23    
full_GGDG_20140411_7_1.bak                    100%  240MB  30.0MB/s   00:08    
full_GGDG_20140411_8_1.bak                    100% 6944KB   6.8MB/s   00:00    
full_GGDG_20140411_9_1.bak                    100%   96KB  96.0KB/s   00:00    
t.txt                                         100%  892     0.9KB/s   00:00 

[oracle@dg1 dbs]$ scp orapwggdg 172.168.56.174:/u01/app/oracle/product/10.2.0/db_1/dbs
oracle@172.168.56.174’s password: 
orapwggdg                                     100% 1536     1.5KB/s   00:00 

5）目标端编辑参数文件
[oracle@dg2 rmanbak]$ vi t.txt 

*.background_dump_dest=’/u01/app/oracle/admin/ggdg/bdump’
*.compatible=’10.2.0.5.0′
*.control_files=’/u01/app/oracle/oradata/ggdg/control01.ctl’,’/u01/app/oracle/oradata/ggdg/control02.ctl’,’/u01/app/oracle/oradata/ggdg/control03.ctl’
ggdg.__java_pool_size=4194304
ggdg.__large_pool_size=4194304
ggdg.__shared_pool_size=117440512
ggdg.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/ggdg/adump’
*.background_dump_dest=’/u01/app/oracle/admin/ggdg/bdump’
*.compatible=’10.2.0.5.0′
*.control_files=’/u01/app/oracle/oradata/ggdg1/control01.ctl’,’/u01/app/oracle/oradata/ggdg1/control02.ctl’,’/u01/app/oracle/oradata/ggdg1/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/ggdg/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’ggdg’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ggdgXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=142606336
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=429916160
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/ggdg/udump’

6)编辑两端listner.ora及tnsnames.ora
生产库
[oracle@dg1 admin]$ vi 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_NAME = ggdg)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)
      (SID_NAME = ggdg)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.56.172)(PORT = 1521))
    )
  )

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

PRIMARY_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.56.172)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ggdg)
    )
  )

STANDBY_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.56.174)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ggdg)
    )
  )

目标端
[oracle@dg2 admin]$ vi 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_NAME = ggdg)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)
      (SID_NAME = ggdg)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.56.174)(PORT = 1521))
    )
  )

[oracle@dg2 admin]$ vi 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.
PRIMARY_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.56.172)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ggdg)
    )
  )

STANDBY_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.56.174)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ggdg)
    )
  )

7）还原目标端数据库
[oracle@dg2 oracle]$ mkdir -p /u01/app/oracle/admin/ggdg/adump
[oracle@dg2 oracle]$ mkdir -p /u01/app/oracle/admin/ggdg/bdump
[oracle@dg2 oracle]$ mkdir -p /u01/app/oracle/admin/ggdg/cdump
[oracle@dg2 oracle]$ mkdir -p /u01/app/oracle/admin/ggdg/dpdump
[oracle@dg2 oracle]$ mkdir -p /u01/app/oracle/admin/ggdg/udump
[oracle@dg2 oracle]$ mkdir -p /u01/app/oracle/admin/ggdg/pfile

SQL> startup nomount pfile=’/oracle/rmanbak/t.txt’;
ORACLE instance started.

Total System Global Area  432013312 bytes
Fixed Size                  2096824 bytes
Variable Size             125829448 bytes
Database Buffers          297795584 bytes
Redo Buffers                6291456 bytes

RMAN> restore controlfile from ‘/u01/rmanbak/ctl_std’;

Starting restore at 11-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/u01/app/oracle/oradata/ggdg1/control01.ctl
output filename=/u01/app/oracle/oradata/ggdg1/control02.ctl
output filename=/u01/app/oracle/oradata/ggdg1/control03.ctl
Finished restore at 11-APR-14

SQL> alter database mount standby database;

Database altered.

SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/ggdg1/system01.dbf
/u01/app/oracle/oradata/ggdg1/undotbs01.dbf
/u01/app/oracle/oradata/ggdg1/sysaux01.dbf
/u01/app/oracle/oradata/ggdg1/users01.dbf
/u01/app/oracle/oradata/ggdg1/example01.dbf

RMAN> restore database;

Starting restore at 11-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/ggdg1/system01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/ggdg1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbak/full_GGDG_20140411_6_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rmanbak/full_GGDG_20140411_6_1.bak tag=TAG20140411T170922
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/ggdg1/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/ggdg1/sysaux01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/ggdg1/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbak/full_GGDG_20140411_7_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rmanbak/full_GGDG_20140411_7_1.bak tag=TAG20140411T170922
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 11-APR-14

3、生产库
SQL> alter system set log_archive_dest_2=’SERVICE=standby_db’ scope=both;

System altered.

4、目标端
SQL> alter database recover managed standby database disconnect from session;

Database altered.

5、生产库
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
         1          1          4   52428800          1 YES INACTIVE
       439625 11-APR-14

         2          1          5   52428800          1 NO  CURRENT
       440075 11-APR-14

         3          1          3   52428800          1 YES INACTIVE
       418788 11-APR-14

SQL> alter system archive log current;

System altered.

6、目标端查看alert日志
[oracle@dg2 bdump]$ tail -f alert_ggdg.log 
Clearing online log 3 of thread 1 sequence number 3
Fri Apr 11 17:51:33 CST 2014
Errors in file /u01/app/oracle/admin/ggdg/bdump/ggdg_mrp0_5769.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: ‘/u01/app/oracle/oradata/ggdg1/redo03.log’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 complete
Media Recovery Waiting for thread 1 sequence 5
Fri Apr 11 17:52:12 CST 2014
RFS[1]: Archived Log: ‘/u01/arch/1_5_844607256.dbf’
Fri Apr 11 17:52:17 CST 2014
Media Recovery Log /u01/arch/1_5_844607256.dbf
Media Recovery Waiting for thread 1 sequence 6

7、关闭dg
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> create spfile from pfile=’/u01/rmanbak/t.txt’;

File created.
8、启动dg
[oracle@dg2 bdump]$ lsnrctl stop
[oracle@dg2 bdump]$ lsnrctl start

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  2096920 bytes
Variable Size             130023656 bytes
Database Buffers          306184192 bytes
Redo Buffers                6291456 bytes
SQL> alter database mount standby database;

Database altered.

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

Database altered.

9、打开恢复模式
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

10、主备切换
1）主切换备
SQL> select status from v$instance;

STATUS
————
OPEN

SQL> alter database commit to switchover to physical standby with session shutdown;把生产切成dg

Database altered.

SQL> select status from v$instance;

STATUS
————
STARTED

修改生产库参数
SQL> create pfile=’/oracle/rmanbak/a.txt’ from spfile;

File created.

[oracle@dg1 rmanbak]$ vi a.txt 
去掉了
*.log_archive_dest_2=’SERVICE=standby_db’
添加了
*.standby_archive_dest=’/u01/app/oracle/oradata/stdarch’
*.standby_file_management=’AUTO’
*.fal_client=’PRIMARY_DB’
*.fal_server=’STANDBY_DB’
*.db_file_name_convert=’/u01/app/oracle/oradata/ggdg1/’,’/u01/app/oracle/oradata/ggdg/’
*.log_file_name_convert=’/u01/app/oracle/oradata/ggdg1/’,’/u01/app/oracle/oradata/ggdg/’

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

2）备切换主
[oracle@dg2 bdump]$ vi /u01/rmanbak/t.txt 
编辑参数文件
去掉了
*.standby_archive_dest=’/u01/arch/’
*.standby_file_management=’AUTO’
*.fal_client=’STANDBY_DB’
*.fal_server=’PRIMARY_DB’
*.db_file_name_convert=’/u01/app/oracle/oradata/ggdg/’,’/u01/app/oracle/oradata/ggdg1/’
*.log_file_name_convert=’/u01/app/oracle/oradata/ggdg/’,’/u01/app/oracle/oradata/ggdg1/’
添加了
*.log_archive_dest_2=’SERVICE=primary_db’

SQL> alter database commit to switchover to primary;——-dg变生产

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup nomount pfile=’/u01/rmanbak/t.txt’;
ORACLE instance started.

Total System Global Area  432013312 bytes
Fixed Size                  2096824 bytes
Variable Size             125829448 bytes
Database Buffers          297795584 bytes
Redo Buffers                6291456 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

3）新目标库
SQL> create spfile from pfile=’/u01/rmanbak/a.txt’;

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  432013312 bytes
Fixed Size                  2096824 bytes
Variable Size             125829448 bytes
Database Buffers          297795584 bytes
Redo Buffers                6291456 bytes

SQL> show parameter fal_

NAME                                 TYPE        VALUE
———————————— ———– ——————————
fal_client                           string      PRIMARY_DB
fal_server                           string      STANDBY_DB
 

SQL> alter database mount standby database;

Database altered.

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

Database altered.

4）新生产库
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
         1          1         11   52428800          1 NO  CURRENT
       447328 11-APR-14

         2          1          0   52428800          1 YES UNUSED
            0

         3          1          0   52428800          1 YES UNUSED
            0

SQL> alter system set log_archive_dest_1=’LOCATION=/u01/app/oracle/product/10.2.0/db_1/dbs’;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> create tablespace test datafile ‘/u01/app/oracle/oradata/ggdg1/test.dbf’ size 10m;

Tablespace created.

SQL> alter system archive log current;

System altered.

5）新目标库
查看归档
[oracle@dg1 stdarch]$ ls
1_11_844607256.dbf  1_12_844607256.dbf  1_13_844607256.dbf  1_14_844607256.dbf

查看日志
[oracle@dg1 bdump]$ tail -f alert_ggdg.log 
————————————————————-
Fri Apr 11 18:33:25 CST 2014
RFS[1]: Archived Log: ‘/u01/app/oracle/oradata/stdarch/1_11_844607256.dbf’
RFS[1]: Archived Log: ‘/u01/app/oracle/oradata/stdarch/1_12_844607256.dbf’
RFS[1]: Archived Log: ‘/u01/app/oracle/oradata/stdarch/1_13_844607256.dbf’
Fri Apr 11 18:33:47 CST 2014
Media Recovery Log /u01/app/oracle/oradata/stdarch/1_11_844607256.dbf
Media Recovery Log /u01/app/oracle/oradata/stdarch/1_12_844607256.dbf
Media Recovery Log /u01/app/oracle/oradata/stdarch/1_13_844607256.dbf
Media Recovery Waiting for thread 1 sequence 14
Fri Apr 11 18:36:44 CST 2014
RFS[1]: Archived Log: ‘/u01/app/oracle/oradata/stdarch/1_14_844607256.dbf’
Fri Apr 11 18:36:48 CST 2014
Media Recovery Log /u01/app/oracle/oradata/stdarch/1_14_844607256.dbf
Recovery created file /u01/app/oracle/oradata/ggdg/test.dbf
Successfully added datafile 6 to media recovery
Datafile #6: ‘/u01/app/oracle/oradata/ggdg/test.dbf’
Media Recovery Waiting for thread 1 sequence 15

查看数据文件
SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/ggdg/system01.dbf
/u01/app/oracle/oradata/ggdg/undotbs01.dbf
/u01/app/oracle/oradata/ggdg/sysaux01.dbf
/u01/app/oracle/oradata/ggdg/users01.dbf
/u01/app/oracle/oradata/ggdg/example01.dbf
/u01/app/oracle/oradata/ggdg/test.dbf

6 rows selected.

11、生产库瘫痪，强行激活目标端

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database activate standby database;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  432013312 bytes
Fixed Size                  2096824 bytes
Variable Size             125829448 bytes
Database Buffers          297795584 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.

未经允许不得转载：SRE空间 » DataGuard配置全过程