Find Registered Database From Recovery Catalog Schemas

C:\Users\rajib.pradhan>SQLPLUS /NOLOG

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 21 12:49:24 2014

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

SQL> CONN RMAN/RMAN@DBRMAN

Connected.

SQL> select * from rc_database;

    DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS

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

       221        222 1350456456 ORCL             390117985 17-JUL-14

         2          4 3796675194 DBRMAN              940976 16-JUN-14

SQL>

Create and search RMAN Scripts in Recovery Catalog

C:\Users\rajib.pradhan>SET ORACLE_SID=ORCL

C:\Users\rajib.pradhan>RMAN TARGET / CATALOG RMAN/RMAN@DBRMAN;

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Sep 21 12:47:05 2014

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

connected to target database: ORCL (DBID=1350456456)
connected to recovery catalog database

RMAN> CREATE SCRIPT SPFILE_BACKUP
2> {
3> BACKUP SPFILE;
4> }

created script SPFILE_BACKUP

RMAN> CREATE SCRIPT BACKUP_DATABASE_PLUS_ARCHIVE
2> {
3> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
4> }

created script BACKUP_DATABASE_PLUS_ARCHIVE

RMAN> EXIT


Recovery Manager complete.

C:\Users\rajib.pradhan>SQLPLUS /NOLOG

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 21 12:49:24 2014

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

SQL> CONN RMAN/RMAN@DBRMAN
Connected.
SQL> SELECT SCR_KEY, SCR_NAME
  2  FROM SCR;

   SCR_KEY
----------
SCR_NAME
--------------------------------------------------------------------------------
      1425
BACKUP_DATABASE_PLUS_ARCHIVE

      1424
SPFILE_BACKUP


SQL> SET LINE 2000
SQL> SELECT SCR_KEY, SCR_NAME
  2  FROM SCR;

   SCR_KEY SCR_NAME
---------- ----------------------------------------------------------------------------------------------------
      1425 BACKUP_DATABASE_PLUS_ARCHIVE
      1424 SPFILE_BACKUP

SQL> SELECT TEXT
  2  FROM SCRL
  3  WHERE SCR_KEY=1424;

TEXT
------------------------
{
BACKUP SPFILE;
}

SQL>

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

Cause: Oracle can't find your data file in your physical location cause of delete or lost your data file.

