Template to Create Database with Groups and privileges in IBM Netezza

Template to Create Database with Groups and privileges in IBM Netezza

SQL scripts to create Database along with their groups and Privileges granted to them, here is the template to create a sample database NZ_DB.

-- Database creation for NZ_DB database.

-- -----------------------------------------------------------------------------

create database NZ_DB;

\c NZ_DB

-- -----------------------------------------------------------------------------

-- Create standard security groups.

-- -----------------------------------------------------------------------------

create group NZ_DB_ADM sessiontimeout 180 querytimeout 120 defpriority normal maxpriority normal rowsetlimit 0;

create group NZ_DB_STATS sessiontimeout 180 querytimeout 120 defpriority normal maxpriority normal rowsetlimit 0;

create group NZ_DB_RW sessiontimeout 180 querytimeout 120 defpriority normal maxpriority normal rowsetlimit 0;

create group NZ_DB_RO sessiontimeout 180 querytimeout 120  defpriority normal maxpriority normal rowsetlimit 0;

-- -----------------------------------------------------------------------------

-- Grant permissions to groups.

-- -----------------------------------------------------------------------------

--

-- Grant permissions to NZ_DB_ADM.

--

grant TABLE, EXTERNAL TABLE, TEMP TABLE, VIEW, FUNCTION, PROCEDURE, SEQUENCE to NZ_DB_ADM;

grant LIST, SELECT on NZ_DB to NZ_DB_ADM;

grant ALTER, DELETE, DROP, GENSTATS, INSERT, LIST, SELECT, TRUNCATE, UPDATE on TABLE to NZ_DB_ADM;

grant ALTER, DELETE, DROP, GENSTATS, INSERT, LIST, SELECT, TRUNCATE, UPDATE on EXTERNAL TABLE to NZ_DB_ADM;

grant ALTER, DROP, LIST, SELECT on VIEW to NZ_DB_ADM;

grant INSERT, LIST, SELECT on SYSTEM TABLE to NZ_DB_ADM;

grant LIST, SELECT on SYSTEM VIEW to NZ_DB_ADM;

grant ALTER, DROP, LIST, SELECT, UPDATE on SEQUENCE to NZ_DB_ADM;

grant ALTER, DELETE, DROP, INSERT, LIST, SELECT, UPDATE on SYNONYM to NZ_DB_ADM;

grant ALTER, DROP, EXECUTE, LIST on FUNCTION to NZ_DB_ADM;

grant ALTER, DROP, EXECUTE, LIST on PROCEDURE to NZ_DB_ADM;

grant ABORT, LIST on USER to NZ_DB_ADM;

--

-- Grant permissions to NZ_DB_STATS.

--

grant GENSTATS on TABLE to NZ_DB_STATS;

grant GENSTATS on EXTERNAL TABLE to NZ_DB_STATS;

--

-- Grant permissions to NZ_DB_RW.

--

grant LIST, SELECT on NZ_DB to NZ_DB_RW;

grant DELETE, INSERT, LIST, SELECT, TRUNCATE, UPDATE on TABLE to NZ_DB_RW;

grant DELETE, INSERT, LIST, SELECT, TRUNCATE, UPDATE on EXTERNAL TABLE to NZ_DB_RW;

grant LIST, SELECT on VIEW to NZ_DB_RW;

grant INSERT, LIST, SELECT on SYSTEM TABLE to NZ_DB_RW;

grant LIST, SELECT on SYSTEM VIEW to NZ_DB_RW;

grant LIST, SELECT, UPDATE on SEQUENCE to NZ_DB_RW;

grant DELETE, INSERT, LIST, SELECT, UPDATE on SYNONYM to NZ_DB_RW;

grant EXECUTE, LIST on FUNCTION to NZ_DB_RW;

grant EXECUTE, LIST on PROCEDURE to NZ_DB_RW;

grant LIST on USER to NZ_DB_RW;

--

-- Grant permissions to NZ_DB_RO.

--

grant LIST, SELECT on NZ_DB to NZ_DB_RO;

grant LIST, SELECT on TABLE to NZ_DB_RO;

grant LIST, SELECT on EXTERNAL TABLE to NZ_DB_RO;

grant LIST, SELECT on VIEW to NZ_DB_RO;

grant LIST, SELECT on SYSTEM TABLE to NZ_DB_RO;

grant LIST, SELECT on SYSTEM VIEW to NZ_DB_RO;

grant LIST, SELECT on SEQUENCE to NZ_DB_RO;

grant LIST, SELECT on SYNONYM to NZ_DB_RO;

grant EXECUTE, LIST on FUNCTION to NZ_DB_RO;

grant EXECUTE, LIST on PROCEDURE to NZ_DB_RO;

grant LIST on USER to NZ_DB_RO;

-- -----------------------------------------------------------------------------

-- Example to add the users to the security groups if you want.

-- -----------------------------------------------------------------------------

-- alter group NZ_DB_ADM add user pleduc;

-- alter group NZ_DB_RO add user pleduc;

-- alter group NZ_DB_RW add user pleduc;

-- alter group NZ_DB_STATS add user pleduc;

