Thursday, July 14, 2011

Oracle 11gR2 New Features for RAC


The Basics:
  • The clusterware is now known as Grid Infrastructure.
  • CRS and ASM binaries reside under a single Oracle Home known as GRID Home.
  • GRID Home and the Oracle Home must be installed in different locations.
  • Oracle Grid Infrastructure cannot be placed under Oracle Base since permissions will be changed to root.
  • OCR and Voting File can no longer be placed on RAW or block devices. They need to be placed either in ASM or on Shared NFS Storage.
  • Voting file does not need to be backed up using dd command and is not supported anymore.The contents of Voting file are backed into the OCR.
For more details on the architecture and understanding of background processes, refer Metalink Note: 1053147.1

SCAN:
Single Client Access Name(SCAN) as the name suggests is a single name for clients to access RAC database. The obvious benefit is as nodes get added to the cluster, client TNS remains unchanged. It's like a virtual load balancer. You can find more details on Metalink Note:887522.1

Always remember the following about SCAN during your RAC installation:
  • SCAN name can be maximum up to 15 characters excluding the domain name. Keep this in mind while requesting for scan name and IPs otherwise you won't be able to proceed with your installation since SCAN is a mandatory requirement.
  • Ask your clients to use 11gR2 version of client coz with older version(11gR1/10gR2), if one of the SCAN listener goes down, the connection will fail. Only 11gR2 client will give full functionality of SCAN. You can have workarounds though by explicitly specifying all the 3 SCAN IPs in the client TNS.
  • Set REMOTE_LISTENER to SCAN. DO NOT set to TNSNAMES alias. Example: remote_listener='scan-clu:1521'. Default installation will not update remote_listener and it's imperative to set it correctly if you wish your clients to use single name in the TNS.
  • LOCAL_LISTENER should be set to node VIP as FQDN(Fully Qualified Domain Name). Default install would put the VIP but not as fully qualified. FQDN is important otherwise you may notice intermittent connection failures.
  • Best way to troubleshoot SCAN is to set ORACLE_HOME to GRID_HOME since SCAN listener runs from Grid Home and check for 'lsnrctl servics SCAN_LISTENER' to verify if all instances are registered correctly.
Posted by at No comments:
Labels:

No Same Machine Create a Duplicate Database with NOCATALOG

  This document describes setup of a duplicate database on same node
  using RMAN without a catalog. This allows creation of a duplicate database
from the backups of a target database (either backup sets or image copies).

  Duplicate creates a new database by restoring the backup. The duplicate
  database has a different DBID  so that it can also be registered with the
  recovery catalog.


SCOPE & APPLICATION
-------------------
  In this document you find:
  1) prerequisites
  2) init.ora configuration duplicate and passwordfile
  3) Setup sqlnet configuration
  4) Making a backup with RMAN
  5) duplicate the primary database
  6) tips and hints

1) Prerequisites
----------------
   - Primary database is in archive mode and must be at least mounted.
   - Rman has been setup
   - Remote login as SYSDBA has been setup
       - add to the init.ora : remote_login_passwordfile=EXCLUSIVE

2) init.ora configuration duplicate and passwordfile
----------------------------------------------------
  Copy init.ora primary to inittdup.ora and change at least
  the following parameters for tdup instance:

  - to avoid the original primary datafile being overwritten 
     db_file_name_convert=('/tar/server/sroonl/sroonl','/tar/server/sroonl/sroonl/dup')

  - to avoid the original primary logfiles being overwritten
      log_file_name_convert=('/tar/server/sroonl/sroonl','/tar/server/sroonl/sroonl/dup')
  - to avoid the original primary control files being overwritten
      control_files = /tar/server/sroonl/sroonl/dup/control01.ctl
  - to change database name
     db_name = tdup
  - to change instance name
     instance_name = tdup

  Set the environment variable ORACLE_SID to duplicate
  (and ORACLE_HOME and other environment variables remain the same)
  and create the passwordfile.

  Example for UNIX (and from dos box you can use SET):
    $ export NLS_LANG=AMERICAN_AMERICA.us7ascii
    $ export ORACLE_SID=tdup
    $ orapwd file=$ORACLE_HOME/dbs/orapwtdup password=secret entries=10

  Startup the duplicate instance in nomount:
    SQL> startup nomount pfile=<location_of_init.ora of duplicate>
 
  Example:
    SQL> startup nomount pfile=/tar/server/sroonl/sroonl/dup/inittdup.ora

