Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

11g “Data Guard” configuration with DGMGRL

11g “Data Guard” configuration with DGMGRL

 

This post describes the commands used to create a Data Guard configuration using the command line dgmgrl interface. This can also be done via the Enterprise Manager Grid Control. We assume that we already have created a physical standby database and the database roles are as follows.

Primary Database: 11gpri

Standby Database: 11gsb

On both Primary as well as Standby database start the Data Guard Broker process

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

Edit the listener.ora on both nodes to add a static entry for DGMGRL

This is to prevent the ORA-12154 error which can be observed on startup of the standby database after performing a switchover.

Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = 11gpri_dgmgrl)

(ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)

(SID_NAME = 11gR2)

)

)

Create the configuration

dgmgrl

DGMGRL> connect sys/xxxx

Connected.

DGMGRL> CREATE CONFIGURATION ’11GDR’

> AS

> PRIMARY DATABASE IS ’11gpri’

> CONNECT IDENTIFIER IS ’11gpri’

> ;

Configuration “11GDR” created with primary database “11gpri”

Add the Standby database to the configuration

DGMGRL> ADD DATABASE

> ’11gsb’

> AS

> CONNECT IDENTIFIER IS ’11gsb’

> ;

Database “11gsb” added

DGMGRL> SHOW CONFIGURATION

Configuration

Name: 11GDR

Enabled: NO

Protection Mode: MaxAvailability

Databases:

11gpri – Primary database

11gsb – Physical standby database

Fast-Start Failover: DISABLED

Current status for “11GDR”:

DISABLED

Enable the configuration

DGMGRL> ENABLE CONFIGURATION

Enabled.

DGMGRL> SHOW CONFIGURATION

Configuration

Name: 11GDR

Enabled: YES

Protection Mode: MaxAvailability

Databases:

11gpri – Primary database

11gsb – Physical standby database

Fast-Start Failover: DISABLED

Current status for “11GDR”:

SUCCESS

View the Standby and Primary database properties

DGMGRL> show database 11gpri

Database

Name: 11gpri

Role: PRIMARY

Enabled: YES

Intended State: TRANSPORT-ON

Instance(s):

11gR2

Current status for “11gpri”:

SUCCESS

DGMGRL> show database 11gsb

Database

Name: 11gsb

Role: PHYSICAL STANDBY

Enabled: YES

Intended State: APPLY-ON

Instance(s):

11gR2

Current status for “11gsb”:

SUCCESS

DGMGRL> show database verbose 11gsb

Database

Name: 11gsb

Role: PHYSICAL STANDBY

Enabled: YES

Intended State: APPLY-ON

Instance(s):

11gR2

Properties:

DGConnectIdentifier = ’11gsb’

ObserverConnectIdentifier = ”

LogXptMode = ‘SYNC’

DelayMins = ‘0’

Binding = ‘OPTIONAL’

MaxFailure = ‘0’

MaxConnections = ‘1’

ReopenSecs = ‘300’

NetTimeout = ’30’

RedoCompression = ‘DISABLE’

LogShipping = ‘ON’

PreferredApplyInstance = ”

ApplyInstanceTimeout = ‘0’

ApplyParallel = ‘AUTO’

StandbyFileManagement = ‘AUTO’

ArchiveLagTarget = ‘0’

LogArchiveMaxProcesses = ‘4’

LogArchiveMinSucceedDest = ‘1’

DbFileNameConvert = ”

LogFileNameConvert = ”

FastStartFailoverTarget = ”

StatusReport = ‘(monitor)’

InconsistentProperties = ‘(monitor)’

InconsistentLogXptProps = ‘(monitor)’

SendQEntries = ‘(monitor)’

LogXptStatus = ‘(monitor)’

RecvQEntries = ‘(monitor)’

HostName = ‘raclinux1’

SidName = ’11gR2′

StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=raclinux1)

(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=11gsb_DGMGRL)(INSTANCE_NAME=11gR2)(SERVER=DEDICATED)))’

StandbyArchiveLocation = ‘USE_DB_RECOVERY_FILE_DEST’

AlternateLocation = ”

LogArchiveTrace = ‘0’

LogArchiveFormat = ‘%t_%s_%r.dbf’

LatestLog = ‘(monitor)’

TopWaitEvents = ‘(monitor)’

Current status for “11gsb”:

SUCCESS

Change the properties of a configured databaase

DGMGRL> EDIT DATABASE ’11gpri’ SET PROPERTY ‘LogXptMode’=’SYNC’;

Property “LogXptMode” updated

Performing a switchover

Note: In this case, currently the Primary Database is 11gsb and the Standby database is 11gpri.

DGMGRL> switchover to ’11gpri’

Performing switchover NOW, please wait…

New primary database “11gpri” is opening…

Operation requires shutdown of instance “11gR2” on database “11gsb”

Shutting down instance “11gR2″…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “11gR2” on database “11gsb”

Starting instance “11gR2″…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “11gpri”

DGMGRL> SHOW CONFIGURATION;

Configuration

Name: 11GDR

Enabled: YES

Protection Mode: MaxAvailability

Databases:

11gpri – Primary database

11gsb – Physical standby database

Fast-Start Failover: DISABLED

Current status for “11GDR”:

SUCCESS

Monitoring the Data Guard Broker Configuration

If we receive any error or warnings we could obtain more information running the commands as shown below. In this case there is no output seen because currently we are not experiencing any errors or warning.

DGMGRL> show database 11gpri statusreport

STATUS REPORT

INSTANCE_NAME SEVERITY ERROR_TEXT

DGMGRL> show database 11gpri logxptstatus

LOG TRANSPORT STATUS

PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS

11gR2 11gsb

DGMGRL> show database 11gpri InconsistentProperties

INCONSISTENT PROPERTIES

INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

  

SoormaCredit Gavin

March 18, 2010 Posted by | oracle | Leave a comment

Oracle Database 11g Release 2 Data Guard deployment using Oracle Grid Control 10.2.0.5

Oracle Database 11g Release 2 Data Guard deployment using Oracle Grid Control 10.2.0.5

This article describes the creation of a standby database using Oracle Database 11g Release 2 and the Oracle Grid Control on the same server, how to perform a switchover from primary to standby database and a switchover from the ex-standby to the original primary. We will look at Data Guard configuration verification using OEM Grid Control and how to reconfigure the Data Guard for example set redo transport to SYNCH and setting up an active Data Guard open for read only. The article assumes the following software components are present, installed and configured.

  • Grid Control (10.2.0.5). An example installation can be found in previous posts.
  • A primary database server, with Oracle Database 11g Release 2 software installed and an the existing database is present as a primary.
  • In this article we look at creating a standby database with data guard on the same server for testing. To deploy the concept in real life the following prerequisites needs to be met.
    • All servers need to have an Oracle EM Grid Control agent install
    • All servers need to have the binaries (software only) for Oracle 11g R2 installed.

With this setup in place, the standby database is created and managed from Oracle Grid Control as follows.

  • Standby Database Creation
  • Switchover
  • Data Guard Verification
  • Data Guard Re-configuration

Standby Database Creation

As the Oracle 11g R2 databases are configured to listen to port 1522 we need to set up the local_listener initialization parameter on the existing Oracle 11g R2 database that will be designated for a primary database..

  1. Set listener in $TNS_ADMIN/tnsnames.ora as follows.

LISTENER =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = linuxoel54.gj.com)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = db11gr2)

)

)

  1. The parameter local_listener is a static parameter. After setting the parameter please bounce the instance.

SQL>alter system set local_listener = ‘listener’ scope=spfile

SQL>shutdown immediate;

………………………………………………………………..

SQL>startup open;

SQL> show parameter local_listener

 

NAME TYPE VALUE

———————————— ———– ——————————

local_listener string listener

SQL>

LISTENER =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = linuxoel54.gj.com)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = db11gr2)

)

)

 

Navigate to the “Availability” tab of the primary database server (Targets > Databases > (Primary Database Name) > Availability). Click on the “Add Standby Database” link under the “Data Guard” section of the page.

 

 

On the resulting page, click on the “Add Standby Database” link.

 

Accept the “Create a new physical standby database” option by clicking the “Continue” button.

 

Accept the “Perform an online backup of the primary database” and “Use Recovery Manager (RMAN) to copy the files” options by clicking the “Next” button.

 

Enter the “Primary Host Credentials”, if they have not already been defaulted as a preferred credentials, then click the “Next” button.

 

Enter the name of the standby host and its host credentials, then click the “Next” button.



Since we use a single server for both primary and standby database we keep the $ORACLE_HOME and $TNS_ANMES as the same Oracle 11g R2 binaries are shared and accept the suggested Oracle Optimal Flexible Architecture directory structure, then click the “Next” button.

Enter a “Database Unique Name” and “Target Name” and select the “Use SYSDBA monitoring credentials” option, then click the “Next” button. Notice the “Use Data Guard Broker” option is checked.

If you are happy with the information on the review page, then click the “Finish” button.


The standby database creation process runs as an Enterprise Manager job. The job will be submitted after completion of several preliminary steps. Wait while the first section of processing completes.

Once the basic processing is complete, you are presented with the Data Guard “Setup and Manage” screen. Towards the bottom of the screen is a list of available standby databases, with the new standby database listed. The status of the new database is “Creation in process”. Clicking on this status link gives a breakdown of the current processing.



Expand the “Task: DBClone” node for a more detailed status of the operation.

The image below shows an example of the expanded progress.





When the standby database creation is complete, the “Setup and Manage” screen will display a status of “Normal” for the standby database.

The “Availability” tab now includes several new links in the “Data Guard” section.


Primary database:

SQL> select CONTROLFILE_TYPE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE from v$database;

 

CONTROL PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE

——- ——————– ——————– —————-

CURRENT MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY

 

SQL> select instance_name from v$instance;

 

INSTANCE_NAME

—————-

db11gr2

 

SQL>

Standby database:

SQL> select CONTROLFILE_TYPE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE from v$database;

 

CONTROL PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE

——- ——————– ——————– —————-

STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY

 

SQL> select instance_name from v$instance;

 

