Insights and Strategies for DBA

Wednesday, 25 February 2015

MASTER NODE IN RAC 11G R2

In this post, I will demonstrate three methods to identify the oracle clusterware’s master node. Pls note that clusterware master is different from Resource master in oracle database instance. To know about how to find the resource master,

Importance of master node in a cluster:
- Master node has the least Node-id in the cluster. Node-ids are assigned to the nodes in the same order as the nodes join the cluster. Hence, normally the node which joins the cluster first is the master node.

- CRSd process on the Master node is responsible to initiate the OCR backup as per the backup policy

- Master node is also responsible to sync OCR cache across the nodes

- CRSd process oth the master node reads from and writes to OCR on disk

- In case of node eviction, The cluster is divided into two sub-clusters. The sub-cluster containing fewer no. of nodes is evicetd. But, in case both the sub-clusters have same no. of nodes, the sub-cluster having the master node survives whereas the other sub-cluster is evicted.

Oracle ClusterWare master’s information can be found

- by scanning ocssd logs from various nodes

- by scanning crsd logs from various nodes.

- by identifying the node which takes the backup of the OCR.

If master node gets evicted/rebooted, another node becomes the master.

I have a 3 node setup. I check the ocssd logs on the 3 nodes for the string ‘master node’ and note that node 3 is the master node.

[grid@host01 root]$ cat $ORACLE_HOME/log/host01/cssd/ocssd.log |grep ‘master node’ |tail -1

2012-11-23 10:14:36.949: [ CSSD][2778696592]clssgmCMReconfig: reconfiguration successful, incarnation 248954981 with 3 nodes, local node number 1, master node number 3

[root@host02 cssd]# cat $ORACLE_HOME/log/host02/cssd/ocssd.log |grep ‘master node’ |tail -1

2012-11-23 10:14:36.953: [ CSSD][778696592]clssgmCMReconfig: reconfiguration successful, incarnation 248954981 with 3 nodes, local node number 2, master node number 3

[root@host03 ~]# cat $ORACLE_HOME/log/host03/cssd/ocssd.log |grep ‘master node’ |tail -1

2012-11-23 10:14:37.001: [ CSSD][778700688]clssgmCMReconfig: reconfiguration successful, incarnation 248954981 with 3 nodes, local node number 3, master node number 3

If I take the OCR backup right now, it will be taken by node3 (master node).

[root@host02 cssd]# ocrconfig -manualbackup

host03 2012/11/24 09:54:48 /u01/app/11.2.0/grid/cdata/cluster01/backup_20121124_095448.ocr

Let us check crsd logs of various nodes, looking for the string OCR MASTER.Note that node3 is the master node presently.

cat /u01/app/11.2.0/grid/log/host01/crsd.log| grep -i 'ocr master'

[grid@host01 crsd]$ cat /u01/app/11.2.0/grid/log/host01/crsd/crsd.log |grep ‘OCR MASTER’ | tail -1

2012-11-23 10:15:01.403: [ OCRMAS][2877356944]th_master: NEW OCR MASTER IS 3

[root@host02 crsd]# cat /u01/app/11.2.0/grid/log/host02/crsd/crsd.log |grep ‘OCR MASTER’ | tail -1

2012-11-23 10:15:03.561: [ OCRMAS][876976016]th_master: NEW OCR MASTER IS 3

[root@host03 crsd]# cat /u01/app/11.2.0/grid/log/host03/crsd/crsd.log |grep ‘OCR MASTER’ | tail -3

2012-11-23 10:11:18.499: [ OCRMAS][877467536]th_master:13: I AM THE NEW OCR MASTER at incar 44. Node Number 3

Let me reboot node3 and check which node is assigned the mastership now.

[root@host03 ~]# init 6

check the ocssd logs on the remaining two nodes (node1 and node2) for the string ‘master node’ and note that node 1 is the master node.

[grid@host01 root]$ cat $ORACLE_HOME/log/host01/cssd/ocssd.log |grep ‘master node’ |tail -1

2012-11-24 10:09:23.522: [ CSSD][2778696592]clssgmCMReconfig: reconfiguration successful, incarnation 248954982 with 2 nodes, local node number 1, master node number 1

[root@host02 cssd]# cat $ORACLE_HOME/log/host02/cssd/ocssd.log |grep ‘master node’ |tail -1