3) Setup sqlnet configuration
-----------------------------
  If not done already, setup servicename for primary database in listener.ora
  and tnsnames.ora (Check Note:126074.1 how to setup a SQL*Net configuration)

  Connect to primary (assuming service is tests_nlsu22) as:
    $ sqlplus <username>/<password>@tests_nlsu22

  Test with RMAN :
    $ rman target internal/secret@tests_nlsu22 nocatalog

  should result in:
     RMAN-06005: connected to target database: TESTS (DBID=493757597)
     RMAN-06009: using target database controlfile instead of recovery catalog


4) Making a backup with RMAN
----------------------------
  Set the environment (ORACLE_SID) to primary database TESTS

  $ rman target / nocatalog

  result:
    RMAN-06005: connected to target database: TESTS (DBID=493757597)
    RMAN-06009: using target database controlfile instead of recovery catalog


  ====start example backup script ======

  RMAN> run { allocate channel C1 type disk;
        backup (database format '/tar/server/sroonl/sroonl/back/b_%d_%t_%s.bkp2');
        backup archivelog all delete input;
}
  ====end   example backup script ======


  /* after connecting to target nocatalog */

  RMAN> list backupset of database;

  ======part of output==============
  ....

    List of Datafiles Included
    File Name                                  LV Type Ckp SCN    Ckp Time
    ---- ------------------------------------- -- ---- ---------- -------------
    1    /tar/server/sroonl/sroonl/system.dbf  0  Full 249328     15-JUL-03
    2    /tar/server/sroonl/sroonl/rbs01.dbf   0  Full 249328     15-JUL-03
    3    /tar/server/sroonl/sroonl/users01.dbf 0  Full 249328     15-JUL-03
    4    /tar/server/sroonl/sroonl/tempT817_01.dbf 0  Full 249328     15-JUL-03
    5    /tar/server/sroonl/sroonl/indx01.dbf  0  Full 249328     15-JUL-03

   List of Backup Sets
   Key     Recid      Stamp      LV Set Stamp  Set Count  Completion Time
   ------- ---------- ---------- -- ---------- ---------- ----------------------
   5       5          499446145  0  499446100  9          15-JUL-03

  ..........
  ====== end part of output==============

