Oracle Learning

RAC Node Pinning & Unpinning

2014-02-27T11:59:00.001+05:30

sam1374:CADP3>olsnodes -n -t -s

sam1372 1 Active Unpinned

sam1373 2 Active Unpinned

sam1374 3 Active Unpinned

sam1374:CADP3>

There is so much we learn every day. One of such is leanred today is pinning and unpinning of RAC node.

Pinning a node means that the association of a node name with a node number is fixed. If a node is not pinned, its node number may change if the lease expires while it is down. The lease of a pinned node never expires.

This concept is active since version 11.2. When we do fresh installtion Clusterware do it for us. For the clusterware which are upgraded from lower version, can be pinned by : crsctl pin css -n node3 node4

I'm still in phase to understand advantage/dis-advantage of pinning and unpinning the node.

OPatch cleanup

2014-02-16T10:36:00.001+05:30

[oracle@localhost OPatch]$ ./opatch util Cleanup

Oracle Interim Patch Installer version 12.1.0.1.0

Oracle Home : /u01/home/oracle/product/11.2.0/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/home/oracle/product/11.2.0/db_1/oraInst.loc

OPatch version : 12.1.0.1.0

OUI version : 12.1.0.1.0

Log file location : /u01/home/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-02-15_07-53-51AM_1.log

Invoking utility "cleanup"

OPatch will clean up 'restore.sh,make.txt' files and 'scratch,backup' directories.

You will be still able to rollback patches after this cleanup.

Do you want to proceed? [y|n]

User Responded with: Y

Size of directory "/u01/home/oracle/product/11.2.0/db_1/.patch_storage" before cleanup is 227169947 bytes.

Size of directory "/u01/home/oracle/product/11.2.0/db_1/.patch_storage" after cleanup is 227169947 bytes.

UtilSession: Backup area for restore has been cleaned up. For a complete list of files/directories

deleted, Please refer log file.

OPatch succeeded.

[oracle@localhost OPatch]$

ORA-39071: Value for EXCLUDE is badly formed

2013-12-12T17:45:00.000+05:30

IN KSH shell below exclude format won't work
exclude=TABLE:"IN ('DATA')" , this will prompt in error ORA-39071: Value for EXCLUDE is badly formed.
This is SHELL issue, solution is to use format : "exclude=TABLE:\"IN ('DATA')\""

Rename Disk group using renamedg

2013-12-01T04:19:00.003+05:30

1.First unmount the diskgroup

[oracle@vm6 ~]$ asmcmd umount FLASH

2.Verify whether diskgroup has been mounted or not

[oracle@vm6 ~]$ asmcmd lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 5114 1525 0 1525 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 10236 10186 0 10186 0 N DATA2/

3.Rename diskgroup

[oracle@vm6 ~]$ renamedg phase=both dgname=FLASH newdgname=FRA verbose=true

Parsing parameters..

Parameters in effect:

Old DG name : FLASH
New DG name : FRA
Phases :
Phase 1
Phase 2
Discovery str : (null)
Clean : TRUE
Raw only : TRUE
renamedg operation: phase=both dgname=FLASH newdgname=FRA verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/32/asm/orcl/1/libasm.so:ORCL:VOL2 with disk number:0 and timestamp (32970697 -482714624)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/32/asm/orcl/1/libasm.so:ORCL:VOL2 with disk number:0 and timestamp (32970697 -482714624)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:VOL2
Modifying the header
Completed phase 2
Terminating kgfd context 0xb7e70050

above command fails use asm_diskstring command

renamedg phase=both dgname=FLASH newdgname=FRA verbose=true asm_diskstring='ORCL:VOL2'

4.Mount the new diskgroup

[oracle@vm6 ~]$ asmcmd mount FRA

5.Verify the diskgroup

[oracle@vm6 ~]$ asmcmd lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 5114 1525 0 1525 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 10236 10186 0 10186 0 N DATA2/
MOUNTED EXTERN N 512 4096 1048576 1019 967 0 967 0 N FRA/
[oracle@vm6 ~]$