INSTANCE_NAME

—————-

Db11gr2s

 

SQL>

Switchover

To perform a switchover, navigate to the “Setup and Manage” screen, select the standby server you wish to become the new primary server, then click the “Switchover” button.


Accept the “Swap Monitoring Settings” and “Transfer Jobs” options by clicking the “OK” button.

Wait while the switchover processing takes place.




Once complete, the “Setup and Manage” screen shows the primary and standby roles have been reversed.

Lets perform a switchover to db11gr2 and have make db11gr2 a primary database. Select db11gr2 and press Switchover button. Select ‘Swap Monitoring Settings’ and ‘Transfer Jobs’ and press OK.





Data Guard Verification

Select ‘Verify configuration’


Wait for the OEM Grid Control to perform checks of the various databases settings.


Upon successful completion of the verification the detailed results are displayed. Press OK to continue.


We are displayed the screen below.


Data Guard re-configuration

Let’s look at the features of OEM Grid Control for reconfiguring Data Guard. Select the link under Data Guard status.


Select Redo Apply Services and ‘Apply on’ and ‘Enable Read real-time query’ and press OK.


Wait until the database property changes get applied.


Lets’ change the redo transport mode to SYNCH. Press Apply to continue.


Wait until configuration completes.



After completion we will notice that the changes have been implemented.


Conclusion

I this article we looked at how easy and cost effective is to setup a Data Guard with OEM Grid Control. We performed switchover to the standby database and from new primary database to the original database using OEM Grid Control. We also refreshed how to use the OEM Grid Control for Data Guard verification. We covered the additional reconfiguration of Data Guard by setting a SYNH redo log transport and setting an active standby database opened in read only mode while the redo keep on being applied.

March 15, 2010 Posted by | oracle | 4 Comments

Creating Oracle Data Guard in 11g R2 using Data Guard Broker with DGMGRL

Creating Oracle Data Guard in 11g R2 using Data Guard Broker with DGMGRL

In this post we will look at the creation of a physical standby database and configuring the physical standby database for management and administration with Data guard broker’s utility DGMGRL in oracle 11g R2.

  1. Setup on the primary database to support the role of a primary database. Follow the steps below.

  1. Creating the physical standby database
  2. Create an Oracle Net service name for the physical standby database in tnsnames.ora

  1. Configure an entry for the standby database in listener.ora

  1. Reload the listener on the DR server and prepare the DR site with the oracle password file, new init.ora file and directories for the standby database.

Prepare for the standby creation from the primary database. In order to duplicate the primary database to the standby location we will start the standby database in nomount state and will use RMAN. We invoke the RMAN from the primary site and connect to the primary database as a target and connect to the standby instance as an auxiliary database.

In oracle 11g R2 we will run the script below for the creation of the standby database. Note that from Oracle 11gR2 we can create a standby database either

  • With connection to the primary as a target and to the standby database as an auxiliary without an existing backup of the primary database.
  • Without connection to the primary database with existing backup of the primary database and with connection to the standby database.

In the RMAN script we also will specify some of the standby database init parameters.

Once the standby database is created verify that the redo transport is operational and redo from the primary is received on the standby database and it is archived. The sequence# from the standby database must match the sequence# from the standby database, i.e. there should not be gaps in the sequence#.

To setup and configure Data Guard Broker we need to start the Data Guard Broker processes. In order to start Data Guard Broker processes set the DG_BROKER_START init parameter to TRUE.

  • Data Guard Monitor (DMON)
  • Broker Resource Manager (RSM)
  • Data Guard Net Server (NVSn)
  • DRCn

Edit the listener.ora on both nodes to add a static entry for DGMGRL. This is to prevent the ORA-12154 error which can be observed on startup of the standby database after performing a switchover. Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain

After creating the physical standby database create the configuration. After that, add the standby database to the already created broker configuration. Note the difference from 10g where the syntax is add database órclstby’ as connect identifier is orclstby maintained as physical’. In Oracle 11g R2 ‘maintained as physical ‘is not required to be specified. As the created configuration is not enabled by default when created it is required to be enabled explicitly. Enabling the configuration let the Broker start everything up. At the end we will verify the configuration.

Let’s see how to read data from the physical standby database using Active Data Guard option. We will setup an Active Data Guard Mode where the physical standby database is open in read-only mode while the redo still keep on being applied while the physical standby database is opened in read-only mode. We will show how in case of an Active Data Guard while the standby is open in read-only mode the transactions from the primary database get applied on the standby database. All we need to do is to stop the application of redo by disabling the managed recovery process. Then we will open the database in read-only mode. At the end we will resume the application of the redo by enabling and resuming the managed recovery process.

Let’s enable the flashback on the primary and the standby databases as this is a prerequisite for setting fast start failover feature (FSFO). After that we will enable the fast start failover feature.

At the end we will verify the configuration with the commands show below.

Last but not least we will look at how to implement a switchover to the standby database. After the completion of the switchover we will check and verify the changes in the configuration. At the end we will perform a switchover to the original configuration.

Summary

We created a physical standby database using RMAN new feature available in 11g R2. We configure Data Guard Broker to manage and administer the Data Guard configuration.

March 14, 2010 Posted by | oracle | 6 Comments

Creating Oracle 11g active standby database from physical standby database

Creating Oracle 11g active standby database from physical standby database

Here in this article we will look at implementing an active standby database that allows as to open the standby database in read only mode and the redo keeps on applying. We assume that we already have a physical standby configuration already in place as shown below.

Primary Database- DB11G

Standby Database- DB11GDG

Now you can bring up the standby database in read-only mode and continue to have the standby database updated from the primary. Here are the steps to achieve that:

1. Stop the managed recovery process on standby database:
In DB11GDG ( Standby database )

SQL > alter database recover managed standby database cancel;
Database altered.

2. Open the DB11GDG – standby database as read-only:
SQL > alter database open read only;
Database altered.

3. Restart the managed recovery process on the standby database:

SQL > alter database recover managed standby database using current logfile disconnect;

Now the standby database is being updated, but it is simultaneously open for read-only access—this is the essence of Oracle Active Data Guard.

4. To test the “active” part of Oracle Active Data Guard, create a table in the primary database:

In Primary database issue the following query
SQL > select table_name from dba_tables where table_name = ‘GJ’;
no rows selected

On Standby databaseissue the following query
SQL > select table_name from dba_tables where table_name = ‘GJ’;
no rows selected

In DB11G database – Primary database
SQL > create table GJ (col_id number(10));

5. After a few seconds, check the existence of the table in the standby database:
SQL > select table_name from dba_tables where table_name = ‘GJ’;

TABLE_NAME
——————————
GJ

The table will be propagated.

The standby database is open in read-only mode and it is applying the logs from the primary database. Voila… This feature enables you to run reports against it without sacrificing the ability to put the standby database into the primary role quickly.

6. To confirm the application of redo logs on the primary database, first switch the log file:
alter system switch logfile;

7. Now observe the alert log of the standby database. Use the automatic diagnostic repository command interpreter (ADRCI) tool, new in Oracle Database 11g:

$ adrci
show alert -tail –f

kcrrvslf: active RFS archival for log 6 thread 1 sequence 15684
RFS[7]: Successfully opened standby log 5: ‘/u01/oradata/DB11GDG/standby02.log’
Media Recovery Log /u01/oradata/DB11GDG/arch/DB11GDG/archivelog/2010_03_10/o1_mf_1_15694_4mk48s9y_.arc
Media Recovery Waiting for thread 1 sequence 15685 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 15695 Reading mem 0
Mem# 0: /u01/oradata/DB11GDG/standby02.log

Conclusion

The physical standby database is a physical replica of the primary database, kept in sync by the application of redo log entries from the primary database. With Oracle Active Data Guard and real time apply, the log entries are applied in real time but the primary database does not wait for the redo application to complete, thereby protecting itself from failures or slowdowns in the shipment of log information from the primary to the standby database. Using the Oracle Active Data Guard option, you can open the physical standby database for read-only operations while the managed recovery process is going on. You can offload all reporting applications and the Oracle RMAN backup to the standby database, easing the load on the primary database considerably. Because the standby database is being recovered continuously with real time apply, the standby database can be activated and used immediately in case the primary database fails. This combination of features makes the investment in Oracle Active Data Guard very worthwhile. Credit Arjun, Arup Nanda

March 14, 2010 Posted by | oracle | 8 Comments

11g New Features – Read only Tables

 

11g New Features – Read only Tables

Did you read that right? It’s read only TABLES…11g has introduced read only tables. It’s now possible to make a table read only to it’s owner also. Earlier we used to grant “SELECT” privilege on a table to other users or create a view to make it read only for others BUT that was only for other users and not the owner. See the example below to switch the table from read-write to read-only and vice-versa…

SQL> create table department as select * from dept;

Table created.

SQL> select * from department;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into department values (50,’New Dept’,’New Location’);

1 row created.

SQL> commit;

Commit complete.

Make it READ ONLY

SQL> alter table department read only;

Table altered.

SQL> insert into department values (60,’New Dep1′,’New Location1′);
insert into department values (60,’New Dep1′,’New Location1′)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SCOTT”.”DEPARTMENT”

How do I find out if a table is read-only or read-write. Check the new column in “USER_TABLES”

SQL> select table_name,read_only from user_tables where table_name=’DEPARTMENT’;

TABLE_NAME REA
—————————— —
DEPARTMENT YES

Make it READ WRITE again to insert data…

SQL> alter table department read write;

Table altered.

SQL> insert into department values (60,’New Dep1′,’New Location1′);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from department;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 New Dept New Location
60 New Dep1 New Location1

6 rows selected.

Now we looked at making tables read-only.

Credit Prasad

March 8, 2010 Posted by | oracle | 2 Comments

Configuring 10g Data Guard Broker and Observer for failover and switchover

Configuring 10g Data Guard Broker and Observer for failover and switchover

  

 

 

 On a previous blog post, creating a physical standby database in Oracle 10g we looked at how to implement a physical standby database configuration. In this blog post we will look at how to configure the broker and observer, setup the database to Maximum Availability and managing switchover from Data Guard Manager, DGMGRL. 

