Oracle 10g R2 RAC Installation for PROD Nodes:
Step by Step instructions for installing Oracle 10g R2 RAC installation. The procedures will provide STEP By STEP guide you for installing two nodes (prodracsrv1and prodracsrv2) and adding to the existing RAC cluster(Configuring Failover).

10g RAC Installation (Part-II Clusterware & Database Installation)

1. Install Oracle Clusterware

Mount the Clusterware dvd in the prodracsrv1 and run the runInstaller

After downloading, as the oracle user on prodracsrv1, execute

1. Welcome: Click on Next.

2. Specify Inventory directory and credentials:

o Enter the full path of the inventory directory:

/u01/app/oracle/oraInventory.

o Specify Operating System group name: oinstall.

3. Specify Home Details:

o Name: OraCrs10g_home

o /u01/app/oracle/product/10.2.0/crs_1

4. Product-Specific Prerequisite Checks:

o Ignore the warning on physical memory requirement.

5. Specify Cluster Configuration: Click on Add.

o Public Node Name: prodracsrv2.mycorpdomain.com

o Private Node Name: prodracsrv2-priv.mycorpdomain.com

o Virtual Host Name: prodracsrv2-vip.mycorpdomain.com

6. Specify Network Interface Usage:

o Interface Name: eth0

o Subnet: 192.168.2.0

o Interface Type: Public

o Interface Name: eth1

o Subnet: 10.10.10.0

o Interface Type: Private

7. Specify Oracle Cluster Registry (OCR) Location: Select External Redundancy.

For simplicity, here you will not mirror the OCR. In a production environment,

you may want to consider multiplexing the OCR for higher redundancy.

o Specify OCR Location: /u01/ocr_config

8. Specify Voting Disk Location: Select External Redundancy.

Similarly, for simplicity, we have chosen not to mirror the Voting Disk.

o Voting Disk Location: /u01/votingdisk

9. Summary: Click on Install.

10. Execute Configuration scripts: Execute the scripts below as the root user

sequentially, one at a time. Do not proceed to the next script until the current

script completes.

o Execute /u01/app/oracle/oraInventory/orainstRoot.sh on prodracsrv1.

o Execute /u01/app/oracle/oraInventory/orainstRoot.sh on prodracsrv2.

o Execute /u01/app/oracle/product/10.2.0/crs_1/root.sh on prodracsrv1.

o Execute /u01/app/oracle/product/10.2.0/crs_1/root.sh on prodracsrv2.

The root.sh script on prodracsrv2 invoked the VIPCA automatically but it failed with the

error “The given interface(s), “eth0″ is not public. Public interfaces should be

used to configure virtual IPs.” As you are using a non-routable IP address

(192.168.x.x) for the public interface, the Oracle Cluster Verification Utility

(CVU) could not find a suitable public interface. A workaround is to run VIPCA

manually.

11. As the root user, manually invokes VIPCA on the second node.

# /u01/app/oracle/product/10.2.0/crs_1/bin/vipca

Welcome: Click on Next.

Network Interfaces: Select eth0.

Virtual IPs for cluster nodes:

o Node name: prodracsrv1

o IP Alias Name: prodracsrv1-vip

o IP address: 192.168.2.31

o Subnet Mask: 255.255.255.0

o Node name: prodracsrv2

o IP Alias Name: prodracsrv2-vip

o IP address: 192.168.2.32

o Subnet Mask: 255.255.255.0

Summary: Click on Finish.

Configuration Assistant Progress Dialog: After the configuration has completed,

Click on OK.

Configuration Results: Click on Exit.

Return to the Execute Configuration scripts screen on prodracsrv1 and click on OK.

Configuration Assistants: Verify that all checks are successful. The OUI does a

Clusterware post-installation check at the end. If the CVU fails, correct the

Problem and re-run the following command as the oracle user.

prodracsrv1-> /u01/app/oracle/product/10.2.0/crs_1/bin/cluvfy stage -post crsinst -n prodracsrv1, prodracsrv2

23. Performing post-checks for cluster services setup

24.

25. Checking node reachability…

26. Node reachability check passed from node “prodracsrv1”.

27.

28. Checking user equivalence…

29. User equivalence check passed for user “oracle”.

30.

31. Checking Cluster manager integrity…

32.

33. Checking CSS daemon…

34. Daemon status check passed for “CSS daemon”.

35.

36. Cluster manager integrity check passed.

37.

38. Checking cluster integrity…

39.

40. Cluster integrity check passed

41.

42. Checking OCR integrity…

43.

44. Checking the absence of a non-clustered configuration…

45. All nodes free of non-clustered, local-only configurations.

46.

47. Uniqueness check for OCR device passed.

48.

49. Checking the version of OCR…

50. OCR of correct Version “2” exists.

51.

52. Checking data integrity of OCR…

53. Data integrity check for OCR passed.

54.

55. OCR integrity check passed.

56.

57. Checking CRS integrity…

58.

59. Checking daemon liveness…

60. Liveness check passed for “CRS daemon”.

61.

62. Checking daemon liveness…

63. Liveness check passed for “CSS daemon”.

64.

65. Checking daemon liveness…

66. Liveness check passed for “EVM daemon”.

67.

68. Checking CRS health…

69. CRS health check passed.

70.

71. CRS integrity check passed.

72.

73. Checking node application existence…

74.

75. Checking existence of VIP node application (required)

76. Check passed.

77.

78. Checking existence of ONS node application (optional)

79. Check passed.

80.

81. Checking existence of GSD node application (optional)

82. Check passed.

83.

84. Post-check for cluster services setup was successful.

85. End of Installation: Click on Exit.

2. Install Oracle Database 10g Release 2

After mounting database 10g R2 DVD run the installer

prodracsrv1-> ./runInstaller

1. Welcome: Click on Next.

2. Select Installation Type:

o Select Enterprise Edition.

3. Specify Home Details:

o Name: OraDb10g_home1

o Path: /u01/app/oracle/product/10.2.0/db_1

4. Specify Hardware Cluster Installation Mode:

o

Select the “Cluster Install” option and make sure both RAC nodes are selected, the click the “Next” button

o Select the “Install database Software only” option, then click the “Next” button.

Wait while the prerequisite checks are done. If you have any failures correct them and retry the tests before clicking the “Next” button.

7. Select the “Install database Software only” option, then click the “Next” button.

8. On the “Summary” screen, click the “Install” button to continue.

9. Wait while the database software installs.

10. Once the installation is complete, wait while the configuration assistants run.

11. Execute the “root.sh” scripts on both nodes, as instructed on the “Execute Configuration scripts” screen, then click the “OK” button.

12. When the installation is complete, click the “Exit” button to leave the installer.

Adding to the cluster

RAC Physical Standby for a RAC Primary