2012-11-24 10:09:23.502: [ CSSD][778696592]clssgmCMReconfig: reconfiguration successful, incarnation 248954982 with 2 nodes, local node number 2, master node number 1

As can be seen from ocssd logs of the remaining two nodes, node1 has become the master now.

Now If I take the OCR backup, it is taken by node1 while earlier backups were taken by node3 which was the then master.

[root@host02 cssd]# ocrconfig -manualbackup

host01 2012/11/24 10:12:29 /u01/app/11.2.0/grid/cdata/cluster01/backup_20121124_101229.ocr

host03 2012/11/24 09:54:48 /u01/app/11.2.0/grid/cdata/cluster01/backup_20121124_095448.ocr

Let us check crsd logs of various nodes, looking for the string OCR MASTER

[grid@host01 crsd]$ cat /u01/app/11.2.0/grid/log/host01/crsd/crsd.log |grep ‘OCR MASTER’ | tail -1

2012-11-24 10:08:45.884: [ OCRMAS][877356944]th_master:13: I AM THE NEW OCR MASTER at incar 47. Node Number 1

[root@host02 crsd]# cat /u01/app/11.2.0/grid/log/host02/crsd/crsd.log |grep ‘OCR MASTER’ | tail -1

2012-11-24 10:08:45.364: [ OCRMAS][876976016]th_master: NEW OCR MASTER IS 1

[root@host03 crsd]# cat /u01/app/11.2.0/grid/log/host03/crsd/crsd.log |grep ‘OCR MASTER’ | tail -1

2012-11-24 10:12:20.282: [ OCRMAS][877422480]th_master: NEW OCR MASTER IS 1

2^nd

$ocrconfig –showbackup

3^rd

select *from gv$gcs_resource;

How to set Compatible Parameter in Oracle ?

backward compatibility with an earlier release. This is helpful if it becomes necessary to revert to the earlier release.

This parameter specifies the release with which Oracle must maintain compatibility. It allows you to take advantage of the maintenance improvements of a new release immediately in your production systems without testing the new functionality in your environment. Some features of the release may be restricted.

When using a standby database, this parameter must have the same value on both the primary and standby databases.

RAC Multiple instances must have the same value.

like Ora-00600 else some other errors.

--How this is possible a bug which is fixed in lower version still exists in higher version ?

The reason was very simple, the compatible parameter of database was set to 11.2.0.2, That is the reason online dba was still seeing this bug.

Command to check compatible parameter

SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

Steps to change compatible parameter of database

After the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for Oracle Database.

1. Change parameter value

SQL> ALTER SYSTEM SET COMPATIBLE = '11.0.0' SCOPE=SPFILE;

2. Shutdown database

SQL> SHUTDOWN IMMEDIATE

3. Start database

SQL> Startup

4. Cross check for parameter for new value

SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

Note: Please take your database backup before changing this parameter, because after changing this parameter database will become incompatible with earlier releases of Oracle Database.

Oracle Database Release DefaultValue MinimumValue MaximumValue

Oracle8i release 8.1.7 8.0.0 8.0.0.0.0 8.1.7.x.x

Oracle9i release 9.0.1 8.1.0 8.1.0.0.0 9.0.1.x.x

Oracle9i release 9.2 8.1.0 8.1.0.0.0 9.2.0.x.x

Oracle Database 10g release 10.1 10.0.0 9.2.0.0.0 10.1.0.x.x

Oracle Database 10g release 10.2 10.2.0 9.2.0.0.0 10.2.0.x.x

Oracle 11g Release 11.1 11.0.0 10.0.0.0.0 11.1.0.x.x

Oracle 11g Release 11.2 11.0.0 10.0.0.0.0 11.2.0.x.x

Wednesday, 18 February 2015

SQL FLOW

----Oracle Transaction.

1. User requests a connection to oracle

2. A new dedicated server process is started for the user

3. User executes a statement to insert data in to a table

4. Oracle checks the users privileges, it first checks the library cache (cache hit) for the information and if not found retrieve it from disk.

5. Check to see if the SQL statement has been parsed before (library cache) if it has then this is called a soft parse, otherwise the code has to be compiled a hard parse.

6. Oracle creates a private SQL area in the users session's PGA

7. Oracle checks to see if the data is in the buffer cache, otherwise perform a read from the data file

8. Oracle will then apply row-level locks where needed to prevent others changing the row (select statements are still allowed on the row)