SQL Query to list tables occupying more space in SPU and disk IBM Netezza

SQL Query to list tables occupying more space in SPU and disk IBM  Netezza

Find Table list in  your Netezza data slice  which have grown a lot and now you want to check which all tables are consuming space on those data slices/disk so that you can try to find if those tables needs to be groomed, etc.

This SQL provide tables list which are using more than 5000 MB on hwid 1009 disk only

select objname as table_name,       database as db_name,       hwid as spu_id, dsid,       (allocated_bytes/1048576)

as allocated_mbytes  from _v_sys_relation_xdb sys,       _v_sys_object_dslice_info ds

 where ds.tblid = sys.objid and allocated_mbytes > 500 and hwid=1009 order by  allocated_mbytes desc,

table_name, db_name, dsid;

Comparison of unstable SQL Plan in Oracle 11gR2.

Comparison of  unstable SQL Plan in Oracle 11gR2.

In oracle there is one of of the more frustrating things about the CBO, It behave sometimes erratic performance. Oracle SQL Plans are being unstable.
We have AWR/ASH which collects a ton of info about databases activity and performance stuff.

Here I am going to discuss how we can check the plan difference and which plan it too much costly.

1. find the SQL statement, Plan_hash_value which is much costly using SQL_ID.

SQL> select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000 etime,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, u.username,
sql_text
from v$sql s, dba_users u
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
and u.user_id = s.parsing_user_id;

Enter value for sql_text:
Enter value for address:
Enter value for sql_id: 0qa98gcnnza7h

SQL_ID         CHILD  PLAN_HASH        EXECS         ETIME     AVG_ETIME USERNAME      SQL_TEXT
------------- ------ ---------- ------------ ------------- ------------- ------------- -----------------------------------------
0qa98gcnnza7h      0 3723858078            5        356.53         71.31 SYS           select max(FD_ID) from scott.sprout where cot1 < 10

0qa98gcnnza7h      1  568322376            1          7.92          7.92 SYS          select max(FD_ID) from scott.sprout where cot1 < 10

0qa98gcnnza7h      2  568322376           10         52.14          5.21 SYS           select max(FD_ID) from scott.sprout where cot1 < 10

0qa98gcnnza7h      3  568322376           30      1,064.19         35.47 KSO           select max(FD_ID) from scott.sprout where cot1 < 10

0qa98gcnnza7h      4 3723858078           10      4,558.62        455.86 KSO           select max(FD_ID) from scott.sprout where cot1 < 10



2. Then find the what changes are happened in Plan in sql, you can for that sql there and different plan hash value with estimated time of execution.

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3;

Enter value for sql_id: 0qa98gcnnza7h

   SNAP_ID   NODE BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME        AVG_LIO
---------- ------ ------------------------------ ------------- --------------- ------------ ------------ --------------
      3206      1 02-OCT-08 08.00.38.743 AM      0qa98gcnnza7h       568322376            4       10.359      121,722.8
      3235      1 03-OCT-08 01.00.44.932 PM      0qa98gcnnza7h                            1       10.865      162,375.0
      3235      1 03-OCT-08 01.00.44.932 PM      0qa98gcnnza7h      3723858078            1      127.664   28,913,271.0
      3236      1 03-OCT-08 01.28.09.000 PM      0qa98gcnnza7h       568322376            1        7.924      162,585.0
      3236      1 03-OCT-08 01.28.09.000 PM      0qa98gcnnza7h      3723858078            1       86.682   27,751,123.0
      3305      1 06-OCT-08 10.00.11.988 AM      0qa98gcnnza7h                            4       64.138   22,616,931.5
      3305      1 06-OCT-08 10.00.11.988 AM      0qa98gcnnza7h       568322376            2        5.710       81,149.0
      3306      1 06-OCT-08 11.00.16.490 AM      0qa98gcnnza7h                            6        5.512      108,198.5
      3307      1 06-OCT-08 12.00.20.716 PM      0qa98gcnnza7h                            2        3.824       81,149.0
      3328      1 07-OCT-08 08.39.20.525 AM      0qa98gcnnza7h                           30       35.473      156,904.7
      3335      1 07-OCT-08 03.00.20.950 PM      0qa98gcnnza7h      3723858078           10      455.862   28,902,128.6

3. Now you can  compare the Plan difference according to estimated time of execution and watch the difference.

SQL>SELECT * FROM table(dbms_xplan.display_awr(nvl(‘&sql_id’,’a96b61z6vp3un’),nvl(‘&plan_hash_value’,null),null,’typical +peeked_binds’));

Enter value for sql_id: 0qa98gcnnza7h
Enter value for plan_hash_value:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0qa98gcnnza7h
--------------------
select max(FD_ID) from scott.sprout where cot1 < 10

Plan hash value: 568322376

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 44497 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|   2 |   TABLE ACCESS FULL| SKEW |    32M|   335M| 44497   (2)| 00:08:54 |
---------------------------------------------------------------------------

SQL_ID 0qa98gcnnza7h
--------------------
select max(FD_ID) from scott.sprout where cot1 < 10

