Pages

Thursday 9 July 2015

Using RMAN Incremental Backups to Refresh a Standby Database

RMAN enables you to synchronize a standby database with a primary database by creating an incremental backup at the source database that contains all changed blocks since the duplicate was created or last refreshed. You then apply the incremental backup to the standby database, which updates it with all changes.

RMAN uses the selected SCN as the basis for this incremental backup. For all files being backed up, RMAN includes all data blocks that were changed at SCNs greater than or equal to the FROM SCN in the incremental backup.

This example shows the steps required to update a standby database using incremental backups.

1. Create the Incremental Backup

Create the needed incremental backup at the source database(Primary), using BACKUP with the INCREMENTAL FROM SCN clause.

Check the current_scn of standby database to get the SCN.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
9188067064236

[oracle@ora-test13-d1 TEST13]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 7 23:09:47 2015

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

connected to target database: TEST13 (DBID=2603591950)

RMAN> run{
sql "alter session set optimizer_mode=RULE";
allocate channel DISK1 device type disk;
allocate channel DISK2 device type disk;
allocate channel DISK3 device type disk;
allocate channel DISK4 device type disk;
allocate channel DISK5 device type disk;
allocate channel DISK6 device type disk;
allocate channel DISK7 device type disk;
allocate channel DISK8 device type disk;
BACKUP INCREMENTAL FROM SCN 9188067064236 DATABASE
     FORMAT '/oracle/backup/RMAN/TEST13_INCREMENTAL_%U';
}

RMAN> run{
2> sql "alter session set optimizer_mode=RULE";
3> allocate channel DISK1 device type disk;
4> allocate channel DISK2 device type disk;
5> allocate channel DISK3 device type disk;
6> allocate channel DISK4 device type disk;
7> allocate channel DISK5 device type disk;
allocate channel DISK6 device type disk;
8> 9> allocate channel DISK7 device type disk;
10> allocate channel DISK8 device type disk;
11> BACKUP INCREMENTAL FROM SCN 9188067064236 DATABASE
12>      FORMAT '/oracle/backup/RMAN/TEST13_INCREMENTAL_%U';
13> }

sql statement: alter session set optimizer_mode=RULE

allocated channel: DISK1
channel DISK1: sid=66 devtype=DISK

allocated channel: DISK2
channel DISK2: sid=6 devtype=DISK

allocated channel: DISK3
channel DISK3: sid=50 devtype=DISK

allocated channel: DISK4
channel DISK4: sid=16 devtype=DISK

allocated channel: DISK5
channel DISK5: sid=116 devtype=DISK

allocated channel: DISK6
channel DISK6: sid=65 devtype=DISK

allocated channel: DISK7
channel DISK7: sid=42 devtype=DISK

allocated channel: DISK8
channel DISK8: sid=62 devtype=DISK

Starting backup at 07-JUL-15
channel DISK1: starting full datafile backupset
channel DISK1: specifying datafile(s) in backupset
input datafile fno=00018 name=/oracle/oradata/TEST13/tstuser12.dbf
input datafile fno=00024 name=/oracle/oradata/TEST13/tstuser16.dbf
input datafile fno=00025 name=/oracle/oradata/TEST13/tstuser17.dbf
input datafile fno=00036 name=/oracle/oradata/TEST13/tstuser29.dbf

...............
..............
.
.
.
channel DISK5: finished piece 1 at 08-JUL-15
piece handle=/oracle/backup/RMAN/TEST13_INCREMENTAL_2cqbg01g_1_1 tag=TAG20150707T231141 comment=NONE
channel DISK5: backup set complete, elapsed time: 01:48:39
channel DISK4: finished piece 1 at 08-JUL-15
piece handle=/oracle/backup/RMAN/TEST13_INCREMENTAL_2bqbg01g_1_1 tag=TAG20150707T231141 comment=NONE
channel DISK4: backup set complete, elapsed time: 01:54:05
Finished backup at 08-JUL-15
released channel: DISK1
released channel: DISK2
released channel: DISK3
released channel: DISK4
released channel: DISK5
released channel: DISK6
released channel: DISK7
released channel: DISK8

RMAN>
RMAN>


2.Make the Incremental Backup Accessible at the Standby Database

Make the backup pieces containing the incremental backup available in some directory accessible on the system containing the standby database.