9. Oracle then writes the change vectors to the redo log buffer

10. Oracle then modifies the row in the data buffer cache

11. The user commits the transaction making it permanent, the row-level locks are released

12. The log writer process immediately writes out the changed data in the redo log buffers to the online redo log files, in other words the change is now recoverable.

13. Oracle informs the user process that the transaction was completed successfully

14. It may be sometime before the data buffer cache writes out the change to the data files.

Note: if the users transaction was an update then the before update row would have been written to the undo buffer cache, this would be used if the user rollsback the change of if another user run's a select on that data before the new update was committed.

TAF / FAN / FCF/ ONS ORACLE RAC

What the differences and relationship among TAF/FAN/FCF/ONS?

1 Definition

1) TAF
a feature of Oracle Net Services for OCI8 clients. TAF is transparent application failover which will move a session to a backup connection if the session fails. With Oracle 10g Release 2, you can define the TAF policy on the service using dbms_service package. It will only work with OCI clients. It will only move the session and if the parameter is set, it will failover the select statement. For insert, update or delete transactions, the application must be TAF aware and roll back the transaction. YES, you should enable FCF on your OCI client when you use TAF, it will make the failover faster.

Note: TAF will not work with JDBC thin.

2) FAN
FAN is a feature of Oracle RAC which stands for Fast Application Notification. This allows the database to notify the client of any change (Node up/down, instance up/down, database up/down). For integrated clients, inflight transactions are interrupted and an error message is returned. Inactive connections are terminated.
FCF is the client feature for Oracle Clients that have integrated with FAN to provide fast failover for connections. Oracle JDBC Implicit Connection Cache, Oracle Data Provider for .NET (ODP.NET) and Oracle Call Interface are all integrated clients which provide the Fast Connection Failover feature.

3) FCF
FCF is a feature of Oracle clients that are integrated to receive FAN events and abort inflight transactions, clean up connections when a down event is received as well as create new connections when a up event is received. Tomcat or JBOSS can take advantage of FCF if the Oracle connection pool is used underneath. This can be either UCP (Universal Connection Pool for JAVA) or ICC (JDBC Implicit Connection Cache). UCP is recommended as ICC will be deprecated in a future release.

4) ONS

http://forums.oracle.com/forums/thread.jspa?messageID=3566976

ONS is part of the clusterware and is used to propagate messages both between nodes and to application-tiers

ONS is the foundation for FAN upon which is built FCF.

RAC uses FAN to publish configuration changes and LBA events. Applications can react as those published events in two way :
- by using ONS api (you need to program it)
- by using FCF (automatic by using JDBC implicit connection cache on the application server)

you can also respond to FAN event by using server-side callout but this on the server side (as their name suggests it)

Rodrigo Mufalani

"ONS send/receive messages about failures automatically. It is a daemon process that runs on each node notifying status from components of database, nodeapps.

If listener process fails on node1 his failure is notified by EVMD, then local ONS communicates the failure to remote ONS in remote nodes, then local ONS on these nodes notifying all aplications about failure that occurred on node1."

2 Relationship
ONS --> FAN --> FCF
ONS -> send/receive messages on local and remote nodes.
FAN -> uses ONS to notify other processes about changes in configuration of service level
FCF -> uses FAN information working with conection pools JAVA and others.
http://forums.oracle.com/forums/thread.jspa?messageID=3566976

3 To use TAF/FAN/FCF/ONS, do you need to configure/install in server or client side?

4 Does ONS automatically send messages ?
or is there any settings to be done ?
Does ONS only broadcast msgs ?

http://forums.oracle.com/forums/thread.jspa?messageID=3566976

Rodrigo Mufalani

"ONS send/receive messages about failures automatically. It is a daemon process that runs on each node notifying status from components of database, nodeapps.If listener process fails on node1 his failure is notified by EVMD, then local ONS communicates the failure to remote ONS in remote nodes, then local ONS on these nodes notifying all aplications about failure that occurred on node1."

5 Are TAF and FAN mutually exclusive? or if TAF and FCF are mutually exclusive?

No. You can use both TAF and FAN at the same time, or both TAF and FCF, it depends on what you want to achieve with it.

6 TAF Basic Configuration with FAN: Example

Oracle Database 10g Release 2 supports server-side TAF with FAN.

To use server-side TAF:

1) create and start your service using SRVCTL

