Configure Oracle Dataguard primary-asm to Physical-asm____oracle

In a production environment, DBAs tend to face dozens of of hundreds of g of data, or even the T-level, file system storage data files in these situations, I/O problems will gradually exposed, so the data storage in the production environment generally use ASM, or bare devices, Oracle 11G does not support raw devices to store data at the beginning, so this talk mainly describes the configuration of the guard physical database in the ASM environment.

Environment Introduction:
Main Library IP:192.168.227.20/24
Main Library SID:ORCL
Main Library DB_NAME:ORCL
Main Library Db_unique_name:primary
Main Library SERVICES_NAME:primary.yang.com

Ip:192.168.227.30/24 of Reserve Library
SID:ORCL of Reserve Library
DB_NAME:ORCL of Reserve Library
Db_unique_name:physical of Reserve Library
SERVICES_NAME:physical.yang.com of Reserve Library

One: Main Library preparation work

1: Configuring the ASM Environment [Root@primary ~]#/u01/app/oracle/product/10.2.0/db_1/bin/localconfig add [Root@primary ~]# ps-ef |grep CSS Root 4180 1 0 13:07? 00:00:00/bin/su-l oracle-c sh-c ' Cd/u01/app/oracle/product/10.2.0/db_1/log/primary/cssd;ulimit-c Unlimited; EXEC/U01/APP/ORACLE/PRODUCT/10.2.0/DB_1/BIN/OCSSD ' Oracle 4332 4180 0 13:08? 00:00:00/u01/app/oracle/product/10.2.0/db_1/bin/ocssd.bin Root 4682 4265 0 13:17 pts/1 00:00:00 grep css [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 4459 1 0 13:10? 00:00:00 asm_pmon_+asm Oracle 4461 1 0 13:10? 00:00:00 asm_psp0_+asm Oracle 4463 1 0 13:10? 00:00:00 asm_mman_+asm Oracle 4465 1 0 13:10? 00:00:00 asm_dbw0_+asm Oracle 4467 1 0 13:10? 00:00:00 asm_lgwr_+asm Oracle 4469 1 0 13:10? 00:00:00 asm_ckpt_+asm Oracle 4471 1 0 13:10? 00:00:00 asm_smon_+asm Oracle 4473 1 0 13:10? 00:00:00 asm_rbal_+asm Oracle 4475 1 0 13:10? 00:00:00 asm_gmon_+asm Oracle 4665 5702 0 13:16 pts/2 00:00:00 grep-i ASM [Oracle@primary ~]$ Export Oracle_sid=+asm [Oracle@primary ~]$ Sqlplus/nolog Sql> Conn/as SYSDBA Sql> select Instance_name,status from V$instance; Instance_n STATUS ---------- ------------------------------------ +asm started sql> Create DiskGroup data normal redundancy 2 Failgroup FG1 Disk 3 '/DEV/RAW/RAW1 ' name Asmdisk1, 4 '/dev/raw/raw2 ' name Asmdisk2 5 Failgroup FG2 Disk 6 '/dev/raw/raw3 ' name Asmdisk3, 7 '/dev/raw/raw4 ' name asmdisk4; DiskGroup created. sql> Create DiskGroup fraexternal redundancy 2 disk '/dev/raw/raw5 ' name asmdisk5; DiskGroup created. Sql> select Name,path,failgroup from V$asm_disk; Namepath Failgroup ---------- ---------------------------------------- -------------------- Asmdisk5/dev/raw/raw5 ASMDISK5 ASMDISK4/DEV/RAW/RAW4 FG2 ASMDISK3/DEV/RAW/RAW3 FG2 ASMDISK2/DEV/RAW/RAW2 FG1 ASMDISK1/DEV/RAW/RAW1 FG1 Sql> select Name,total_mb,free_mb,usable_file_mb from V$asm_diskgroup; NAMETOTAL_MB FREE_MB USABLE_FILE_MB ---------- ---------- ---------- -------------- DATA 81920 8181430667 FRA 58368 5831858318 2: Building a library [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; Nametypevalue ------------------------------------ ----------- ------------------------------ SPFile String+data/orcl/spfileorcl.ora Sql> show parameter control; Nametypevalue ------------------------------------ ----------- ------------------------------ Control_file_record_keep_time Integer 7 Control_files string+data/orcl/controlfile/current .260.765985991, +fra/orcl/cont rolfile/current.256.765985991 sql> archive log list; Database Log Mode Archive mode Automatic Archival Enabled Archive Destination +FRA/ORCL Oldest online log sequence 3 Next Log sequence to archive 5 Current log Sequence 5 Sql> Show parameter db_recovery_file_dest; Nametypevalue ------------------------------------ ----------- --------- Db_recovery_file_dest String+fra Db_recovery_file_dest_size Big Integer 50000M 3: Add standby Log Group 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; MemberType -------------------------------------------------- ------- +data/orcl/onlinelog/group_3.263.765986013 ONLINE +fra/orcl/onlinelog/group_3.259.765986017 ONLINE +data/orcl/onlinelog/group_2.262.765986005 ONLINE +fra/orcl/onlinelog/group_2.258.765986009 ONLINE +data/orcl/onlinelog/group_1.261.765985997 ONLINE +fra/orcl/onlinelog/group_1.257.765986003 ONLINE +data/primary/onlinelog/group_4.268.765996737 STANDBY +fra/primary/onlinelog/group_4.264.765996743 STANDBY +data/primary/onlinelog/group_5.269.765996751 STANDBY +fra/primary/onlinelog/group_5.265.765996757 STANDBY +data/primary/onlinelog/group_6.270.765996763 STANDBY MemberType -------------------------------------------------- ------- +fra/primary/onlinelog/group_6.266.765996767 STANDBY +data/primary/onlinelog/group_7.271.765996775 STANDBY +fra/primary/onlinelog/group_7.267.765996779 STANDBY 4: Configure data Guard related parameters 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; Nametypevalue ------------------------------------ ----------- ------------------------------ Remote_login_passwordfile stringexclusive 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>alter system set log_archive_format= '%t_%s_%r.arc ' scope=spfile; 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: Configure Listener.ora and Tnsnames.ora files (the same configuration is required on the standby), reboot the Listener and database, and ensure that the database is connected properly [Oracle@primary ~]$ cat $ORACLE _home/network/admin/listener.ora Sid_list_listener = (Sid_list = (Sid_desc = (Sid_name = Plsextproc) (Global_dbname = primary_DGMGRL.yang.com) (Oracle_home =/u01/app/oracle/product/10.2.0/db_1) (program = Extproc) ) ) LISTENER = (Description_list = (DESCRIPTION = (address = (PROTOCOL = TCP) (HOST = 192.168.227.20) (PORT = 1521)) (address = (PROTOCOL = IPC) (KEY = EXTPROC0)) ) [Oracle@primary ~]$ cat $ORACLE _home/network/admin/tnsnames.ora Primary = (DESCRIPTION = (Address_list = (address = (PROTOCOL = TCP) (HOST = 192.168.227.20) (PORT = 1521)) ) (Connect_data = (service_name = primary.yang.com) ) ) Physical = (DESCRIPTION = (Address_list = (address = (PROTOCOL = TCP) (HOST = 192.168.227.30) (PORT = 1521)) ) (Connect_data = (service_name = physical.yang.com) ) ) [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 a idle instance. Sql> Startup Sql> Connsys/123456@primaryas SYSDBA Connected. Sql> show parameter name; Nametypevalue ------------------------------------ ----------- ---------------------- Db_file_name_convert string Db_name STRINGORCL Db_unique_name stringprimary Global_names Booleanfalse instance_name STRINGORCL Lock_name_space string Log_file_name_convert string Service_names stringprimary.yang.com 6: Back up the main library related files 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> backup format '/home/oracle/dg_backup/ctl_asm_%u ' current controlfile for standby; [Oracle@primary ~]$ ll-h/home/oracle/dg_backup/ Total 670M -RW-R-----1 Oracle oinstall 6.8M Oct 15:32 ctl_asm_05mqg6vt_1_1 -RW-R-----1 Oracle oinstall 64M Oct 15:28 dg_01mqg6o9_1_1 -RW-R-----1 Oracle oinstall 592M Oct 15:29 dg_02mqg6oj_1_1 -RW-R-----1 Oracle oinstall 6.9M Oct 15:29 dg_03mqg6qa_1_1 -RW-R-----1 Oracle oinstall 25K Oct 15:29 dg_04mqg6qh_1_1 -rw-r--r--1 Oracle oinstall 1.5K Oct 15:24 Initorcl.ora