Plan hash value: 3723858078

--------------------------------------------------
| Id  | Operation                    | Name      |
--------------------------------------------------
|   0 | SELECT STATEMENT             |           |
|   1 |  SORT AGGREGATE              |           |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |
|   3 |    INDEX RANGE SCAN          | SKEW_cot1 |
--------------------------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

8 rows selected.

OEM 12c: Removing Host Agent Binaries and Target from monitoring

OEM 12c: Removing Host Agent Binaries and Target from monitoring

1.       Remove target from OEM .

Navigate to Setup => Manage Cloud Control => Agents.

Expand the drop-down menu near the "Agent"

Expand the "Target Setup" option.

Select "Remove Target"

2.       Stop agent from host .

Login to agent host with oracle e.g edw-ntz-0a1-lp

$ emctl stop agent.

3.       Decommission Agent from OEM server .

Navigate to Setup => Manage Cloud Control => Agents

Select your target.

Then Navigate to Agent > Target setup > Agent Decommission.

Then continue.

Again continue > OK

And wait for few mins to complete this process.

4.       Remove the Agent software on the Agent host using AgentDeinstall perl script.

Login to agent host with oracle.

$export AGENT_HOME= /usr/oracle/agent12c/core/12.1.0.5.0

$perl $AGENT_HOME/sysman/install/AgentDeinstall.pl -agentHome $AGENT_HOME

In last you will get the output like this.

Deinstall in progress (Tuesday, September 20, 2016 2:49:49 PM PDT)

Configuration assistant "Agent Deinstall Assistant" succeeded

............................................................... 100% Done.

Deinstall successful

End of install phases.(Tuesday, September 20, 2016 2:49:56 PM PDT)

End of deinstallations

Please check '/home/oracle/oraInventory/logs/silentInstall2016-09-20_02-49-46PM.log' for more details.

NOTE: The targets monitored by this Management Agent will not be deleted in the Enterprise Manager Repository by this deinstall script. Make sure to delete the targets manually from the Cloud Control Console for a successful deinstallation.

How To Setup/Configure 11.2.0.X DBFS Filesystem On RAC Configurations CRS Managed (Non-Exadata) For Golden gate

How To Setup/Configure 11.2.0.X DBFS Filesystem On RAC Configurations CRS Managed (Non-Exadata) For Golden gate

This document provides an example about how to setup, configure and implement a CRS(RAC-cluster) managed DBFS filesystem on Non-Exadata configurations For Golden Gate

Oracle Database File System (DBFS) creates a standard file system interface on top of files and directories that are stored in database tables. Oracle DBFS is similar to the Network File System (NFS) protocol in that it provides a shared network file system that looks like a local file system.

Steps to implement a DBFS filesystem on a RAC/Cluster configuration (CRS managed) For Golden Gate:

Note: The following implementation was performed on a 2 node RAC cluster Oracle Server 11.2 release.

1.        First of all, fuse fuse-libs kernel-devel needs to be installed & configured on both  the nodes:

With root user.

# yum install fuse fuse-libs kernel-devel.

2.       Add the user_allow_other option in the /etc/fuse.conf file on both  the nodes:

With root user.

#echo user_allow_other > /etc/fuse.conf

#chmod 644 /etc/fuse.conf

#cat /etc/fuse.conf

user_allow_other

#ls -l /etc/fuse.conf

-rw-r--r-- 1 root root 17 Aug 12 21:10 /etc/fuse.conf

3.       Create the DBFS mount point on both nodes:

              # mkdir -p /oracle/dbfs_direct

              #chown oracle:dba /oracle/dbfs_direct

              #ls -ld /oracle/dbfs_direct

              drwxr-xr-x 2 oracle dba 4096 Aug 17 21:13 /oracle/dbfs_direct

              #su - oracle

              $ cd /oracle/dbfs_direct

              $ pwd

               /oracle/dbfs_direct

4.       Create the DBFS database user and the DBFS database repository from any node as follows:

Sqlplus / as sysdba

SQL> create bigfile tablespace dbfs_ts

          datafile '+DATA' size 1024M

          autoextend on

          next 100M

          maxsize 3G

          NOLOGGING

          EXTENT MANAGEMENT LOCAL

          AUTOALLOCATE

          SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL>

SQL> create user dbfs_user identified by dbfs_user

default tablespace dbfs_ts

quota unlimited on dbfs_ts; 2 3

User created.

SQL>

SQL> grant create session, create table, create view, create procedure,

dbfs_role to dbfs_user;

Grant succeeded.

SQL>

5.       Please execute bellows  from any one Node .