Overview……………………………………………………………………………………………….2

Task 1: Gather Files and Perform Back Up…………………………………………..3

Task 2: Configure Oracle Net SERVICES on the Standby……………………3

Task 3: Create the Standby Instances and Database………………………………4

Task 4: Configure The Primary Database For Data Guard……………………9

Task 5: Verify Data Guard Configuration……………………………………………10

the database unique name of the RAC database as DEVDB. The instance names of the two RAC instances are DEVDB1 (on node DEVDB_tempracsrv3) and DEVDB2 (on node DEVDB_tempracsrv4). The database unique name of the RAC standby database is PROD, and the two standby instance names are PROD1 (on node PROD_prodracsrv1) and PROD2 (on node PROD_prodracsrv2).

This document includes the following tasks:

• Task 1: Gather Files and Perform Back Up

• Task 2: Configure Oracle Net on the Standby

• Task 3: Create the Standby Instances and Database

• Task 4: Configure the Primary Database for Data Guard

• Task 5: Verify Data Guard Configuration

This document assumes that the following conditions are met:

• The primary RAC database is in archivelog mode

Creating a RAC Physical Standby for a RAC Primary

• The primary and standby databases are using a flash recovery area.

• The standby RAC hosts have existing Oracle software installation.(prodracsrv1,prodracsrv2)…..

• Oracle Managed Files (OMF) is used for all storage.

TASK 1: GATHER FILES AND PERFORM BACK UP

1. On tempracsrv3 node, create a staging directory. For example:

[oracle@DEVDB_tempracsrv3 oracle]$ mkdir -p /opt/oracle/stage

2. Create the same exact path on one of the standby hosts:

[oracle@PROD_prodracsrv1 oracle]$ mkdir -p /opt/oracle/stage

3. On the tempracsrv3 node, connect to the primary database and create a PFILE from the SPFILE in the staging directory. For example:

SQL> CREATE PFILE=’/opt/oracle/stage/initDEVDB.ora’ FROM SPFILE;

4. On the tempracsrv3 node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory. For example:

[oracle@DEVDB_host1 stage]$ rman target /

RMAN> BACKUP DEVICE TYPE DISK FORMAT ‘/opt/oracle/stage/%U’ DATABASE PLUS ARCHIVELOG;

RMAN> BACKUP DEVICE TYPE DISK FORMAT ‘/opt/oracle/stage/%U’ CURRENT CONTROLFILE FOR STANDBY;

5. Place a copy of the listener.ora, tnsnames.ora, and sqlnet.ora files into the staging directory. For example:

[oracle@DEVDB_tempracsrv3 oracle]$ cp $ORACLE_HOME/network/admin/*.ora /opt/oracle/stage

6. Copy the contents of the staging directory on the RAC primary node to the standby node on which the staging directory was created on in step 2. For example:

[oracle@DEVDB_host1 oracle]$ scp /opt/oracle/stage/* \

oracle@PROD_prodracsrv1:/opt/oracle/stage

and from there copy all the dbf’s the current logfiles,standby controlfile to the according locations(Note the location should be the same location as it was in the primary).

TASK 2: CONFIGURE ORACLE NET SERVICES ON THE STANDBY

1. Copy the listener.ora, tnsnames.ora, and sqlnet.ora files from the staging directory on the standby host to the $ORACLE_HOME/network/admin directory on all standby hosts.

2. Modify the listener.ora file each standby host to contain the VIP address of that host.

Creating a RAC Physical Standby for a RAC Primary

3. Modify the tnsnames.ora file on each node, including the primary RAC nodes and standby RAC nodes, to contain all primary and standby net service names. You should also modify the Oracle Net aliases that are used for the local_listener and remote_listener parameters to point to the listener on each standby host. In this example, each tnsnames.ora file should contain all of the net service names in the following table:

Example Entries in the tnsnames.ora Files

4. Start the standby listeners on all standby hosts.

TASK 3: CREATE THE STANDBY INSTANCES AND DATABASE

1. To enable secure transmission of redo data, make sure the primary and standby databases use a password file, and make sure the password for the SYS user is identical on every system. For example:

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwPROD password=oracle

2. Copy and rename the primary database PFILE from the staging area on all standby hosts to the $ORACLE_HOME/dbs directory on all standby hosts. For example:

[oracle@PROD_host1 stage]$ cp initDEVDB1.ora $ORACLE_HOME/dbs/initPROD1.ora

3. Modify the standby initialization parameter file copied from the primary node to include Data Guard parameters as illustrated in the following table:

Creating a RAC Physical Standby for a RAC Primary

Initialization Parameter Modifications

5. Connect to the standby database on one standby host, with the standby in the IDLE state, and create an SPFILE in the standby DATA disk group:

SQL> CREATE SPFILE=’+DATA/PROD/spfilePROD.ora’ FROM PFILE=’?/dbs/initPROD.ora’;

6. In the $ORACLE_HOME/dbs directory on each standby host, create a PFILE that is named initoracle_sid.ora that contains a pointer to the SPFILE. For example:

[oracle@PROD_prodracsrv1 oracle]$ cd $ORACLE_HOME/dbs

[oracle@PROD_prodracsrv1 dbs]$ echo spfilePROD.ora > initPROD1.ora

7. Create the dump directories on all standby hosts as referenced in the standby initialization parameter file. For example:

[oracle@PROD_prodracsrv1 oracle]$ mkdir -p $ORACLE_BASE/admin/PROD/bdump

[oracle@PROD_prodracsrv1 oracle]$ mkdir -p $ORACLE_BASE/admin/PROD/cdump

[oracle@PROD_prodracsrv1 oracle]$ mkdir -p $ORACLE_BASE/admin/PROD/udump

[oracle@PROD_prodracsrv1 oracle]$ mkdir -p $ORACLE_BASE/admin/PROD/adump

8. After setting up the appropriate environment variables on each standby host, such as ORACLE_SID, ORACLE_HOME, and PATH, start the standby database instance on the standby host that has the staging directory, without mounting the control file.

SQL> STARTUP NOMOUNT

9. From the standby host where the standby instance was just started, duplicate the primary database as a standby into the ASM disk group. For example:

$ rman target sys/oracle@DEVDB auxiliary /

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY;

10. Connect to the standby database, and create the standby redo logs to support the standby role. The standby redo logs must be the same size as

the primary database online logs. The recommended number of standby redo logs is:

(maximum # of logfiles +1) * maximum # of threads

This example uses two online log files for each thread. Thus, the number of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 5 SIZE 10M,

GROUP 6 SIZE 10M,

GROUP 7 SIZE 10M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2

GROUP 8 SIZE 10M,

GROUP 9 SIZE 10M,

GROUP 10 SIZE 10M;

These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter. Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;

You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;

You can also see the members created by querying the V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;

.

11. On only one standby host (and this is your designated Redo Apply instance), start managed recovery and real-time apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

12. On either node of the standby cluster, register the standby database and the database instances with the Oracle Cluster Registry (OCR) using the Server Control (SRVCTL) utility. For example:

$ srvctl add database -d PROD –o /u01/app/oracle/product/10.2.0/db_1

$ srvctl add instance -d PROD -i PROD1 -n PROD_prodracsrv1

$ srvctl add instance -d PROD -i PROD2 -n PROD_prodracsrv2

The following are descriptions of the options in these commands:

The -d option specifies the database unique name (DB_UNIQUE_NAME) of the database.

The -i option specifies the database insance name.

The -n option specifies the node on which the instance is running.

The -o option specifies the Oracle home of the database.

TASK 4: CONFIGURE THE PRIMARY DATABASE FOR DATA GUARD

1. Configure the primary database initialization parameters to support both the primary and standby roles.

*.log_archive_config=’dg_config=(PROD,DEVDB)’

*.log_archive_dest_2=’service=PROD

valid_for=(online_logfiles,primary_role)

db_unique_name=PROD’

*.standby_file_management=auto

*.fal_server=’PROD’

*.fal_client=’DEVDB’

*.service_names=DEVDB

Note that all the parameters listed above can be dynamically modified with the exception of the standby role parameters log_file_name_convert and db_file_name_convert. It is recommended to set the parameters with “scope=spfile” so that they can be put into effect upon the next role change.

2. Create standby redo logs on the primary database to support the standby role. The standby redo logs are the same size as the primary database online logs. The recommended number of standby redo logs is one more than the number of online redo logs for each thread. Because this example has two online redo logs for each thread, three standby redo logs are required for each thread.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 5 SIZE 10M,

GROUP 6 SIZE 10M,

GROUP 7 SIZE 10M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2

GROUP 8 SIZE 10M,

GROUP 9 SIZE 10M,

GROUP 10 SIZE 10M;

These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter. Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;

You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;

You can also see the members created by querying V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;

TASK 5: VERIFY DATA GUARD CONFIGURATION

1. On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

2. On the primary database, issue the following SQL statement to force a log switch and archive the current online redo log file group:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

3. On the standby database, query the V$ARCHIVED_LOG view to verify that the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Now we have setup a Failover instance for RAC on prodracsrv1,prodracsrv2.

— Server A – Is a Live Server
— Server B – Is a Physical Standby Server
Both were configured for a data guard

One fine day a standby blows away and we need to recreate it from Live

At Live Server A

1. There is no need but if you want please disable Dataguard Broker

SQLPLUS> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

SQLPLUS> alter system set dg_broker_start=FALSE

DGMGRL> show configuration verbose;

Configuration
Name: odba
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
catp_hongkong – Primary database
catp_morocco – Physical standby database (disabled)

2. Put current database into a HOT backup mode.
Backup Datafiles, Controlfiles and Redo logs

Copy files from backup set on a standby database.

At Standby Server B

3. Please make sure following parameters are set on a Physical Standby database init.ora file

4. Recover Standby Database Now

SQL> connect / as sysdba
Connected.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle1/oradata/catp
Oldest online log sequence 199166
Next log sequence to archive 199169
Current log sequence 199169
SQL> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean FALSE

SQL> alter system set dg_broker_start=TRUE scope=both;
System altered.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2030296 bytes
Variable Size 503317800 bytes
Database Buffers 1627389952 bytes
Redo Buffers 14745600 bytes

SQL> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

SQL> recover managed standby database disconnect from session;
ORA-01507: database not mounted

SQL> alter database mount standby database;

Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

Monitor Alrt log for messages for ARCHIVE Success

Media Recovery Waiting for thread 1 sequence 199169
Fetching gap sequence in thread 1, gap sequence 199169-199177
Tue Jun 5 15:54:45 2007
Redo Shipping Client Connected as PUBLIC
— Connected User is Valid
RFS[2]: Assigned to RFS process 3397
RFS[2]: Identified database type as ‘physical standby’
RFS[2]: No standby redo logfiles created
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199179_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199169_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199170_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199171_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199172_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199173_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199174_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199175_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199176_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199177_1_528884944.arc’
Tue Jun 5 15:55:15 2007
Media Recovery Log /oracle1/oradata/catp/catp_0000199169_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199170_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199171_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199172_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199173_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199174_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199175_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199176_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199177_1_528884944.arc
Media Recovery Waiting for thread 1 sequence 199178
Fetching gap sequence in thread 1, gap sequence 199178-199178
Tue Jun 5 15:55:18 2007
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199178_1_528884944.arc’

You can check v$archived_log and v$dataguard_status views to find out logs applied and errors if any respectively.

1. Logs Applied on Standby
Select sequence#, applied from v$archived_log where STANDBY_DEST=’YES’ order by sequence#;
OR
select sequence#, applied ,completion_time from v$archived_log where STANDBY_DEST=’YES’ order by 3 desc

2. Error message at Dataguard
Select to_char(TIMESTAMP, ‘dd-mon-yyyy hh24:mm:ss’), MESSAGE from v$dataguard_status;

Sometimes Oracle throws error messages at ALRTLOG – Failed to request gap sequence

Reason : DG FAL failed to copy archive files from PRIMARY to STANDBY

Media Recovery Waiting for thread 1 sequence 84730
Fetching gap sequence in thread 1, gap sequence 84730-85042
FAL[client]: Trying FAL server: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=zagreb)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=bfp_zagreb_XPT.yell)(SERVER=dedicated)))
Mon Nov 12 15:06:45 2007
Completed: ALTER DATABASE RECOVER managed standby database d
Mon Nov 12 15:11:45 2007
FAL[client]: Failed to request gap sequence for thread 1 gap sequence 84730-85042
FAL[client]: All defined FAL servers have been attempted.

Fix:

SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> Select sequence#, applied from v$archived_log where STANDBY_DEST=’YES’ order by sequence#;

no rows selected

SQL> recover standby database;
ORA-01153: an incompatible media recovery is active

STOP EARLIER recovery session

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> recover standby database;
ORA-00279: change 1967405177515 generated at 11/08/2007 10:06:55 needed for
thread 1
ORA-00289: suggestion : /oracle1/oradata/bfp/bfp_1_569156389_0000084730.arc
ORA-00280: change 1967405177515 for thread 1 is in sequence #84730

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto

This will end up as

ORA-00308: cannot open archived log
‘/oracle1/oradata/bfp/bfp_1_569156389_0000084800.arc’
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Rman Script to Create Standby From Live Server
connect target rman/obst@live
connect auxiliary sys/ams@standby
connect rcvcat rmanc/obss@rcvcat
Run {
allocate auxiliary channel tp1 type “sbt_tape”;
allocate auxiliary channel tp2 type “sbt_tape”;
send ‘NSR_ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default,NSR_CLIENT=miles)’;
duplicate target database for standby dorecover nofilenamecheck;
release channel tp1;
release channel tp2;
}

Above script will return an error
ORA-01830: date format picture ends before converting entire input string
Add a line
Run {
set until time “to_date(‘Dec 11 2007 06:30:09′,’Mon DD YYYY HH24:MI:SS’)”;

Restore Archivelogs which were backed up thru rman

connect rcvcat rmanc/obseq@rcvcat
connect target rmant/obus@live
run {
allocate channel tp1 type “sbt_tape”
parms ‘ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default)’;
allocate channel tp2 type “sbt_tape”
parms ‘ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default)’;
set archivelog destination to ‘f:\oracle\oradata\live’;
restore archivelog from logseq 111094 until logseq 111111;
release channel tp1;
release channel tp2;

Run RMAN script to create log as
rman cmdfile=create_standby.rman msglog=create_standby.log

repA>SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
————
NOARCHIVELOG

repA> alter system set log_archive_dest_1=’location=/opt/oracle/oraarchive/repA/’
scope=spfile;

repA>show parameter log_archive_format

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_format string %t_%s.dbf

repA>alter system set log_archive_format=’repA_%s.arc’ scope=spfile;

System altered.

repA>alter system set log_archive_start=TRUE scope=spfile;

System altered.

repA>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

repA>startup mount
Total System Global Area 320563864 bytes
Fixed Size 735896 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 1060864 bytes
Database mounted.

repA>alter database archivelog;

Database altered.

repA>alter database open;

Database altered.

repA>alter system switch logfile;

System altered.

repA>SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
————
ARCHIVELOG

$ ls -l
total 788
-rw-r—– 1 oracle dba 395264 Sep 25 11:06 repA_25.arc
-rw-r—– 1 oracle dba 2048 Sep 25 11:06 repA_26.arc

I have 2 databases (repA and repB) which are part of multi master replication cluster server A and Server B. I now need to add another Server C with database name of repC. Consider this as a Live system and require a very minimum downtime.

Pre-Requisites :

Step 1> Check there are no pending X’actions at both sites repA,repB

Step 1> Find if there are any Transactions Currently Being Propagated to a Remote Master

repAA> SELECT /*+ ORDERED */ P.XID "Tran Being Propagated",
(MAX(C.CALLNO) + 1) "Number of Calls in Tran",
(P.SEQUENCE/MAX(C.CALLNO) + 1) * 100 "% Processed Calls"
FROM V$REPLPROP P, DEFCALL C
WHERE P.NAME LIKE '%SLAVE%'
AND upper(P.DBLINK) = 'REPB'
AND C.DEFERRED_TRAN_ID = P.XID
GROUP BY P.XID, P.SEQUENCE;

