Correct SCN on Standby Database

There are many documents that explains how to resolve a gap on the standby database using rman incremental backups. It is pretty useful when you do not want to ship and apply all the missing archive logs to refresh standby database. Recently I had such problem on a new dataguard. My problem was not the size of the missing archive logs that I need to ship and apply. The archive logs were missing on the primary system. Instead of restoring the archivelogs from the tape I prefer to use incremental backup from the primary database.
But It did not work for me. Later I realized that I was calculating wrong scn on standby. Restore time of the datafiles on standby was 4 days before the standby controlfile that I use. When I use the “select current_scn from v$database;” to calculate the scn It gave me the scn on the control file which was pretty close to the primary. But scn on the datafiles was 4 days ago. When I used the scn on the datafiles I was successfully restored the incremental backup and my problem was resolved.

MOS doc :
Steps to perform for Rolling forward a standby database using RMAN Incremental Backup. (Doc ID 836986.1)

How it works :
1 – Determine latest scn on the dataguard. this must reflect the scn on the datafiles so it is better to use x$kcvfh table (take the minimum SCN from the below queries)

		
SQL> select current_scn from v$database;

-- If no READ ONLY Tablespaces/datafiles in database use 
SQL> select min(fhscn) from x$kcvfh;

-- If READ ONLY tablespaced/datafiles in database use
SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d
	  where f.hxfil =d.file#
		and d.enabled != 'READ ONLY' ;

2 - Take incremental backup on the primary database from that scn

	
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 750983 DATABASE  FORMAT '/primary_archive/incr_standby_%U';

3 - Ship the incremental backup to the standby location (scp , ftp or aircargo :) whatever you like )

4 - Catalog the backup on the standby database

RMAN> CATALOG START WITH '/standby_archive/incrback/';

5 - Stop the recovery on the standby database

RMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

6 - Restore the standby database using the cataloged incremental backup

RMAN> RECOVER DATABASE NOREDO;	 

Monitor dataguard apply rate

BASH SCRIPT
Monitoring the apply rate of the dataguard is pretty important. If you have a slow disk system or you had a batch update on the primary system then the dataguard might not catch up with the primary database. On the dataguard database you can use the v$recovery_progress view for this purpose. I wrote a bash script so that I can monitor the apply rate, last apply rate on the system. On the other hand I wanted to monitor the system resources so that I use the “top” command.

#!/bin/bash
date;
hostname;
export ORACLE_HOME=/oracle/product/11.2.0
export ORACLE_SID=TESTDB

echo "*******************************************************************************************************************"
sqlplus -S / as sysdba  <<EOF
 set lines 200
 set feedback off
 col comments for a20
 col start_time for a30
 col item for a30
 col units for a15
 col last_apply_time for a30
 select to_char(R.START_TIME,'DD.MON.YYYY HH24:MI')start_time , 
        R.ITEM, R.UNITS, R.SOFAR , 
        to_char(R.TIMESTAMP,'DD.MON.YYYY HH24:MI:SS') last_apply_time
    from v\$recovery_progress R
    where start_time = (select max(start_time) from v\$recovery_progress) order by start_time, item;
 select process, status, thread#, sequence#, block#, blocks from v\$managed_standby;	
EOF

echo ""
echo "*******************************************************************************************************************"
top -cbn 1 | head -n 40

USAGE
It is pretty nice to use watch command on linux for continuous update and monitoring of the screen. It refreshes the screen every n minutes.

watch -n 5 ./guard_apply_takip.sh 

OUTPUT:

Every 5.0s: ./guard_apply_takip.sh                                                                                                                          Sat Apr  5 13:22:27 2014

Sat Apr  5 13:22:27 EEST 2014
*******************************************************************************************************************

START_TIME        ITEM                             UNITS                SOFAR LAST_APPLY_TIME
----------------- -------------------------------- --------------- ---------- --------------------
05.APR.2014 11:56 Active Apply Rate                KB/sec               58062
05.APR.2014 11:56 Active Time                      Seconds               4998
05.APR.2014 11:56 Apply Time per Log               Seconds                  9
05.APR.2014 11:56 Average Apply Rate               KB/sec               43143
05.APR.2014 11:56 Checkpoint Time per Log          Seconds                  2
05.APR.2014 11:56 Elapsed Time                     Seconds               5137
05.APR.2014 11:56 Last Applied Redo                SCN+Time                 0 04.APR.2014 22:57:54
05.APR.2014 11:56 Log Files                        Files                  436
05.APR.2014 11:56 Maximum Apply Rate               KB/sec               84430
05.APR.2014 11:56 Redo Applied                     Megabytes           216436