A.      Then execute the following commands through the database instance (e.g. GGTEST1) as the dbfs_user database user (from Node #1 only):

$ sqlplus dbfs_user/dbfs_user

SQL> show user

USER is "DBFS_USER"

SQL>

SQL> start dbfs_create_filesystem dbfs_ts FS1

Above command should be complete with No error.

B.      Set the NOCACHE storage option to the LOB segment used by DBFS, connect to the database instance (e.g. GGTEST1) “/as sysdba” (from Node #1 only) and execute the following commands as follows:

$ sqlplus "/as sysdba"

SQL> ALTER TABLE DBFS_USER.T_FS1 MODIFY LOB (FILEDATA) (NOCACHE LOGGING);

Table altered.

SQL> SELECT owner

, table_name

, segment_name

, logging

, cache

FROM dba_lobs

WHERE tablespace_name='DBFS_TS';

OWNER            TABLE_NAME          SEGMENT_NAME      LOGGING             CACHE

------------------------------ ------------------------------ ------------------------------ ------- ----------

DBFS_USER          T_FS1                   LOB_SFS$_FST_1    YES                       NO

6.       Then configure fuse library links on Both the nodes (connected as root OS user):

# echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf

# cd /usr/local/lib

#ln -s $ORACLE_HOME/lib/libclntsh.so.11.1

#ln -s $ORACLE_HOME/lib/libnnz11.so

# locate libfuse.so

/lib/libfuse.so.2

/lib/libfuse.so.2.7.4

/lib64/libfuse.so.2

/lib64/libfuse.so.2.7.4

/usr/local/lib/libfuse.so

/usr/local/lib/libfuse.so.2

#ln -s /lib64/libfuse.so.2 libfuse.so

# ldconfig

# ln -s $ORACLE_HOME/bin/dbfs_client   /sbin/mount.dbfs

7.       As a following step, please download the latest version of the “mount-dbfs.sh” script from the following document:

 Configuring DBFS on Oracle Database Machine [ID 1591515.1]

File Name:- mount-dbfs-20160215.rar

Contains: - mount-dbfs.sh and mount-dbfs.conf

8.       Then edit the mount-dbfs.conf script and update the next parameters with the correct/respective values and place it to /etc/oracle/mount-dbfs.conf location on both nodes.

            DBNAME= GGTEST

            MOUNT_POINT= /apps/oracle/dbfs_direct

            DBFS_USER= dbfs_user

            ORACLE_HOME= /apps/oracle/product/11.2.0.4/dbhome_1

            DBFS_PASSWD= dbfs_user

# ls -lrth /etc/oracle/mount-dbfs.conf

Permission and ownership

-rw-r--r-- 1 root oinstall 3.7K Aug 23 13:37 /etc/oracle/mount-dbfs.conf

9.       Place the mount-dbfs.sh script at the “<Grid Infrastructure _HOME>/crs/script/” directory (on both the nodes):

#cd /apps/infra/product/11.2.0.4/dbhome_1/crs/script/

Permission and ownership on both nodes

#ls -lrth mount-dbfs.sh

-rwxr-xr-x 1 grid oinstall 13K Aug 31 15:30 mount-dbfs.sh

chown grid:oinstall /apps/infra/product/11.2.0.4/dbhome_1/crs/script/mount-dbfs.sh 

chmod +x /bin/fusermount 

Change dos to Linux on both node

#dos2unix mount-dbfs.sh

10.   Create a script to add dbfs mount resource .

cat add-dbfs-resource.sh

##### start script add-dbfs-resource.sh

#!/bin/bash

ACTION_SCRIPT=/apps/infra/product/11.2.0.4/dbhome_1/crs/script/mount-dbfs.sh 

RESNAME=dbfs_mount

DBNAME=GGTEST

DBNAMEL=`echo $DBNAME | tr A-Z a-z`

ORACLE_HOME=/apps/infra/product/11.2.0.4/dbhome_1

PATH=$ORACLE_HOME/bin:$PATH

export PATH ORACLE_HOME

crsctl add resource $RESNAME \

-type local_resource \

-attr "ACTION_SCRIPT=$ACTION_SCRIPT, \

CHECK_INTERVAL=30,RESTART_ATTEMPTS=10, \

START_DEPENDENCIES='hard(ora.$DBNAMEL.db)pullup(ora.$DBNAMEL.db)',\

STOP_DEPENDENCIES='hard(ora.$DBNAMEL.db)',\

SCRIPT_TIMEOUT=300"

##### end script add-dbfs-resource.sh

11.   Run script with oracle user.

$./add-dbfs-resource.sh

12.    Start dbfs resource from grid user it will start dbfs mount point on both node.

$crsctl start resource dbfs_mount

            CRS-2672: Attempting to start 'dbfs_mount' on ora-rac-105-lq

            CRS-2672: Attempting to start 'dbfs_mount' on ora-rac-106-lq

            CRS-2676: Start of 'dbfs_mount' on ora-rac-105-lq succeeded

CRS-2676: Start of 'dbfs_mount' on ora-rac-106-lq succeeded

Node1#

[oracle@ora-rac-105-lq script]$ df -h|grep /apps/oracle/dbfs_direct

dbfs-dbfs_user@:/     3.0G  120K  3.0G   1% /apps/oracle/dbfs_direct

[oracle@ora-rac-105-lq script]$

Node2#

[oracle@ora-rac-106-lq ~]$ df -h|grep /apps/oracle/dbfs_direct

dbfs-dbfs_user@:/     3.0G  120K  3.0G   1% /apps/oracle/dbfs_direct

[oracle@ora-rac-106-lq ~]$

Now Go and create files in this mount point from nodes.

[oracle@ora-rac-105-lq script]$ cd /apps/oracle/dbfs_direct/FS1/

[oracle@ora-rac-105-lq FS1]$ touch node1.txt

[oracle@ora-rac-106-lq ~]$ cd /apps/oracle/dbfs_direct/FS1/

 [oracle@ora-rac-106-lq FS1]$ touch node2.txt

[oracle@ora-rac-106-lq FS1]$ ls -lrth

total 0

-rw-r--r-- 1 oracle oinstall 0 Sep 14 11:54 node1.txt

-rw-r--r-- 1 oracle oinstall 0 Sep 14 11:54 node2.txt

Additional Redo Generation During Online(HOT) Backup

In this article I'll describe you the reason, why there are excessive (additional) redo information generate when we take the online Database/Tablespace backup.

Going further let me clear one thing, during online backup the database performs normal read write operation on datafile and generates online redologs but with some additional/excessive information.  

When we put the Database/TBS in backup mode by the below command.

alter database begin backup;

or

alter tablespace test begin backup;

Oracle immediately flushes all the earlier changes from SGA memory (Buffer cache) to physical disk (oracle blocks) and then checkpoints the tablespace.

Now the datafile header of the tablespace is frozen i.e. the SCN is marked and it will not increase by any of the update in datafile. This helps oracle to find out when tablespace datafile has the last consistent data and what archive redo log files might be needed to fully recover that file.

When a block changed for the FIRST time in a datafile, Oracle writes the ENTIRE BLOCK into the redo log files inspite to write only the changed bytes. Normally Oracle writes only the changed bytes into the online redolog. Now we have the ENTIRE BLOCK of the redo vector in redo logfile, when the same block is get updated by NEXT time then only changed redo vector would get written down into the online redolog inspite of the ENTIRE BLOCK. The reason behind it is, might be the DBWR and copying process is working over the same block simultaneously.

Lets clear it by the below situation.

In Linux, filesystem have a default blocksize of 2k, while Oracle's is 8k. This means that the filesystem read factor is 2k at a time. The backup program goes to read 8k Oracle block. The OS already copied the 4k of the block, meanwhile DBWR has called to rewrite the same block. The entire 8k block is rewritten. The backup program reads the last 4k of the block. 

Above situation is, where the DBWR's written activity happend over block  at the same time backup process is copying that block. In this case backup copy of the DB block could contain FRACTURED BLOCK.

Hence Oracle log the ENTIRE block image in the redologs for event of recovery, if there is any fractured block it will be totally rewritten from redologs and will be consistent. 

Create ASM Standby Database for a NON-ASM Primary Database in 11gR2

Create ASM Standby Database for a NON-ASM Primary Database in 11g

In this post I'll create a ASM Physical Database on a different host for a NON-ASM Primary database.
Here:-

Database_Type     Database_Name      Host   
Primary                  db1                         prim.oracle.com
Standby                 sdb1                        stby.oracle.com

I'll follow the below step to configure the same.

1. Take the full backup of Production database including archives as well.

RMAN> backup database format '/u01/bkp/db_%U.bkp';

Starting backup at 10-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0op09ghd_1_1.bkp tag=TAG20140210T153053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0pp09gkd_1_1.bkp tag=TAG20140210T153053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14

RMAN> backup archivelog all format '/u01/bkp/arc_%U.bkp';

Starting backup at 10-FEB-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=7 STAMP=838829575
input archived log thread=1 sequence=10 RECID=8 STAMP=838884652
input archived log thread=1 sequence=11 RECID=9 STAMP=838901252
input archived log thread=1 sequence=12 RECID=10 STAMP=838901331
input archived log thread=1 sequence=13 RECID=11 STAMP=838905416
input archived log thread=1 sequence=14 RECID=12 STAMP=838939737
input archived log thread=1 sequence=15 RECID=13 STAMP=838978232
input archived log thread=1 sequence=16 RECID=14 STAMP=839014248
input archived log thread=1 sequence=17 RECID=15 STAMP=839052021
input archived log thread=1 sequence=18 RECID=16 STAMP=839095566
input archived log thread=1 sequence=19 RECID=17 STAMP=839140243
input archived log thread=1 sequence=20 RECID=18 STAMP=839161908
input archived log thread=1 sequence=21 RECID=19 STAMP=839162150
input archived log thread=1 sequence=22 RECID=20 STAMP=839172779
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/arc_0qp09glc_1_1.bkp tag=TAG20140210T153300 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-FEB-14

RMAN> backup current controlfile for standby format '/u01/bkp/control_%U.ctl';

Starting backup at 10-FEB-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/control_0rp09goe_1_1.ctl tag=TAG20140210T153438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14

Step 2.

Copy the backups to the standby server.

[oracle@prim bkp]$ scp -r * oracle@stby:/u01/bkp/

oracle@stby's password:

Permission denied, please try again.

oracle@stby's password:

arc_0qp09glc_1_1.bkp                          100%  269MB  17.9MB/s   00:15

control_0rp09goe_1_1.ctl                      100% 9568KB   9.3MB/s   00:01

db_0op09ghd_1_1.bkp                           100% 1072MB  13.6MB/s   01:19

db_0pp09gkd_1_1.bkp                           100% 9600KB   9.4MB/s   00:00

Step 3. 

Make an entry for both the database servers in the tnsnames.ora file and the file should be similar on both the host.

[oracle@prim dbs]$ more /u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora

DB1 =

  (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx )(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db1)

    )

  )