Second: The configuration on the standby library

1: Configure ASM [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, +, Oracle. All rights reserved. Sql> Conn/as SYSDBA Connected. Sql> select Name,path,failgroup from V$asm_disk; Namepath Failgroup ---------- ---------------------------------------- ---------- Asmdisk5/dev/raw/raw5 ASMDISK5 ASMDISK4/DEV/RAW/RAW4 FG2 ASMDISK3/DEV/RAW/RAW3 FG2 ASMDISK2/DEV/RAW/RAW2 FG1 ASMDISK1/DEV/RAW/RAW1 FG1 2: Copy the backup data on the main library [Oracle@physical ~]$ scp-rp primary:/home/oracle/dg_backup./ [Oracle@physical ~]$ Cat Dg_backup/initorcl.ora (Modify the following parameters) *.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, +, Oracle. All rights reserved. Sql> Conn/as SYSDBA Connected. Sql> select Open_mode,name from V$database; Open_modename ---------- --------- Mounted ORCL sql> ALTER DATABASE recover managed standby database disconnect from session; Database Altered Total System Global Area167772160 bytes Fixed Size 1218316 bytes Variable Size 83888372 bytes Database buffers 79691776 bytes Redo buffers 2973696 bytes [Oracle@physical ~]$ Rman targetsys/123456@primaryauxiliary/ Recovery manager:release 10.2.0.1.0-production on Mon Oct 31 17:26:18 2011 Copyright (c) 1982, +, Oracle. All rights reserved. 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 Verify: [Oracle@physical ~]$ Sqlplus/nolog Sql*plus:release 10.2.0.1.0-production on Mon Oct 31 17:43:16 2011 Copyright (c) 1982, +, Oracle. All rights reserved. Sql> Conn/as SYSDBA Connected. Sql> Select sequence#,first_time,next_time,applied from V$archived_log order by 1; sequence# First_timenext_time APP ---------- ------------------- ------------------- --- 2 2011-10-31:13:57:04 2011-10-31:14:02:38 NO 3 2011-10-31:14:02:38 2011-10-31:14:24:53 NO 4 2011-10-31:14:24:53 2011-10-31:14:25:57 NO 5 2011-10-31:14:25:57 2011-10-31:15:28:03 NO 6 2011-10-31:15:28:03 2011-10-31:15:29:17 NO 7 2011-10-31:15:29:17 2011-10-31:16:59:50 NO 8 2011-10-31:16:59:50 2011-10-31:17:01:33 YES 7 rows selected. To view log information: [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 Switch Log on Main library: sql> alter system switch logfile; System altered. sql> alter system switch logfile; System altered. Re-query on the standby: Sql> Select sequence#,first_time,next_time,applied from V$archived_log where sequence# >= 8 order by 1; sequence# First_timenext_time APP ---------- ------------------- ------------------- --- 8 2011-10-31:16:59:50 2011-10-31:17:01:33 YES 9 2011-10-31:17:01:33 2011-10-31:17:45:49 YES Ten 2011-10-31:17:45:49 2011-10-31:17:49:26 YES One 2011-10-31:17:49:26 2011-10-31:17:50:42 YES To view log information: [Oracle@physical admin]$ tail-f/u01/app/oracle/admin/orcl/bdump/alert_orcl.log Fetching Gap sequence