The Enviroment  

  • 2 Linux servers.
  • Oracle Database 10gR2 Enterprise Edition.
  • ssh is configured for user oracle on both nodes.
  • Oracle Home is on identical path on both nodes.
  • Primary database 10GPRI  
  • Standby database 10GSB

 Pre requisites are Oracle Enterprise Edition 10g on the primary and the standby  server and a third server for the Observer.  On both primary and standby servers databases must use spfile instead of pfile. The listeners on both sides also should be configured with a static service for the Data Guard Broker. 

Data Guard Broker permits to manage a Data Guard Configuration, from both the Enterprise Manager Grid Control console, or from a command line interface. In this post we will explore command line interface for setting a physical standby database Data Guard. 

 Step by Step Implementation of Data Guard Broker 

Enable Data Guard Broker Start on the Primary and Standby databases
    SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
    System altered.
Setup the Local_Listener parameter on both the Primary and Standby databases
    SQL> ALTER SYSTEM SET LOCAL_LISTENER=’LISTENER_VMRACLINUXTEST’ SCOPE=BOTH;
    System altered.
Setup the tnsnames to enable communication with both the Primary and Standby databases. The listener.ora should include a service named global_db_name_DGMGRL to enable the broker to start the databases on the event of switchover. This configuration needs to be included on both servers. 

Listener.ora on Node 1 

    LISTENER_VMRACLINUXTEST =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1.gj.com)(PORT = 1521)(IP = FIRST))
       )
     )
    SID_LIST_LISTENER_VMRACLINUXTEST =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = 10gpri)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1  )
         (SID_NAME = 10gpri)
       )
       (SID_DESC =
         (SID_NAME= 10gpri)
         (GLOBAL_DBNAME = 10gpri_DGMGRL)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 )
       )
     ) 

Listener.ora on Node 2 

   LISTENER_VMRACLINUXTEST =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2.gj.com)(PORT = 1521)(IP = FIRST))
       )
     )
    SID_LIST_LISTENER_VMRACLINUXTEST =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = 10gsb)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1  )
         (SID_NAME = 10gsb)
       )
       (SID_DESC =
         (SID_NAME= 10gsb)
         (GLOBAL_DBNAME = 10gsb_DGMGRL)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 )
       )
     ) 

Tnsnames.ora on Node 1, 2 and the observer node 

   10GPRI =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1.gj.com)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = 10gpri_DGMGRL)
      )
    )
   10GSB =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =  raclinux2.gj.com)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = 10gsb_DGMGRL)
      )
 ) 

Setup the Broker configuration files 

The broker configuration files are automatically created when the broker is started using 

ALTER SYSTEM SET DG_BROKER_START=TRUE. 

The default destination can be modified using the parameters DG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2 

On Primary:
SQL>SHOW PARAMETERS DG_BROKER_CONFIG 

NAME                                 TYPE        VALUE
———————————— ———– ——————————
dg_broker_config_file1               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr110gpri.dat
dg_broker_config_file2               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr210gpri.dat 

On standby:
SQL> SHOW PARAMETERS DG_BROKER_CONFIG 

NAME                                 TYPE        VALUE
———————————— ———– ——————————
dg_broker_config_file1               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr110gsb.dat
dg_broker_config_file2               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr210gsb.dat 

Next create from within the DGMGRL the configuration
[oracle@raclinux1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 – Production Copyright (c) 2000, 2005, Oracle. All rights reserved. 

Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/oracle@10gpri
Connected.
DGMGRL> create configuration 10GPRI AS
> PRIMARY DATABASE IS 10gpri
> CONNECT IDENTIFIER IS 10gpri; 

Configuration “10gpri” created with primary database “10gpri” 

Add the standby to the configuration and check it.   

DGMGRL> ADD DATABASE 10gsb AS
> CONNECT IDENTIFIER IS 10gsb
> MAINTAINED AS PHYSICAL;
Database “10gsb” added 

DGMGRL> SHOW CONFIGURATION; 

Configuration
  Name:                10gpri
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database 

Current status for “10gpri”:
DISABLED 

DGMGRL> SHOW DATABASE VERBOSE 10gsb; 

Database
  Name:            10gsb
  Role:            PHYSICAL STANDBY
  Enabled:         NO
  Intended State:  OFFLINE
  Instance(s):
    10gsb 

  Properties:
    InitialConnectIdentifier        = ’10gsb’
    LogXptMode                      = ‘ARCH’
    Dependency                      = ”
    DelayMins                       = ‘0’
    Binding                         = ‘OPTIONAL’
    MaxFailure                      = ‘0’
    MaxConnections                  = ‘1’
    ReopenSecs                      = ‘300’
    NetTimeout                      = ‘180’
    LogShipping                     = ‘ON’
    PreferredApplyInstance          = ”
    ApplyInstanceTimeout            = ‘0’
    ApplyParallel                   = ‘AUTO’
    StandbyFileManagement           = ‘auto’
    ArchiveLagTarget                = ‘0’
    LogArchiveMaxProcesses          = ’30’
    LogArchiveMinSucceedDest        = ‘1’
    DbFileNameConvert               = ‘/u01/app/oracle/oradata/10gpri/, /u01/app /oracle/oradata/10gsb/’
    LogFileNameConvert              = ‘/u01/app/oracle/oradata/10gpri/, /u01/app /oracle/oradata/10gsb/’
    FastStartFailoverTarget         = ”
    StatusReport                    = ‘(monitor)’
    InconsistentProperties          = ‘(monitor)’
    InconsistentLogXptProps         = ‘(monitor)’
    SendQEntries                    = ‘(monitor)’
    LogXptStatus                    = ‘(monitor)’
    RecvQEntries                    = ‘(monitor)’
    HostName                        = ‘raclinux2′
    SidName                         = ’10gsb’
    LocalListenerAddress            = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux2.gj.com)(PORT=1521))’
    StandbyArchiveLocation          = ‘/u01/app/oracle/oradata/10gsb/arch/’
    AlternateLocation               = ”
    LogArchiveTraclinuxe                 = ‘0’
    LogArchiveFormat                = ‘%t_%s_%r.arc’
    LatestLog                       = ‘(monitor)’
    TopWaitEvents                   = ‘(monitor)’ 

Current status for “10gsb”:
DISABLED 

DGMGRL> show database verbose 10gpri; 

Database
  Name:            10gpri
  Role:            PRIMARY
  Enabled:         NO
  Intended State:  OFFLINE
  Instance(s):
    10gpri 

  Properties:
    InitialConnectIdentifier        = ’10gpri’
    LogXptMode                      = ‘ASYNC’
    Dependency                      = ”
    DelayMins                       = ‘0’
    Binding                         = ‘OPTIONAL’
    MaxFailure                      = ‘0’
    MaxConnections                  = ‘1’
    ReopenSecs                      = ‘300’
    NetTimeout                      = ‘180’
    LogShipping                     = ‘ON’
    PreferredApplyInstance          = ”
    ApplyInstanceTimeout            = ‘0’
    ApplyParallel                   = ‘AUTO’
    StandbyFileManagement           = ‘auto’
    ArchiveLagTarget                = ‘0’
    LogArchiveMaxProcesses          = ’30’
    LogArchiveMinSucceedDest        = ‘1’
    DbFileNameConvert               = ‘/u01/app/oracle/oradata/10gsb/, /u01/app/o raclinuxle/oradata/10gpri/’
    LogFileNameConvert              = ‘/u01/app/oracle/oradata/10gsb/, /u01/app/o raclinuxle/oradata/10gpri/’
    FastStartFailoverTarget         = ”
    StatusReport                    = ‘(monitor)’
    InconsistentProperties          = ‘(monitor)’
    InconsistentLogXptProps         = ‘(monitor)’
    SendQEntries                    = ‘(monitor)’
    LogXptStatus                    = ‘(monitor)’
    RecvQEntries                    = ‘(monitor)’
    HostName                        = ‘raclinux1′
    SidName                         = ’10gpri’
    LocalListenerAddress            = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux1.gj.com)(PORT=1521))’
    StandbyArchiveLocation          = ‘/u01/app/oracle/oradata/10gpri/arch/’
    AlternateLocation               = ”
    LogArchiveTraclinuxe                 = ‘0’
    LogArchiveFormat                = ‘%t_%s_%r.arc’
    LatestLog                       = ‘(monitor)’
    TopWaitEvents                   = ‘(monitor)’ 

Current status for “10gpri”:
DISABLED 

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration; 

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database 

Current status for “10gpri”:
SUCCESS 

DGMGRL> enable database 10gsb;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE 10gsb; 

Database
  Name:            10gsb
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    10gsb 

  Properties:
    InitialConnectIdentifier        = ’10gsb’
    LogXptMode                      = ‘ARCH’
    Dependency                      = ”
    DelayMins                       = ‘0’
    Binding                         = ‘OPTIONAL’
    MaxFailure                      = ‘0’
    MaxConnections                  = ‘1’
    ReopenSecs                      = ‘300’
    NetTimeout                      = ‘180’
    LogShipping                     = ‘ON’
    PreferredApplyInstance          = ”
    ApplyInstanceTimeout            = ‘0’
    ApplyParallel                   = ‘AUTO’
    StandbyFileManagement           = ‘auto’
    ArchiveLagTarget                = ‘0’
    LogArchiveMaxProcesses          = ’30’
    LogArchiveMinSucceedDest        = ‘1’
    DbFileNameConvert               = ‘/u01/app/oracle/oradata/10gpri/, /u01/app/oracle/oradata/10gsb/’
    LogFileNameConvert              = ‘/u01/app/oracle/oradata/10gpri/, /u01/app/oracle/oradata/10gsb/’
    FastStartFailoverTarget         = ”
    StatusReport                    = ‘(monitor)’
    InconsistentProperties          = ‘(monitor)’
    InconsistentLogXptProps         = ‘(monitor)’
    SendQEntries                    = ‘(monitor)’
    LogXptStatus                    = ‘(monitor)’
    RecvQEntries                    = ‘(monitor)’
    HostName                        = ‘raclinux2′
    SidName                         = ’10gsb’
    LocalListenerAddress            = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux2.gj.com)(PORT=1521))’
    StandbyArchiveLocation          = ‘/u01/app/oracle/oradata/10gsb/arch/’
    AlternateLocation               = ”
    LogArchiveTraclinuxe                 = ‘0’
    LogArchiveFormat                = ‘%t_%s_%r.arc’
    LatestLog                       = ‘(monitor)’
    TopWaitEvents                   = ‘(monitor)’ 