SDB1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db1)

    )

  )

listener file of db1.

[oracle@prim dbs]$ more /u01/app/oracle/product/11.2.0/db_2/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = db1)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Listener file of sdb1

[oracle@stby dbs]$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx )(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = db1)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Step 4.

Create the ora password file on the primary database and copy the same to the standby database server $ORACLE_HOME/dbs.

[oracle@prim dbs]$ orapwd file=orapwdb1 password=oracle force=y

[oracle@prim dbs]$ scp orapwdb1 oracle@stby:/u01/app/oracle/product/11.2.0/db_1/dbs

oracle@stby's password:

orapwdb1                                                                                                                   100% 1536     1.5KB/s   00:00

Step 5.

Copy the parameter file from Primary to Standby database host and change the parameter file accordingly to standby .

db1.__db_cache_size=687865856

db1.__java_pool_size=16777216

db1.__large_pool_size=16777216

db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

db1.__pga_aggregate_target=687865856

db1.__sga_target=1006632960

db1.__shared_io_pool_size=0

db1.__shared_pool_size=268435456

db1.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/db1/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA','+DATA'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain='oracle.com'

*.db_name='db1'

*.db_unique_name='sdb1'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.db_recovery_file_dest='+DATA'

*.db_recovery_file_dest_size=2147483648

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'

