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

swap area in database server

The command to see what’s available is:

free –m This will show you the free space in megabytes

Here’s the output:

             total       used       free     shared    buffers     cached
Mem:         48299      26358      21940          0        647      19719
-/+ buffers/cache:       5992      42306
Swap:         4095          0       4095

So, the swap line shows you that you have 4 gig of swap free…excellent.

For the amount of free memory, you want to look at the -/+ buffers/cache line. So, looking at that line, you have 21940 megabytes (21 gig) free.

Keep in mind the cached value and the buffers value will be reclaimed by the system is needed. You actually want a large cached value. I’m not sure what’s stored in it but….


Caution ! Running out  of SWAP area may  cause  the server to hang

Rupam