$ srvctl add service -d RACDB -s AP -r I1,I2

$ srvctl start service -d RACDB -s AP

2) configure TAF in the RDBMS by using the DBMS_SERVICE package.

execute dbms_service.modify_service ( ,-

service_name => 'AP' ,-

aq_ha_notifications => true ,-

failover_method => dbms_service.failover_method_basic ,-

failover_type => dbms_service.failover_type_session ,-

failover_retries => 180, failover_delay => 5 ,-

clb_goal => dbms_service.clb_goal_long);

3) When done, make sure that you define a TNS entry

for it in your tnsnames.ora file.

AP =

(DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=ON)

(ADDRESS=(PROTOCOL=TCP)(HOST=N1VIP)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=N2VIP)(PORT=1521))

(CONNECT_DATA = (SERVICE_NAME = AP)))

Note that this TNS name does not need to specify TAF parameters

as with the previous slide.

7 TAF Basic Configuration without FAN: Example

Before using TAF, it is recommended that you create and start a service that is used during

connections.

By doing so, you benefit from the integration of TAF and services. When you want to

use BASIC TAF with a service, you should have the -P BASIC option when creating the service.

After the service is created, you simply start it on your database.

$ srvctl add service -d RACDB -s AP -r I1,I2 -P BASIC

$ srvctl start service -d RACDB -s AP

Then, your application needs to connect to the service by using a connection descriptor similar to the

one shown in the slide. The FAILOVER_MODE parameter must be included in the CONNECT_DATA

section of your connection descriptor.

AP =

(DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=ON)

(ADDRESS=(PROTOCOL=TCP)(HOST=N1VIP)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=N2VIP)(PORT=1521))

(CONNECT_DATA =

(SERVICE_NAME = AP)

(FAILOVER_MODE =

(TYPE=SESSION)

(METHOD=BASIC)

(RETRIES=180)

(DELAY=5))))

Note: If using TAF, do not set the GLOBAL_DBNAME parameter in your listener.ora file.

8 Metalink notes

--Understanding Transparent Application Failover (TAF) and Fast Connection Failover (FCF) [ID 334471.1]

--How To Verify And Test Fast Connection Failover (FCF) Setup From a JDBC Thin Client Against a 10.2.x RAC Cluster [ID 433827.1]

--Fast Connection Failover (FCF) Test Client Using 11g JDBC Driver and 11g RAC Cluster [ID 566573.1]

--Questions about how ONS and FCF work with JDBC [ID 752595.1]

--Configuring ONS For Fast Connection Failover

--How To Implement (Fast Connection Failover) FCF Using JDBC driver ? [ID 414199.1]

--How to Implement Load Balancing With RAC Configured System Using JDBC [ID 247135.1]

Monday, 9 February 2015

Duplicate Record Removes

----- Total number of duplicate records.

select A.vehregno,count(A.vehregno)
from scanlog A
where exists ( select *
from scanlog B
where A.rowid <> B.rowid and
B.VEHREGNO = A.VEHREGNO and
B.SCANDATE = A.SCANDATE and
B.FILENAME=A.FILENAME )
group by A.VEHREGNO having count(A.VEHREGNO) > 1 order by count(A.VEHREGNO);

----- Total number of records after eliminating Duplicate record.

SELECT count(1)
FROM scanlog A
WHERE ROWID IN (SELECT rid
FROM (SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY vehregno, scandate, filename ORDER BY ROWID) rn
FROM scanlog)
WHERE rn=1) order by vehregno;

----- Create Table After Eliminating the duplicate record.

CREATE TABLE scanlog NOLOGGING
AS
SELECT A.*
FROM scanlog A
WHERE ROWID IN (SELECT rid
FROM (SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY vehregno, scandate, filename ORDER BY ROWID) rn
FROM scanlog)
WHERE rn=1);

----- Find Duplicate Record.
select * from
(select d.* , count(*) over(partition by binwardno) cnt
from biolic d )
where cnt > 1;

Thursday, 29 January 2015

Resolving Gaps in Data Guard Apply Using Incremental RMAN BAckup

Recently, we had a glitch on a Data Guard (physical standby database) on infrastructure.
This is not a critical database; so the monitoring was relatively lax. And that being done by an outsourcer does not help it either.
In any case, the laxness resulted in a failure remaining undetected for quite some time and it was eventually discovered only when the
customer complained. This standby database is usually opened for read only access from time to time.
This time, however, the customer saw that the data was significantly out of sync with primary and raised a red flag. Unfortunately,
at this time it had become a rather political issue.