*.fal_client='DB1'

*.fal_server='SDB1'

*.log_archive_config='DG_CONFIG=(db1,sdb1)'

*.log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sdb1'

*.log_archive_dest_2='SERVICE=db1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE DB_UNIQUE_NAME=db1'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

Parameter file of Primary database.

[oracle@prim dbs]$ more initdb1.ora

db1.__db_cache_size=687865856

db1.__java_pool_size=16777216

db1.__large_pool_size=16777216

db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

db1.__pga_aggregate_target=687865856

db1.__sga_target=1006632960

db1.__shared_io_pool_size=0

db1.__shared_pool_size=268435456

db1.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/db1/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/db1/control01.ctl','/u01/app/oracle/fast_recovery_area/db1/control02.ctl'

*.db_block_size=8192

*.db_domain='oracle.com'

*.db_name='db1'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4322230272

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'

#*.local_listener='LISTENER'

*.memory_target=1694498816

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.fal_client='DB1'

*.fal_server='SDB1'

*.log_archive_config='DG_CONFIG=(db1,sdb1)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/DB1/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db1'

*.log_archive_dest_2='SERVICE=sdb1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE DB_UNIQUE_NAME=sdb1'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

Step 6.

Start the Standby database in nomount mode using the new parameter file.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initsdb1.ora';

ORACLE instance started.

Total System Global Area  171581440 bytes

Fixed Size                  1343668 bytes

Variable Size             117444428 bytes

Database Buffers           50331648 bytes

Redo Buffers                2461696 bytes

Database mounted.

Step 7.

Restore the control file using the standby control file backup.

RMAN> restore standby controlfile from '/u01/bkp/control_0rp09goe_1_1.ctl';

Starting restore at 10-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=+DATA/sdb1/controlfile/current.262.839184505

output file name=+DATA/sdb1/controlfile/current.263.839184509

Finished restore at 10-FEB-14

Step 8. Bounce the database and update the parameter file with the control file information.

*.control_files='+DATA/sdb1/controlfile/current.262.839184505','+DATA/sdb1/controlfile/current.263.839184509'

Step 9.

Start the standby database in mount mode and perform the restoration process of the database.

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initsdb1.ora';

ORACLE instance started.

Total System Global Area  171581440 bytes

Fixed Size                  1343668 bytes

Variable Size             117444428 bytes

Database Buffers           50331648 bytes

Redo Buffers                2461696 bytes

Database mounted.

SQL> host rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 10 18:51:22 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB1 (DBID=1432532444, not open)

RMAN> run

{

set newname for datafile 1 to '+DATA';

set newname for datafile 2 to '+DATA';

set newname for datafile 3 to '+DATA';

set newname for datafile 4 to '+DATA';

set newname for datafile 5 to '+DATA';

set newname for tempfile 1 to '+DATA';

restore database;

switch datafile all;

switch tempfile all;

}

2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 10-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=33 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DATA

channel ORA_DISK_1: restoring datafile 00002 to +DATA

channel ORA_DISK_1: restoring datafile 00003 to +DATA

channel ORA_DISK_1: restoring datafile 00004 to +DATA

channel ORA_DISK_1: restoring datafile 00005 to +DATA

channel ORA_DISK_1: reading from backup piece /u01/bkp/db_0op09ghd_1_1.bkp