Solution : In this situation if you have RMAN backup you can use this solution and If you have not RMAN Backup then you can drop your data file information from control file using this (http://rajiboracle.blogspot.com/2014/03/ora-01157-cannot-identifylock-data-file.html) way. Drop data file will lost your all of data inside the data file. Drop data file is last option for recover your remaining data from database.


C:\Users\rajib.pradhan>SET ORACLE_SID=DBRMAN

C:\Users\rajib.pradhan>RMAN TARGET/

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 16 11:09:55 2014

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

connected to target database: DBRMAN (DBID=3796675194, not open)

RMAN> ALTER DATABASE OPEN;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/16/2014 11:12:12
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\APP\ORADATA\DBRMAN\USERS01.DBF'

RMAN> SHUTDOWN ABORT;

Oracle instance shut down

RMAN> STARTUP FORCE MOUNT;

Oracle instance started
database mounted

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes
Variable Size                406847952 bytes
Database Buffers             121634816 bytes
Redo Buffers                   5804032 bytes

RMAN> RESTORE DATABASE;

Starting restore at 16-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 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 D:\APP\ORADATA\DBRMAN\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\ORADATA\DBRMAN\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\ORADATA\DBRMAN\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\ORADATA\DBRMAN\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\APP\ORADATA\DBRMAN\CATALOG_SPC01.DBF
channel ORA_DISK_1: restoring datafile 00006 to D:\APP\ORADATA\DBRMAN\TBS_RAMN_CAT_01.DBF
channel ORA_DISK_1: restoring datafile 00010 to D:\APP\ORADATA\DBRMAN\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\ORADATA\DBRMAN_BACKUP\BKP_DFDBRMAN_858423127_94_1_2UPIKVQN
channel ORA_DISK_1: piece handle=D:\APP\ORADATA\DBRMAN_BACKUP\BKP_DFDBRMAN_858423127_94_1_2UPIKVQN tag=TAG20140916T105207
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:46
Finished restore at 16-SEP-14

RMAN> RECOVER DATABASE;

Starting recover at 16-SEP-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 169 is already on disk as file D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000169_0850433149.0001
archived log for thread 1 with sequence 170 is already on disk as file D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000170_0850433149.0001
archived log for thread 1 with sequence 171 is already on disk as file D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000171_0850433149.0001
archived log for thread 1 with sequence 172 is already on disk as file D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000172_0850433149.0001
archived log for thread 1 with sequence 173 is already on disk as file D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000173_0850433149.0001
archived log file name=D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000169_0850433149.0001 thread=1 sequence=169
archived log file name=D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000170_0850433149.0001 thread=1 sequence=170
archived log file name=D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000171_0850433149.0001 thread=1 sequence=171
media recovery complete, elapsed time: 00:00:27
Finished recover at 16-SEP-14

RMAN> ALTER DATABASE OPEN;

database opened

RMAN>

Update Serial Number In Table

CREATE TABLE TEST_DATA(ID NUMBER, VALUE_DATE DATE, SERIAL NUMBER);

SELECT ID, VALUE_DATE, SERIAL
FROM TEST_DATA
ORDER BY 1, 2,3;

SET DEFINE OFF;
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (3, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
   (ID, VALUE_DATE)
 Values
   (3, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;


BEGIN
   FOR S IN (SELECT DISTINCT ID SL FROM TEST_DATA)
   LOOP
      FOR I IN (SELECT ROWNUM SERIAL_NUM,
                       ID,
                       VALUE_DATE,
                       ROWID UPDATE_NUMBER
                  FROM (  SELECT ID, VALUE_DATE, ROWID
                            FROM TEST_DATA
                           WHERE ID = S.SL
                        ORDER BY 1, 2))
      LOOP
         UPDATE TEST_DATA
            SET SERIAL = I.SERIAL_NUM
          WHERE ROWID = I.UPDATE_NUMBER;
      END LOOP;
   END LOOP;
END;

SELECT ID, VALUE_DATE, SERIAL
FROM TEST_DATA
ORDER BY 1, 2,3;

SQL> CREATE TABLE TEST_DATA(ID NUMBER, VALUE_DATE DATE, SERIAL NUMBER);

Table created.

SQL>
SQL> SELECT ID, VALUE_DATE, SERIAL
  2  FROM TEST_DATA
  3  ORDER BY 1, 2,3;

no rows selected

SQL>
SQL> SET DEFINE OFF;
SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (3, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> Insert into TEST_DATA
  2     (ID, VALUE_DATE)
  3   Values
  4     (3, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> BEGIN
  2     FOR S IN (SELECT DISTINCT ID SL FROM TEST_DATA)
  3     LOOP
  4        FOR I IN (SELECT ROWNUM SERIAL_NUM,
  5                         ID,
  6                         VALUE_DATE,
  7                         ROWID UPDATE_NUMBER
  8                    FROM (  SELECT ID, VALUE_DATE, ROWID
  9                              FROM TEST_DATA
 10                             WHERE ID = S.SL
 11                          ORDER BY 1, 2))
 12        LOOP
 13           UPDATE TEST_DATA
 14              SET SERIAL = I.SERIAL_NUM
 15            WHERE ROWID = I.UPDATE_NUMBER;
 16        END LOOP;
 17     END LOOP;
 18  END;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT ID, VALUE_DATE, SERIAL
  2  FROM TEST_DATA
  3  ORDER BY 1, 2,3;

        ID VALUE_DAT     SERIAL
---------- --------- ----------
         1 01-SEP-14          1
         1 01-SEP-14          2
         1 01-SEP-14          3
         1 01-SEP-14          4
         1 02-SEP-14          5
         1 02-SEP-14          6
         2 01-SEP-14          1
         2 01-SEP-14          2
         2 03-SEP-14          3
         2 03-SEP-14          4
         3 01-SEP-14          1

        ID VALUE_DAT     SERIAL
---------- --------- ----------
         3 02-SEP-14          2

12 rows selected.

SQL>

How to set Client Information in V$SESSION

We can set client information (USER ID) Using DBMS_APPLICATION_INFO package. If we set this client information it is easy to trace client id using CLIENT_INFO from V$SESSION data dictionary view.



C:\Users\rajib.pradhan>SQLPLUS /NOLOG

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 15 18:26:30 2014

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

SQL> CONN RND/rnd@ORCL
Connected.
SQL> SELECT CLIENT_INFO, SID, SERIAL# FROM V$SESSION
  2  WHERE USERNAME='RND';

CLIENT_INFO                                                             SID
---------------------------------------------------------------- ----------
   SERIAL#
----------
                                                                        132
         8


SQL> SET LINE 2000
SQL> SELECT CLIENT_INFO, SID, SERIAL# FROM V$SESSION
  2  WHERE USERNAME='RND';

CLIENT_INFO                                                             SID    SERIAL#
---------------------------------------------------------------- ---------- ----------
                                                                        132          8

SQL> begin
  2  DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('RND_USER');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT CLIENT_INFO, SID, SERIAL# FROM V$SESSION
  2  WHERE USERNAME='RND';

CLIENT_INFO                                                             SID    SERIAL#
---------------------------------------------------------------- ---------- ----------
RND_USER                                                                132          8

SQL>

RMAN Tape Backup Script

RMAN Full Backup :- Using this scripts you can take full database backup.

RUN
 {
ALLOCATE CHANNEL OCH1 TYPE 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' FORMAT 'ora_d%d_T%T_%s_%p.bak';
BACKUP
INCREMENTAL LEVEL 0
DATABASE;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG ALL FORMAT 'RMAN_ARCHIVE_%d_%t_%s_%p_%U.arch' DELETE INPUT;
BACKUP CURRENT CONTROLFILE;
BACKUP SPFILE;
 }

RMAN Incremental Backup :- Using this scripts you can take Incremental database backup.

RUN
 {
ALLOCATE CHANNEL OCH1 TYPE 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' FORMAT 'ora_d%d_T%T_%s_%p.bak';
BACKUP
INCREMENTAL LEVEL 1
DATABASE;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG ALL FORMAT 'RMAN_ARCHIVE_%d_%t_%s_%p_%U.arch' DELETE INPUT;
BACKUP CURRENT CONTROLFILE;
BACKUP SPFILE;

 }

ORA-20005: object statistics are locked (stattype = ALL)

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'NUMBER_ARITHMETIC');
BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,'NUMBER_ARITHMETIC'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

SQL>

Cause:- Object statistic has been locked. 

Action: - First find object which object statistic locked and then unlock this object using DBMS_STATS package.


SQL> SELECT OWNER, TABLE_NAME, STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE OWNER=USER AND STATTYPE_LOCKED IS NOT NULL;

OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
RND                            NUMBER_ARITHMETIC              ALL

SQL>


SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS(USER,'NUMBER_ARITHMETIC');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'NUMBER_ARITHMETIC');

PL/SQL procedure successfully completed.

SQL>