Since the DBA in charge couldn’t resolve the problem, I was called in. In this post, I will describe the issue and how it was resolved. In summary,
there are two parts of the problem:

(1) What happened
(2) How to fix it

What Happened

Let’s look at the first question – what caused the standby to lag behind. First, I looked for the current SCN numbers of the primary and standby databases.

On the primary:
SQL> col current_scn format 999999999999999
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447102

On the standby:
SQL> col current_scn format 999999999999999
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1301571

Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lag behind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is lagging in the terms of wall clock. To know that I used the scn_to_timestamp function to translate the SCN to a timestamp:

SQL> select scn_to_timestamp(1447102) from dual;

SCN_TO_TIMESTAMP(1447102)
-------------------------------
18-DEC-09 08.54.28.000000000 AM

I ran the same query to know the timestamp associated with the SCN of the standby database as well (note, I ran it on the primary database, though; since it will fail in the standby in a mounted mode):

SQL> select scn_to_timestamp(1301571) from dual;

SCN_TO_TIMESTAMP(1301571)
-------------------------------
15-DEC-09 07.19.27.000000000 PM

This shows that the standby is two and half days lagging! The data at this point is not just stale; it must be rotten.

The next question is why it would be lagging so far back in the past. This is a 10.2 database where FAL server should automatically resolved any gaps in archived logs. Something must have happened that caused the FAL (fetch archived log) process to fail. To get that answer, first, I checked the alert log of the standby instance. I found these lines that showed the issue clearly:

…
Fri Dec 18 06:12:26 2009
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 700
Fetching gap sequence in thread 1, gap sequence 700-700
… …
Fri Dec 18 06:13:27 2009
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 700-700
DBID 846390698 branch 697108460
FAL[client]: All defined FAL servers have been attempted.
…

Going back in the alert log, I found these lines:

Tue Dec 15 17:16:15 2009
Fetching gap sequence in thread 1, gap sequence 700-700
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:15 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Tue Dec 15 17:16:45 2009
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:45 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

This clearly showed the issue. On December 15th at 17:16:15, the Managed Recovery Process encountered an error while receiving the log information from the primary. The error was ORA-12514 “TNS:listener does not currently know of service requested in connect descriptor”.
This is usually the case when the TNS connect string is incorrectly specified. The primary is called DEL1 and there is a connect string called DEL1 in the standby server.

The connect string works well. Actually, right now there is no issue with the standby getting the archived logs; so there connect string is fine - now.
The standby is receiving log information from the primary. There must have been some temporary hiccups causing that specific archived log not to
travel to the standby. If that log was somehow skipped (could be an intermittent problem), then it should have been picked by the FAL process
later on; but that never happened. Since the sequence# 700 was not applied, none of the logs received later – 701, 702 and so on – were applied either.
This has caused the standby to lag behind since that time.

So, the fundamental question was why FAL did not fetch the archived log sequence# 700 from the primary. To get to that, I looked into the alert
log of the primary instance. The following lines were of interest:

…
Tue Dec 15 19:19:58 2009
Thread 1 advanced to log sequence 701 (LGWR switch)
Current log# 2 seq# 701 mem# 0: /u01/oradata/DEL1/onlinelog/o1_mf_2_5bhbkg92_.log
Tue Dec 15 19:20:29 2009Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Dec 15 19:20:29 2009
FAL[server, ARC1]: FAL archive failed, see trace file.
Tue Dec 15 19:20:29 2009
Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed.
Archiver continuing
Tue Dec 15 19:20:29 2009
ORACLE Instance DEL1 - Archival Error. Archiver continuing.
…

These lines showed everything clearly. The issue was:

ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

The archived log simply was not available. The process could not see the file and couldn’t get it across to the standby site.

Upon further investigation I found that the DBA actually removed the archived logs to make some room in the filesystem without
realizing that his action has removed the most current one which was yet to be transmitted to the remote site. The mystery surrounding why the
FAL did not get that log was finally cleared.

==========
Solution
==========
Now that I know the cause, the focus was now on the resolution. If the archived log sequence# 700 was available on the primary, I could have easily copied it over to the standby, registered the log file and let the managed recovery process pick it up. But unfortunately, the file was gone and I couldn’t just recreate the file. Until that logfile was applied, the recovery will not move forward. So, what are my options?

