Rman Duplicate (RAC 2-Node)… ASM (same storage/disk group)….

I would like to share the some of issues and things which we should concentrate while performing duplicate of production/development databases on same server and same shared storage. Most of the Administrators performed the task but still most of the upcoming dba’s didn’t get good hold of rman stuff. Just and lesson learning's.  I tried to play around on server as I was known with consequences (don’t play as I do – do take your management approval).

I have created initially static listener and tns entry for duplicate database which I intended to do build on same cluster.

### dup ####       
dup,dup.test.com =
  (DESCRIPTION =
   (LOAD_BALANCE=ON)
    (ADDRESS_LIST =
     (ADDRESS=(PROTOCOL=TCP)(HOST=node4-vip)(PORT=1536))
     (ADDRESS=(PROTOCOL=TCP)(HOST=node5-vip)(PORT=1536))
    )
   (CONNECT_DATA =
     (SERVICE_NAME = dup)
   )
)
### dup ####       

##########################################################
# listener_dup
##########################################################

listener_dup =
(ADDRESS_LIST=
  (ADDRESS=(PROTOCOL=tcp)
           (HOST=node4-vip.us.oracle.com)
           (QUEUESIZE=256)
           (PORT=1536)
  )
)
sid_list_listener_dup =
(SID_LIST =
  (SID_DESC= (GLOBAL_DBNAME = dup.test.com)
             (SID_NAME=dup1)
             (SDU=32768)
             (ORACLE_HOME =/u01/app/oracle/product/dup )
  )
)

########################################################

At first glance, I tot the entries are good and didn’t have any issue and then I continued with task of rman duplicate. Next step we need to create initORACLE_SID.ora file to start the instance in nomount mode.

DB_NAME=DUP
DB_UNIQUE_NAME=DUP
DB_FILE_NAME_CONVERT='+DATA/PROD','+DATA/DUP','+RECO/PROD','+RECO/DUP'
LOG_FILE_NAME_CONVERT='+DATA/PROD','+DATA/DUP','+RECO/PROD','+RECO/DUP'
sga_target=18G
pga_aggregate_target=4G
diagnostic_dest=/u02/diag/dup

I have started the instance and continued with rman duplicate (Note- I’m using the 11203 binaries)

rman target sys/sys1@prod auxiliary /

You will face any issue as below

Starting Duplicate Db at 22-MAY-13
released channel: prim1
released channel: dup1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/22/2013 02:21:15
RMAN-05501: aborting duplication of target database
RMAN-06217: not connected to auxiliary database with a net service name

Never try to connect to auxiliary instance as Host Authentication you will face an issue. Next continued with below

rman targetsys/sys1@prod auxiliary sys/sys1@dup (Note –Copy the password file of production,else it won’t work)

Now this time it failed with no listener issue. Now, I have reviewed my tnsnames.ora and listener.ora (bounced the listener) many times, still issue persist. Couldn’t able figure out the issue/cause. Then if you carefully observe,