3.Catalog the Incremental Backup Files at the Standby Database

Use the RMAN CATALOG command to register the backup sets in the RMAN repository at the standby. With an RMAN client connected to the standby database and the recovery catalog (if you are using catalog db), mount the standby and run the following command:

RMAN> CATALOG START WITH '/oracle/backup/RMAN/';

RMAN> catalog start with '/oracle/backup/RMAN/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/backup/RMAN/

List of Files Unknown to the Database
=====================================
File Name: /oracle/backup/RMAN/TEST13_INCREMENTAL_2eqbg01h_1_1
File Name: /oracle/backup/RMAN/TEST13_INCREMENTAL_28qbg01f_1_1
File Name: /oracle/backup/RMAN/TEST13_INCREMENTAL_2fqbg01h_1_1
File Name: /oracle/backup/RMAN/TEST13_INCREMENTAL_29qbg01f_1_1
File Name: /oracle/backup/RMAN/TEST13_INCREMENTAL_2bqbg01g_1_1
File Name: /oracle/backup/RMAN/TEST13_INCREMENTAL_2dqbg01g_1_1
File Name: /oracle/backup/RMAN/TEST13_INCREMENTAL_2aqbg01f_1_1
File Name: /oracle/backup/RMAN/TEST13_INCREMENTAL_2cqbg01g_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...


4.Apply the Incremental Backup to the Standby Database

Use the RMAN RECOVER command with the NOREDO option to apply the incremental backup to the standby database. All changed blocks captured in the incremental backup are updated at the standby database, bringing it up to date with the primary database. With an RMAN client connected to the standby database, run the following command:

RMAN> RECOVER DATABASE NOREDO;

-bash-4.1$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jul 8 01:46:40 2015

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

connected to target database: TEST13 (DBID=2603591950, not open)

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 08-JUL-15
Starting implicit crosscheck backup at 08-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
.....
.....
..
...

You can now resume managed recovery at the standby. Any redo logs required at the standby with changes since those contained in the incremental are automatically requested from the primary and applied.

Wednesday 22 April 2015

RMAN Duplicate fails with "ORA-01276: Cannot add file" error

NSTANCE 'i2'
  GROUP   5 ( '/oracle/oradata/teststby/group_5.264.789509951', '/oracle/oradata/teststby/group_5.296.789509953' ) SIZE 300 M  REUSE,
  GROUP   6 ( '/oracle/oradata/teststby/group_6.263.789509955', '/oracle/oradata/teststby/group_6.297.789509955' ) SIZE 300 M  REUSE,
  GROUP   7 ( '/oracle/oradata/teststby/group_7.274.789509957', '/oracle/oradata/teststby/group_7.298.789509959' ) SIZE 300 M  REUSE,
  GROUP   8 ( '/oracle/oradata/teststby/group_8.299.789510045', '/oracle/oradata/teststby/group_8.275.789510045' ) SIZE 300 M  REUSE
sql statement: ALTER DATABASE ADD LOGFILE

INSTANCE 'i3'
  GROUP  19 ( '/oracle/oradata/teststby/o1_mf_19_9hcqsyb9_.log' ) SIZE 100 M  REUSE,
  GROUP  20 ( '/oracle/oradata/teststby/o1_mf_20_9hcqsyhf_.log' ) SIZE 100 M  REUSE
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: aux
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/22/2015 02:22:55
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-01276: Cannot add file /oracle/oradata/teststby/o1_mf_19_9hcqsyb9_.log.  File has an Oracle Managed Files file name.

Reason:

OMF parameter set in the database, hence the file name cannot be specified while adding log file

Solution:

Remove following parameters if they are set in parameter file and rerun rman duplicate script

db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_recovery_file_dest


For more refer Oracle Doc ID:1926495.1

Tuesday 24 February 2015

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed while applying patch

While applying 12c patch, it failed with below error

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed while applying patch

[root test.db.com] /oracle/home/oracle$ opatchauto apply /oracle/archivelog/patch/20132450 -ocmrf /oracle/archivelog/patch/unconfig_GI.rsp -nonrolling
OPatch Automation Tool
Copyright (c) 2015, Oracle Corporation.  All rights reserved.

