Configure Oracle Dataguard Primary-ASM to Physical-ASM
来源:互联网 发布:股票数据接口api 编辑:程序博客网 时间:2024/06/09 14:53
环境介绍:
主库IP:192.168.227.20/24
主库SID: orcl
主库DB_NAME:orcl
主库DB_UNIQUE_NAME:primary
主库SERVICES_NAME: primary.yang.com
备库IP:192.168.227.30/24
备库SID: orcl
备库DB_NAME:orcl
备库DB_UNIQUE_NAME:physical
备库SERVICES_NAME: physical.yang.com
一:主库准备工作
[root@primary ~]# /u01/app/oracle/product/10.2.0/db_1/bin/localconfig add
[root@primary ~]# ps -ef |grep css
root
exec /u01/app/oracle/product/10.2.0/db_1/bin/ocssd '
oracle
root
[root@primary ~]# cd /u01/app/oracle/product/10.2.0/db_1/bin/
[root@primary bin]# ./crsctl check crs
CSS appears healthy
Cannot communicate with CRS
Cannot communicate with EVM
[oracle@primary ~]# ps -ef |grep -i asm
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
[oracle@primary ~]$ export ORACLE_SID=+ASM
[oracle@primary ~]$ sqlplus /nolog
SQL> conn /as sysdba
SQL> select instance_name,status
INSTANCE_N STATUS
---------- ------------------------------------
+ASM
SQL> create diskgroup data normal redundancy
Diskgroup created.
SQL> create diskgroup fra
Diskgroup created.
SQL> select name,path,failgroup from v$asm_disk;
NAME
---------- ---------------------------------------- --------------------
ASMDISK5
ASMDISK4
ASMDISK3
ASMDISK2
ASMDISK1
SQL> select name,total_mb,free_mb,usable_file_mb from v$asm_diskgroup;
NAME
---------- ---------- ---------- --------------
DATA
FRA
2:建库
[oracle@primary ~]$ env |grep ORA
ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
SQL> select file_name from dba_data_files;
FILE_NAME
------------------------------------------
+DATA/orcl/datafile/users.259.765985893
+DATA/orcl/datafile/sysaux.257.765985893
+DATA/orcl/datafile/undotbs1.258.765985893
+DATA/orcl/datafile/system.256.765985891
+DATA/orcl/datafile/example.265.765986057
SQL> select member from v$logfile;
MEMBER
--------------------------------------------
+DATA/orcl/onlinelog/group_3.263.765986013
+FRA/orcl/onlinelog/group_3.259.765986017
+DATA/orcl/onlinelog/group_2.262.765986005
+FRA/orcl/onlinelog/group_2.258.765986009
+DATA/orcl/onlinelog/group_1.261.765985997
+FRA/orcl/onlinelog/group_1.257.765986003
SQL> show parameter spfile;
NAME
------------------------------------ ----------- ------------------------------
spfile
SQL> show parameter control;
NAME
------------------------------------ ----------- ------------------------------
control_file_record_keep_time
control_files
SQL> archive log list;
Database log mode
Automatic archival
Archive destination
Oldest online log sequence
Next log sequence to archive
Current log sequence
SQL> show parameter db_recovery_file_dest;
NAME
------------------------------------ ----------- ---------
db_recovery_file_dest
db_recovery_file_dest_size
3:添加standby日志组
SQL> alter database add standby logfile group 4 size 50M;
SQL> alter database add standby logfile group 5 size 50M;
SQL> alter database add standby logfile group 6 size 50M;
SQL> alter database add standby logfile group 7 size 50M;
SQL> select member,type from v$logfile;
MEMBER
-------------------------------------------------- -------
+DATA/orcl/onlinelog/group_3.263.765986013
+FRA/orcl/onlinelog/group_3.259.765986017
+DATA/orcl/onlinelog/group_2.262.765986005
+FRA/orcl/onlinelog/group_2.258.765986009
+DATA/orcl/onlinelog/group_1.261.765985997
+FRA/orcl/onlinelog/group_1.257.765986003
+DATA/primary/onlinelog/group_4.268.765996737
+FRA/primary/onlinelog/group_4.264.765996743
+DATA/primary/onlinelog/group_5.269.765996751
+FRA/primary/onlinelog/group_5.265.765996757
+DATA/primary/onlinelog/group_6.270.765996763
MEMBER
-------------------------------------------------- -------
+FRA/primary/onlinelog/group_6.266.765996767
+DATA/primary/onlinelog/group_7.271.765996775
+FRA/primary/onlinelog/group_7.267.765996779
4:配置Data guard相关参数
SQL> alter system set db_unique_name='primary' scope=spfile;
System altered.
SQL> alter system set log_archive_config='DG_CONFIG=(primary,physical)';
System altered.
SQL> show parameter remote_login;
NAME
------------------------------------ ----------- ------------------------------
remote_login_passwordfile
SQL> alter system set log_archive_dest_1='LOCATION=+FRA/orcl valid_for=(all_logfiles,all_roles) db_unique_name=primary' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_2='service=physical lgwr async valid_for=(online_logfile,primary_role) db_unique_name=physical' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_state_1=enable;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL>
System altered.
SQL> alter system set fal_server='physical';
System altered.
SQL> alter system set fal_client='primary';
System altered.
SQL> alter database force logging;
Database altered
5:配置listener.ora和tnsnames.ora文件(备库上需要做同样的配置),重启监听器和数据库,确保数据库连接正常
[oracle@primary ~]$ cat $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
LISTENER =
[oracle@primary ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
primary =
physical =
[oracle@primary ~]$ sqlplus /nolog
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
[oracle@primary ~]$ lsnrctl stop
[oracle@primary ~]$ lsnrctl start
[oracle@primary ~]$ sqlplus /nolog
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
SQL> conn
Connected.
SQL> show parameter name;
NAME
------------------------------------ ----------- ----------------------
db_file_name_convert
db_name
db_unique_name
global_names
instance_name
lock_name_space
log_file_name_convert
service_names
6:备份主库相关文件
SQL> !mkdir -p /home/oracle/dg_backup
SQL> create pfile='/home/oracle/dg_backup/initorcl.ora' from spfile;
File created.
[oracle@primary ~]$ rman target /
RMAN> backup incremental level 0 format '/home/oracle/dg_backup/dg_%U'
2> tag 'dg_asm' database plus archivelog;
RMAN>
total 670M
-rw-r----- 1 oracle oinstall 6.8M Oct 31 15:32 ctl_asm_05mqg6vt_1_1
-rw-r----- 1 oracle oinstall
-rw-r----- 1 oracle oinstall 592M Oct 31 15:29 dg_02mqg6oj_1_1
-rw-r----- 1 oracle oinstall 6.9M Oct 31 15:29 dg_03mqg6qa_1_1
-rw-r----- 1 oracle oinstall
-rw-r--r-- 1 oracle oinstall 1.5K Oct 31 15:24 initorcl.ora
二:备库上的配置
[oracle@physical ~]$ export ORACLE_SID=+ASM
[oracle@physical ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 31 17:18:29 2011
Copyright (c) 1982, 2005, Oracle.
SQL> conn /as sysdba
Connected.
SQL> select name,path,failgroup from v$asm_disk;
NAME
---------- ---------------------------------------- ----------
ASMDISK5
ASMDISK4
ASMDISK3
ASMDISK2
ASMDISK1
2:复制主库上的备份数据
[oracle@physical ~]$ scp -rp primary:/home/oracle/dg_backup ./
[oracle@physical ~]$ cat dg_backup/initorcl.ora (修改如下参数)
*.db_unique_name='physical'
*.fal_client='physical'
*.fal_server='primary'
*.log_archive_dest_1='LOCATION=+FRA/orcl valid_for=(all_logfiles,all_roles) db_unique_name=physical'
*.log_archive_dest_2='service=primary lgwr async valid_for=(online_logfile,primary_role) db_unique_name=primary'
[oracle@physical ~]$ cp dg_backup/initorcl.ora $ORACLE_HOME/dbs
[oracle@physical ~]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=123456 entries=5
[oracle@physical ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 31 17:33:30 2011
Copyright (c) 1982, 2005, Oracle.
SQL> conn /as sysdba
Connected.
SQL> select open_mode,name from v$database;
OPEN_MODE
---------- ---------
MOUNTED
SQL> alter database recover managed standby database disconnect from session;
Database altered
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
[oracle@physical ~]$ rman target
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Oct 31 17:26:18 2011
Copyright (c) 1982, 2005, Oracle.
connected to target database: ORCL (DBID=1293766727)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby nofilenamecheck;
SQL> alter database recover managed standby database disconnect from session;
Database altered
验证:
[oracle@physical ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 31 17:43:16 2011
Copyright (c) 1982, 2005, Oracle.
SQL> conn /as sysdba
Connected.
SQL> select sequence#,first_time,next_time,applied from v$archived_log order by 1;
---------- ------------------- ------------------- ---
7 rows selected.
查看日志信息:
[oracle@physical ~]$ cd /u01/app/oracle/admin/orcl/bdump/
[oracle@physical bdump]$ tail -f alert_orcl.log
Archivelog restore complete. Elapsed time: 0:00:01
Archivelog restore complete. Elapsed time: 0:00:00
Archivelog restore complete. Elapsed time: 0:00:01
Archivelog restore complete. Elapsed time: 0:00:02
Archivelog restore complete. Elapsed time: 0:00:03
Archivelog restore complete. Elapsed time: 0:00:06
Archivelog restore complete. Elapsed time: 0:00:00
Mon Oct 31 17:41:33 2011
Media Recovery Log +FRA/orcl/1_8_765985997.arc
Media Recovery Waiting for thread 1 sequence 9
主库上切换日志:
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
备库上再次查询:
SQL> select sequence#,first_time,next_time,applied from v$archived_log where sequence# >= 8 order by 1;
---------- ------------------- ------------------- ---
查看日志信息:
[oracle@physical admin]$ tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log
Fetching gap sequence in thread 1, gap sequence 9-10
Mon Oct 31 17:51:14 2011
RFS[1]: Archived Log: '+FRA/orcl/1_9_765985997.arc'
RFS[1]: Archived Log: '+FRA/orcl/1_10_765985997.arc'
RFS[1]: Archived Log: '+FRA/orcl/1_11_765985997.arc'
Mon Oct 31 17:51:48 2011
Media Recovery Log +FRA/orcl/1_9_765985997.arc
Media Recovery Log +FRA/orcl/1_10_765985997.arc
Media Recovery Log +FRA/orcl/1_11_765985997.arc
Media Recovery Waiting for thread 1 sequence 12
三:排错
SQL> select sequence#,first_time,next_time,applied from v$archived_log;
no rows selected
SQL> archive log list;
Database log mode
Automatic archival
Archive destination
Oldest online log sequence
Next log sequence to archive
Current log sequence
SQL> select * from v$archive_gap;
no rows selected
[oracle@physical ~]$ cd /u01/app/oracle/admin/orcl/bdump/
[oracle@physical bdump]$ ls
alert_orcl.log
[oracle@physical bdump]$ tail -f alert_orcl.log
FAL[client]: Failed to request gap sequence
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
主库上查看归档日志信息
SQL> archive log list;
Database log mode
Automatic archival
Archive destination
Oldest online log sequence
Next log sequence to archive
Current log sequence
在备库上还原归档日志
RMAN> restore archivelog all;
channel ORA_DISK_1: reading from backup piece /home/oracle/dg_backup/dg_09mqgc7f_1_1
ORA-19870: error reading backup piece /home/oracle/dg_backup/dg_09mqgc7f_1_1
ORA-19504: failed to create file "+FRA/orcl/1_8_765985997.arc"
ORA-17502: ksfdcre:4 Failed to create file +FRA/orcl/1_8_765985997.arc
ORA-15173: entry 'orcl' does not exist in directory '/'
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/31/2011 17:39:52
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 1 seq 8 lowscn 489318 found to restore
RMAN-06025: no backup of log thread 1 seq 7 lowscn 485345 found to restore
RMAN-06025: no backup of log thread 1 seq 6 lowscn 485300 found to restore
RMAN-06025: no backup of log thread 1 seq 5 lowscn 482789 found to restore
RMAN-06025: no backup of log thread 1 seq 4 lowscn 482752 found to restore
RMAN-06025: no backup of log thread 1 seq 3 lowscn 479773 found to restore
RMAN-06025: no backup of log thread 1 seq 2 lowscn 472799 found to restore
[oracle@physical dg_backup]$ export ORACLE_SID=+ASM
[oracle@physical dg_backup]$ asmcmd
ASMCMD> cd FRA
ASMCMD> ls
PHYSICAL/
ASMCMD> mkdir orcl
ASMCMD> ls
PHYSICAL/
orcl/
RMAN> restore archivelog all;
ASMCMD> cd orcl
ASMCMD> ls
1_2_765985997.arc
1_3_765985997.arc
1_4_765985997.arc
1_5_765985997.arc
1_6_765985997.arc
1_7_765985997.arc
1_8_765985997.arc
2:关闭主库后,重启物理备库,报错如下,找不到控制文件
SQL> startup mount
ORA-00205: error in identifying control file, check alert log for more info
[oracle@physical dbs]$ grep
*.control_files='+DATA/physical/controlfile/Current.256.765999025','+FRA/physcial/controlfile/backup.256.765999027'
SQL> conn /as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
配置物理备库以spfile方式启动,spfile不使用ASM管理
SQL> create spfile from pfile;
File created.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown immediate
SQL> startup mount
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> show parameter spfile;
NAME
------------------------------------ ----------- ------------------------------
spfile
3:配置ASM报错如下
[root@physical ~]# /u01/app/oracle/product/10.2.0/db_1/bin/localconfig add
/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized
Adding to inittab
Startup will be queued to init within 90 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
Giving up: Oracle CSS stack appears NOT to be running.
Oracle CSS service would not start as installed
Automatic Storage Management(ASM) cannot be used until Oracle CSS service is started
[root@physical ~]# /etc/init.d/init.cssd run >/dev/null 2>&1 [1] 16323
[root@physical ~]# ps -ef |grep css
root
root
root
[root@physical ~]# cd /u01/app/oracle/product/10.2.0/db_1/bin/
[root@physical bin]# ./crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM
[root@physical bin]# ./crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly
[root@physical bin]# ./crsctl check crs
CSS appears healthy
Cannot communicate with CRS
Cannot communicate with EVM
4:物理备库standby日志组invalid问题排错
主库:
SQL> select member,type from v$logfile;
MEMBER
------------------------------------------------------------ ----------
+DATA/orcl/onlinelog/group_3.263.765986013
+FRA/orcl/onlinelog/group_3.259.765986017
+DATA/orcl/onlinelog/group_2.262.765986005
+FRA/orcl/onlinelog/group_2.258.765986009
+DATA/orcl/onlinelog/group_1.261.765985997
+FRA/orcl/onlinelog/group_1.257.765986003
+DATA/primary/onlinelog/group_4.268.765996737
+FRA/primary/onlinelog/group_4.264.765996743
+DATA/primary/onlinelog/group_5.269.765996751
+FRA/primary/onlinelog/group_5.265.765996757
+DATA/primary/onlinelog/group_6.270.765996763
MEMBER
------------------------------------------------------------ ----------
+FRA/primary/onlinelog/group_6.266.765996767
+DATA/primary/onlinelog/group_7.271.765996775
+FRA/primary/onlinelog/group_7.267.765996779
备库:
SQL> select member,type from v$logfile;
MEMBER
------------------------------------------------------------ ----------
+DATA/physical/onlinelog/group_3.264.765999293
+FRA/physical/onlinelog/group_3.259.765999299
+DATA/physical/onlinelog/group_2.263.765999285
+FRA/physical/onlinelog/group_2.258.765999291
+DATA/physical/onlinelog/group_1.262.765999279
+FRA/physical/onlinelog/group_1.257.765999283
+DATA
+FRA