*******************************************************************************************************************
top - 13:22:28 up 3 days,  1:20,  3 users,  load average: 4.47, 3.69, 3.55
Tasks: 502 total,   1 running, 501 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.8%us,  0.3%sy,  0.0%ni, 84.1%id, 14.6%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  148443976k total, 101675228k used, 46768748k free,   379152k buffers
Swap: 141590520k total,        0k used, 141590520k free,  1974632k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
18689 root      16   0  761m 130m  79m S 55.7  0.1 101:34.00 /opt/simpana/Base/cvd
23924 oracle    15   0 90.3g  67m  27m S 50.0  0.0  12:14.53 ora_pr00_TESTDB
23971 oracle    15   0 90.2g  24m  18m S 42.3  0.0   3:13.71 ora_pr08_TESTDB
23975 oracle    15   0 90.2g  22m  16m S 30.7  0.0   3:17.01 ora_pr09_TESTDB
23983 oracle    15   0 90.2g  24m  17m S 21.1  0.0   3:18.59 ora_pr0b_TESTDB
23943 oracle    15   0 90.2g  24m  18m S 13.4  0.0   3:08.81 ora_pr01_TESTDB
23967 oracle    15   0 90.2g  22m  16m S 13.4  0.0   3:09.19 ora_pr07_TESTDB
23987 oracle    15   0 90.2g  24m  18m S 11.5  0.0   3:20.01 ora_pr0c_TESTDB
23947 oracle    15   0 90.2g  24m  18m S  9.6  0.0   3:19.58 ora_pr02_TESTDB
23951 oracle    15   0 90.2g  24m  17m S  7.7  0.0   3:25.46 ora_pr03_TESTDB
23959 oracle    15   0 90.2g  24m  18m S  7.7  0.0   3:22.38 ora_pr05_TESTDB
23963 oracle    15   0 90.2g  24m  18m S  7.7  0.0   3:11.85 ora_pr06_TESTDB
23955 oracle    15   0 90.2g  24m  18m S  5.8  0.0   3:09.00 ora_pr04_TESTDB
23979 oracle    15   0 90.2g  24m  18m S  5.8  0.0   3:10.95 ora_pr0a_TESTDB
 4537 oracle    15   0 13020 1296  720 R  1.9  0.0   0:00.01 top -cbn 1    
18860 oracle    18   0 90.2g  19m  14m S  1.9  0.0   0:12.26 ora_dia0_TESTDB
    1 root      15   0 10368  684  572 S  0.0  0.0   0:06.21 init [5]     
    2 root      RT  -5     0    0    0 S  0.0  0.0   0:02.20 [migration/0]
    3 root      34  19     0    0    0 S  0.0  0.0   0:00.89 [ksoftirqd/0]
    4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 [watchdog/0] 
    5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.29 [migration/1]
    6 root      34  19     0    0    0 S  0.0  0.0   0:01.68 [ksoftirqd/1]
    7 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 [watchdog/1] 
    8 root      RT  -5     0    0    0 S  0.0  0.0   0:00.51 [migration/2]
    9 root      34  19     0    0    0 S  0.0  0.0   0:02.17 [ksoftirqd/2]
   10 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 [watchdog/2] 
   11 root      RT  -5     0    0    0 S  0.0  0.0   0:00.15 [migration/3]
   12 root      34  19     0    0    0 S  0.0  0.0   0:02.23 [ksoftirqd/3]
   13 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 [watchdog/3] 
   14 root      RT  -5     0    0    0 S  0.0  0.0   0:00.31 [migration/4]
   15 root      34  19     0    0    0 S  0.0  0.0   0:01.17 [ksoftirqd/4]
   16 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 [watchdog/4]
   17 root      RT  -5     0    0    0 S  0.0  0.0   0:00.24 [migration/5]