service_name and global_dbname in listener.ora file and tnsnames.ora both mis-matches with domain name, which caused me to pause my work for a while. (To be honest, I made my entries to upper case and made sure that both matches then I was able to proceed further.

Now, next I have resolved my connections issue and continued with task, and next issue continues

Errors in file /u02/oraout/dup/diag/rdbms/dup/dup1/trace/dup1_ora_18406.trc:
ORA-19505: failed to identify file "+DATA/dup/controlfile/control_01.ctl"
ORA-17502: ksfdcre:3 Failed to create file +DATA/dup/controlfile/control_01.ctl
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15204: database version 11.2.0.0.0 is incompatible with diskgroup DATA

As my compatible parameter maps to lower version (perhaps I didn’t placed any parameter in my auxiliary init.ora file), in order to resolve this I modified my initialization parameter with “compatible=11.2.0.3.0” and continued with task.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/22/2013 03:01:48
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name +DATA/admin/prod/db/test_02.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name +DATA/admin/prod/db/users_06.dbf conflicts with a file used by the target database

Now, the cause of this issue was, I missed one of the entry to place (which production datafiles get maps to) an entry in db_file_name_convert, due to which the task was aborted.

Note – In case if any one missed out the entries to place the in parameters (db_file_name_convert or log_file_name_convert), rman takes care of aborting the process, instead of overriding /corrupting production data files.

Now, I have updated my db_file_name_convert parameter as below

DB_FILE_NAME_CONVERT='+DATA/PROD','+DATA/DUP','+RECO/PROD','+RECO_UBA/DUP','+DATA/admin/prod','+DATA/admin/dup'

 

RMAN> run {
2> allocate channel prim1 device type disk;
3> allocate auxiliary channel dup1 device type disk;
4> duplicate database to dup from active database;
5> }

Let ‘s  me provide the actual process of duplication,

Task 1 = (Spfile and control file)
    Rebounce of instance (opens in mount mode)
    set’s db_name and db_unique_name to spfile
    Rebounce of instance (opens in mount mode)
    Take’s backup of controlfile from production
    Now mount the database with controlfile.

 

Task 2 = Data file copy

    set newname for datafile n (setting new location in current rman session)
    backup as copy reuse datafile  1 auxiliary format
    Note - AS COPY --performing image copy of datafile
    So, it copies from prod === dup over network(via.. sqlnet)
    sql 'alter system archive log current';
    Note - Last Step making sure that, we are making/recovering our db till the last Archived Online Redo log file.

Task 3 =

    Archive Files Copy and re-using
    backup as copy reuse
       archivelog like  "+RECO/prod/archivelog/2013_05_22/thread_2_seq_248.126795.816051805"
       auxiliary format "/u01/app/oracle/product/dup/dbs/arch2_248_809881982.dbf" 
       .....
       .....
       ...
       catalog clone archivelog "/u01/app/oracle/product/dup/dbs/arch2_248_809881982.dbf" (updating information to controlfile)
    switch clone datafile all;

Task 4 = Last task is recovery

    set until scn  13239404328241;
       recover
       clone database
        delete archivelog ;

    archived log for thread 1 with sequence 303 is already on disk as file /u01/app/oracle/product/dup/dbs/arch1_303_809881982.dbf
    archived log for thread 2 with sequence 249 is already on disk as file /u01/app/oracle/product/dup/dbs/arch2_249_809881982.dbf
    archived log file name=/u01/app/oracle/product/dup/dbs/arch1_303_809881982.dbf thread=1 sequence=303
    archived log file name=/u01/app/oracle/product/dup/dbs/arch2_249_809881982.dbf thread=2 sequence=249
    media recovery complete, elapsed time: 00:00:02
    Finished recover at 22-MAY-13
    Oracle instance started

Note - we can controlfile the archive log generation by using parameter "db_recovery_file_dest" or "log_archive_dest_%' else it maps to %ORACLE_HOME/dbs" folder, that's what we have seen here.

Further, I would like to show the importance of alert log monitoring, if you see the below messages

Reconfiguration complete
ORACLE_BASE from environment = /u01/app/oracle
Wed May 22 03:20:07 2013
alter database mount
Set as converted control file due to db_unique_name mismatch
Changing di2dbun from prod to DUP
Successful mount of redo thread 1, with mount id 1039698535
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
Wed May 22 03:20:17 2013
Starting background process ASMB

If you try to understand, due the process/execution of  Task –1, the Oracle is setting the db_name and db_unique_name @rman level, same mapped/written to alert log, perhaps the difference of communication to alert log is informative. So, do monitor alert log and traces files during task/execution.

I hope it the post helps to visualize things in different way…!!

Let me know your comments and things which you would like to see in upcoming posts.

V$Log and V$LogFile vs GV$Log and GV$logfile…

Today, I will discuss about the basic difference about the gv$log and v$log views, which was known by most of the experts who are working on Oracle from past decades. It’s an just different view/presentation of information how we can look at the stuff. Coming back to the topic, v$log  and v$logfile provides the information about the Online Redo log file information. If you check carefully v$log view provides the information about the thread#, that is the information about other nodes of RAC.

If you check the base table of the V$LOG – it fetches from GV$LOG further – maps to x$kccle le and x$kccrt

 

GV$LOG                         select le.inst_id, le.lenum, le.lethr, le.leseq, le.lesiz*le.lebsz, le.lebsz, ledup, decode(bitand(l
                               e.leflg,1),0,'NO','YES'), decode(bitand(le.leflg,24), 8, 'CURRENT',                            16,'C
                               LEARING',                            24,'CLEARING_CURRENT',        decode(sign(leseq),0,'UNUSED',
                                    decode(sign((to_number(rt.rtckp_scn)-to_number(le.lenxs))*        bitand(rt.rtsta,2)*(64-bitand
                               (rtsta, 64))),        -1,'ACTIVE','INACTIVE'))), to_number(le.lelos), to_date(le.lelot,'MM/DD/RR HH2
                               4:MI:SS','NLS_CALENDAR=Gregorian'), to_number(le.lenxs), to_date(le.lenxt,'MM/DD/RR HH24:MI:SS','NLS
                               _CALENDAR=Gregorian')  from x$kccle le, x$kccrt rt where le.ledup!=0 and le.lethr=rt.rtnum and  le.i
                               nst_id = rt.inst_id

 

V$LOG                          select   GROUP# , THREAD# , SEQUENCE# , BYTES , BLOCKSIZE, MEMBERS , ARCHIVED , STATUS , FIRST_CHANG
                               E# , FIRST_TIME, NEXT_CHANGE#, NEXT_TIME from GV$LOG where inst_id = USERENV('Instance')

GV$LOGFILE                     select inst_id,fnfno, decode(fnflg,0,'',   decode(bitand(fnflg,1),1,'INVALID',   decode(bitand(fnflg
                               ,2),2,'STALE',   decode(bitand(fnflg,4),4,'DELETED',   decode(bitand(fnflg,8+32),8,'',32,'',40,'','U
                               NKNOWN'))))), decode(bitand(fnflg,8),0,'ONLINE','STANDBY'), fnnam, decode(bitand(fnflg, 32),0,'NO','
                               YES') from x$kccfn where fnnam is not null and fntyp=3

 

V$LOGFILE                      select  GROUP# , STATUS , TYPE , MEMBER, IS_RECOVERY_DEST_FILE from GV$LOGFILE where inst_id = USERE
                               NV('Instance')

From the above queries we check the table x$kccfn – it provides all the information files are maintained across in database.

SYS@test> select count(*),FNTYP from x$kccfn group by FNTYP;

  COUNT(*)      FNTYP
---------- ----------
        63          4  --data files
        32          3    -- log files
         1          7  -- temp file
         1          0  -- snapshot

So, if we verify v$logfile and GV$logfile both refers to same information perhaps, gv$log provides the duplex information as below

SYS@test> select count(*) from v$logfile;

  COUNT(*)
----------
        32
SYS@test>  select count(*) from gv$logfile;

  COUNT(*)
----------
        64

Let me execute the base query and verify across the result set with above output

SYS@test> select count(*)
                               from x$kccfn
                               where fnnam is not null
                               and fntyp=3  2    3    4  ;

  COUNT(*)
----------
        32

The query works fine, then how come the result from gv$logfile is misleading (duplexes). Reason for that is, when we execute the query with gv$views query, it would get executed on other instances as well to get the sum up of data,due to that the output is misleading. When coming to GV$LOG, it uses x$kccle le and x$kccrt

x$kccle ---provides information about the logfile entries

x$kccrt – provides information about the redo thread

First instance
================
SYS@test1> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +RECO
Oldest online log sequence     59
Next log sequence to archive   66
Current log sequence           66

Second Instance
===============
SYS@test2>  archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +RECO
Oldest online log sequence     48
Next log sequence to archive   55
Current log sequence           55

   THREAD#     GROUP#  SEQUENCE# STATUS     MEMBER
---------- ---------- ---------- ---------- ------------------------------------------------------------
         2         15         55 CURRENT    +DATA/test/onlinelog/group_15.1269.815360505
         2         15         55 CURRENT    +RECO/test/onlinelog/group_15.18794.815360507
         1         12         66 CURRENT    +DATA/test/onlinelog/group_12.1279.815362377
         1         12         66 CURRENT    +RECO/test/onlinelog/group_12.16544.815362379

let’s try to fetch same details from x$kccrt

   INST_ID      RTNUM      RTSTA RTCKP_SCN                                        RTCKP_RBA_SEQ RTCKP_RBA_BNO RTCKP_RBA_BOF      RTLFH      RTLFT RTSID
---------- ---------- ---------- ------------------------------------------------ ------------- ------------- ------------- ---------- ---------- ----------
         1          1        527 13219244105416                                              66             2            16          1         13 test1
         1          2        527 13219244105429                                              55             2            16          5         17 test2

If we verify the column RTCKP_RBA_SEQ – it maps to archive log list and v$log (current) status record. But still question stands why we find the discrepancy when we query GV$LOG. If we try to understand the query “from” clause

from x$kccle le, x$kccrt rt where le.ledup!=0 and le.lethr=rt.rtnum and  le.inst_id = rt.inst_id

Both tables doesn’t have any column to map or distinguish records or data across the instance. Both tables hold database wide information (logical container). So, the query executes on both nodes with dynamic behavior of x$tables that would get result into duplex records.

SYS@test> select distinct INST_ID from x$kccfn group by INST_ID;

   INST_ID
----------
         2

SYS@test> select distinct INST_ID from x$kccfn group by INST_ID;

   INST_ID
----------
         1

If you observe I have executed same query on both nodes and resulted into different results and that explains the things of duplex records and misleading output from GV$log and GV$logfile views.

I hope it finds useful …!!

RMAN List syntax/command….

Many of the upcoming dba’s are looking for troubleshooting / understanding the internals of rman. But if we visualize the things in different way, they are missing to go through fine manuals of Oracle Documentation. I’m sure how many does know the full syntax of LIST Command in rman.

Reference:- List Command Syntax

Main Syntax
============

LIST {DB_UNIQUE_NAME {ALL | OF DATABASE [['] database_name [']]} | EXPIRED {listObjectSpec [ { {maintQualifier | recoverableClause}}...] | recordSpec} [forDbUniqueNameOption] | FAILURE [ { { {ALL | CRITICAL | HIGH | LOW | failureNumber [, failureNumber]...} | CLOSED}}...] [EXCLUDE FAILURE failureNumber [, failureNumber]...] [DETAIL] | INCARNATION [OF DATABASE [['] database_name [']]] | { {listObjectSpec [ { {maintQualifier | recoverableClause}}...] | recordSpec} | RESTORE POINT restore_point_name | RESTORE POINT ALL} [forDbUniqueNameOption] | [ {ALL | GLOBAL}] SCRIPT NAMES}

I have sub-divided the above syntax to below deduced form, which enable to understand things in easiest way.

LIST DB_UNIQUE_NAME
     ALL  (or)
     OF DATABASE db_name (or)
     EXPIRED {listObjectSpec [ { {maintQualifier | recoverableClause}}...] | recordSpec}
     ...
        expired listObjectSpec
        expired listObjectSpec maintQualifier
        expired listObjectSpec recoverableClause
        expired recordSpec

List    listObjectSpec
            listObjectSpec maintQualifier
            listObjectSpec recoverableClause
            recordSpec
list    FAILURE [ { { {ALL | CRITICAL | HIGH | LOW | failureNumber [, failureNumber]...} | CLOSED}}...]
list   { {listObjectSpec [ { {maintQualifier | recoverableClause}}...]  |  recordSpec      

(listObjectSpec::=, recordSpec::=, maintQualifier::=, forDbUniqueNameOption::=, untilClause::=)

                listObjectSpec

                (listObjList::=, listBackupOption::=, archivelogRecordSpecifier::=, foreignlogRecordSpecifier::=)

        List Object List
        ****************
        list archivelogRecordSpecifier
                archivelogRecordSpecifier
                =========================
                archivelog all
                archivelog range like 'string_pattern' thread integer
                       range options
                       =============       
                       from time t1
                       from time t1 until time t2
                       until time t2
                       from scn s1
                       from scn s1 until scn s2
                       until scn s2
                       from sequence q1
                       from sequence q1 until sequence q2
                       until sequence q2
                       incarnation all
                       incarnation current
                       incarnation i
                archivelog like 'string_pattern'
        list controlfile
        list database skip tablespace
        list dbObject
                ========
            Database
            Datafile 1
            datafile 'path'
            tablespace 'tablespace_name'

        list     foreignlogRecordSpecifier
            =========================
            all
            like ...similar to above options of archivelog           

        list spfile           

        List Backup Option
        ******************
        by file
        by summary

Main qualifier
================

completed after 'date string'
completed before 'date string'
completed between 'date string' and 'date string'

device type disk
device type tape

like 'string pattern'
backed up integer1 times to device type device_specifier
tag 'tag_name'
copy like '/disk2/%';

Record Specification
===================

archivelog
backupset
backuppiece
proxy
controlfilecopy
        tag
datafilecopy
    all
     * noduplicates
until clause
=============

until scn integer
until sequence integer
until sequence integer thread thread_number
until time 'date_string'

Backup Option
==============

by file
by summary

DB Object
=========

Database
Datafile 1
datafile 'path'
tablespace 'tablespace_name'

I hope it’s useful for dba’s  to understand the importance of Oracle Manual’s and their reference. I hope the post is useful.

ORA-15242: could not set attribute PHYS_META_REPLICATED

It’s been an busy day testing and learning things as usual apart from my work schedule. Today, I will discuss about issue while I was restoring the DISK GROUP which was missing. Please have a look at the error information

ASMCMD [+] > md_restore -b /u01/app/oracle/pre_backup/data_dg_bkp --full -G 'DATA'
Current Diskgroup metadata being restored: DATA
Diskgroup DATA created!
ORA-15032: not all alterations performed
ORA-15242: could not set attribute PHYS_META_REPLICATED
ORA-15241: attribute phys_meta_replicated is read only (DBD ERROR: OCIStmtExecute)
ASMCMD-9360: ADD or ALTER ATTRIBUTE failed
ORA-15032: not all alterations performed
ORA-15242: could not set attribute PHYS_META_REPLICATED
ORA-15241: attribute phys_meta_replicated is read only (DBD ERROR: OCIStmtExecute)

If you observe carefully i was trying to restore the backup (metadata of ASM Diskgroup DATA from backup file “/u01/app/oracle/pre_backup/data_dg_bkp”). The ASM was about to create the Parent Disk group but not the sub-folders.

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     21942    21874                0           21874              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     14628    12208                0           12208              0             N  FRA/

When you face such issues, let see how to resolve. Current Oracle Instance which I dealt is “ORCL”. So, I have created an folder under “DATA”, further continued with recovery.

RMAN> set dbid 1342306480

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 10-MAY-13
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/10/2013 11:49:43
RMAN-12010: automatic channel allocation initialization failed
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

I need to start(allocate shared memory) instance prior to restoring spfile, perhaps I don’t have backup copy of spfile (we couldn’t able to restore spfile inspite of existing backups @ASM level, since only control file knows about the backup information – pls have a look at below)

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora'  --we can able get location of spfile
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-15173: entry 'spfileorcl.ora' does not exist in directory 'orcl'
ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile

RMAN> restore spfile from autobackup;

Starting restore at 10-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=172 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130510
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130509
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130508
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130507
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130506
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130505
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130504
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/10/2013 11:50:56
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Try to create an pfile by taking parameters from alert log and start the instance as below

startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'

Once we have started the instance we need to recover the Control file which is having the information about the backups as below

 

RMAN> restore controlfile from autobackup;

Starting restore at 10-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK

recovery area destination: +FRA
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +FRA/ORCL/AUTOBACKUP/2013_05_10/s_815050063.280.815050069 found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP +FRA/ORCL/AUTOBACKUP/2013_05_10/s_815050063.280.815050069
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATA/ORCL/CONTROLFILE/current.256.815054773
output file name=+FRA/ORCL/CONTROLFILE/control02.ctl
Finished restore at 10-MAY-13

Once we have restored control file then proceed with restoring spfile  as below

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> list backup of spfile;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    9.64M      DISK        00:00:27     10-MAY-13      
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20130510T093419
        Piece Name: +FRA/ORCL/AUTOBACKUP/2013_05_10/s_815045668.273.815045681
  SPFILE Included: Modification time: 10-MAY-13
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16      Full    9.64M      DISK        00:00:03     10-MAY-13      
        BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: TAG20130510T093939
        Piece Name: +FRA/ORCL/AUTOBACKUP/2013_05_10/s_815045979.269.815045981
  SPFILE Included: Modification time: 10-MAY-13
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20      Full    9.64M      DISK        00:00:06     10-MAY-13      
        BP Key: 20   Status: AVAILABLE  Compressed: NO  Tag: TAG20130510T094524
        Piece Name: +FRA/ORCL/AUTOBACKUP/2013_05_10/s_815046325.266.815046329
  SPFILE Included: Modification time: 10-MAY-13
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21      Full    9.64M      DISK        00:00:09     10-MAY-13      
        BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20130510T094646
        Piece Name: +FRA/ORCL/AUTOBACKUP/2013_05_10/s_815046407.272.815046413
  SPFILE Included: Modification time: 10-MAY-13
  SPFILE db_unique_name: ORCL

RMAN> restore spfile from autobackup;

Starting restore at 10-MAY-13
Starting implicit crosscheck backup at 10-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
Crosschecked 14 objects
Finished implicit crosscheck backup at 10-MAY-13

Starting implicit crosscheck copy at 10-MAY-13
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 10-MAY-13

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +FRA/ORCL/AUTOBACKUP/2013_05_10/s_815050063.280.815050069
File Name: +FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_61.281.815050525
File Name: +FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_62.282.815050525
File Name: +FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_63.283.815050529

using channel ORA_DISK_1

recovery area destination: +FRA
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +FRA/ORCL/AUTOBACKUP/2013_05_10/s_815050063.280.815050069 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130510
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +FRA/ORCL/AUTOBACKUP/2013_05_10/s_815050063.280.815050069
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 10-MAY-13

 Note – we can use below specific command to restore to spfile to specific location

restore spfile to '+DATA/orcl/spfileorcl.ora' from autobackup.

Once, we have spfile and controlfiles, then we can rebounce the instance and would continue with recovery of database. Pls find the output of recovery steps below.

RMAN> restore database;

Starting restore at 10-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK

skipping datafile 5; already restored to file /u01/backup/orcl/bartbs.dbf
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 00003 to +DATA/ORCL/DATAFILE/sysaux.256.814525525
channel ORA_DISK_1: reading from backup piece /u01/backup/orcl/12o997gn_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/10/2013 12:18:18
ORA-19870: error while restoring backup piece /u01/backup/orcl/12o997gn_1_1
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

I have forgotten to specify that my backups are encrypted, so we can open the wallet and set the decrypt password to restore the database from backups.

RMAN> alter system set encryption wallet open identified by "oracle";

Starting restore at 10-MAY-13
using channel ORA_DISK_1

skipping datafile 5; already restored to file /u01/backup/orcl/test123.dbf
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 00003 to +DATA/ORCL/DATAFILE/sysaux.256.814525525
channel ORA_DISK_1: reading from backup piece /u01/backup/orcl/12o997gn_1_1
channel ORA_DISK_1: piece handle=/u01/backup/orcl/12o997gn_1_1 tag=TRANSPARENT
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:46
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/ORCL/DATAFILE/system.257.814525599
channel ORA_DISK_1: restoring datafile 00002 to +DATA/ORCL/DATAFILE/example.269.814525837
channel ORA_DISK_1: restoring datafile 00004 to +DATA/ORCL/DATAFILE/undotbs1.259.814525677
channel ORA_DISK_1: reading from backup piece /u01/backup/orcl/13o997j2_1_1
channel ORA_DISK_1: piece handle=/u01/backup/orcl/13o997j2_1_1 tag=TRANSPARENT
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
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 00006 to +DATA/ORCL/DATAFILE/users.258.814525675
channel ORA_DISK_1: reading from backup piece /u01/backup/orcl/14o997le_1_1
channel ORA_DISK_1: piece handle=/u01/backup/orcl/14o997le_1_1 tag=TRANSPARENT
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 10-MAY-13

RMAN> recover database;

Starting recover at 10-MAY-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 56 is already on disk as file +FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_56.267.815046393
archived log for thread 1 with sequence 57 is already on disk as file +FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_57.268.815047055
archived log for thread 1 with sequence 58 is already on disk as file +FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_58.276.815049665
archived log for thread 1 with sequence 59 is already on disk as file +FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_59.256.815050041
archived log for thread 1 with sequence 60 is already on disk as file +FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_60.278.815050053
archived log for thread 1 with sequence 61 is already on disk as file +FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_61.281.815050525
archived log for thread 1 with sequence 62 is already on disk as file +FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_62.282.815050525
archived log for thread 1 with sequence 63 is already on disk as file +FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_63.283.815050529
archived log for thread 1 with sequence 64 is already on disk as file +FRA/ORCL/ONLINELOG/group_1.258.815041099
archived log file name=+FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_56.267.815046393 thread=1 sequence=56
archived log file name=+FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_57.268.815047055 thread=1 sequence=57
archived log file name=+FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_58.276.815049665 thread=1 sequence=58
archived log file name=+FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_59.256.815050041 thread=1 sequence=59
archived log file name=+FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_60.278.815050053 thread=1 sequence=60
archived log file name=+FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_61.281.815050525 thread=1 sequence=61
archived log file name=+FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_62.282.815050525 thread=1 sequence=62
archived log file name=+FRA/ORCL/ARCHIVELOG/2013_05_10/thread_1_seq_63.283.815050529 thread=1 sequence=63
archived log file name=+FRA/ORCL/ONLINELOG/group_1.258.815041099 thread=1 sequence=64
media recovery complete, elapsed time: 00:00:24
Finished recover at 10-MAY-13

Finally my Oracle database came online and opened the database with “Reset logs” Successfully.

I hope the demo provided some helpful tips in order to recover your databases while you dealt with ASM.

ORA-17628: Oracle error 19505 returned by remote Oracle server

One of colleague was performing an rman cloning and came across issue as stated in subject of post. At an quick glance even, it didn’t went into my brain (got stuck to understand), what might went wrong. I have verified the below things at first instance.

Does orapwd$ORACLE_SID – are in sync with source and destination. ?

Can I able to login to and fro from source to destination as sysdba ?

Does path referred in ASM (db_file_name_convert and log_file_name_convert) exists correct or not ?

Certainly after verifying every thing, just saw that while referring to ASM location in db_file_name_convert and “+” is missing.  If I checked/verify using oerr

$ oerr ora 17628
17628, 00000, "Oracle error %s returned by remote Oracle server"
// *Cause:  Oracle server on the remote instance has returned an error.
// *Action: Look at remote instance alert log/trace file for more information
//          and take appropriate action or contact Oracle Support Services
//          for further assistance

So, the remote instance couldn’t able copy from source to destination, so either case in case of non-asm we might have issues with permissions, perhaps in my case it’s +ASM, so permissions issue are out-of scope.

When we tried to look after the “db_file_name_convert” – path referred is missing with an “+” (this was issue which caused the rman to fail).

Once we rectified the cloning of database went fine and completed successfully.

Hope this post is useful for some one facing with same kind of issue.

ORA-01012: not logged on ……!!

Technorati Tags: sysresv,ora-01012

Today, while I was shutting down one of Oracle RAC Databases instances, I came across this issue ORA-01012: not logged on. Let me explain the tiny background of this issue, how does it arouse, during the work. As I was busy with work and in parallel while discussing with my colleague, I pressed CTRL+C  after issuing “shutdown database” through srvctl, which lead this. Then I tried to start the database via SRVCTL, I ended with below alert logs messages.

Instance shutdown cancelled
Thu Apr 18 05:35:25 2013
License high water mark = 15
USER (ospid: 26519): terminating the instance
Instance terminated by USER, pid = 26519.

From console, I see below informative message,

starting up instance, please be patient, this takes some time......
PRCR-1013 : Failed to start resource ora.test.db
PRCR-1064 : Failed to start resource ora.test.db on node test003
CRS-5017: The resource action "ora.test.db start" encountered the following error:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.3/grid/log/test003/agent/crsd/oraagent_oracle/oraagent_oracle.log".

Now, I thought to verify the semaphores and shared segments where they still persist or not and further I saw oracle process which are alive. Here I go

test0003:oracle> (test:11.2.0.3_test) /u01/app/oracle/admin/test/bin
$ sysresv

IPC Resources for ORACLE_SID "test" :
Shared Memory:
ID              KEY
20217874        0x00000000
20250643        0x00000000
20283412        0x00000000
20316181        0x00000000
20348950        0x00000000
20381719        0x00000000
20414488        0x00000000
20447257        0x00000000
20480026        0x00000000
20512795        0x00000000
20545564        0x00000000
20578333        0x713b9410
Semaphores:
ID              KEY
5668870         0x926d2ee8
Oracle Instance alive for sid "test"

I have cleaned up the existing process manually and further the instance came with out any issues. It’s looks weird when error message states that “Not logged on” …. logically at first instance we might think the existing sqlplus session is aborted (or) didn’t getting interact IPC call to server process.  Some times, we can try in such situations to retry with “shutdown abort” so that existing process would get cleaned that helps to resolve the issue.

I hope it would helpful to some one who would experiencing similar kind of issue..!!