Dataguard By Rman On Same Directory Structure
ON PRIMARY DATABASE ENABLE ARCHIVE LOG
à SQL> alter database FORCE logging
1à SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2à SQL> startup mount
ORACLE instance started.
Total System Global Area 247463936 bytes
Fixed Size 1218748 bytes
Variable Size 79693636 bytes
Database Buffers 159383552 bytes
Redo Buffers 7168000 bytes
Database mounted.
à SQL> alter database archivelog;
Database altered.
à SQL> alter database open;
à Do Oracle 10g software only installation on Standby site
SETUP TNSNAMES.ORA ON PRIMARY DB
PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tamirpm)
)
)
stby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tamirpm)
)
)
SETUP TNSNAMES.ORA ON STANDBY
PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tamirpm)
)
)
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tamirpm)
)
)
SETUP INITIALIZATION PARAMETER
The fetch archive log (FAL) client and server resolve gaps detected in the range of archived redo log files generated at the primary database and received at the physical standby database.
■ The FAL client requests the transfer of archived redo log files automatically.
■ The FAL server services the FAL requests coming from the FAL client.
Alter system set FAL_SERVER=STBY scope=both sid=’*’;
Alter system set FAL_CLIENT=PRIM scope=both;
Alter system set standby_file_management=auto scope=both;
à Alter system set LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' scope=both;
OR
Alter system set LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/archive' scope=both;
à ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STBY LGWR SYNC AFFIRM' scope=both;
Reference Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2) B14239-05
Specifying Synchronous or Asynchronous Network Transmission
The LGWR process synchronously writes to the local online redo log files at the same time it transmits redo data to the remote destination:
■ The SYNC attribute performs all network I/O synchronously, in conjunction with each write operation to the online redo log file, and waits for the network I/O to complete.
This is the default network transmission setting.
Specifying the SYNC attribute on the LOG_ARCHIVE_DEST_n parameter is optional, because this is the default for LGWR archival processing.
■ The ASYNC attribute performs all network I/O asynchronously and control is returned to the executing application or user immediately, without waiting for the network I/O to complete.
When the LGWR and ASYNC attributes are specified, the log writer process writes to the local online redo log file, while the network server (LNSn) processes (one for each destination) asynchronously transmit the redo to remote destinations. The LGWR process continues processing the next request without waiting for the LNS network I/O to complete.
If redo transport services transmit redo data to multiple remote destinations, the LNSn processes (one for each destination) initiate the network I/O to all of the destinations in parallel.
ADD THE STANDBY REDOLOG FILE AT PRIMARY SITE ONLY FOR SWITCH OVER
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/opt/oracle/db/oradata/tamirpm/stlog11.rdo') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('/opt/oracle/db/oradata/tamirpm/stlog12.rdo') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('/opt/oracle/db/oradata/tamirpm/stlog13.rdo') SIZE 50M;
à SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
11 0 0 YES UNASSIGNED
12 0 0 YES UNASSIGNED
13 0 0 YES UNASSIGNED
COPY THE PASSWORD FILE, SPFILE, LISTENER files FROM PRIMARY TO STANBY SERVER
[oracle@rhel4 ~]$ scp -p /opt/oracle/db/dbs/inittamirpm.ora 192.168.1.11:/opt/oracle/db/dbs/
[oracle@rhel4 ~]$ scp -p /opt/oracle/db/dbs/PWDoratamirpm 192.168.1.11:/opt/oracle/db/dbs/
[oracle@rhel4 ~]$ scp -p /opt/oracle/db/network/admin/listner.ora 192.168.1.11:/opt/oracle/db/network/admin/
CREATE A FOLDER FOR RMAN BACKUP ON BOTH MACHINES
[root@rhel4 oracle]# mkdir bkup
[root@rhel4 oracle]# chown -R oracle.dba bkup
[root@rhel4 oracle]# chmod -R 775 bkup
Make Directory on standby server
Bdump , Udump, cdump, archive , oradata/SID
[root@rhel4 oracle]# mkdir bkup
[root@rhel4 oracle]# chown -R oracle.dba bkup
[root@rhel4 oracle]# chmod -R 775 bkup
TAKE A BACKUP ON PRIMARY DB
[oracle@rhel4 bin]$ ./rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Feb 11 15:22:25 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TAMIRPM (DBID=3686906617)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/oracle/bkup/%d_%F.rman';
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/opt/oracle/bkup/bk%d_%s_%p.rman';
Backup the current production database to create a standby database:
RMAN>
run { allocate channel c1 type disk; allocate channel c2 type disk; backup as compressed backupset database format '/opt/oracle/bkup/D%U'; backup current controlfile for standby format '/opt/oracle/bkup/C%U'; sql 'alter system archive log current';
}OR
RMAN> Backup as compressed backupset database include current controlfile for standby;
SQL> alter system archive log current;
Move backup & last 3 archived logs from primary to standby server
[oracle@rhel4 ~]$ scp -p /opt/oracle/bkup/* 192.168.1.11:/opt/oracle/bkup/
oracle@192.168.1.11's password:
bkTAMIRPM_1_1.rman 100% 4026KB 3.9MB/s 00:01
bkTAMIRPM_2_1.rman 100% 569MB 2.4MB/s 03:57
bkTAMIRPM_3_1.rman 100% 6976KB 2.3MB/s 00:03
bkTAMIRPM_4_1.rman 100% 20KB 20.0KB/s 00:00
[oracle@rhel4 ~]$
Create password file on standby server if not moved from source Db and start standby DB to nomount;
Note Password should be same as primary server DB
orapwd file='/opt/oracle/db/dbs/orapwtamirpm' password=oracle entries=5
sqlplus connect / as sysdba
sql> startup nomount
Connect to Rman on standby server
Note enter service name of Primary site.
RMAN target sys/oracle@prim auxiliary sys/oracle@stbyRMAN> duplicate target database for standby nofilenamecheck dorecover;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;Database altered.
Now Switch the logfile on Primary Site
Alter system switch logfile;
Now let’s see if our logs get applied on the standby server.
From the standby db issue:
SQL> select sequence#, applied, status from v$archived_log;----------
22
Select Group#, status from v$standby_log;Set the data protection mode.
To specify a data protection mode, issue the
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE}
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;Confirm the configuration is operating in the new protection mode.
Query the V$DATABASE view to confirm the Data Guard configuration is operating in
the new protection mode. For example:
SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;
PROTECTION_MODE PROTECTION_LEVEL
--------------------- ---------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILIT
To display the list of archived redo log files that are on the standby system,
query the V$ARCHIVED_LOG view on the standby database:
SQL> SELECT NAME FROM V$ARCHIVED_LOG;
NAME
--------------------------------------------------------------------------------
/arc_dest/log_1_771.arc
/arc_dest/log_1_772.arc
/arc_dest/log_1_773.arc
/arc_dest/log_1_774.arc
Table 5–2 Minimum Requirements for Data Protection Modes
Maximum Protection | Maximum Availability | Maximum Performance | |
Redo archival process | LGWR | LGWR | LGWR or ARCH |
Network transmission mode | SYNC | SYNC | SYNC or ASYNC when using LGWR process. SYNC if using ARCH process |
Disk write option | AFFIRM | AFFIRM | AFFIRM or NoAFFRIM |
Standby redo log required? | Yes | Yes | No, but it is recommended |
On a physical standby database
To determine if there is an archive gap on your physical standby database, query the V$ARCHIVE_GAP view as shown in the following example:
SQL> SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
1 7 10
The output from the previous example indicates your physical standby database is currently missing log files from sequence 7 to sequence 10 for thread 1. After you identify the gap, issue the following SQL statement on the primary database to locate the archived redo log files on your primary database (assuming the local archive destination on the primary database is LOG_ARCHIVE_DEST_1):
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND
2> SEQUENCE# BETWEEN 7 AND 10;
NAME
--------------------------------------------------------------------------------
/primary/thread1_dest/arcr_1_7.arc
/primary/thread1_dest/arcr_1_8.arc
Copy these log files to your physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE statement on your physical standby
database. For example:
SQL> ALTER DATABASE REGISTER LOGFILE
'/physical_standby1/thread1_dest/arcr_1_7.arc';
SQL> ALTER DATABASE REGISTER LOGFILE
'/physical_standby1/thread1_dest/arcr_1_8.arc';
After you register these log files on the physical standby database, you can restart Redo Apply.
Monitoring Log File Archival Information
Step 1 Determine the status of redo log files.
Enter the following query on the primary database to determine the status of all online redo log files:
SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG order by 1,2;
SQL> SELECT THREAD#, SEQUENCE#,applied, STATUS FROM V$ARCHIVED_LOG order by 1, 2;
Step 2 Determine the most recent archived redo log file.
Enter the following query on the primary database to determine recently archived thread and sequence number:
SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
Step 3 Determine the most recent archived redo log file at each destination.
Enter the following query on the primary database to determine which archived redo log file was most recently transmitted to each of the archiving destinations:
SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#
2> FROM V$ARCHIVE_DEST_STATUS
3> WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';
DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ#
------------------ ------ ---------------- -------------
/private1/prmy/lad VALID 1 947
standby1 VALID 1 947
The most recently written archived redo log file should be the same for each archive destination listed. If it is not, a status other than VALID might identify an error encountered during the archival operation to that destination.
Step 4 Find out if archived redo log files have been received.
You can issue a query at the primary database to find out if an archived redo log file was not received at a particular site. Each destination has an ID number associated with it. You can query the DEST_ID column of the V$ARCHIVE_DEST fixed view on the primary database to identify each destination’s ID number. Assume the current local destination is 1, and one of the remote standby destination IDs is 2. To identify which log files are missing at the standby destination, issue the following query:
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)
3> LOCAL WHERE
4> LOCAL.SEQUENCE# NOT IN
5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
6> THREAD# = LOCAL.THREAD#);
Monitoring the Performance of Redo Transport Services
SQL> select error from V$archive_dest;