Copy Datafile from Physical Standby to Primary – ASM

You can also copy a datafile using asm command like “cp” command. But there are some side-effects as far as I see.

1 – When you copy a datafile to asm. It is somehow violates the OMF structure.
2 – For the copied datafiles,you have to live with asm symbolic links.
2 – During the copy process, you have to stop the apply process on the dataguard side.

on standby stop the apply process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

on standby copy the datafile to primary 

[oracle@standbyhost ~]$ asmcmd
ASMCMD> cp --port 1536 +DATA/stnbtest/datafile/users.293.839761205  sys@192.168.1.10.+ASM1:+DATA/primtest/datafile/users1
Enter password: ********
sh: -c: line 0: unexpected EOF while looking for matching `''
sh: -c: line 1: syntax error: unexpected end of file
sh: -c: line 0: unexpected EOF while looking for matching `''
sh: -c: line 1: syntax error: unexpected end of file
copying +DATA/stnbtest/datafile/users.293.839761205 -> 192.168.1.10:+DATA/primtest/datafile/users1
ASMCMD>exit

on standby resume the apply process

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT PARALLEL 16;

on primary switch the datafile on the datafile copy from the dataguard

ASMCMD> ls -l 
Type      Redund  Striped  Time             Sys  Name
DATAFILE  MIRROR  COARSE   FEB 17 12:00:00  Y    SYSAUX.304.839760443
DATAFILE  MIRROR  COARSE   FEB 17 12:00:00  Y    SYSTEM.305.839760429
DATAFILE  MIRROR  COARSE   FEB 17 12:00:00  Y    UNDOTBS1.303.839760457
DATAFILE  MIRROR  COARSE   FEB 24 09:00:00  Y    USERS.952.840360665
                                            N    users1 => +DATA/asm/datafile/users1.301.840365997

[oracle@primaryhost ~]$ rman 
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 24 11:08:24 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys@vtytest
target database Password: 
connected to target database: VTYTEST (DBID=2360494746)

RMAN> list copy of datafile 4 ;
using target database control file instead of recovery catalog
specification does not match any datafile copy in the repository

RMAN> catalog datafilecopy '+DATA/asm/datafile/users1.301.840365997';
cataloged datafile copy
datafile copy file name=+DATA/asm/datafile/users1.301.840365997 RECID=5 STAMP=840366582

RMAN>  sql 'alter database datafile 4 offline';
sql statement: alter database datafile 4 offline

RMAN>  run {
2> set newname for datafile 4 to '+DATA/ASM/DATAFILE/users1.301.840365997';
3> switch datafile 4;
4> }
executing command: SET NEWNAME
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=840366582 file name=+DATA/asm/datafile/users1.301.840365997

RMAN> recover datafile 4 ;
Starting recover at 24-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=303 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-FEB-14

RMAN> sql 'alter database datafile 4 online';
sql statement: alter database datafile 4 online

RMAN> report schema ;
Report of database schema for database with db_unique_name VTYTEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    16384    SYSTEM               ***     +DATA/vtytest/datafile/system.305.839760429
2    16384    SYSAUX               ***     +DATA/vtytest/datafile/sysaux.304.839760443
3    16384    UNDOTBS1             ***     +DATA/vtytest/datafile/undotbs1.303.839760457
4    1024     USERS                ***     +DATA/asm/datafile/users1.301.840365997
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32768    TEMP                 524288      +DATA/vtytest/tempfile/temp.302.839760921

Copy Datafile from Physical Standby to Primary – RMAN

If you have a corrupt datafile on production system you can perform the recovery using the dataguard sysem. You can backup the datafile from dataguard system to the primary system using rman. It is like copying the datafile over the network and registering that copy on the primary database and switching the corrupt datafile with that copy. To bring that datafile online you run the standart datafile recovery senario.

P.S.  This method can be also used to replace a datafile on the dataguard with a production copy.

List the current schema for the primary database.

[oracle@primaryhost ~]$ rman 

RMAN> connect target sys@primarytest
target database Password: 
connected to target database: PRIMTEST (DBID=2360494746)