OPatchauto version : 12.1.0.1.5
OUI version        : 12.1.0.2.0
Running from       : /oracle/product/grid/12.1.0

opatchauto log file: /oracle/product/grid/12.1.0/cfgtoollogs/opatchauto/20132450/opatch_gi_2015-02-24_03-25-09_deploy.log

Parameter Validation: Successful

Grid Infrastructure home:
/oracle/product/grid/12.1.0
RAC home(s):
/oracle/product/db/12.1.0

Configuration Validation: Successful

Patch Location: /oracle/archivelog/patch/20132450
Grid Infrastructure Patch(es): 19769473 19769479 19769480 19872484
RAC Patch(es): 19877336 19769479 19769480

Patch Validation: Successful

Stopping RAC (/oracle/product/db/12.1.0) ... Successful

Applying patch(es) to "/oracle/product/db/12.1.0" ...
Command "/oracle/product/db/12.1.0/OPatch/opatch napply -phBaseFile /tmp/OraDB12Home1_patchList -local  -invPtrLoc /oracle/product/grid/12.1.0/oraInst.loc -oh /oracle/product/db/12.1.0 -silent -ocmrf /oracle/archivelog/patch/unconfig_GI.rsp" execution failed:
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Log file Location for the failed command: /oracle/product/db/12.1.0/cfgtoollogs/opatch/opatch2015-02-24_03-26-10AM_1.log

For more details, please refer to the log file "/oracle/product/grid/12.1.0/cfgtoollogs/opatchauto/20132450/opatch_gi_2015-02-24_03-25-09_deploy.debug.log".

Apply Summary:

Following patch(es) failed to be installed:
GI Home: /oracle/product/grid/12.1.0: 19769473, 19769479, 19769480, 19872484
RAC Home: /oracle/product/db/12.1.0: 19877336, 19769479, 19769480

opatchauto failed with error code 2.


Checking the log it shows that

[Feb 24, 2015 3:26:17 AM]    Finish fuser command /sbin/fuser /oracle/product/db/12.1.0/lib/libclntsh.so.12.1 at Tue Feb 24 03:26:17 PST 2015
[Feb 24, 2015 3:26:17 AM]    Start fuser command /sbin/fuser /oracle/product/db/12.1.0/lib/libasmclntsh12.so at Tue Feb 24 03:26:17 PST 2015
[Feb 24, 2015 3:26:17 AM]    Finish fuser command /sbin/fuser /oracle/product/db/12.1.0/lib/libasmclntsh12.so at Tue Feb 24 03:26:17 PST 2015
[Feb 24, 2015 3:26:17 AM]    Following executables are active :
                             /oracle/product/db/12.1.0/lib/libclntsh.so.12.1
[Feb 24, 2015 3:26:17 AM]    Prerequisite check "CheckActiveFilesAndExecutables" failed.
                             The details are:


                             Following executables are active :
                             /oracle/product/db/12.1.0/lib/libclntsh.so.12.1
[Feb 24, 2015 3:26:17 AM]    OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Feb 24, 2015 3:26:17 AM]    Finishing UtilSession at Tue Feb 24 03:26:17 PST 2015
[Feb 24, 2015 3:26:17 AM]    Log file location: /oracle/product/db/12.1.0/cfgtoollogs/opatch/opatch2015-02-24_03-26-10AM_1.log
[Feb 24, 2015 3:26:17 AM]    Stack Description: java.lang.RuntimeException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Feb 24, 2015 3:26:17 AM]    StackTrace: oracle.opatch.OPatchSessionHelper.runApplyPrereqs(OPatchSessionHelper.java:6516)
[Feb 24, 2015 3:26:17 AM]    StackTrace: oracle.opatch.opatchutil.NApply.process(NApply.java:1617)


Reason:

Some one is accessing Oracle library

Solution:

Using fuser, find who is accessing and kill it.

[root test.db.com] /oracle/home/oracle$ fuser  /oracle/product/db/12.1.0/lib/libclntsh.so.12.1
/oracle/product/db/12.1.0/lib/libclntsh.so.12.1: 112053m


[root test.db.com] /oracle/home/oracle$ ps -ef|grep 112053
oracle   112053 111991  0 Feb23 pts/0    00:00:00 sqlplus

[root test.db.com] /oracle/home/oracle$ kill -9 112053