Current status for “10gsb”:
SUCCESS 

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database 

Current status for “10gpri”:
SUCCESS 

Enabling the configuration and databases 

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration; 

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database 

Current status for “10gpri”:
SUCCESS 

DGMGRL> enable database 10gsb;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE 10gsb; 

Database
  Name:            10gsb
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    10gsb 

  Properties:
    InitialConnectIdentifier        = ’10gsb’
    LogXptMode                      = ‘ARCH’
    Dependency                      = ”
    DelayMins                       = ‘0’
    Binding                         = ‘OPTIONAL’
    MaxFailure                      = ‘0’
    MaxConnections                  = ‘1’
    ReopenSecs                      = ‘300’
    NetTimeout                      = ‘180’
    LogShipping                     = ‘ON’
    PreferredApplyInstance          = ”
    ApplyInstanceTimeout            = ‘0’
    ApplyParallel                   = ‘AUTO’
    StandbyFileManagement           = ‘auto’
    ArchiveLagTarget                = ‘0’
    LogArchiveMaxProcesses          = ’30’
    LogArchiveMinSucceedDest        = ‘1’
    DbFileNameConvert               = ‘/u01/app/oracle/oradata/10gpri/, /u01/app/oracle/oradata/10gsb/’
    LogFileNameConvert              = ‘/u01/app/oracle/oradata/10gpri/, /u01/app/oracle/oradata/10gsb/’
    FastStartFailoverTarget         = ”
    StatusReport                    = ‘(monitor)’
    InconsistentProperties          = ‘(monitor)’
    InconsistentLogXptProps         = ‘(monitor)’
    SendQEntries                    = ‘(monitor)’
    LogXptStatus                    = ‘(monitor)’
    RecvQEntries                    = ‘(monitor)’
    HostName                        = ‘raclinux2′
    SidName                         = ’10gsb’
    LocalListenerAddress            = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux2.gj.com)(PORT=1521))’
    StandbyArchiveLocation          = ‘/u01/app/oracle/oradata/10gsb/arch/’
    AlternateLocation               = ”
    LogArchiveTraclinuxe                 = ‘0’
    LogArchiveFormat                = ‘%t_%s_%r.arc’
    LatestLog                       = ‘(monitor)’
    TopWaitEvents                   = ‘(monitor)’ 

Current status for “10gsb”:
SUCCESS 

Enabling Fast Start Failover and the Observer 

These are the steps required to enable and check Fast Start Failover and the Observer: 

  

1. Ensure standby redologs are configured on all databases. 

On the primary site issue the following SQL statements: 

SQL> SELECT TYPE,MEMBER FROM V$LOGFILE; 

TYPE    MEMBER
——- ————————————————–
ONLINE  /u01/app/oracle/oradata/10gsb/redo03.log
ONLINE  /u01/app/oracle/oradata/10gsb/redo02.log
ONLINE  /u01/app/oracle/oradata/10gsb/redo01.log
STANDBY /u01/app/oracle/oradata/10gb/redoby04.log
STANDBY /u01/app/oracle/oradata/10gsb/redoby05.log
STANDBY /u01/app/oracle/oradata/10gsb/redoby06.log 

On the standby site issue the following SQ L statements: 

SQL> SELECT TYPE,MEMBER FROM V$LOGFILE; 

TYPE    MEMBER
——- ————————————————–
ONLINE  /u01/app/oracle/oradata/10gpri/redo03.log
ONLINE  /u01/app/oracle/oradata/10gpri/redo02.log
ONLINE  /u01/app/oracle/oradata/10gpri/redo01.log
STANDBY /u01/app/oracle/oradata/10gpri/redoby04.log
STANDBY /u01/app/oracle/oradata/10gpri/redoby05.log
STANDBY /u01/app/oracle/oradata/10gpri/redoby06.log 

2. Ensure the LogXptMode Property is set to SYNC.
Note: These commands will succeed only if database is configured with standby redo logs.
DGMGRL> EDIT DATABASE 10gpri SET PROPERTY ‘LogXptMode’=’SYNC’;
Property “LogXptMode” updated
DGMGRL> EDIT DATABASE 10gsb SET PROPERTY ‘LogXptMode’=’SYNC’;
Property “LogXptMode” updated 

3.Specify the FastStartFailoverTarget property 

DGMGRL> EDIT DATABASE 10gpri SET PROPERTY FastStartFailoverTarget=’10gsb’;
Property “faststartfailovertarget” updated
DGMGRL> EDIT DATABASE 10gsb SET PROPERTY FastStartFailoverTarget=’10gpri’;
Property “faststartfailovertarget” updated 

4.Upgrade the protection mode to MAXAVAILABILITY, if necessary. 

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Operation requires shutdown of instance “10gpri” on database “10gpri”
Shutting down instance “10gpri”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “10gpri” on database “10gpri”
Starting instance “10gpri”…
ORACLE instance started.
Database mounted. 

note: if ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed.
You are no longer connected to ORACLE
Please connect again. 

you must start instance (primary database) manually
SQL> conn / as sysdba
SQL> startup mount; 

5. Enable Flashback Database on the Primary and Standby Databases. 

 On Both databases
 To enter the standby into Flashback mode you must shutdown the both databases, then while the primary is down execute the
 following commands on the standby:
   SQL> ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
   System altered.
   SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=’AUTO’ SCOPE=SPFILE;
   System altered.
   SQL> startup mount; 

SQL> ALTER DATABASE FLASHBACK ON; 

Enable fast start failover 