no rows selected

repBB> SELECT /*+ ORDERED */ P.XID "Tran Being Propagated",
(MAX(C.CALLNO) + 1) "Number of Calls in Tran",
(P.SEQUENCE/MAX(C.CALLNO) + 1) * 100 "% Processed Calls"
FROM V$REPLPROP P, DEFCALL C
WHERE P.NAME LIKE '%SLAVE%'
AND upper(P.DBLINK) = 'REPA'
AND C.DEFERRED_TRAN_ID = P.XID
GROUP BY P.XID, P.SEQUENCE;

no rows selected

Step 2> Listing the Number of Deferred Transactions for Each Destination Master Site

repAA> SELECT DBLINK DEST, COUNT(*) TRANS
FROM DEFTRANDEST D
GROUP BY DBLINK;

no rows selected

repBB> SELECT DBLINK DEST, COUNT(*) TRANS
FROM DEFTRANDEST D
GROUP BY DBLINK;

no rows selected

Step 3> Determining the Total Number of Transactions Queued for Propagation

SELECT COUNT(DISTINCT DEFERRED_TRAN_ID) “Transactions Queued” FROM DEFTRANDEST;

Transactions Queued
——————-
0

repAA>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y NORMAL

repBB>>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y NORMAL
repAA> Alter system set job_queue_processes=0 scope=both;

repBB> Alter system set job_queue_processes=0 scope=both;

repAA> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

repBB>>select sname, master, status from sys.dba_repcat;
SNAMEM STATUS
—————————— – ———
SCOTT_REPGY NORMAL
repAA>cd /opt/oracle/oradata
repAA>ls -l
total 4
drwxr-xr-x 2 oracle dba 1024 Sep 18 16:57 repA
drwxr-xr-x 2 oracle dba 1024 Sep 18 17:08 repB

repAA>ls -l
total 2468686
-rw-r—– 1 oracle dba 1871872 Sep 23 16:47 control01.ctl
-rw-r—– 1 oracle dba 1871872 Sep 23 16:47 control02.ctl
-rw-r—– 1 oracle dba 1871872 Sep 23 16:47 control03.ctl
-rw-r–r– 1 oracle dba 20975616 Sep 23 16:47 drsys01.dbf
-rw-Br–r– 1 oracle dba 143527936 Sep 23 16:47 example01.dbf
-rw-r–r– 1 oracle dba 26218496 Sep 23 16:47 indx01.dbf
-rw-r–r– 1 oracle dba 20975616 Sep 23 16:47 odm01.dbf
-rw-r—– 1 oracle dba 104858624 Sep 23 11:08 redo01.log
-rw-r—– 1 oracle dba 104858624 Sep 23 16:47 redo02.log
-rw-r—– 1 oracle dba 104858624 Sep 22 12:19 redo03.log
-rw-r–r– 1 oracle dba 346034176 Sep 23 16:47 system01.dbf
-rw-r–r– 1 oracle dba 99618816 Sep 18 17:10 temp01.dbf
-rw-r–r– 1 oracle dba 10489856 Sep 23 16:47 tools01.dbf
-rw-r–r– 1 oracle dba 209719296 Sep 23 16:47 undotbs01.dbf
-rw-r–r– 1 oracle dba 26218496 Sep 23 16:47 users01.dbf
-rw-r–r– 1 oracle dba 39981056 Sep 23 16:47 xdb01.dbf
Note : The same timestamp for all datafiles is same & means we have consistent backup set.

*** Prepare repCC instance as a new Master to be added into repication Cluster.

repAA>

cp -Rf repAA repCC
repAA>ls -l
total 6
drwxr-xr-x 2 oracle dba 1024 Sep 18 16:57 repAA
drwxr-xr-x 2 oracle dba 1024 Sep 18 17:08 repBB
drwxr-xr-x 2 oracle dba 1024 Sep 23 16:55 repCC

I have copied initrepB.ora init file as initrepC.ora and changed all datafile paths to repCC. I aos edited job_queue_processes and set it to 0;