RMAN> report schema;
Report of database schema for database with db_unique_name PRIMTEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    16384    SYSTEM               ***     +DATA/primtest/datafile/system.305.839760429
2    16384    SYSAUX               ***     +DATA/primtest/datafile/sysaux.304.839760443
3    16384    UNDOTBS1             ***     +DATA/primtest/datafile/undotbs1.303.839760457
4    1024     USERS                ***     +DATA/primtest/datafile/users.301.839760477
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32768    TEMP                 524288      +DATA/primtest/tempfile/temp.302.839760921

Copy the datafile from the dataguard to primary instance

[oracle@primaryhost ~]$ rman 

RMAN> connect target sys@standbytest
target database Password: 
connected to target database: PRIMTEST (DBID=2360494746, not open)

RMAN> connect auxiliary sys@primarytest
auxiliary database Password: 
connected to auxiliary database: PRIMTEST (DBID=2360494746)

RMAN> backup as copy datafile 4 auxiliary format '+DATA';

Starting backup at 24-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=993 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/stnbtest/datafile/users.293.839761205
output file name=+DATA/primtest/datafile/users.952.840360665 tag=TAG20140224T085510
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 24-FEB-14

On the primary database, switch the datafile that is copied from th standby with the original datafile.

[oracle@primaryhost ~]$ rman 

RMAN> connect target sys@primarytest

target database Password: 
connected to target database: PRIMTEST (DBID=2360494746)

RMAN> list copy of datafile 4;
using target database control file instead of recovery catalog
specification does not match any datafile copy in the repository

RMAN> catalog datafilecopy '+DATA/primtest/datafile/users.952.840360665';

cataloged datafile copy
datafile copy file name=+DATA/primtest/datafile/users.952.840360665 RECID=3 STAMP=840361417

RMAN>  sql 'alter database datafile 4 offline';
sql statement: alter database datafile 4 offline

RMAN> run {
2>  set newname for datafile 4 to '+DATA/primtest/datafile/users.952.840360665';
3>  switch datafile 4;
4> }
executing command: SET NEWNAME
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=840361417 file name=+DATA/primtest/datafile/users.952.840360665

RMAN> recover datafile 4;
Starting recover at 24-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=303 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-FEB-14

RMAN> SQL 'alter database datafile 4 online ';
sql statement: alter database datafile 4 online 

RMAN> report schema ;
Report of database schema for database with db_unique_name PRIMTEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    16384    SYSTEM               ***     +DATA/primtest/datafile/system.305.839760429
2    16384    SYSAUX               ***     +DATA/primtest/datafile/sysaux.304.839760443
3    16384    UNDOTBS1             ***     +DATA/primtest/datafile/undotbs1.303.839760457
4    1024     USERS                ***     +DATA/primtest/datafile/users.952.840360665

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32768    TEMP                 524288      +DATA/primtest/tempfile/temp.302.839760921

dbca ora-02249

On exadata when you create a database using dbca you need to use the templates special to exadata. like “oltp x2-2″ and “dwh x2-2″. I tried to install a single instance test database and even if I used the “oltp x2-2″ template I got an error during controlfile creation.

ORA-02249: missing or invalid value for MAXLOGMEMBERS

in this oracle support document it says you need to use the “Custom database” template but I got an error about the compatibility value for my db over the asm instance. I asked one of my colleagues and she said you need to change the MAXLOGMEMBERS and keep using the “oltp x2-2″ template. I have changed the MAXLOGMEMBERS from 24 to a lower value and it worked. But I don’t know why it worked :)

DBCA Errors with ORA-02249 When Creating Single-instance Database On Exadata (Doc ID 1456161.1)

oradism

Under oracle home, some files are owned by root user. They are used by oracle as if they are run by the root login and permissions. oradism is one of them. There are other files like that and this support document is pretty good.

Why are files under $ORACLE_HOME owned by root user? (Doc ID 461144.1)

you can verify if SUID (Set User ID) on the file is set or not just looking at the file permissions. Instead of “rwx”, if you see “rws” ,it means is is set.

what is oradism ?
It is used to set higher execution priorities for some executables such as lms processes for RAC. If it has wrong permission then the processes are run having normal priorities.

Example
On hostnode01 oradism is set to wrong permissions.

