RMAN Restore from Consistent and Inconsistent BACKUP

RMAN Restore from Inconsistent BACKUP

# start of script

# change DBID  ###

# change NB_ORA_CLASS as appropriate ###

# change set option as appropriate  ###

set echo on;

host 'echo Starting restore at $(date).';

set DBID = DBID;

startup nomount;

run {

  allocate channel t1 type sbt

    parms='ENV=(NB_ORA_CLASS=RMAN_Tier3)';

  allocate channel t2 type sbt

    parms='ENV=(NB_ORA_CLASS=RMAN_Tier3)';

# restore controlfile from autobackup maxseq 0;

# restore controlfile from 'c-1232717171-040130-00';

# restore controlfile from ‘/bkup/oracle/backup_controlfile.1223’

  restore controlfile from autobackup;

  alter database mount;

# set until scn 1239965260;

# set until time "to_date('01-28-2004 11:40:54','mm-dd-yyyy hh24:mi:ss')";

  restore database;

  recover database;

  alter database open resetlogs;

}

host 'echo Ending restore at $(date).';

# end of script

RMAN Restore from Consistent BACKUP

# start of script

# change DBID ##

# change NB_ORA_CLASS as appropriate  ###

set echo on;

host 'echo Starting restore at $(date).';

set DBID = DBID;

startup nomount;

run {

  allocate channel t1 type sbt

    parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';

  allocate channel t2 type sbt

    parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';

  set command id to 'rman restore';

#  in noarchivelog mode, no set means most current backup.

# set until scn 562449965260;

# set until time "to_date('01-30-2004 13:00:00','mm-dd-yyyy hh24:mi:ss')";

# restore controlfile from autobackup maxseq 0;

# restore controlfile from 'c-1232717171-040130-00';

# restore controlfile from ‘/bkup/oracle/backup_controlfile.1223’

  restore controlfile from autobackup;

  alter database mount;

  restore database;

  recover database noredo;

  alter database open resetlogs;

}

host 'echo Ending restore at $(date).';

# end of script

Hope this helps!  Rupam

RMAN-06207 RMAN-06208 RMAN-06214 - backup standby controlfile is missing from standby server/database

Symptoms

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     676    15-NOV-11          /ora01/oracle/admin/BACKUP/cisdbc3/cisdbc3_controlfile_bak_11-15-11_20:02:56

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   /ora01/oracle/admin/BACKUP/cisdbc3/cisdbc3_controlfile_bak_11-15-11_20:02:56

 

Cause

rman delete fails due to mismatched status of backup pieces

In this case,  backup standby controlfile is missing from standby server/database

Solution

Step 1 thru 5

Step 1

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

Report of obsolete backups and copies

Type                 Key    Completion Time    Filename/Handle

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

Control File Copy     676    15-NOV-11          /ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56

Step 2

RMAN> crosscheck copy of controlfile;

validation failed for control file copy

control file copy filename=/ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56 recid=676 stamp=767304177

Crosschecked 1 objects

Step 3

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

Deleting the following obsolete backups and copies:

Type                 Key    Completion Time    Filename/Handle

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

Control File Copy     676    15-NOV-11          /ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56

deleted control file copy

control file copy filename=/ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56 recid=676 stamp=767304177

Deleted 1 objects

Step 4

RMAN>  crosscheck copy of controlfile;

Step 5

RMAN>  report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

no obsolete backups found

RMAN>

Hope this helps!Rupam

What is causing account lock

 -- check profile

col PROFILE format a10

col LIMIT format a15

select * from dba_profiles where resource_type='PASSWORD' order by 1,2;

-- options for trouble shooting

1. audit not successful connections

2. trace 1017 event

-- Steps for option 1

Reference: oracle doc # 352389.1

Reference:   oracle doc #  221944.1

-- setup audit

sqlplus / as sysdba

AUDIT CREATE SESSION BY ACCESS WHENEVER NOT SUCCESSFUL;

AUDIT CONNECT BY ACCESS WHENEVER NOT SUCCESSFUL;

-- Get information related to failed connections

col USERID format a10

col USERHOST format a25

col TERMINAL format a10

select returncode, action#, userid, userhost, terminal from aud$ where returncode='1017' and action#=100;

SELECT TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE   from aud$ where returncode=1017;

NOTE : TIME is in GMT, so subtract -5 hours

-- Steps for option 2

-- to trace what is locking the account

1.       Login to sqlplus as sysdba

2.       enable trace, using

                   alter system set events ‘1017 trace name errorstack level 10’;

3.       Show parameter dump

4.       Goto the udump location

5.       grep ORA – 01017 *

6.       get the timestamp and related information  from trace generated

7.       When trace is no more needed , set it off using:

              alter system set events ‘1017 trace name errorstack off’

8.       Goto the listener.log file location

9.       Get the connectivity information for the selected time from listener log file.

Hope this help. Regards Rupam

Image/PDF data via DML

 

1.  Upload the mybook.pdf to IMAGES folder /tmp

2. Create directory in the database using Create directory images as ‘/tmp/’;

3.  Run this DML

DECLARE

    f_lob BFILE;

    b_lob BLOB;

BEGIN

    INSERT INTO dummy_table (BILLING_ID,

                                      BILLING_RUN_ID,

                                      BILLING_INFO_ID,

                                      STMT_DATA,

                                      CREATED_DATE)

    VALUES (myseqeunce.NEXTVAL,

            108,

            26753,

            EMPTY_BLOB (),

            TO_DATE ('31-AUG-2011'))

    RETURN STMT_DATA

    INTO   b_lob;

    f_lob := BFILENAME ('IMAGES', 'mybook.pdf');

    DBMS_LOB.fileopen (f_lob, DBMS_LOB.file_readonly);

    DBMS_LOB.loadfromfile (b_lob, f_lob, DBMS_LOB.getlength (f_lob));

    DBMS_LOB.fileclose (f_lob);

    COMMIT;

END;

Hope this help. Regards Rupam

Use of Message Broker

 Testing the use of Message Broker to the database and then to an object in another database via a database link. They received an ORA-24777 error. Metalink reveals this note:

Error "ORA-24777: Use Of Non-Migratable Database Link Not Allowed" Using Oracle XA Datasource When Executing a Select via Database Link. [ID 879543.1]

Which recommends configuring shared servers for network connections. To test this, I made the following changes in the spfile for database db1:

ALTER SYSTEM SET shared_servers=5 SCOPE=BOTH SID='*';

ALTER SYSTEM SET dispatchers='(PROTOCOL=TCP)' SCOPE=BOTH SID='*';

The listener now shows a shared server listener available for db1:

oracle@tiger  > lsnrctl services

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 11-OCT-2011 15:13:31

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

..snipped..

Service "db1" has 1 instance(s).

  Instance "db1", status READY, has 2 handler(s) for this service...

    Handler(s):

      "D000" established:592 refused:0 current:9 max:1022 state:ready

         DISPATCHER

         (ADDRESS=(PROTOCOL=tcp)(HOST=tiger)(PORT=23033))

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

developed a small test using Message Broker connecting to db1 and referencing an object in db2 via a database link. It worked. They did not get the ORA-24777 error and they did get the expected results.

Hope this help. Regards Rupam