repAA> alter database backup controlfile to trace;

Database altered.

repCC>export ORACLE_SID=repCC
repCC>set| grep ORACLE_SID
ORACLE_SID= repCC

$ orapwd file=orapwrepCC password=repcc entries=5

repCC> conn / as sysdba
Connected to an idle instance.

repCC> startup nomount pfile=’/opt/oracle/admin/repC/pfile/initrepC.ora’;
ORACLE instance started.

Total System Global Area 320563864 bytes
Fixed Size 735896 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 1060864 bytes

repCC> show parameter job_queue

NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 0

repCC> show parameter db_name

NAME TYPE VALUE
———————————— ———– ——————————
db_name string repAA

repCC> CREATE CONTROLFILE set DATABASE “repAA” NORESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ‘/opt/oracle/oradata/repCC/redo01.log’ SIZE 100M,
GROUP 2 ‘/opt/oracle/oradata/repCC/redo02.log’ SIZE 100M,
GROUP 3 ‘/opt/oracle/oradata/repCC/redo03.log’ SIZE 100M
DATAFILE
‘/opt/oracle/oradata/repCC/system01.dbf’,
‘/opt/oracle/oradata/repCC/undotbs01.dbf’,
‘/opt/oracle/oradata/repCC/drsys01.dbf’,
‘/opt/oracle/oradata/repCC/example01.dbf’,
‘/opt/oracle/oradata/repCC/indx01.dbf’,
‘/opt/oracle/oradata/repCC/odm01.dbf’,
‘/opt/oracle/oradata/repCC/tools01.dbf’,
‘/opt/oracle/oradata/repCC/users01.dbf’,
‘/opt/oracle/oradata/repCC/xdb01.dbf’
CHARACTER SET WE8ISO8859P1;
/

repCC>alter database open NORESETLOGS;

Database altered.

repCC> ALTER TABLESPACE TEMPORARY ADD TEMPFILE ‘/opt/oracle/oradata/repCC/TEMPORARY2.dbf’
SIZE 5242880 REUSE AUTOEXTEND OFF; 2

Tablespace altered.

repCC>select instance from v$thread;

INSTANCE
—————-
repAA

At any cost do not drop replication objects using API "EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'EMP', 'TABLE'); "
Just drop replication group - >
repCC>execute dbms_repcat.drop_master_repgroup('SCOTT_REPG');

Problem : At one event Oracle returned error so I used following API to drop Objects before Group.. Don't do this.
repCC>>execute dbms_repcat.drop_master_repgroup('SCOTT_REPG');
BEGIN dbms_repcat.drop_master_repgroup('SCOTT_REPG'); END;
*
ERROR at line 1:
ORA-23353: deferred RPC queue has entries for object group "PUBLIC"."SCOTT_REPG" so drop all individual objects before dropping group.

repCC>> EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'EMP', 'TABLE');
repCC> EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'DEPT', 'TABLE');
PL/SQL procedure successfully completed.

I dropped objects using above API which were then added to repCC administrative queue. When enabled replication ,it dropped objects at repBB.
Also this method failed to move group from QUIESCED to NORMAL condition which could prove a disaster on live system.
The trick here is dropping replication group by keeping DB name as repAA and then renaming database to repCC. Please make sure jobs are set to 0.
Rename repAA instance after dropping replication group to RepCC

repCC>CREATE CONTROLFILE set DATABASE “repCC” RESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ‘/opt/oracle/oradata/repCC/redo01.log’ SIZE 100M,
GROUP 2 ‘/opt/oracle/oradata/repCC/redo02.log’ SIZE 100M,
GROUP 3 ‘/opt/oracle/oradata/repCC/redo03.log’ SIZE 100M
DATAFILE
‘/opt/oracle/oradata/repCC/system01.dbf’,
‘/opt/oracle/oradata/repCC/undotbs01.dbf’,
‘/opt/oracle/oradata/repCC/drsys01.dbf’,
‘/opt/oracle/oradata/repCC/example01.dbf’,
‘/opt/oracle/oradata/repCC/indx01.dbf’,
‘/opt/oracle/oradata/repCC/odm01.dbf’,
‘/opt/oracle/oradata/repCC/tools01.dbf’,
‘/opt/oracle/oradata/repCC/users01.dbf’,
‘/opt/oracle/oradata/repCC/xdb01.dbf’
CHARACTER SET WE8ISO8859P1;
repCC>>alter database rename global_name to repCC;

Database altered.

6. Add new database instance repCC at $ORACLE_HOME/network/admin/listener.ora

LSNRCTL>reload<br>Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bmhp01.uk.pri.o2.com)(PORT=1521)))
The command completed successfully

7. Create fresh DB links from existing master sites to new one and vice versa. Create new links at new database repC. Note repC does have links pointing to repA.

repCC – Should have links for repAA, repBB
repAA – Should have links for repBB, repCC
repBB – Should have links for repAA, repCC

repCC>>alter database rename global_name to repCC;

Database altered.
repAA>conn sys/repa@repaa as sysdba
Connected.
repBB>create public database link repcc using ‘repcc’;

Database link created.

repBB>conn sys/repb@repbb as sysdba
Connected.
repB>create public database link repcc using ‘repcc’;

Database link created.

repBB>conn sys/repc@repcc as sysdba
Connected.

repC>create public database link repbb using ‘repbb’;
create public database link repb using ‘repb’
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

repCC>alter database rename global_name to repCC;

Database altered.

repCC>create public database link repbb using ‘repbb’;

Database link created.

repAA>connect repadmin/repadmin@repaa

repAA>create database link repcc connect to repadmin identified by repadmin;

repBB>connect repadmin/repadmin@repbb

repBB>create database link repcc connect to repadmin identified by repadmin;

repBB>>select count(*) from tab@repcc;

COUNT(*)
———-
0
repCC>connect repadmin/repadmin@repcc

repCC>create database link repbb connect to repadmin identified by repadmin;

repCC>show parameter db_name

NAME TYPE VALUE
———————————— ———– ——————————
db_name string repCC

repCC>>select sname, master, status from sys.dba_repcat;

no rows selected
repAA>>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y NORMAL

repAA>>BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => ‘SCOTT_REPG’);
END;
/

PL/SQL procedure successfully completed.
repAA>>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y QUIESCED

repAA>>SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;

COUNT(*)
———-
0
repAA>>show user
USER is “REPADMIN”
repAA>> BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ‘scott_repg’,
master => ‘repcc’,
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => ‘ASYNCHRONOUS’);
END;
/
PL/SQL procedure successfully completed.

repAA>> SELECT GNAME, substr(DBLINK,1,20), MASTERDEF FROM DBA_REPSITES WHERE MASTER = ‘Y’ AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = ‘Y’) ORDER BY GNAME;