Standby archive apply hang due to space issue

2013-11-27T00:55:00.002+05:30

Ideally at designing stage we should have allocated sufficient space for both PROD and STANDBY and they should match. For example, if a file system U05 is 500GB on PROD you should size the file system to 500GB on STANDBY as well.

Well but not always. :)

We hang on issue when a DBA had added a datafile in Prod at U05 file system but due to lack space at DR Side for file system U05, archive apply stopped.

Standby_file_management is set to auto.

It was noted when archive apply was stopped due to space issue.

Because is the steps taken to carried away:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/11.2.0/db_1/dbs/UNNAMED00143' AS '/u06/realfilename/gdata05.dbf';

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

or, If you are using real time apply (it makes use of standby redo log file)

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

Hope this helps...

RMAN-08137: WARNING: archive log not deleted as it is still needed

2013-10-01T08:11:00.000+05:30

Recently my friend raised a discussion for “RMAN-08137: WARNING: archived log not deleted “ .
That make me remind of couple of issues I have faced with archive deletion policy when standby is configured.

Issue discussed is on 3 node RAC database with on version 11gR2 with 3 node RAC on standby configuration.

We have set Archive deletion to

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

Case (1) is for the ticket when error RMAN-08137 raised when user is trying to delete archive log using command “backup archivelog all delete input”.

The error is very informative and it is because archive is not yet applied at Standby Database. Same can be checked with below command on production database:

select dest_id, thread#, sequence#, first_time, completion_time, registrar, archived, applied, deleted, status from v$archived_log where standby_dest='YES' and status='A';

Case (2) is user facing the FRA space, tried to deleted the archive log with below command and surprising archive logs are deleted before even shipped at standby database.

rman> backup archivelog all not backed up delete all input skip inaccessible;

Issue is stated as Bug:16692232 in MOS and One of patch is provided.

I have also faced couples of more cases for the rman-08137 when database is configured with streams replication and those are because archived logs where still to be used by logminer .

query to check what query running into specific user at given time

2013-07-12T20:29:00.004+05:30

SET HEADING ON

SET LINESIZE 300

SET PAGESIZE 60

COLUMN Sample_Time FOR A12

COLUMN username FOR A20

COLUMN sql_text FOR A40

COLUMN program FOR A25

COLUMN module FOR A25

SELECT

to_char(sample_time,'DD Mon HH24:MI') as Sample_Time,

u.username,

h.program,

h.module,

s.sql_text

FROM

DBA_HIST_ACTIVE_SESS_HISTORY h,

DBA_USERS u,

DBA_HIST_SQLTEXT s

WHERE sample_time between '11-JUL-13 05.45.03.793 PM' and '11-JUL-13 06.45.04.455 PM'

AND h.user_id=u.user_id

AND h.sql_id = s.sql_iD

AND u.username='SCOTT'

order by 1;

To check given query performance for any specific table:-

select a. snap_id, begin_interval_time,c.sql_text,

executions_delta,(elapsed_time_delta /executions_delta )/1000000,

plan_hash_value,(cpu_time_delta /executions_delta )/1000000,

rows_processed_delta/executions_delta ,(iowait_delta /executions_delta )/1000000

,(fetches_delta/ executions_delta)

,(disk_reads_delta/ executions_delta)

,(buffer_gets_delta/ executions_delta)

from dba_hist_sqlstat a, dba_hist_snapshot b ,

dba_hist_sqltext c where sql_text like '%table_name%' and a. snap_id= b. snap_id and executions_delta ! =0 and a.sql_id=c.sql_id order by snap_id desc

Excess redo generation in begin backup mode: test case

2013-06-19T21:24:00.000+05:30