[oracle@hostnode01]# ls -l  $ORACLE_HOME/bin/oradism
-rwxr-x--- 1 oracle oinstall 71758 Sep 17  2011 $ORACLE_HOME/bin/oradism
[oracle@hostnode01]$  ps -eo pid,priority,cmd | grep lms 
  9248  -2 asm_lms0_+ASM1
 36204  20 grep lms
113634  20 ora_lms0_DBFSDB1
113638  20 ora_lms1_DBFSDB1
115210  20 ora_lms0_DBTEST1
115214  20 ora_lms1_DBTEST1
115218  20 ora_lms2_DBTEST1
115222  20 ora_lms3_DBTEST1

On hostnode02 oradism is set to correct permissions.

[oracle@hostnode02]#  ls -l  $ORACLE_HOME/bin/oradism
-rwsr-x--- 1 root oinstall 71758 Jul 29  2013 $ORACLE_HOME/bin/oradism
[oracle@hostnode02]$  ps -eo pid,priority,cmd | grep lms 
  9276  -2 asm_lms0_+ASM2
 12348  -2 ora_lms0_DBFSDB2
 12359  -2 ora_lms1_DBFSDB2
 13314  -2 ora_lms0_DBTEST2
 13318  -2 ora_lms1_DBTEST2
 13322  -2 ora_lms2_DBTEST2
 13326  -2 ora_lms3_DBTEST2
 

Hot to FIX file permissions
you can change the file permission when oracle is running. If you want the fix to be effective, then you need to bounce the instance.

[root@hostnode01]# chmod 4750 $ORACLE_HOME/bin/oradism
[root@hostnode01]# ls -l  $ORACLE_HOME/bin/oradism
-rwsr-x--- 1 oracle oinstall 71758 Sep 17  2011 $ORACLE_HOME/bin/oradism

OS Watcher Install & Usage

OS Watcher is a great tool. Pretty easy to install and use.
It uses the common linux commands like top, vmstat, iostat….
First of all Lets give credit to oracle support :)

Usage :
OS Watcher Black Box User’s Guide (Doc ID 1531223.1)
Download :
OSWatcher Black Box (Includes: [Video]) (Doc ID 301137.1)
Service Setup : 
How To Start OSWatcher Black Box (OSWBB) Every System Boot (Doc ID 580513.1)

Installation
just untar the downloaded os watcher black box executables into a directory.

[oracle@host oswatcher]$ tar -xvf oswbb711.tar
.
.

Start Using no service

[oracle@host oswatcher]$ ls
     oswbb  oswbb711.tar
[oracle@host oswatcher]$ mkdir /oradata/oswatcher_archive
[oracle@host oswatcher]$ cd oswbb
[oracle@host oswbb]$ nohup ./startOSWbb.sh 3 48 gzip /oradata/oswatcher_archive  &

Start Using oswbb service

Download the rpms for the oswatcher service and install

[root@host oswatcher]# rpm -Uvh oswbb-service-1.1.5-1.noarch.rpm 
Preparing...                ########################################### [100%]
   1:oswbb-service          ########################################### [100%]

Edit the conf file for the service

/etc/oswbb.conf contains the parameters for the collection schedule for the scripts.

[root@host oswbb]# cat /etc/oswbb.conf 
# Set OSW_HOME to the directory where you unpacked OSW or OSWbba
OSW_HOME='/oradata /oswatcher/oswbb'
# Set OSW_INTERVAL to the number of seconds between collections
OSW_INTERVAL='3'
# Set OSW_RETENTION to the number of hours logs are to be retained
OSW_RETENTION='48'
# Set OSW_USER to the owner of the OSW_HOME directory
OSW_USER='oracle'
# Set OSW_COMPRESSION to the desired compression facility
OSW_COMPRESSION='gzip'

Start the service

[root@host oswbb]# /sbin/service oswbb start
Starting OSWatcher: [  OK  ]

Set to start after every reboot

[root@host  ]# /sbin/chkconfig oswbb on
[root@host  ]# /sbin/chkconfig --list oswbb
    oswbb           0:off   1:off   2:on    3:on    4:on    5:on    6:off

Check if it is running

[oracle@host ]$ ps -ef | grep -i  osw
Follow

Get every new post delivered to your Inbox.