[oracle@raclinux1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 – Production 

Copyright (c) 2000, 2005, Oracle. All rights reserved. 

Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/oracle@10gpri;
Connected.
DGMGRL> show configuration verbose; 

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database 

Current status for “10gpri”:
SUCCESS 

DGMGRL> show database 10gsb; 

Database
  Name:            10gsb
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    10gsb 

Current status for “10gsb”:
SUCCESS 

DGMGRL> ENABLE FAST_START FAILOVER;
Enabled. 

start the observer 

Start the observer from a third server on background. You may use a script like this: 

—————- script start on next line ——————–
#!/bin/ksh
# startobserver
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export BASE_PATH=/u01/app/oracle/oracle/scripts/general:/opt/CTEact/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/bin:/etc:/usr/local/maint/oracle:/usr/ccs/bin:/usr/openwin/bin:/usr/dt/bin:/usr/local/bin:.
export PATH=$ORACLE_HOME/bin:$BASE_PATH
dgmgrl << eof
connect sys/oracle@10gpri
START OBSERVER;
eof
—————- script end on previous line ——————–
[oracle@raclinux3 ~]$ nohup ./startobserver
nohup: appending output to `nohup.out’
[1] 27392
Verify the fast-start failover configuration.
[oracle@raclinux3 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 – Production 

Copyright (c) 2000, 2005, Oracle. All rights reserved. 

Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/oracle@10gpri
Connected.
DGMGRL> show configuration verbose 

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: ENABLED
  Databases:
    10gpri – Primary database
    10gsb   – Physical standby database
           – Fast-Start Failover target 

Fast-Start Failover
  Threshold: 30 seconds
  Observer:  raclinux1 

Current status for “10gpri”:
SUCCESS 

Check that primary and standby are healthy
This check must return ‘SUCCESS’ as the status for both databases, otherwise it means there is a configuration
problem.
DGMGRL> show database 10gpri 

Database
  Name:            10gpri
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    10gpri 

Current status for “10gpri”:
SUCCESS 

DGMGRL> show database 10gsb 

Database
  Name:            10gsb
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    10gsb 

Current status for “10gsb”:
SUCCESS 

EXECUTE THE SWITCHOVER:  

  

DGMGRL> SWITCHOVER TO 10gsb;
Performing switchover NOW, please wait…
Operation requires shutdown of instance “10gpri” on database “10gpri”
Shutting down instance “10gpri”…
ORA-01109: database not open 

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “10gsb” on database “10gsb”
Shutting down instance “10gsb”…
ORA-01109: database not open 

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “10gpri” on database “10gpri”
Starting instance “10gpri”…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “10gsb” on database “10gsb”
Starting instance “10gsb”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “10gsb”
DGMGRL> 

DGMGRL> show configuration verbose 

Configuration
  Name:                10gpri
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: ENABLED
  Databases:
    10gpri – Physical standby database
           – Fast-Start Failover target
    10gsb   – Primary database 

Fast-Start Failover
  Threshold: 30 seconds
  Observer:  raclinux1 

Current status for “10gpri”:
SUCCESS 

DGMGRL> show database 10gsb 

Database
  Name:            10gsb
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    10gsb 

Current status for “10gsb”:
SUCCESS 

DGMGRL> show database 10gpri 

Database
  Name:            10gpri
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    10gpri 

Current status for “10gpri”:
SUCCESS

March 6, 2010 Posted by | oracle | 1 Comment

Creating A DataGuard Broker Configuration Using DGMGRL in Oracle 10g

Creating A DataGuard Broker Configuration Using DGMGRL in Oracle 10g
  
Intro
Though Grid Control offers a graphical interface for much more than just configuring a high availability database, sometimes its Installation maybe considered a huge overhead. Or you have other reasons to favor the command line. So this documentation will help you to create a DataGuard Configuration including a primary and a standby database.
Now we also need to connect to the primary database: CONNECT sys/syspassword. Though DGMGRL will not throw any error when using CONNECT /, this would probably cause some ORA-01031 later since it cannot handle the OS-authentication correctly. So better explicitly specify an account with SYSDBA privileges here. Forget about the AS SYSDBA suffix: This will simply throw an error. Specifying an account with SYSDBA privileges will make DGMGRL connect AS SYSDBA automatically.
 
Preconditions
In order to successfully create the configuration, both your primary and standby database must be up and running using SPFILE. If you did not yet create the SPFILE, you must do so first:

CREATE SPFILE FROM PFILE;

SHUTDOWN IMMEDIATE STARTUP [MOUNT]

As indicated, you have to restart the database after creating the SPFILE, since the database can only use it if it is started with it. The keyword “MOUNT” is put in square brackets, since it applies only to the standby database.

 
Preparations
To make sure we have a clean start, we have to remove all “evidence” left over from possible failed attempts. This is done as follows:

  1. Make sure the listener is configured with a special entry for DGMGRL (see below)
  2. CleanUp archive log destination on the STANDBY database:
    ALTER SYSTEM SET log_archive_dest_1=”;
  3. Stop DG broker process (DMON) on both, primary and standby database:
    ALTER SYSTEM SET dg_broker_start=FALSE SCOPE=spfile SID=’*’;
  4. (Re)Move any existing DG broker configuration files from the following locations: $ORACLE_BASE/admin/<db_unique_name> or $ORACLE_HOME/dbs, namely dr1<db_unique_name>.dat and dr2<db_unique_name>.dat
  5. Start the DG broker process (DMON) again on both, the primary and standby databases:
    ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=spfile SID=’*’;
    Make sure it is running – check output of
    SHOW PARAMETER DG
 
Creating the DataGuard configuration
Now it’s time to startup the DataGuard manager CLI interface. On the primary database machine (make sure the $ORACLE_SID is pointing to your primary instance), simply issue the command dgmgrl. This will bring you to the CLI interface command prompt, indicated by DGMGRL> replacing the OS prompt.
 
Create Configuration
To create an initial configuration, we use the CREATE CONFIGURATION command. This will look like following:

CREATE CONFIGURATION ‘sample’ AS

PRIMARY DATABASE IS ‘primary_db’ CONNECT IDENTIFIER IS

primary_db.world;

As this is missing in most documentations, I want to explicitly state what the parameters must be:

  • CREATE CONFIGURATION ‘sample’: ‘sample’ can be anything you want – it is simply the name of the configuration you may want to remember. So you can chose anything suitable here.
  • PRIMARY DATABASE IS ‘primary_db’: ‘primary_db’ must match your primary databases db_unique_name. You can issue the command SHOW PARAMETER db_unique_name in SQL*Plus to verify.
  • CONNECT IDENTIFIER IS primary_db.world: This specifies how the database is resolved, and should reflect the database alias as defined in your tnsnames.ora (or whatever name service you are using)
Once created, you can display the details issuing the SHOW CONFIGURATION command. You may wonder about additional settings being displayed – well, since we did not define more specials, we implicitely adviced the broker to use its defaults for these. You may note that the current status will display “DISABLED”: That’s okay, we are not yet done 🙂
 
Adding the standby database
When creating the configuration, we only specified the primary database – so now it’s time to add our standby database(s). The command to use for this step is ADD DATABASE, and to explain it, we assume the following example:

ADD DATABASE ‘standby_db’ AS CONNECT

IDENTIFIER IS standby_db.world MAINTAINED AS

PHYSICAL;

Not that much to explain here, if you’ve been reading the details on the CREATE CONFIGURATION command above: standby_db again has to reflect the db_unique_name, this time of your standby database. The CONNECT IDENTIFIER again is how the database is resolved. Last but not least, MAINTAINED AS PHYSICAL is quite self-explaining: We just added a physical standby database.
 
Verifying the configuration
Now you may want to check what you configured. As already mentioned before, you may issue the command SHOW CONFIGURATION – this time there will be some more details, since one more database has been added. Similarly, you can display details on the database(s) configured issuing the SHOW DATABASE [VERBOSE] <db_unique_name>; command. You may already have guessed it: the “VERBOSE” keyword will cause a much more verbose output 😉
 
Enabling the configuration
Even after adding our standby database(s), a SHOW CONFIGURATION always told us it is not yet enabled. That’s still right – we have to do so explicitly. And now is the time for it, so we issue a ENABLE CONFIGURATION at the DGMGRL> prompt. If we configured everything correctly, this step will take some time. After the prompt returned, you may issue the SHOW CONFIGURATION command again. Don’t worry if you now get some “…in progress” warnings – in fact, this is a good sign: If it’s in progress, the configuration must be available.
But after a couple of minutes, SHOW CONFIGURATION should end with the word “SUCCESS” – then you are done, congratulations!
 
Troubleshooting
When it comes to errors, the returned errors maybe not that helpful. Sometimes all they say is simply “well, there was some error – go, check and fix that”. To give you an example: Warning: ORA-16607: one or more databases have failed Now go and check!
 
ORA-16607 on SHOW CONFIGURATION
Problem: After creating your configuration and adding the standby database, you issued a SHOW CONFIGURATION as suggested. Instead of the expected SUCCESS, the report ends up with

Warning: ORA-16607: one or more databases have failed

. Checking with oerr ora 16607 was not very helpful (see above), and you neither can find anything in your alert.log nor any trace files.

Cause: Probably at least one of your databases is not using an SPFILE.
Solution: Check whether your databases have an SPFILE associated. It is usually located in $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora. If it does not exist, create it: Login to your database as SYSDBA, and issue the command CREATE SPFILE FROM PFILE;. Even if it exists, to make the database using it you need to restart the instance – it must be used already at startup.
 
ORA-16608 at SWITCHOVER
Problem: You issued a SWITCHOVER TO <standby_db> at the DGMGRL prompt. But instead of initiating the switchover, DGMGRL throws the error

Error: ORA-16608: one or more databases have warnings

Cause: This is similar to the error above – just a different error since you issued a different command.
Solution: See above.
 
ORA-01031 during switchover
Problem: You started a switchover, which is performed successfully by DGMGRL. However, DGMGRL does not startup the databases again but issues an ORA-01031: insufficient privileges.
Cause: You lack some privileges (obviously). Probably you wonder why, since you may have connected with the command CONNECT /. NOTE: DGMGRL does not support OS authentication!
Solution: You need to explicitly connect as a user with the SYSDBA privileges, e.g. CONNECT sys/password.
 
ORA-16675 during SWITCHOVER
Problem: You issued a SWITCHOVER TO <standby> at the DGMGRL prompt. But instead of the switchover to be performed, you get an

Error: ORA-16775: target standby database in broker operation has potential data loss

Cause: Recovery has not caught up (yet).
Solution: Make sure your standby database is mounted and recovery is running. If not, issue a STARTUP MOUNT to mount the standby database, and start managed recovery with ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;. Wait a few minutes, and try again.
 
ORA-12514 during SWITCHOVER
Problem: You perform a switchover using DGMGRL. Though the switchover itself completes successful, databases don’t get started up again. Instead, DGMGRL output holds an

ORA-12514: TNS:listener does not currently know of service requested in connect

Cause: This is a configuration issue: DGMGRL needs a special entry in the listener.ora.
Solution: Details on this can be found in the Metalink note 308943.1. Make sure both, your primary and standby database have an explicit entry in the listener.ora like this:

SID_LIST_LISTENER = (

SID_LIST = (

SID_DESC = (

GLOBAL_DBNAME = <db_unique_name>_DGMGRL.<db_domain> ) ( SERVICE_NAME

= <db_unique_name>.<db_domain> ) ( SID_NAME = <ORACLE_SID> ) ( ORACLE_HOME =

<ORACLE_HOME> ) ) )

Take special care, that:

  • <db_unique_name> reflects the databases unique name (SHOW PARAMETER db_unique_name)
  • <db_domain> reflects the databases domain (SHOW PARAMETER db_domain)
  • and reflect the corresponding settings of the database
Having adjusted your listener.ora files, restart the listener (lsnrctl stop && lsnrctl start). Give your databases a few minutes to register with the listener again, and then – just to be on the safe side – let the listener tell you its configuration: lsnrctl status && lsnrctl services. Check the output if everything matches.
 
Sources
Next to my own experiences, this article is based on:

  • two MetaLink service requests (private)
  • MetaLink document 260112.1 (“10g DGMGRL CLI Configuration”)
  • MetaLink document 308943.1 (“Automatic Restart of Databases during Switchover fail with ORA-12514 in DGMGRL”)

March 6, 2010 Posted by | oracle | 1 Comment

Data Guard Broker Improvements in Oracle 10g

Data Guard Broker Improvements in Oracle 10g

The Oracle 10g Data Guard also has its share of improvements, including the following, which are discussed in more detail next:

  • Broker support for RAC databases
  • Enhancements to the 10g Enterprise Manager Data Guard GUI
  • New Data Guard Broker commands

Broker Support for RAC Databases

If you use the GUI interface to create a standby from a RAC database, the standby will be created as a single-instance database.

Starting in 10g, the Data Guard Broker has added support for a RAC database environment. This means that DBAs who run a RAC environment can now configure and support Data Guard configurations using the Broker GUI or the command-line interface. You can use the same Broker commands and GUI pages you used for a single instance, now for a RAC instance. It is important to understand, however, that the Broker only supports sending redo to one instance of the standby database. The standby database can be either a single instance or a RAC-enabled instance, but in either case, only one instance would act as the receiving instance and apply the redo information. It would be convenient to have a standby RAC database apply redo information in case of standby instance failure. If the receiving instance fails, the Broker automatically detects the failure, selects a new receiving instance on the RAC standby, redirects redo shipment to the new receiving instance, and starts log apply on the instance. Just as in prior releases, to perform any switchover with a RAC database in place, all instances except one must be shut down. Because Data Guard and RAC are integrated, however, the Broker will invoke the shutdown commands and perform all necessary work for the switchover.

Enhancements to the Enterprise Manager Data Guard GUI

The 10g Enterprise Manager has updated the interface for the Data Guard Broker GUI. From the Data Guard home page, shown in Figure 16.2, you can now see an overview of the configuration, a summary of the amount of data shipping and applied to each standby, and the status of the standby databases. As a result, DBAs can now easily manage the Data Guard environment.

Figure 16.2. The Data Guard home page via 10g EM Grid Control.


 

The Performance Overview page, which you access by clicking the Performance Overview link in the Performance section of the Data Guard home page, consists of four new charts:

  • Data Archived (MB). This chart indicates the amount of redo generated on the primary over time.
  • Standby Progress Summary. This chart includes Data Not Applied and Data Not Received statistics.
  • Data Applied. This chart conveys the amount of redo being applied on each standby over time.
  • Log Services Summary. This chart includes detailed information about the current log file being used by each database.

This page, shown in Figure 16.3, is monitored in real-time, manually or automatically (every 30 or 60 seconds). There is no difference in the appearance if the databases are RAC or single-instance. If the primary database is a RAC database, each chart will aggregate the information across all necessary threads.

Figure 16.3. The 10g Data Guard Performance Overview page.


 

Also new in 10g Enterprise Manager is the Add Standby Database wizard, which you access by clicking the Add Standby Database button in the Standby Databases section of the Data Guard home page. Using the Add Standby Database wizard enables individuals who previously did not have an advanced understanding of Data Guard to quickly and easily build and run standby databases in any environment.

Building a standby database with the Standby Database Creation Wizard is as easy as providing a few bits of information about your configuration. Figure 16.4 displays the first screen of the Add Standby Database wizard.

Figure 16.4. The new 10g Standby Database Creation wizard.

[View full size image]



 

To use the new 10g Standby Database Creation wizard to build your new standby environment, your primary database must be in archive log mode. If it is not, you will receive an error message about the NOARCHIVELOG MODE status.

When you use the wizard to build a new standby database, the wizard will first display a window to allow you to choose your backup method. Because a hot or cold backup is needed to begin the process, Data Guard Manager utilizes RMAN to perform this backup process (see Figure 16.5). You may also receive a warning at this point if your database is not in force-logging mode. Force logging is not required, but Oracle recommends that your primary database be placed in force-logging mode to ensure that no no-logging operations can be executed at the primary.

Figure 16.5. Choosing a backup method for the 10g Standby Database Creation wizard.


 

Next, as shown in Figure 16.6, the wizard will prompt you for a working directory and the user admin credentials of the Oracle Home owner. The working directory will be used to house the primary database backup files during the standby build process. You can also choose to delete or retain the working directory for future builds.

Figure 16.6. Providing the backup options for the 10g Standby Database Creation wizard.


 

The wizard’s third step, shown in Figure 16.7, prompts you for the standby instance name as well as the user admin credentials over the standby Oracle Home. In this example we used the same Oracle Home for the primary and standby; note, however, that the primary and standby Oracle Homes could have different user admin credentials.

Figure 16.7. Providing the necessary instance name and host credentials for the 10g Standby Database Creation wizard.

]


 

Next, the wizard verifies the standby file locations as well as the network configuration file location, as shown in Figure 16.8. Because our sample standby database uses the same host as the primary, Data Guard Manager automatically uses the OFA (Oracle Flexible Architecture) file structure.

Figure 16.8. Verifying the standby database and network file locations for the 10g Standby Database Creation wizard.


 

The next screen, shown in Figure 16.9, prompts you for the specific information about your standby configuration. Here you will need your database unique name, target name, and standby archive location.

Figure 16.9. Configuring the new standby database using the 10g Standby Database Creation wizard.



 

Possible Error with Standby Filenames

When using the wizard to build your logical or physical standby database, it is possible that you may run into bug 3655231 if you are using Version 10.1.0.3 or below. If you receive the Can’t find unicode character property definition via main->r or r.pl at unicode/Is/r.pl line 0 error message during your standby build you have hit this bug. The workaround is to remove any standby data file and/or log file names that contain \p. If this exists, either rename the directory or choose a different directory to house the files. Then, rename the files accordingly using the Customize button in the screen shown in Figure 16.8. You could also avoid this issue by applying the 10.1.0.4 patch set or oneoff patch 4039195 to version 10.1.0.2.

 

Finally, the wizard displays a summary of your standby build options, as shown in Figure 16.10. Review your choices. If any are incorrect, click the Back button repeatedly until you reach the relevant screen to correct them. When you’re satisfied with your selections, click the Finish button.

Figure 16.10. Overall summary for new target standby database using the 10g Standby Database Creation wizard.


 

Data Guard Manager proceeds with the build by creating your configuration (see Figure 16.11), preparing and submitting the build job (see Figure 16.12), and adding your standby target to Enterprise Manager Grid Control (see Figure 16.13).

Figure 16.11. Process screen showing the creation of the new standby database environment.



 

Figure 16.12. Process screen showing the necessary jobs submitted to build the new standby database.

[View full size image]



 

Figure 16.13. Process screen showing the necessary jobs submitted to build the new standby database.

[View full size image]


 

When the target is complete, Enterprise Manager redirects you to the Data Guard home page so you can monitor the standby database build (see Figure 16.14).

Figure 16.14. The 10g Data Guard home page showing the new standby database in process.



 

New 10g Broker Commands

With Oracle 10g, most DGMGRL commands have changed. Many have been improved with simplified meanings; others are just brand new due to the changes with the 10g Data Guard Broker configuration model. Following are the more notable changes with the 10g Broker command line syntax.

CREATE CONFIGURATION

One command that has become highly simplified with 10g is the CREATE CONFIGURATION command. In previous releases, the CREATE CONFIGURATION command was highly complex; with 10g, however, all you need to supply is three arguments to create your new configurationconfiguration name, database name, and connect identifieras shown here:

DGMGRL> create configuration ‘DR_CONFIG’ as primary database is ‘PROD’

connect identifier is ‘RAC_PROD’;

  • The configuration name is a name you provide for the Broker configuration. The name, like most names in Oracle, must be made up of alphanumeric characters and can only consist of 30 characters. The preceding example shows DR_CONFIG as the configuration name.
  • The database name is the name that will be used by the Broker to refer to the primary database object. The name must match that value of the corresponding database DB_UNIQUE_NAME parameter. In the preceding example, PROD is the database name.
  • The connect identifier can be a fully specified connect descriptor or name that is reprehensive in an Oracle Net environment (a tnsnames.ora enTRy) to the primary database. In a RAC environment, you must make sure your connect identifier can be used to address at least one instance of the primary database. In the preceding example, RAC_PROD is the connect identifier.

When your configuration is set, you can then use the new ADD DATABASE command to add standby databases to the configuration.

ADD DATABASE

This new Broker command is used to add databases to a configuration. The database name is the DB_UNIQUE_NAME of the standby and the connect identifier must resolve to the standby database. The type of standby (physical or logical) can be interchangeable with this command.

DGMGRL> add database ‘DRPROD’ as connect identifier is ‘RAC_DRPROD’ maintained as physical;


 

REMOVE DATABASE

The REMOVE DATABASE command is used to remove a standby database from your configuration. The database name is the DB_QUIQUE_NAME of the standby you want removed.

DGMGRL> remove database ‘DRPROD’;


 

DISABLE DATABASE (Replaces DISABLE_RESOURCE)

The DISABLE DATABASE command is used to disable Broker management of a standby database within a configuration. This means that all Broker-directed modifications will be ignored for this specific standby database. Also, the Broker will no longer monitor the specified database for health-status checks. The database name is the DB_UNIQUE_NAME of the standby you want to disable.

DGMGRL> disable database ‘DRPROD’;


 

ENABLE DATABASE (Replaces ENABLE_RESOURCE)

The ENABLE DATABASE command is used to restore Broker management of a standby database that was previously disabled. The database name is the DB_UNIQUE_NAME of the standby you want to disable.

DGMGRL> enable database ‘DRPROD’;


 

EDIT DATABASE (Replaces ALTER_RESOURCE)

The EDIT DATABASE command is used to set a new property value to your specified database, rename your database, or modify the state of the database. The database name is the DB_UNIQUE_NAME of the standby you want to disable.

DGMGRL> edit database ‘DRPROD’ set property ‘logarchivetrace’=’127’;

DGMGRL> edit database ‘DRPROD’ set state=’read-only’;

DGMGRL> edit database ‘DRPROD’ rename to ‘DRPROD_STDBY’;


 

EDIT CONFIGURATION (Replaces ALTER_CONFIGURATION)

The EDIT CONFIGURATION command is used to modify the current protection mode setting for a predefined configuration.

DGMGRL> edit configuration set protection mode as maxprotection;

Valid protection modes for a configuration are MAXPROTECTION, MAXAVAILABILITY, and MAXPERFORMANCE. The default is MAXPERFORMANCE.

EDIT INSTANCE

The EDIT INSTANCE command is used to modify the value of a property for a specified instance. For these commands, if your instance name is unique across the configuration, then the database name is not required.

DGMGRL> edit instance ‘DRPROD_N1’ on database ‘DRPROD’ set property lsbymaxservers = ‘4’;

Properties that can be modified with the EDIT INSTANCE command include the following:

  • StandbyArchiveLocation. Location of archived redo logs arriving from a primary database.
  • AlternateLocation. Alternate location to be used if the standby can no longer archive to StandbyArchiveLocation.
  • LogArchiveTrace. Integer value to denote the progression of the archiving of redo logs on the primary and standby databases.
  • LogArchiveFormat. Format for filenames of archived redo log files.
  • LsbyMaxSga. Number of megabytes allocated from the System Global Area (SGA) for the log apply services cache. The default value is one quarter (1/4) of the value set for SHARED_POOL_SIZE parameter.
  • LsbyMaxServers. Number of parallel query servers specifically reserved for log apply services.

FAILOVER

The new FAILOVER command specifies that a standby database will take on the role of a primary database.

DGMGRL> failover to DRPROD_STDBY;

If the Data Guard Broker configuration is in Maximum Protection or Maximum Availability mode, a failover will force the protection mode to be MAXIMUM PERFORMANCE. After the failover has occurred, the DBA can change this mode if needed.

SHOW

The new SHOW command displays the current settings of your configuration model. The SHOW command can be used at the configuration, database, or instance level.

DGMGRL> show configuration;

DGMGRL> show database ‘DRPROD’;

DGMGRL> show instance ‘RAC_DRPROD’;


 

SWITCHOVER

The new SWITCHOVER command performs a switchover operation to the database object corresponding to the named site.

DGMGRL> switchover to DRPROD_STDBY;

March 6, 2010 Posted by | oracle | 23 Comments

Oracle EM Grid Control Agent Target Discovery

Oracle EM Grid Control Agent Target Discovery

Sometimes, you may install the 10g OEM GRID agent before you actually create a new database or you may add a new database to an existing box.

This new database will have to be discovered by GRID CONTROL.

I created a database called db11gr2 on a box which already had a Grid agent running on it.

This database has to be discovered by the agent and their details uploaded to the GRID.

Make sure the ORAINVENTORY location in /etc/oraInst.loc matches the location when you installed the AGENT.

Go to the new AGENT_HOME/bin and issue command agentca- d

[oracle@raclinux2 bin]$ agentca -d

Stopping the agent using /u01/app/oracle/OracleHomes/agent10g/bin/emctl stop agent

Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.

Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.

Stopping agent … stopped.

Running agentca using /u01/app/oracle/OracleHomes/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/u01/app/oracle/OracleHomes/agent10g ACTION=Configure MODE=Perform RESPONSE_FILE=/u01/app/oracle/OracleHomes/agent10g/response_file RERUN=TRUE INV_PTR_LOC=/u01/app/oracle/OracleHomes/agent10g/oraInst.loc COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}

Perform – mode is starting for action: Configure

Perform – mode finished for action: Configure

You can see the log file: /u01/app/oracle/OracleHomes/agent10g/cfgtoollogs/oui/configActions2010-03-05_08-33-08-PM.log

[oracle@raclinux2 bin]$

Now login to the database as sys, unlock the dbsnmp user and also change
the password of dbsnmp user – for example to temp.

Login to the GRID, and click on targets – Select the new database and press configure.


Once this is complete, the GRID screen will show the
database on the targets page.

March 5, 2010 Posted by | oracle | Leave a comment

Creating physical standby in Oracle 10g

Creating physical standby in Oracle 10g

primary database name:  10gpri on raclinux2 server

standby database name: 10gsb on raclinux1 server
Create a Physical Standby environment, General Review.


Detailed implementation of a physical standby environment
Primary Database Steps
Primary Database General View

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Current log sequence           1
SQL> select name from v$database;

The Enviroment
     2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux
     Oracle Database 10g Enterprise Edition Release 10g R2
     ssh is configured for user Oracle on both nodes
     Oracle Home is on identical path on both nodes
Implementation notes:
     Once you have your primary database up and running these are the steps to follow:
     1.  Enable Forced Logging
     2.  Create a Password File
     3.  Configure a Standby Redo Log
     4.  Enable Archiving
     5.  Set Primary Database Initialization Parameters
     Having followed these steps to implement the physical standby you need to follow these steps:
     1. Create a Control File for the Standby Database
     2. Backup the Primary Database and transfer a copy to the Standby node.
     3. Prepare an Initialization Parameter File for the Standby Database
     4. Configure the listener and tnsnames to support the database on both nodes
     5. Set Up the Environment to Support the Standby Database on the standby node.
     6. Start the Physical Standby Database
     7. Verify the Physical Standby Database Is Performing Properly
physical standby manually.implementconfiguration is necessary to  additionalsuccessfully and no  is installed, Oracle database are met, that accommodate an Oracle database. All prerequisites raclinux2and  raclinux1Manually setting up a Physical standby database is a simple task when all prerequisites and setup steps are carefully planed and executed. In this example We use 2 hosts,

NAME
———
10GPRI

SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/10gpri/system01.dbf
/u01/app/oracle/oradata/10gpri/undotbs01.dbf
/u01/app/oracle/oradata/10gpri/sysaux01.dbf
/u01/app/oracle/oradata/10gpri/users01.dbf

SQL> show parameters unique

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_unique_name                       string      10gpri
SQL>

Enable Forced Logging
  In order to implement Standby Database we enable ‘Forced Logging’.
  This option ensures that even in the event that a ‘nologging’ operation is done, force logging takes precedence and all operations are logged   into the redo logs.
  SQL> ALTER DATABASE FORCE LOGGING;
  Database altered.
Create a Password File
  A password file must be created on the Primary and copied over to the Standby site. The sys password must be identical on both sites. This is   a key pre requisite in order to be able to ship and apply archived logs from Primary to Standby.

 [oracle@raclinux2 ~]$ cd $ORACLE_HOME/dbs
[oracle@raclinux2 dbs]$ orapwd file=orapw10gpri password=oracle force=y

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
—————————— —– —–
SYS                            TRUE  TRUE

Configure a Standby Redo Log
  A Standby Redo log is added to enable Data Guard Maximum Availability and maximum Protection modes. It is important to configure the   Standby Redo Logs (SRL) with the same size as the online redo logs.  In this example I’m using Oracle Managed Files, that’s why I don’t need to provide the SRL path and file name. If you are not using OMF’s   you then must pass the full qualified name.


SQL> select group#,type,member from v$logfile;

    GROUP# TYPE    MEMBER
———- ——- ————————————————–
         3 ONLINE  /u01/app/oracle/oradata/10gpri/redo03.log
         2 ONLINE  /u01/app/oracle/oradata/10gpri/redo02.log
         1 ONLINE  /u01/app/oracle/oradata/10gpri/redo01.log


SQL> select bytes from v$log;
     BYTES
———-
  52428800
  52428800
  52428800
Edit the pfile to add the standby parameters, here shown highlighted:Once the new parameter file is ready we create from it the spfile:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>  startup nomount pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/init10gpri.ora
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux Error: 2: No such file or directory
note:
create an archive log destination(location) folder as per init parameter file and then startup the database.
SQL> startup nomount pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/init10gpri.ora
ORACLE instance started.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/u01/app/oracle/oradata/10gpri/stby04.log’ size 50m;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
‘/u01/app/oracle/oradata/10gpri/stby05.log’ size 50m;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
‘/u01/app/oracle/oradata/10gpri/stby06.log’ size 50m;

Database altered.

SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;

    GROUP# TYPE    MEMBER
———- ——- ————————————————–
         3 ONLINE  /u01/app/oracle/oradata/10gpri/redo03.log
         2 ONLINE  /u01/app/oracle/oradata/10gpri/redo02.log
         1 ONLINE  /u01/app/oracle/oradata/10gpri/redo01.log
         4 STANDBY /u01/app/oracle/oradata/10gpri/stby04.log
         5 STANDBY /u01/app/oracle/oradata/10gpri/stby05.log
         6 STANDBY /u01/app/oracle/oradata/10gpri/stby06.log

6 rows selected.

Set Primary Database Initialization Parameters
  Data Guard must use spfile, in order to configure it we create and configure the standby parameters on a regular pfile, and once it is ready we   convert it to an spfile.
  Several init.ora parameters control the behavior of a Data Guard environment. In this example the Primary database init.ora is configured so   that it can hold both roles, as Primary or Standby.

SQL> CREATE PFILE FROM SPFILE;

File created.

(or)

SQL> CREATE PFILE=’/tmp/init10gpri.ora’ from spfile;

File created.

10gpri.__db_cache_size=184549376
10gpri.__java_pool_size=4194304
10gpri.__large_pool_size=4194304
10gpri.__shared_pool_size=88080384
10gpri.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/10gpri/adump’
*.background_dump_dest=’/u01/app/oracle/admin/10gpri/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u01/app/oracle/oradata/10gpri/control01.ctl’,’/u01/app/oracle/oradata/10gpri/control02.ctl’,’/u01/app/oracle/oradata/10gpri/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/10gpri/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’10gpri’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=10gpriXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=285212672
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/10gpri/udump’
db_unique_name=’10gpri’
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(10gpri,10gsb)’
LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/oradata/10gpri/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gpri’
LOG_ARCHIVE_DEST_2=’SERVICE=10gsb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gsb’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
#Standby role parameters——————————————
fal_server=10gsb
fal_client=10gpri
standby_file_management=auto
db_file_name_convert=’/u01/app/oracle/oradata/10gsb/’,’/u01/app/oracle/oradata/10gpri/’
log_file_name_convert=’/u01/app/oracle/oradata/10gsb/’,’/u01/app/oracle/oradata/10gpri/’


Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
SQL> create spfile from pfile;

File created.

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

ORACLE instance shut down.

Enable Archiving
  On 10g you can enable archive log mode by mounting the database and executing the archivelog command:
SQL> startup mount
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oradata/10gpri/arch/
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL>

Standby Database Steps
Here, we are going to create standby database using backup of the primary database using rman.where in comparison to user managed backup, rman is comfortable and flexible method.

Create an RMAN backup which we will use later to create the standby:

[oracle@raclinux2 ~]$ . oraenv
ORACLE_SID = [orcl] ? 10gpri
[oracle@raclinux2 ~]$ rman target=/

Recovery Manager: Release 10.2.0.1.0 – Production on Wed Jan 20 18:41:51 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: 10GPRI (DBID=3603807872)

RMAN> backup full database format ‘/u01/app/oracle/backup/%d_%U.bckp’ plus archivelog format ‘/u01/app/oracle/backup/%d_%U.bckp’;

Next, create a standby controlfile backup via RMAN:
RMAN> configure channel device type disk format ‘/u01/app/oracle/backup/%U’;

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   ‘/u01/app/oracle/backup/%U’;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

RMAN> BACKUP ARCHIVELOG ALL;

In this simple example, We are backing up the primary database to disk; therefore,we must make the backupsets available to the standby host if we want to use them as the basis for my duplicate operation:
[oracle@raclinux2 ~]$ cd /u01/app/oracle/backup
[oracle@raclinux2 backup]$ ls -lart
total 636080
drwxrwxr-x  9 oracle oinstall      4096 Jan 20 18:42 ..
-rw-r—–  1 oracle oinstall  50418176 Jan 20 18:43 10GPRI_01l3v1uv_1_1.bckp
-rw-r—–  1 oracle oinstall 531472384 Jan 20 18:54 10GPRI_02l3v203_1_1.bckp
-rw-r—–  1 oracle oinstall   7143424 Jan 20 18:54 10GPRI_03l3v2jf_1_1.bckp
-rw-r—–  1 oracle oinstall   1346560 Jan 20 18:54 10GPRI_04l3v2jv_1_1.bckp
-rw-r—–  1 oracle oinstall   7110656 Jan 20 19:19 05l3v41r_1_1
drwxr-xr-x  2 oracle oinstall      4096 Jan 20 19:20 .
-rw-r—–  1 oracle oinstall  53174272 Jan 20 19:21 06l3v448_1_1
[oracle@raclinux2 backup]$ scp * oracle@raclinux1:/u01/app/oracle/backup/
05l3v41r_1_1                                  100% 6944KB   6.8MB/s   00:00
06l3v448_1_1                                  100%   51MB  16.9MB/s   00:03
10GPRI_01l3v1uv_1_1.bckp                       100%   48MB   2.7MB/s   00:18
10GPRI_02l3v203_1_1.bckp                       100%  507MB   1.5MB/s   05:47
10GPRI_03l3v2jf_1_1.bckp                       100% 6976KB 996.6KB/s   00:07
10GPRI_04l3v2jv_1_1.bckp                       100% 1315KB   1.3MB/s   00:01

NOTE:

The primary and standby database location for backup folder must be same.

for eg: /u01/app/oracle/backup folder

On the standby node create the required directories to get the datafiles

mkdir -p /u01/app/oracle/oradata/10gsb
mkdir -p /u01/app/oracle/oradata/10gsb/arch
mkdir -p /u01/app/oracle/admin/10gsb
mkdir -p /u01/app/oracle/admin/10gsb/adump
mkdir -p /u01/app/oracle/admin/10gsb/bdump
mkdir -p /u01/app/oracle/admin/10gsb/udump
mkdir -p /u01/app/oracle/flash_recovery_area/10GPRI
mkdir -p /u01/app/oracle/flash_recovery_area/10GPRI/onlinelog
Prepare an Initialization Parameter File for the Standby Database

Copy from the primary pfile to the standby destination
[oracle@raclinux2 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@raclinux2 dbs]$ scp init10gpri.ora oracle@raclinux1:/tmp/init10gsb.ora
init10gpri.ora                                 100% 1704     1.7KB/s   00:00

Copy and edit the primary init.ora to set it up for the standby role,as shown highlighted below:


10gsb.__db_cache_size=188743680
10gsb.__java_pool_size=4194304
10gsb.__large_pool_size=4194304
10gsb.__shared_pool_size=83886080
10gsb.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/10gsb/adump’
*.background_dump_dest=’/u01/app/oracle/admin/10gsb/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u01/app/oracle/oradata/10gsb/control01.ctl’,’/u01/app/oracle/oradata/10gsb/control02.ctl’,’/u01/app/oracle/oradata/10gsb/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/10gsb/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_file_name_convert=’/u01/app/oracle/oradata/10gpri/’,’/u01/app/oracle/oradata/10gsb/’
*.db_name=’10gpri’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.db_unique_name=’10gsb
*.dispatchers='(PROTOCOL=TCP) (SERVICE=10gsbXDB)’
*.fal_client=’10gsb
*.fal_server=’10gpri
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(10gpri,10gsb)’
*.LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/oradata/10gsb/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gsb
*.LOG_ARCHIVE_DEST_2=’SERVICE=10gpri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gpri
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.LOG_ARCHIVE_FORMAT=’%t_%s_%r.arc’
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.log_file_name_convert=’/u01/app/oracle/oradata/10gpri/’,’/u01/app/oracle/oradata/10gsb/’
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=285212672
*.standby_file_management=’auto’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/10gsb/udump’

Configure the listener and tnsnames to support the database on both nodes

Configure listener.ora on both servers to hold entries for both databases


#on RACLINUX2 Machine
LISTENER_VMRACLINUXTEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2.gj.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER_VMRACLINUXTEST =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = 10gpri)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = 10gpri)
    )
)

#on raclinux1 machine

LISTENER_VMRACLINUXTEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1.gj.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER_VMRACLINUXTEST =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = 10gsb)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = 10gsb)
    )
)

Configure tnsnames.ora on both servers to hold entries for both databases


#on raclinux2 machine


LISTENER_VMRACLINUXTEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2.gj.com)(PORT = 1521))
    )
  )

10GPRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2.gj.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 10gpri)
    )
  )
10GSB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1.gj.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 10gsb)
    )
  )
#on raclinux1 machine


LISTENER_VMRACLINUXTEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1.gj.com)(PORT = 1521))
    )
  )

10GPRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2.gj.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 10gpri)
    )
  )

10GSB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1.gj.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 10gsb)
    )
  )

Start the listener and check tnsping on both nodes to both services

#on machine raclinux1

[oracle@raclinux1 tmp]$ lsnrctl stop LISTENER_VMRACLINUXTEST

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 20-JAN-2010 23:59:41

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux1.gj.com)(PORT=1521)))
The command completed successfully


[oracle@raclinux1 tmp]$ lsnrctl start LISTENER_VMRACLINUXTEST


LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 25-DEC-2009 00:00:00

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 10.2.0.1.0 – Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener_vmraclinuxtest.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=raclinux1.gj.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux1.gj.com)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     LISTENER_VMRACLINUXTEST
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date                25-DEC-2009 00:00:00
Uptime                    0 days 0 hr. 0 min. 0 sec
Traclinuxe Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener_vmraclinuxtest.log
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=raclinux1.gj.com)(PORT=1521)))
Services Summary…
Service “10gsb” has 1 instance(s).
  Instance “10gsb”, status UNKNOWN, has 1 handler(s) for this service…
Service “10gsb_DGMGRL” has 1 instance(s).
  Instance “10gsb”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

[oracle@raclinux1 tmp]$ tnsping 10gsb

TNS Ping Utility for Linux: Version 10.2.0.1.0 – Production on 25-DEC-2009 00:00:21

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1.gj.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = 10gsb)))
OK (10 msec)


[oracle@raclinux1 tmp]$ tnsping 10gpri

TNS Ping Utility for Linux: Version 10.2.0.1.0 – Production on 25-DEC-2009 00:00:29
#on raclinux2 machine

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2.gj.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = 10gpri)))
OK (10 msec)


[oracle@raclinux2 dbs]$ lsnrctl stop LISTENER_VMRACLINUXTEST

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 25-DEC-2009 00:22:48[oracle@raclinux2 dbs]$ lsnrctl start LISTENER_VMRACLINUXTEST

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 25-DEC-2009 00:23:08

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux2.gj.com)(PORT=1 521)))
The command completed successfully

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 10.2.0.1.0 – Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/liste ner.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener _vmraclinuxtest.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=raclinux2.gj.com)(PORT=1 521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=raclinux2.gj.com)(PORT=1 521)))
STATUS of the LISTENER
————————
Alias                     LISTENER_VMRACLINUXTEST
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date                25-DEC-2009 00:23:08
Uptime                    0 days 0 hr. 0 min. 0 sec
Traclinuxe Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/list ener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listen er_vmraclinuxtest.log
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=raclinux2.gj.com)(PORT=1521)))
Services Summary…
Service “10gpri” has 1 instance(s).
  Instance “10gpri”, status UNKNOWN, has 1 handler(s) for this service…
Service “10gpri_DGMGRL” has 1 instance(s).
  Instance “10gpri”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully


[oracle@raclinux2 dbs]$ tnsping 10gpri

TNS Ping Utility for Linux: Version 10.2.0.1.0 – Production on 25-DEC-2009 00:23 :14

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2.loc aldomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = whi te)))
OK (0 msec)

[oracle@raclinux2 dbs]$ tnsping 10gsb

TNS Ping Utility for Linux: Version 10.2.0.1.0 – Production on 25-DEC-2009 00:23 :18

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1.loc aldomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bla ck)))
OK (10 msec)

Set Up the Environment to Support the Standby Database on the standby node.

Create a passwordfile for the standby:
[oracle@raclinux1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapw10gsb password=oracle
note: sys password must be identical for both primary and standby databaseoratab:Startup nomount the Standby database

Append an entry to

[oracle@raclinux1 ~]$ echo “10gsb:/u01/app/oracle/product/10.2.0/db_1:N” >> /etc/oratab

Nomount the standby instance in preparation for the duplicate operation:
 Startup nomount the Standby database and generate an spfile

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jan 21 00:38:03 2010
SQL> startup nomount pfile=’/tmp/init10gsb.ora’
ORACLE instance started.
ORA-01507: database not mounted
SQL> startup nomount
ORACLE instance started.
Create the standby database using rman:
[oracle@raclinux1 ~]$ . oraenv
ORACLE_SID = [orcl1] ? 10gsb
[oracle@raclinux1 ~]$ rman target=sys/oracle@10gpri auxiliary=/

[oracle@raclinux1 ~]$ . oraenv
ORACLE_SID = [orcl1] ? 10gsb
[oracle@raclinux1 ~]$ sqlplus ‘/as sysdba’

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes

SQL> create spfile from pfile=’/tmp/init10gsb.ora’;

File created.

SQL> shutdown immediate

ORACLE instance shut down.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes

Recovery Manager: Release 10.2.0.1.0 – Production on Thu Jan 21 00:43:11 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: 10GPRI (DBID=3603807872)
connected to auxiliary database: 10GPRI (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

 Start the redo apply:

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


Test the configuration by generating archive logs from the primary and then querying the standby to see if the logs are being successfully applied.

On the Primary:

SQL> alter system switch logfile;

SQL> alter system archive log current;

 SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/10gpri/arch/

Oldest online log sequence 8

Next log sequence to archive 10

Current log sequence 10

 On the Standby:

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/10gsb/arch/

Oldest online log sequence 8

Next log sequence to archive 0

Current log sequence 10

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 
 

 Stop the managed recovery process on the standby:

SQL> alter database recover managed standby database cancel;

 

March 5, 2010 Posted by | oracle | 5 Comments