channel ORA_DISK_1: piece handle=/u01/bkp/db_0op09ghd_1_1.bkp tag=TAG20140210T153053

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:56

Finished restore at 10-FEB-14

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=839184818 file name=+DATA/sdb1/datafile/system.259.839184701

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=839184818 file name=+DATA/sdb1/datafile/sysaux.257.839184703

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=839184818 file name=+DATA/sdb1/datafile/undotbs1.260.839184705

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=839184818 file name=+DATA/sdb1/datafile/users.261.839184705

datafile 5 switched to datafile copy

input datafile copy RECID=10 STAMP=839184819 file name=+DATA/sdb1/datafile/test.258.839184703

renamed tempfile 1 to +DATA in control file

RMAN> list backup of archivelog all;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

1       137.97M    DISK        00:00:07     06-FEB-14

        BP Key: 1   Status: EXPIRED  Compressed: NO  Tag: TAG20140206T161117

        Piece Name: /u01/bkp/01ovv1d5_1_1.bkp

  List of Archived Logs in backup set 1

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    3       791516     04-FEB-14 816435     05-FEB-14

  1    4       816435     05-FEB-14 858512     05-FEB-14

  1    5       858512     05-FEB-14 859840     05-FEB-14

  1    6       859840     05-FEB-14 859843     05-FEB-14

  1    7       859843     05-FEB-14 869252     05-FEB-14

  1    8       869252     05-FEB-14 898900     06-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

4       6.50K      DISK        00:00:00     06-FEB-14

        BP Key: 4   Status: EXPIRED  Compressed: NO  Tag: TAG20140206T161256

        Piece Name: /u01/bkp/04ovv1g8_1_1.bkp

  List of Archived Logs in backup set 4

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    9       898900     06-FEB-14 898952     06-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

7       177.34M    DISK        00:00:07     07-FEB-14

        BP Key: 7   Status: EXPIRED  Compressed: NO  Tag: TAG20140207T120732

        Piece Name: /u01/bkp/07p017g5_1_1.bkp

  List of Archived Logs in backup set 7

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    3       791516     04-FEB-14 816435     05-FEB-14

  1    4       816435     05-FEB-14 858512     05-FEB-14

  1    5       858512     05-FEB-14 859840     05-FEB-14

  1    6       859840     05-FEB-14 859843     05-FEB-14

  1    7       859843     05-FEB-14 869252     05-FEB-14

  1    8       869252     05-FEB-14 898900     06-FEB-14

  1    9       898900     06-FEB-14 898952     06-FEB-14

  1    10      898952     06-FEB-14 926119     07-FEB-14

  1    11      926119     07-FEB-14 933143     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

10      3.00K      DISK        00:00:00     07-FEB-14

        BP Key: 10   Status: EXPIRED  Compressed: NO  Tag: TAG20140207T120851

        Piece Name: /u01/bkp/0ap017ij_1_1.bkp

  List of Archived Logs in backup set 10

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    12      933143     07-FEB-14 933178     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

14      178.69M    DISK        00:00:09     07-FEB-14

        BP Key: 14   Status: EXPIRED  Compressed: NO  Tag: TAG20140207T131656

        Piece Name: /u01/bkp/archivelog_0fp01bi8_1_1.bkp

  List of Archived Logs in backup set 14

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    3       791516     04-FEB-14 816435     05-FEB-14

  1    4       816435     05-FEB-14 858512     05-FEB-14

  1    5       858512     05-FEB-14 859840     05-FEB-14

  1    6       859840     05-FEB-14 859843     05-FEB-14

  1    7       859843     05-FEB-14 869252     05-FEB-14

  1    8       869252     05-FEB-14 898900     06-FEB-14

  1    9       898900     06-FEB-14 898952     06-FEB-14

  1    10      898952     06-FEB-14 926119     07-FEB-14

  1    11      926119     07-FEB-14 933143     07-FEB-14

  1    12      933143     07-FEB-14 933178     07-FEB-14

  1    13      933178     07-FEB-14 934988     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

21      263.13M    DISK        00:00:17     10-FEB-14

        BP Key: 21   Status: EXPIRED  Compressed: NO  Tag: TAG20140210T123550

        Piece Name: /u01/bkp/arc_0mp09696_1_1.bkp

  List of Archived Logs in backup set 21

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    9       898900     06-FEB-14 898952     06-FEB-14

  1    10      898952     06-FEB-14 926119     07-FEB-14

  1    11      926119     07-FEB-14 933143     07-FEB-14

  1    12      933143     07-FEB-14 933178     07-FEB-14

  1    13      933178     07-FEB-14 934988     07-FEB-14

  1    14      934988     07-FEB-14 953914     07-FEB-14

  1    15      953914     07-FEB-14 974151     08-FEB-14

  1    16      974151     08-FEB-14 993762     08-FEB-14

  1    17      993762     08-FEB-14 1016061    09-FEB-14

  1    18      1016061    09-FEB-14 1039552    09-FEB-14

  1    19      1039552    09-FEB-14 1062286    10-FEB-14

  1    20      1062286    10-FEB-14 1090918    10-FEB-14

  1    21      1090918    10-FEB-14 1091253    10-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