5) Duplicate the primary database
---------------------------------
  To duplicate the primary database, set all environment variables to
  the duplicate database in this case TDUP

  Double check if you are connected to the duplicate instance with :
     $ sqlplus /nolog
     SQL> connect / as sysdba
     SQL> select * from v$instance;
            => This should report the instance name TDUP


  $ rman target internal/secret@tests_nlsu22 nocatalog
       RMAN-06005: connected to target database: TESTS (DBID=493757597)
       RMAN-06009: using target database controlfile instead of recovery catalog

    RMAN> connect auxiliary /
       RMAN-06020: connected to auxiliary database 

   Use the following RMAN commands for the duplication :
    RMAN> run {
        set until scn 249328;   # The SCN is derived from the output of
                                # step (4) : List backupset of database
        allocate channel ch1 type disk;
        allocate auxiliary channel ch2 type disk;
        duplicate target database to tdup;  
       }

  Output results :

    RMAN-03027: printing stored script: Memory Script
    {
       catalog clone datafilecopy  "/tar/server/sroonl/sroonl/dup/system.dbf";
       catalog clone datafilecopy  "/tar/server/sroonl/sroonl/dup/rbs01.dbf";
       catalog clone datafilecopy  "/tar/server/sroonl/sroonl/dup/users01.dbf";
       catalog clone datafilecopy  "/tar/server/sroonl/sroonl/dup/tempT817_01.dbf";
       catalog clone datafilecopy  "/tar/server/sroonl/sroonl/dup/indx01.dbf";
       switch clone datafile  1 to datafilecopy
              "/tar/server/sroonl/sroonl/dup/system.dbf";
       switch clone datafile  2 to datafilecopy
              "/tar/server/sroonl/sroonl/dup/rbs01.dbf";
       switch clone datafile  3 to datafilecopy
              "/tar/server/sroonl/sroonl/dup/users01.dbf";
       switch clone datafile  4 to datafilecopy
              "/tar/server/sroonl/sroonl/dup/tempT817_01.dbf";
       switch clone datafile  5 to datafilecopy
             "/tar/server/sroonl/sroonl/dup/indx01.dbf";
       Alter clone database open resetlogs;
    }
    RMAN-03021: executing script: Memory Script
    RMAN-03022: compiling command: catalog
    RMAN-03023: executing command: catalog
    RMAN-08050: cataloged datafile copy
    RMAN-08513: datafile copy filename=/tar/server/sroonl/sroonl/dup/system.dbf rec7
     .....
     .....
    RMAN-03022: compiling command: catalog
    RMAN-03023: executing command: catalog
    RMAN-08050: cataloged datafile copy
    RMAN-08513: datafile copy filename=/tar/server/sroonl/sroonl/dup/indx01.dbf rec1

    RMAN-03022: compiling command: switch
    RMAN-03023: executing command: switch
    RMAN-08015: datafile 1 switched to datafile copy
    RMAN-08507: input datafilecopy recid=1 stamp=499522637 filename=/tar/server/srof
     ....
     ....
    RMAN-03022: compiling command: switch
    RMAN-03023: executing command: switch
    RMAN-08015: datafile 5 switched to datafile copy
    RMAN-08507: input datafilecopy recid=5 stamp=499522641 filename=/tar/server/srof

    RMAN-03022: compiling command: alter db
    RMAN-06400: database opened
    RMAN-08031: released channel: ch1
    RMAN-08031: released channel: ch2 


   You now have a database tdup and tests on same node that
   are equivalent at the moment the backup at scn used was taken.


6) Tips and hints
-----------------

  The relocation of primary database files to duplicate datafiles is done
  with init.ora DB_FILE_NAME_CONVERT for all the datafiles. If you wish to
  relocate datafile(s) to another location then use the RMAN command:
       SET NEWNAME FOR DATAFILE <file#> TO <new path and name>;

  Example:
    RMAN> set newname for datafile 4 to '/tar/server/sroonl/sroonl/dup/indx01.dbf';
 
Posted by at No comments:
Labels:

Significant virtual memory paging was detected on the host OS windows

During my experimentation with ADDM with Oracle10g on Windows 2008 x64 I found frequent warning at the top of the report, such as:
Significant virtual memory paging was detected on the host operating system.

RECOMMENDATION 1: Host Configuration, 100% benefit (8567 seconds)
ACTION: Host operating system was experiencing significant paging but no
particular root cause could be detected. Investigate processes that
do not belong to this instance running on the host that are consuming
significant amount of virtual memory. Also consider adding more
physical memory to the host.
I knew that this message is likely a bogus one, considering that Windows OS handles virtual memory a bit different than other operating systems. If you launch Task Manager you can easily observe Page Faults rapidly increasing during warm up phase of the Oracle instance (for example after fresh startup):