GNAME SUBSTR(DBLINK,1,20) M
—————————— ——————– –
SCOTT_REPG REPAA.US.ORACLE.COM Y
SCOTT_REPG REPBB.US.ORACLE.COM N
SCOTT_REPG REPCC.US.ORACLE.COM N

repAA>>SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;

COUNT(*)
———-
9

repBB>>SELECT GNAME, substr(DBLINK,1,20), MASTERDEF FROM DBA_REPSITES WHERE MASTER = ‘Y’ AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = ‘Y’) ORDER BY GNAME;

GNAME SUBSTR(DBLINK,1,20) M
—————————— ——————– –
SCOTT_REPG REPBB.US.ORACLE.COM N
SCOTT_REPG REPAA.US.ORACLE.COM Y
SCOTT_REPG REPCC.US.ORACLE.COM N

repBB>>SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;

COUNT(*)
———-
0

repCC>>SELECT GNAME, substr(DBLINK,1,20), MASTERDEF FROM DBA_REPSITES WHERE MASTER = ‘Y’ AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = ‘Y’) ORDER BY GNAME;

GNAME SUBSTR(DBLINK,1,20) M
—————————— ——————– –
SCOTT_REPG REPCC.US.ORACLE.COM N
SCOTT_REPG REPAA.US.ORACLE.COM Y
SCOTT_REPG REPBB.US.ORACLE.COM N

repCC>>SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;

COUNT(*)
———-
6

repBB> select count(*) from REPCAT$_REPOBJECT;

COUNT(*)
———-
6
repAA>select count(*) from REPCAT$_REPOBJECT;

COUNT(*)
———-
6
repCC>select count(*) from REPCAT$_REPOBJECT;
COUNT(*)
———-
0

You should wait until the DBA_REPCATLOG view is empty. This view has temporary information that is cleared after successful execution.

Run do_deferred_repcat_admin Manually to push the Admin Queue. Execute the following SELECT statement in another SQL*Plus session to monitor the DBA_REPCATLOG view:

repAA>SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;

COUNT(*)
———-
9
repBB>SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;

COUNT(*)
———-
0
repCC>SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;

COUNT(*)
———-
6

The easiset method to reoslve admin request is to use grid contorl else Oracle EM. I used it at instances repAA,BB,CC to resolve pending admin requests.

repAA> BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname => ‘scott_repg’);
END;

repAA>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y NORMAL

repBB>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y NORMAL

repCC>>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y QUIESCED

I had to run dbms_defer_sys.push as well as do_deferred_repcat_admin at repAA/CC to get this group ito normal State

repCC>>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y NORMAL

repAA

repBB

repCC

Most important Checks after a new database is added into Replication Clustyer

1. Make sure the target (remote) database does have all replication support created for objects.
select sname,oname,type,STATUS from DBA_REPOBJECT minus
select sname,oname,type,STATUS from DBA_REPOBJECT<at>remote_db;

select object_name from dba_objects where object_name like ‘%$RP%’
minus
select object_name from dba_objects<@>remote_db where object_name like ‘%$RP%’;

If above SQL return any values menas there is a replication support missing at those objects at target database. Please use post

2. If you have used SQLPLUS “RECOVER until Change ” to carry recovery at new master  then DBID would be same for remote database as existing master. This would prove a problem for rman backup.
Please use nid utility to change DBID of target Database

CRS DAEMON FUNCTIONALITY

CRSD: Performs high availability recovery and management operations such as maintaining the OCR and managing application resources.
– Engine for HA operation
– Manages ‘application resources’
– Starts, stops, and fails ‘application resources’ over
– Spawns separate ‘actions’ to start/stop/check application resources
– Maintains configuration profiles in the OCR (Oracle Configuration Repository)
– Stores current known state in the OCR.
– Runs as root
– Is restarted automatically on failure

OCSSD:
– OCSSD is part of RAC and Single Instance with ASM
– Provides access to node membership
– Provides group services
– Provides basic cluster locking
– Integrates with existing vendor clusteware, when present
– Can also runs without integration to vendor clustware
– Runs as Oracle.
– Failure exit causes machine reboot.
— This is a feature to prevent data corruption in event of a split brain.

EVMD: Event manager daemon. This process also starts the racgevt process to manage FAN server callouts.
– Generates events when things happen
– Spawns a permanent child evmlogger
– Evmlogger, on demand, spawns children
– Scans callout directory and invokes callouts.
– Runs as Oracle.
– Restarted automatically on failure

RESOURCE STATUS
Status of the database, all instances and all services

srvctl status database -d ORACLE -v

Status of named instances with their current services.

srvctl status instance -d ORACLE -i RAC01, RAC02 -v

Status of a named services

srvctl status service -d ORACLE -s ERP -v

Status of all nodes supporting database applications

srvctl status node

START RESOURCES
Start the database with all enabled instances

srvctl start database -d ORACLE

Start named instances

srvctl start instance -d ORACLE -i RAC03, RAC04

Start named services. Dependent instances are started as needed

srvctl start service -d ORACLE -s CRM

Start a service at the named instance

srvctl start service -d ORACLE -s CRM -i RAC04

Start node applications

srvctl start nodeapps -n myclust-4

STOP RESOURCES
Stop the database, all instances and all services

srvctl stop database -d ORACLE

Stop named instances, first relocating all existing services

srvctl stop instance -d ORACLE -i RAC03,RAC04

Stop the service

srvctl stop service -d ORACLE -s CRM

Stop the service at the named instances

srvctl stop service -d ORACLE -s CRM -i RAC04

Stop node applications. Note that instances and services also stop

srvctl stop nodeapps -n myclust-4

ADD RESOURCES

Add a new node

srvctl add nodeapps -n myclust-1 -o $ORACLE_HOME –A 139.184.201.1/255.255.255.0/hme0

Add a new database

srvctl add database -d ORACLE -o $ORACLE_HOME

Add named instances to an existing database

srvctl add instance -d ORACLE -i RAC01 -n myclust-1
srvctl add instance -d ORACLE -i RAC02 -n myclust-2
srvctl add instance -d ORACLE -i RAC03 -n myclust-3

Add a service to an existing database with preferred instances (-r) and available instances (-a). Use basic failover to the available instances

srvctl add service -d ORACLE -s STD_BATCH -r RAC01,RAC02 -a RAC03,RAC04

Add a service to an existing database with preferred instances in list one and available instances in list two. Use preconnect at the available instances

srvctl add service -d ORACLE -s STD_BATCH -r RAC01,RAC02 -a RAC03,RAC04 -P PRECONNECT