25      268.79M    DISK        00:00:18     10-FEB-14

        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20140210T153300

        Piece Name: /u01/bkp/arc_0qp09glc_1_1.bkp

  List of Archived Logs in backup set 25

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    9       898900     06-FEB-14 898952     06-FEB-14

  1    10      898952     06-FEB-14 926119     07-FEB-14

  1    11      926119     07-FEB-14 933143     07-FEB-14

  1    12      933143     07-FEB-14 933178     07-FEB-14

  1    13      933178     07-FEB-14 934988     07-FEB-14

  1    14      934988     07-FEB-14 953914     07-FEB-14

  1    15      953914     07-FEB-14 974151     08-FEB-14

  1    16      974151     08-FEB-14 993762     08-FEB-14

  1    17      993762     08-FEB-14 1016061    09-FEB-14

  1    18      1016061    09-FEB-14 1039552    09-FEB-14

  1    19      1039552    09-FEB-14 1062286    10-FEB-14

  1    20      1062286    10-FEB-14 1090918    10-FEB-14

  1    21      1090918    10-FEB-14 1091253    10-FEB-14

  1    22      1091253    10-FEB-14 1096401    10-FEB-14

Step 10.

Perform the recover of database.

RMAN> run

2> {

3> set until sequence 22 thread 1;

4> recover database;

5> }

executing command: SET until clause

Starting recover at 11-FEB-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=42 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 02/11/2014 12:36:18

RMAN-06556: datafile 1 must be restored from backup older than SCN 1091253

Then I looked into the Oracle community and found the similar case and the solution given by user 491476 worked here.

https://community.oracle.com/thread/646983?start=0&tstart=0

SQL> select group#, first_change#, status, archived from v$log;

    GROUP# FIRST_CHANGE# STATUS           ARC

----------      -------------            ---------------- ---

         1          1161769                  CLEARING         YES

         3          1090918                  INACTIVE         YES

         2          1175084                  CURRENT          YES

RMAN> run

2> {

3> set until sequence 1175084 thread 1;

4> recover database;

5> }

executing command: SET until clause

Starting recover at 11-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=43 device type=DISK

starting media recovery

archived log for thread 1 with sequence 23 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_23.264.839248363

archived log for thread 1 with sequence 24 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_24.276.839248363

archived log for thread 1 with sequence 25 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_25.265.839248361

archived log for thread 1 with sequence 26 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_26.275.839248367

archived log for thread 1 with sequence 27 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_27.274.839248369

archived log for thread 1 with sequence 28 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_28.273.839248369

archived log for thread 1 with sequence 29 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_29.272.839248371

archived log for thread 1 with sequence 30 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_30.283.839248371

archived log for thread 1 with sequence 31 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_31.281.839248371

archived log for thread 1 with sequence 32 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_32.289.839248373

archived log for thread 1 with sequence 33 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_33.288.839248373

archived log for thread 1 with sequence 34 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_34.287.839248375

archived log for thread 1 with sequence 35 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_35.286.839248375

archived log for thread 1 with sequence 36 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_36.284.839248375

archived log for thread 1 with sequence 37 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_37.282.839248379

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=22

channel ORA_DISK_1: reading from backup piece /u01/bkp/arc_0qp09glc_1_1.bkp

channel ORA_DISK_1: piece handle=/u01/bkp/arc_0qp09glc_1_1.bkp tag=TAG20140210T153300

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:16

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_22.280.839249075 thread=1 sequence=22

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_23.264.839248363 thread=1 sequence=23

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_24.276.839248363 thread=1 sequence=24

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_25.265.839248361 thread=1 sequence=25

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_26.275.839248367 thread=1 sequence=26

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_27.274.839248369 thread=1 sequence=27

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_28.273.839248369 thread=1 sequence=28

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_29.272.839248371 thread=1 sequence=29

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_30.283.839248371 thread=1 sequence=30

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_31.281.839248371 thread=1 sequence=31

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_32.289.839248373 thread=1 sequence=32

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_33.288.839248373 thread=1 sequence=33

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_34.287.839248375 thread=1 sequence=34

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_35.286.839248375 thread=1 sequence=35

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_36.284.839248375 thread=1 sequence=36

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_37.282.839248379 thread=1 sequence=37

unable to find archived log

archived log thread=1 sequence=38

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 02/11/2014 12:45:24

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 38 and starting SCN of 1175084

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 02/11/2014 12:46:12

ORA-01666: control file is for a standby database

SQL> alter database open;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

DB1       READ ONLY            PHYSICAL STANDBY

SQL> select process from v$managed_standby;

PROCESS

---------

ARCH

ARCH

ARCH

ARCH

RFS

RFS

RFS

RFS

8 rows selected.

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

SQL> select process from v$managed_standby;

PROCESS

---------

ARCH

ARCH

ARCH

ARCH

RFS

RFS

RFS

RFS

MRP0

9 rows selected.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

DB1       READ ONLY WITH APPLY PHYSICAL STANDBY