One option is of course to recreate the standby - possible one but not technically feasible considering the time required. The other option is to apply the incremental backup of primary from that SCN number. That’s the key – the backup must be from a specific SCN number. I have described the process since it is not very obvious. The following shows the step by step approach for resolving this problem. I have shown where the actions must be performed – [Standby] or [Primary].

1. [Standby] Stop the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.

2. [Standby] Shutdown the standby database

3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:

------------------------------------------- Rman Backup Status ------------------------------------------

SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS;

select
sid,
start_time,
totalwork
sofar,
(sofar/totalwork) * 100 pct_done
from
   v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';

select sid, spid, client_info,
   event, seconds_in_wait,
   p1, p2, p3
from v$process p, v$session s
where p.addr = s.paddr
and client_info like 'rman channel=%';
-----------------------------------------------------------------------------------------------------------

RMAN> run {
2> allocate channel c1 type disk format '/u01/oraback/%U.rmb';
3> backup incremental from scn 1301571 database;
4> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=139 devtype=DISK

Starting backup at 18-DEC-09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oradata/DEL1/datafile/o1_mf_system_5bhbh59c_.dbf
… …
piece handle=/u01/oraback/06l16u1q_1_1.rmb tag=TAG20091218T083619 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:06
Finished backup at 18-DEC-09
released channel: c1

4. [Primary] On the primary, create a new standby controlfile:

SQL> alter database create standby controlfile as '/u01/oraback/DEL1_standby.ctl';

Database altered.

5. [Primary] Copy these files to standby host:

oracle@oradba1 /u01/oraback# scp *.rmb *.ctl oracle@oradba2:/u01/oraback
oracle@oradba2's password:
06l16u1q_1_1.rmb 100% 43MB 10.7MB/s 00:04
DEL1_standby.ctl 100% 43MB 10.7MB/s 00:04

6. [Standby] Bring up the instance in nomount mode:

SQL> startup nomount

7. [Standby] Check the location of the controlfile:

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/oradata/standby_cntfile.ctl

8. [Standby] Replace the controlfile with the one you just created in primary.

9. $ cp /u01/oraback/DEL1_standby.ctl /u01/oradata/standby_cntfile.ctl

10.[Standby] Mount the standby database:

SQL> alter database mount standby database;

11.[Standby] RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:

$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 18 06:44:25 2009

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

connected to target database: DEL1 (DBID=846390698, not open)
RMAN> catalog start with '/u01/oraback';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/oraback

List of Files Unknown to the Database
=====================================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb

12.Recover these files:

RMAN> recover database;

Starting recover at 18-DEC-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/DEL2/datafile/o1_mf_system_5lptww3f_.dbf
...…
channel ORA_DISK_1: reading from backup piece /u01/oraback/05l16u03_1_1.rmb
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oraback/05l16u03_1_1.rmb tag=TAG20091218T083619
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

starting media recovery

archive log thread 1 sequence 8012 is already on disk as file /u01/oradata/1_8012_697108460.dbf
archive log thread 1 sequence 8013 is already on disk as file /u01/oradata/1_8013_697108460.dbf
… …

13. After some time, the recovery fails with the message:

archive log filename=/u01/oradata/1_8008_697108460.dbf thread=1 sequence=8009
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2009 06:53:02
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/oradata/1_8008_697108460.dbf'
ORA-00310: archived log contains sequence 8008; sequence 8009 required
ORA-00334: archived log: '/u01/oradata/1_8008_697108460.dbf'

ELSE

----I'm Getting this error
starting media recovery
unable to find archive log
archive log thread=1 sequence=37391
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/05/2016 10:33:44
RMAN-06054: media recovery requesting unknown log: thread 1 seq 8008 lowscn 2224474915633

RMAN> shutdown immediate

This happens because we have come to the last of the archived logs. The expected archived log with sequence# 8008 has not been generated yet.

14.At this point exit RMAN and start managed recovery process:

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

Database altered.

15.Check the SCN’s in primary and standby:

[Standby] SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447474

[Primary] SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447478

Now they are very close to each other. The standby has now caught up.

Use Roll Forward Feature12c R2 onwards:

Roll Forward Physical Standby Using RMAN Incremental Backup in Single Command (Doc ID 2431311.1)

Labels