REMOVE  RESOURCES
Remove the applications for a database.
srvctl remove database -d ORACLE
Remove the applications for named instances of an existing database.
srvctl remove instance -d ORACLE -i RAC03
srvctl remove instance -d ORACLE -i RAC04
Remove the service.
srvctl remove service -d ORACLE -s STD_BATCH
Remove the service from the instances.
srvctl remove service -d ORACLE -s STD_BATCH -i RAC03,RAC04
Remove all node applications from a node.
srvctl remove nodeapps -n myclust-4

MODIFY RESOURCES
Modify an instance to execute on another node.
srvctl modify instance -d ORACLE -n my

Case 1 : EM console is working at Node 1. Node 1 is shutdown , services failover to Node 2 but oemctl doesn’t failover to Node 2

[oracle@wygora02 ~]$ showcrs
HA Resource                                   Target     State
-----------                                   ------     -----
ora.wygora01.ASM1.asm                         ONLINE     OFFLINE
ora.wygora01.LISTENER_WYGORA01.lsnr           ONLINE     OFFLINE
ora.wygora01.gsd                              ONLINE     OFFLINE
ora.wygora01.ons                              ONLINE     OFFLINE
ora.wygora01.vip                              ONLINE     OFFLINE
ora.wygora02.ASM2.asm                         ONLINE     ONLINE on wygora02
ora.wygora02.LISTENER_WYGORA02.lsnr           ONLINE     ONLINE on wygora02
ora.wygora02.gsd                              ONLINE     UNKNOWN on wygora02
ora.wygora02.ons                              ONLINE     UNKNOWN on wygora02
ora.wygora02.vip                              ONLINE     ONLINE on wygora02
ora.wygprod.db                                ONLINE     ONLINE on wygora02
ora.wygprod.wygprod.cs                        ONLINE     ONLINE on wygora02
ora.wygprod.wygprod.wygprod1.srv              ONLINE     OFFLINE
ora.wygprod.wygprod.wygprod2.srv              ONLINE     ONLINE on wygora02
ora.wygprod.wygprod1.inst                     OFFLINE    OFFLINE
ora.wygprod.wygprod2.inst                     ONLINE     ONLINE on wygora02

emctl start dbconsole
Z set to GB-Eire
racle Enterprise Manager 10g Database Control Release 10.2.0.1.0
opyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
ttp://wygora01.wyg-asp.com:1158/em/console/aboutApplication
gent Version     : 10.1.0.4.1
MS Version       : 10.1.0.4.0
rotocol Version  : 10.1.0.2.0
gent Home        : /u01/app/oracle/product/10.2.0/db_1/wygora02_wygprod2
gent binaries    : /u01/app/oracle/product/10.2.0/db_1
gent Process ID  : 26599
arent Process ID : 26554
gent URL         : http://wygora02.wyg-asp.com:3938/emd/main
tarted at        : 2008-03-13 15:58:50
tarted by user   : oracle
ast Reload       : 2008-03-13 15:58:50
ast successful upload                       : 2008-03-13 16:43:03
ast attempted upload                        : 2008-03-13 16:44:54
otal Megabytes of XML files uploaded so far :     6.40
umber of XML files pending upload           :        1
ize of XML files pending upload(MB)         :     0.00
vailable disk space on upload filesystem    :    65.82%
gent is already started. Will restart the agent
his will stop the Oracle Enterprise Manager 10g Database Control process. Continue [y/n] :y
topping Oracle Enterprise Manager 10g Database Control ...
...  Stopped.
gent is not running.
tarting Oracle Enterprise Manager 10g Database Control ..... started.
-----------------------------------------------------------------
ogs are generated in directory /u01/app/oracle/product/10.2.0/db_1/wygora02_wygprod2/sysman/log

  No grid Console running

[oracle@wygora01 ~]$ showcrs
HA Resource                                   Target     State
-----------                                   ------     -----
ora.wygprod.db                                ONLINE     ONLINE on wygora01
ora.wygprod.wygprod.cs                        ONLINE     ONLINE on wygora02
ora.wygprod.wygprod.wygprod1.srv              ONLINE     OFFLINE
ora.wygprod.wygprod.wygprod2.srv              ONLINE     ONLINE on wygora02
ora.wygprod.wygprod1.inst                     OFFLINE    OFFLINE
ora.wygprod.wygprod2.inst                     ONLINE     ONLINE on wygora02

Check out current configuration information

Starting a Database Service
srvctl start service -d <database_name> [-s “<service_name_li$t>” [-1 <in$t_name>]] [-0 <start_options>] [-c <connect_str> | -q]

londonl$ srvctl start service -d RAC -s “SERVICEl,SERVICE2”

Stopping a Database Service
srvctl stop service -d <database_name> [-s “<service_naine_list>” [-1 <inst_name>]] [-C <connect_str> | -q] [-f]

londonl$ srvctl stop service -d RAC -s “SERVICE2,SERVICE3” -f

Checking the Current Database Service Configuration
srvctl config service -d <database_name> [-s <service_name>] [-a] [-S <level>]

londonl$ srvctl config service -d RAC -a
The -a option includes information about the configuration of TAF for the database service

Checking Current Database Service Status
srvctl status service -d <name> -s “<service_name_list>” [-f] [-v] [-S <level>]

londonl$ srvctl status service -d RAC -s “SERVICEl,SERVICE4”

Enabling and Disabling a Database Service
srvctl disable service -d <database_name> -s “<service_name_list>” [-i <in$t_name>]

londonl$ srvctl disable service -d RAC -s SERVICE2 -i RAC4

srvctl enable service -d <database_name> -s “<service_name_list>” [-i <inst_name>]

londonl$ srvctl enable service -d RAC -s SERVICE2 -i RAC4

Removing a Database Service
srvctl remove service -d <database_name> -s <service_name> [ – i <inst_narne>] [-f]

londonl$ srvctl remove service -d RAC -s SERVICE4

Relocating a Database Service
srvctl relocate service -d <database_name> -s <service_name> -i <old_inst_name> -r <new_inst_name> [-f]

londonl$ srvctl relocate service -d RAC -s SERVICES -i RAC2 -t RAC4

Administering Instances

Starting an instance : srvctl start instance -d prod -i “prod1,prod2”

Stopping an instance: srvctl stop instance -d prod -i “prod1,prod2”

Checking the status of an instance : srvctl status instance -d prod – i “prod1,prod2”

Adding a new instance configuration : srvctl add instance -d prod – i prod3 -n prod3_node

Removing an existing instance configuration: srvctl remove instance -d prod3-i prod3_node

Disabling an instance: srvctl disable instance -d prod -i “prod1,prod2”

Enabling an instance : srvctl enable instance -d prod -i “prod1,prod2

Oracle Data Guard is the management, monitoring, and automation software infrastructure that creates, maintains, and monitors one or more standby databases to protect enterprise data from failures, disasters, errors, and corruptions.