Note:395957.1 ADDM Reports "Significant Virtual Memory Paging Was Detected On The Host Operating System"
================SOLUTION FROM ORACLE =====================

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.2
z*OBSOLETE: Microsoft Windows Server 2003
z*OBSOLETE: Microsoft Windows Vista x64 (64-bit)
Microsoft Windows (32-bit)
z*OBSOLETE: Microsoft Windows XP
Microsoft Windows Itanium (64-bit)
z*OBSOLETE: Microsoft Windows Server 2003 R2 (64-bit AMD64 and Intel EM64T)
Microsoft Windows x64 (64-bit)
z*OBSOLETE: Microsoft Windows Vista (32-bit)

Symptoms

On a Windows system (32 or 64 bit) Automatic Database Diagnostic Monitor (ADDM) reports:

=========================
FINDING 1: 100% impact (858 seconds)
Significant virtual memory paging was detected on the host operating system.

RECOMMENDATION 1: Host Configuration, 100% benefit (858 seconds)
ACTION: Host operating system was experiencing significant paging but no
particular root cause could be detected. Investigate processes that
do not belong to this instance running on the host that are consuming
significant amount of virtual memory. Also consider adding more
physical memory to the host.
=========================

Cause

The Windows operating system handles virtual memory differently from other operating systems.

NOTE: This condition is Oracle version independent as it is a function of the Windows Operating System
Solution
High virtual memory use is not a problem on Windows platforms unless you are experiencing database performance issues.
Therefore, ignore the recommendation/action by ADDM unless you are experiencing database performance issues


=======================================================================

My recommendation is that ADDM advice on Windows platform should be cross checked with OS trying to find out if real memory paging is going on. If you found that for example Oracle process Peak Memory Usage is (was) close to the amount of physical memory, it's very likely that ADDM warning is not a bogus one and that real memory paging is going on, that need to be addressed.


Posted by at No comments:
Labels:

Move Database objects from one Tablespace to other

set echo off
set heading off
set verify off
set feedback off
set echo off
spool /d01/oracle/proddb/APPS_MOVE.sql
 
select 'alter table APPS.'||table_name||' move tablespace apps_data_test;' from DBA_tableS where OWNER='APPS';

select 'alter index APPS.'||INDEX_name||' rebuild tablespace APPS_DATA_TEST_IND;' from DBA_INDEXES where OWNER='APPS';

SELECT 'alter table APPS.'||table_name||' MOVE LOB('||column_name||') store as (tablespace APPS_DATA_TEST);' from dba_lobs where owner ='APPS' ;

SELECT 'alter table APPS.'||TABLE_NAME||' move partition '|| PARTITION_NAME||' tablespace APPS_TEST_DATA;' from dba_tab_partitions where table_owner ='APPS';

SELECT 'alter index APPS.'||index_owner||'.'||index_name||' rebuild partition '||partition_NAME||' TABLESPACE Apps_data_test_ind;' from dba_ind_partitions where index_owner ='APPS';

select 'alter TABLE APPS.'||TABLE_name||' MOVE tablespace APPS_DATA_TEST;' from DBA_INDEXES where OWNER='APPS';
 
SELECT 'alter table APPS.'||tABLE_NAME||' move partition '||partition_NAME||' lob('||COLUMN_NAME||') store as (tablespace APPS_DATA_TEST);' from dba_lob_partitions where table_owner='APPS';
 
select 'ALTER  MATERIALIZED VIEW apps.'||MView_name||' MOVE TABLESPACE APPS_DATA_test_ind;' from dba_mviews where owner='APPS';

set heading on
set verify on
set feedback on
set echo on
REM UNCOMMENT TO AUTO RUN the generated commands
REM ELSE edit APPS_MOVE.sql, modify as needed and run it
REM @tmp
Posted by at No comments:
Labels:

Creating Dataguard with Rman

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@stby

RMAN> duplicate target database for standby nofilenamecheck dorecover;

We will now put our DB into managed recovery mode. This starts the redo apply service.
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;
SEQUENCE#
----------
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;
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)