Long backup I had asked with Ritesh (http://education.oracle.com/education/otn/rsingh.htm) about the hidden parameter “_LOG_BLOCKS_DURING_BACKUP’ and he explain what exactly happened when any tablespace in taken in the begin backup mode.

Below are the highlights:

Step 1 : Freeze the Checkpoint for the requested datafiles, this allows the identification of backup mode for the datafile.

Step 2 : Any block writing / updating to the datafiles by DBWn will also copy the same block to Redo Log Files.

Step 1 is required for identifying the backup mode and initiating the image copy to be written to redo log files.

Step 2 is required for Fractured block recovery at a later stage, when the backup copy will be used for restore.

Let’s take a test case

e.g : I started a backup at 9:30 in the morning, backup started by putting USERS tablespace into backup mode, during the backup there were some batch process running simultaneously. Since the block size of my database is 8K, an update to Table (T)will affect a change inblocks of 8K Size, but since the backup is done by CP command, OS will copy the block of 512b as per OS Blocksize in each call.

When cp command will start copying, there would be a possibility that in order to copy one oracle block it has to make 16 calls and in between each call if the entire block of oracle is changed by the batch process, the outcome backup copy will be fractured. To avoid this oracle marks the same copy to Redo log, thus generating lots of redo.

Testing the scenario:

SQL> conn scott/tiger

Connected.

SQL> set autotrace trace stat

SQL> update test set ename = 'test' where empno = 7369;

1 row updated.

Statistics

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

0 recursive calls

3 db block gets

3 consistent gets

0 physical reads

348 redo size

671 bytes sent via SQL*Net to client

617 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> rollback;

Rollback complete.

We can see, the redo is generated in minimum amount of time. After then I put the uses tablespace in begin backup mode and executed the same statement:

SQL> update test set ename = 'test' where empno = 7369;

1 row updated.

Statistics

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

0 recursive calls

2 db block gets

3 consistent gets

0 physical reads

8636 redo size

671 bytes sent via SQL*Net to client

617 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

But this time it generated large amount of redo. But when I executed the same statement again, the redo generation is normal.

SQL> update test set ename = 'test' where empno = 7369;

1 row updated.

Statistics

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

0 recursive calls

1 db block gets

3 consistent gets

0 physical reads

348 redo size

671 bytes sent via SQL*Net to client

617 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

The first time ORACLE has generated the maximum redo (copying the entire block in the redo ) in order to recover from the fuzziness of data file at time of recovery if happen. But why ORACLE won’t generate the redo for second time, I have executed the same statement but for the different block:

SQL> update test set ename = 'test' where empno = 7934;

1 row updated.

Statistics

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

1 recursive calls

1 db block gets

3 consistent gets

0 physical reads

8636 redo size

673 bytes sent via SQL*Net to client

617 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

This time, ORACLE has again generated the lage amount of redo. So what I concludes is ORACLE generate of redo by copying the entire block in redo logfile. But time happens for one time only. Even if any row changes in the same block, it won’t be copied again.

http://www.oracledba-in.com/2011/10/backup-generating-redo-disable.html

emcli configuration

2013-06-10T22:49:00.000+05:30

/oms/grid/oms/oms/bin > ./emcli status

Oracle Enterprise Manager Cloud Control 12c Release 2.

Instance Home : /oravl01/oracle

Status : Not Configured

/oms/grid/oms/oms/bin > ./emcli setup -url=https://suzan.com:7810/em -dir=/oms/grid/oms/oms -username=sysman -nocertvalidate -trustall

Oracle Enterprise Manager Cloud Control 12c Release 2.

Enter password

Emcli setup successful

/oms/grid/oms/oms/bin > ./emcli status

Oracle Enterprise Manager Cloud Control 12c Release 2.

Instance Home : /oms/grid/oms/oms/.emcli

Verb Jars Home : /oms/grid/oms/oms/.emcli

Status : Configured

EMCLI Home : /oms/grid/oms/oms/bin/.

EMCLI Version : 12.1.0.2.0

Java Home : /oms/grid/oms/jdk16/jdk/jre

Java Version : 1.6.0_24

Log file : /oms/grid/oms/oms/.emcli/.emcli.log

EM URL : https://suzan.com:7810/em

EM user : sysman

Auto login : false

Trust all certificates : true

oms/grid/oms/oms/bin >

import error ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 1677

2013-06-06T21:28:00.000+05:30

When doing import using fromuser touser parameter got below error:

IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 108
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 1677
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully

Cause : the target schema got snapshot log.

Solution : Either delete the snapshot log, again take the export and re-import.

Ensure user which got the snapshot log present in target database to prevent the error.

changing SYSMAN password in OEM12c

2013-05-27T09:58:00.001+05:30

#./emctl stop oms

Oracle Enterprise Manager Cloud Control 12c Release 2

Stopping WebTier...

WebTier Successfully Stopped

Stopping Oracle Management Server...

Oracle Management Server Successfully Stopped

Oracle Management Server is Down

#./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd sys -new_pwd octco

Oracle Enterprise Manager Cloud Control 12c Release 2

Changing passwords in backend ...

Passwords changed in backend successfully.

Updating repository password in Credential Store...

Successfully updated Repository password in Credential Store.

Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.

Successfully changed repository password.

#./emctl stop oms -all

Oracle Enterprise Manager Cloud Control 12c Release 2

Stopping WebTier...

WebTier Successfully Stopped

Stopping Oracle Management Server...

Oracle Management Server Already Stopped

AdminServer Successfully Stopped

Oracle Management Server is Down

#./emctl start oms

Oracle Enterprise Manager Cloud Control 12c Release 2

Starting Oracle Management Server...

Starting WebTier...

WebTier Successfully Started

Oracle Management Server Successfully Started

Oracle Management Server is Up

dba_free_space

2013-03-12T08:46:00.001+05:30

Static view dba_free_space, mainly used for calculating the free space in any tablespace (excluding temporary tablespace) will not display any row if tablespace is 100% used.

Below is the test for undo tablespace, that is 100% utilized.

SQL> select file_id,bytes/1024 from dba_data_files where tablespace_name='UNDOTBS';

FILE_ID BYTES/1024

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

3 8192000

61 8192000

62 8192000

SQL> select file_id,bytes/1024 from dba_free_space where tablespace_name='UNDOTBS';

no rows selected

SQL>

Configuring the Primary database backup from DG database

2013-02-28T21:28:00.001+05:30

[oracle@primary u01]$ rman | tee rman_offload_check.log

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 25 08:49:27 2013

RMAN> connect catalog rman/rman@rman

connected to recovery catalog database

RMAN> connect target sys@JAY

target database Password:

connected to target database: JAY (DBID=2467455158)

RMAN> CONFIGURE DB_UNIQUE_NAME DGJAY CONNECT IDENTIFIER 'DGJAY';

new RMAN configuration parameters:

CONFIGURE DB_UNIQUE_NAME 'DGJAY' CONNECT IDENTIFIER 'DGJAY';

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> LIST DB_UNIQUE_NAME OF DATABASE;

List of Databases

DB Key DB Name DB ID Database Role Db_unique_name

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

2 JAY 2467455158 PRIMARY JAY

2 JAY 2467455158 STANDBY DGJAY

RMAN> list backup of database;

specification does not match any backup in the repository

RMAN> list backup of database for Db_unique_name DGJAY;

specification does not match any backup in the repository

RMAN>

Backup from DG database.

[oracle@secondary JAY]$ rman target sys@dgJAY catalog rman/rman@rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 25 19:00:11 2011

target database Password:

connected to target database: JAY (DBID=2467455158)

connected to recovery catalog database

RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME DGJAY;

RMAN> backup database plus archivelog;

Starting backup at 25-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 device type=DISK

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=291 RECID=322 STAMP=771248790

input archived log thread=1 sequence=292 RECID=323 STAMP=771248853

input archived log thread=1 sequence=293 RECID=324 STAMP=771248855

input archived log thread=1 sequence=294 RECID=325 STAMP=771248927

input archived log thread=1 sequence=295 RECID=326 STAMP=771248928

input archived log thread=1 sequence=296 RECID=327 STAMP=771258396

input archived log thread=1 sequence=297 RECID=328 STAMP=771258609

input archived log thread=1 sequence=298 RECID=329 STAMP=771258905

input archived log thread=1 sequence=299 RECID=330 STAMP=771259204

input archived log thread=1 sequence=300 RECID=331 STAMP=771259505

input archived log thread=1 sequence=301 RECID=332 STAMP=771259811

input archived log thread=1 sequence=302 RECID=333 STAMP=771260105

input archived log thread=1 sequence=303 RECID=334 STAMP=771260390

input archived log thread=1 sequence=304 RECID=335 STAMP=771260592

input archived log thread=1 sequence=305 RECID=336 STAMP=771260710

input archived log thread=1 sequence=306 RECID=337 STAMP=771261005

input archived log thread=1 sequence=307 RECID=338 STAMP=771261225

input archived log thread=1 sequence=308 RECID=339 STAMP=771263270

input archived log thread=1 sequence=309 RECID=340 STAMP=771263405

input archived log thread=1 sequence=310 RECID=341 STAMP=771263707

input archived log thread=1 sequence=311 RECID=342 STAMP=771264005

input archived log thread=1 sequence=312 RECID=343 STAMP=771264305

input archived log thread=1 sequence=313 RECID=344 STAMP=771264605

input archived log thread=1 sequence=314 RECID=345 STAMP=771264904

input archived log thread=1 sequence=315 RECID=346 STAMP=771265122

input archived log thread=1 sequence=316 RECID=347 STAMP=771268049

input archived log thread=1 sequence=317 RECID=348 STAMP=771268206

input archived log thread=1 sequence=318 RECID=349 STAMP=771268506

input archived log thread=1 sequence=319 RECID=350 STAMP=771268807

input archived log thread=1 sequence=320 RECID=351 STAMP=771269105

input archived log thread=1 sequence=321 RECID=352 STAMP=771269404

input archived log thread=1 sequence=322 RECID=353 STAMP=771269705

input archived log thread=1 sequence=323 RECID=354 STAMP=771270004

input archived log thread=1 sequence=324 RECID=355 STAMP=771270304

input archived log thread=1 sequence=325 RECID=356 STAMP=771270605

input archived log thread=1 sequence=326 RECID=357 STAMP=771270906

input archived log thread=1 sequence=327 RECID=358 STAMP=771271205

input archived log thread=1 sequence=328 RECID=359 STAMP=771271506

input archived log thread=1 sequence=329 RECID=360 STAMP=771271805

input archived log thread=1 sequence=330 RECID=361 STAMP=771272106

input archived log thread=1 sequence=331 RECID=362 STAMP=771272404

input archived log thread=1 sequence=332 RECID=363 STAMP=771272705

input archived log thread=1 sequence=333 RECID=364 STAMP=771273005

input archived log thread=1 sequence=334 RECID=365 STAMP=771273304

input archived log thread=1 sequence=335 RECID=366 STAMP=771273563

channel ORA_DISK_1: starting piece 1 at 25-JAN-13

channel ORA_DISK_1: finished piece 1 at 25-JAN-13

piece handle=/u01/bkup/JAY/ora_df771274960_s101_s1 tag=TAG20111230T281237 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

Finished backup at 25-JAN-13

Starting backup at 25-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/JAY/datafile/users01.dbf

input datafile file number=00001 name=/u01/JAY/datafile/system.dbf

input datafile file number=00002 name=/u01/JAY/datafile/sysaux.dbf

input datafile file number=00003 name=/u01/JAY/datafile/undotbs1.dbf

channel ORA_DISK_1: starting piece 1 at 25-JAN-13

channel ORA_DISK_1: finished piece 1 at 25-JAN-13

piece handle=/u01/bkup/JAY/ora_df771275005_s102_s1 tag=TAG20111230T190325 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:56

Finished backup at 25-JAN-13

Starting backup at 25-JAN-13

using channel ORA_DISK_1

specification does not match any archived log in the repository

backup cancelled because there are no files to backup

Finished backup at 25-JAN-13

Starting Control File and SPFILE Autobackup at 25-JAN-13

piece handle=/u01/bkup/JAY/c-2467455158-20111230-03 comment=NONE

Finished Control File and SPFILE Autobackup at 25-JAN-13

RMAN>

Manually deleting Primary datafile for backup testing

[oracle@primary JAY]$ sqlplus sys@JAY as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 30 19:25:49 2011

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application JAYing options

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/JAY/datafile/system.dbf

/u01/JAY/datafile/sysaux.dbf

/u01/JAY/datafile/undotbs1.dbf

/u01/JAY/datafile/users01.dbf

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application JAYing options

[oracle@primary JAY]$ mv /u01/JAY/datafile/users01.dbf /u01/JAY/datafile/users01.dbf.bkp

[oracle@primary JAY]$

Copying all the backup piece to primary from standby datatase box.

[oracle@secondary JAY]$ scp * oracle@primary:/u01/bkup/JAY

oracle@primary's password:

c-2467455158-20111230-03 100% 9952KB 9.7MB/s 00:01

ora_df771274960_s101_s1 100% 202MB 8.1MB/s 00:25

ora_df771275005_s102_s1 100% 729MB 9.0MB/s 01:21

[oracle@secondary JAY]$

[oracle@primary JAY]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 25 19:30:53 2011

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2142679040 bytes

Fixed Size 1337720 bytes

Variable Size 503318152 bytes

Database Buffers 1627389952 bytes

Redo Buffers 10633216 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/u01/JAY/datafile/users01.dbf'

SQL>exit

Recovery from DG backup for primary datafile.

[oracle@primary JAY]$ rman target sys@JAY catalog rman/rman@rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 25 19:33:14 2011

target database Password:

connected to target database: JAY (DBID=2467455158, not open)

connected to recovery catalog database

RMAN> list backup of database;

specification does not match any backup in the repository

RMAN> list backup for db_unique_name DGJAY;

List of Backup Set for database with db_unique_name DGJAY

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

BS Key Size Device Type Elapsed Time Completion Time

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

19026 202.44M DISK 00:00:40 25-JAN-13

BP Key: 19028 Status: AVAILABLE Compressed: NO Tag: TAG20111230T281237

Piece Name: /u01/bkup/JAY/ora_df771274960_s101_s1

List of Archived Logs in backup set 19026

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 291 3276590 25-JAN-13 3296594 25-JAN-13

1 292 3296594 25-JAN-13 3296906 25-JAN-13

1 293 3296906 25-JAN-13 3296910 25-JAN-13

1 294 3296910 25-JAN-13 3296967 25-JAN-13

1 295 3296967 25-JAN-13 3296970 25-JAN-13

1 296 3296970 25-JAN-13 3319957 25-JAN-13

1 297 3319957 25-JAN-13 3320270 25-JAN-13

1 298 3320270 25-JAN-13 3320796 25-JAN-13

1 299 3320796 25-JAN-13 3321375 25-JAN-13

1 300 3321375 25-JAN-13 3321819 25-JAN-13

1 301 3321819 25-JAN-13 3322168 25-JAN-13

1 302 3322168 25-JAN-13 3322594 25-JAN-13

1 303 3322594 25-JAN-13 3322952 25-JAN-13

1 304 3322952 25-JAN-13 3322956 25-JAN-13

1 305 3322956 25-JAN-13 3323380 25-JAN-13

1 306 3323380 25-JAN-13 3323762 25-JAN-13

1 307 3323762 25-JAN-13 3324350 25-JAN-13

1 308 3324350 25-JAN-13 3324354 25-JAN-13

1 309 3324354 25-JAN-13 3324693 25-JAN-13

1 310 3324693 25-JAN-13 3325131 25-JAN-13

1 311 3325131 25-JAN-13 3325635 25-JAN-13

1 312 3325635 25-JAN-13 3326576 25-JAN-13

1 313 3326576 25-JAN-13 3326969 25-JAN-13

1 314 3326969 25-JAN-13 3327379 25-JAN-13

1 315 3327379 25-JAN-13 3327679 25-JAN-13

1 316 3327679 25-JAN-13 3327683 25-JAN-13

1 317 3327683 25-JAN-13 3328074 25-JAN-13

1 318 3328074 25-JAN-13 3328423 25-JAN-13

1 319 3328423 25-JAN-13 3328888 25-JAN-13

1 320 3328888 25-JAN-13 3329819 25-JAN-13

1 321 3329819 25-JAN-13 3330168 25-JAN-13

1 322 3330168 25-JAN-13 3330706 25-JAN-13

1 323 3330706 25-JAN-13 3331224 25-JAN-13

1 324 3331224 25-JAN-13 3331632 25-JAN-13

1 325 3331632 25-JAN-13 3331987 25-JAN-13

1 326 3331987 25-JAN-13 3332367 25-JAN-13

1 327 3332367 25-JAN-13 3332768 25-JAN-13

1 328 3332768 25-JAN-13 3333160 25-JAN-13

1 329 3333160 25-JAN-13 3333599 25-JAN-13

1 330 3333599 25-JAN-13 3333961 25-JAN-13

1 331 3333961 25-JAN-13 3334379 25-JAN-13

1 332 3334379 25-JAN-13 3334757 25-JAN-13

1 333 3334757 25-JAN-13 3335100 25-JAN-13

1 334 3335100 25-JAN-13 3335656 25-JAN-13

1 335 3335656 25-JAN-13 3336049 25-JAN-13

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

19027 Full 729.26M DISK 00:02:48 25-JAN-13

BP Key: 19029 Status: AVAILABLE Compressed: NO Tag: TAG20111230T190325

Piece Name: /u01/bkup/JAY/ora_df771275005_s102_s1

List of Datafiles in backup set 19027

File LV Type Ckp SCN Ckp Time Name

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

1 Full 3276592 25-JAN-13 /u01/JAY/datafile/system.dbf

2 Full 3276592 25-JAN-13 /u01/JAY/datafile/sysaux.dbf

3 Full 3276592 25-JAN-13 /u01/JAY/datafile/undotbs1.dbf

4 Full 3276592 25-JAN-13 /u01/JAY/datafile/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

19083 Full 9.70M DISK 00:00:01 25-JAN-13

BP Key: 19088 Status: AVAILABLE Compressed: NO Tag: TAG20111230T190622

Piece Name: /u01/bkup/JAY/c-2467455158-20111230-03

SPFILE Included: Modification time: 25-JAN-13

SPFILE db_unique_name: DGJAY

Standby Control File Included: Ckp SCN: 3276592 Ckp time: 25-JAN-13

RMAN> catalog start with '/u01/bkup/JAY';

searching for all files that match the pattern /u01/bkup/JAY

List of Files Unknown to the Database

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

File Name: /u01/bkup/JAY/ora_df771274960_s101_s1

File Name: /u01/bkup/JAY/c-2467455158-20111230-03

File Name: /u01/bkup/JAY/ora_df771275005_s102_s1

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/bkup/JAY/ora_df771274960_s101_s1

File Name: /u01/bkup/JAY/c-2467455158-20111230-03

File Name: /u01/bkup/JAY/ora_df771275005_s102_s1

RMAN> restore datafile 4 from tag=TAG20111230T190325;

Starting restore at 25-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=14 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 00004 to /u01/JAY/datafile/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/bkup/JAY/ora_df771275005_s102_s1

channel ORA_DISK_1: piece handle=/u01/bkup/JAY/ora_df771275005_s102_s1 tag=TAG20111230T190325

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 25-JAN-13

RMAN> recover datafile 4;

Starting recover at 25-JAN-13

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 336 is already on disk as file /u01/archive/1_336_766348429.dbf

channel ORA_DISK_1: starting archived log restore to default destination