The process below is applicable if you have separate Windows/Unix  servers for Datguard primary & Standby Install . For single machine Dataguard Install use my other post.

Oracle Data Guard Overview

Windows 2000 with Oracle 9.2.0.8
Primary Server is called “Primary”
Standby Server is called “Standby”
A working DNS is available for both servers.
Oracle Instance is called DATAGUARD on both Servers
TNS Listener is configured as Primary.world and Standby.world

IMP:
– Configure listener.ora /Tnsnames.ora and add both Primary as well as Standby Database Services
– Always Copy the up -to- date password file from Primary to Standby
– Use Spfile for all configuration settings

Sample Listener file listener.ora
Sample Tnsnames file tnsnames.ora

At Primary Database

1. Check Archivelog mode and force database into Archivelog

SQLPLUS>archive log list;
If this returns “Noarchivelog Mode” then
Add following parameters at initPrimary.ora ….
log_archive_dest_1 = ‘location=f:\Oracle\oradata\Primary MANDATORY REOPEN=5’
log_archive_format = “Primary_%S.ARC”
log_archive_start = true
log_buffer = 1000000
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800

SQL>Startup nomount;
SQL>Alter database archivelog;
Do “alter system switch logfile” and see archive files are dunped at right location

SQL> archive log list;
Standbybase log mode Archive Mode
Automatic archival Enabled
Archive destination f:\Oracle\primary\DATAGUARD
Oldest online log sequence 14
Next log sequence to archive 17
Current log sequence 17

SQL> create spfile from pfile;
File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

At this stage
– COLD backup Primary Database files and ship it to Standby Server.
– At primary create standby control file
SQLPLUS> Alter Database Create Standby Controlfile as ‘D:\Oracle\Oradata\Primary\control.ctl’;
– Copy standby.ctl on to Standby Machine

SQL> startup;
SQL> show parameter spfile;
NAME TYPE VALUE
———————————— ———– ——————————
spfile string %ORACLE_HOME%\StandbyBASE\SPFILE%
ORACLE_SID%.ORA

SQL> connect / as sysdba
Connected.

SQL> alter system set dg_broker_start=TRUE scope=both;
System altered.

C:\Documents and Settings\oracle>dgmgrl
DGMGRL for 32-bit Windows: Version 9.2.0.1.0 – Production.
(c) Copyright 2002 Oracle Corporation. All rights reserved.
Welcome to DGMGRL, type “help” for information.

DGMGRL> connect sys/oracle
Connected.

DGMGRL> show configuration verbose;
Error: ORA-16532: Standby Guard configuration does not exist

SQL> connect / as sysdba
Connected.

SQL> show parameter dg_broker_start
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

DGMGRL> show configuration verbose;
Error: ORA-16532: Standby Guard configuration does not exist

Above Error indicates we need configuration at Data Guard

Configure Data Guard

Create a new DRC and define a primary Site

DGMGRL> create configuration ‘Primary Site’
as primary site is ‘Primary_site’
resource is ‘Primary_db’
hostname is ‘Primary’
instance name is ‘dataguard’
service name is ‘primary’
site is maintained as physical;
Configuration “Primary Site” added with primary site “Primary_site”
Standbybase resource “Primary_db” added.
Create Secondary Site

DGMGRL> create site ‘Standby_site’
resource is ‘Standby_db’
hostname is ‘Standby’
instance name is ‘dataguard’
service name is ‘standby’
site is maintained as physical;
Site “Standby_site” added to configuration.
Standbybase resource “Standby_db” added.
View DRC config

DGMGRL> show configuration verbose;
Configuration
Name: ‘Primary Site ‘
Enabled: ‘no’
Default state: ‘ONLINE’
Intended state: ‘OFFLINE’
Protection Mode: ‘MaxPerformance’
Number of sites: 2
Sites:
Primary Site: Primary_site
Standby Site: Standby_site
Current status for “Primary Site”:
DISABLED
Enable DRC
Please review alrtlog as well as log for DMON process($BDUMP/alrtDMON.log).

DGMGRL> enable configuration
Enabled.

DGMGRL> show configuration;
Configuration ‘Primary Site’ is
Primary Site is ‘Primary_site’
Standby Site is ‘Standby_site’
Current status for “Genesys Newport”:
SUCCESS

DGMGRL> show resource Primary_db
Resource ‘Primary_db’ on site ‘Primary_site’
depends on ‘Primary_site’
Current status for “Primary_db”:
SUCCESS

DGMGRL> show resource Standby_db
Resource ‘Standby_db’ on site ‘Standby_site’
depends on ‘Standby_site’
Current status for “Standby_db”:
SUCCESS

DGMGRL> exit

At Physical Standby Database

– Configure listener.ora /Tnsnames.ora and add both Primary as well as Standby Database Services
– Always Copy up -to- date password file from Primary to Standby
– Use Spfile for all Configuration Settings
=========================================================================

Add following 2 parameters at standby init.ora file
standby_file_management=auto
standby_archive_dest=’f:\oracle\oradata\standby’

C:\Documents and Settings\oracle>oradim -new -sid DATAGUARD -startmode m

C:\Documents and Settings\oracle>set oracle_sid=DATAGUARD

C:\Documents and Settings\oracle>sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 – Production on Wed Feb 14 16:12:33 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 655843824 bytes
Fixed Size 455152 bytes
Variable Size 335544320 bytes
Database Buffers 318767104 bytes
Redo Buffers 1077248 bytes

SQL> alter database mount standby database;
Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination f:\Oracle\oradata\primary
Oldest online log sequence 14
Next log sequence to archive 17
Current log sequence 17

SQL> create spfile from pfile;
File created.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 655843824 bytes
Fixed Size 455152 bytes
Variable Size 335544320 bytes
Database Buffers 318767104 bytes
Redo Buffers 1077248 bytes
ORA-01666: controlfile is for a standby database
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 655843824 bytes
Fixed Size 455152 bytes
Variable Size 335544320 bytes
Database Buffers 318767104 bytes
Redo Buffers 1077248 bytes

SQL> alter database mount standby database;
Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> alter system set dg_broker_start=TRUE scope=both;
System altered.

SQL> show parameter dg_broker_start;
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRU

Sample Listener file listener.ora
Sample Tnsnames file tnsnames.ora

Solution: I tried looking into statspack report but it wouldn’t flag the insert/update/delete activity. Also statspack won’t record some of system activity which may contribute to redo logs.Only option to use was “LOGMINER”.

1. Start TOAD and click on DBA-> logminer

2. Select ftp directory else local files to mine

3. Enter FTP details of server

4. Look into file timestamp and select required files

5. I have selected nmst_0000000700.arc file

6. Click on “options” and Select options to display in TOAD window

7. Now click on Green arrow and logminer will start reading log files

8. Please be patient for some time and you would see a report