Error in naming a datafile

At times life as a DBA gets so tiring that you goof up even a simplest task of adding a datafile to a tablespace, I did too (chuckles), I entered a comma(,) instead of a period(.) for the datafile extentsion.

SQL> alter tablespace BLOB_TS add datafile
  2  '/prd/u01/oradata/prod/lob08,dbf' size 5000M;

Only after hitting return did I realize that I made a blunder in the datafile name, I had to immediately take the datafile offline to prevent any users from filling it up with data.

SQL> alter database datafile '/prd/u01/oradata/prod/lob08,dbf' offline;

Database altered.

SQL> select status,name from v$datafile where name like '%lob08%';

STATUS  NAME
------- ----------------------------------------------------------------
RECOVER /prd/u01/oradata/prod/lob08,dbf

The status above shows that the datafile needs recovery, I ran a 'RECOVER DATAFILE' command to recover the datafile and have its status changed from 'RECOVER' to 'OFFLINE'. In order for this to be successful, the same log sequence should be running in the database if the database is in no-archive mode or it could be a different log sequence if the database is in archive mode but the archive logs be present on disk to be applied if needed.

In my case, it was in archive mode and luckily the same log sequence was running when I ran the 'RECOVER DATAFILE' command

SQL> recover datafile '/prd/u01/oradata/prod/lob08.dbf';
Media recovery complete.

SQL> select status,name from v$datafile where name like '%lob08%';

STATUS  NAME
------- ----------------------------------------------------------------
OFFLINE /prd/u01/oradata/prod/lob08,dbf

Now the file has to be copied with the corrected name at the OS level.

SQL> !ls -ltr lob08*
-rw-r-----   1 oracle   dba      524296192 Jul 16 10:58 lob08,dbf

SQL> !cp "lob08,dbf" "lob08.dbf"
SQL>

SQL> !ls -ltr lob08*
-rw-r-----   1 oracle   dba      524296192 Jul 16 10:58 lob08,dbf
-rw-r-----   1 oracle   dba      524296192 Jul 16 11:10 lob08.dbf

The file is then renamed at the database level and should be brought online.

SQL> alter database rename file '/prd/u01/oradata/prod/lob08,dbf' to
  2  '/prd/u01/oradata/prod/lob08.dbf';

SQL> alter database datafile '/prd/u01/oradata/prod/lob08.dbf' online;

Database altered.

SQL> select status,name from v$datafile where name like '%lob08%';

STATUS  NAME
------- ----------------------------------------------------------------
ONLINE  /prd/u01/oradata/prod/lob08.dbf

It is now safe to remove the file with comma(,) in its name at the OS level

SQL> !rm "lob08,dbf"
SQL> !ls -ltr lob08*
-rw-r-----   1 oracle   dba      524296192 Jul 16 11:10 lob08.dbf

ORA-06510, ORA-06512 errors during export.

Have you ever received an error like ORA-06510 or ORA-06512 during an export backup of a database ? - The reason behind this is some Oracle shops have a specific database user using which the databases are exported out rather than the traditional admin users like SYS, SYSTEM or an OS authenticated user "/".

The non-admin database user running the export backup does not have an execute privilege on the two packages namely DBMS_EXPORT_EXTENSION and DBMS_DEFER_IMPORT_INTERNAL. A successful execute grant on the two packages to the non-admin user resolves the issue of ORA-06510 and ORA-06512

EXP-00008: ORACLE error 6510 encountered
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 50
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126
ORA-06512: at line 1

SQL> select grantee,owner,table_name,privilege from dba_tab_privs where 
  2  grantee='EXPORT_USER';

no rows selected

SQL> grant execute on DBMS_EXPORT_EXTENSION to EXPORT_USER;

Grant succeeded.

SQL> grant execute on DBMS_DEFER_IMPORT_INTERNAL to EXPORT_USER;

Grant succeeded.

SQL> select grantee,owner,table_name,privilege from dba_tab_privs where 
  2  grantee='EXPORT_USER';

GRANTEE    OWNER      TABLE_NAME                     PRIVILEGE
---------- ---------- ------------------------------ ----------
EXPORTER   SYS        DBMS_EXPORT_EXTENSION          EXECUTE
EXPORTER   SYS        DBMS_DEFER_IMPORT_INTERNAL     EXECUTE

11g Installation

Weeeeeehhho, today I got my hand over the installation of 11gR1(11.1.0.6.0), actually the installation process is no different than its predecessors. The X-windows utility that I used did drag the feet around in the whole installation process as I had to wait patiently (HEIGHTS OF PATIENCE) to get the control over to the buttons/text boxes etc. Although it took quite a while for the installation to complete but in the end it brought a big grin on my face.

RMAN-20011: target database incarnation is not current in recovery catalog

A surprising database backup failure report was delivered to my mailbox, upon checking with the log revealed the below pasted RMAN- errors. This left me wondering as to how come the rman backup would fail for no reason with an error like this, there is something fishy about the situation.

RMAN>
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target 
database incarnation is not current in recovery catalog

here are the steps below that were performed to fix the issue of incarnation.

UNIX:~/dba/log > rman target / catalog rman_user/rman_pass@rcat.acme.com

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 26 06:45:20 2009

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

connected to target database: PROD (DBID=2706003900)
connected to recovery catalog database

RMAN>  list incarnation of database;


RMAN> exit

as you can see above, no incarnations listed.

UNIX:~/dba/log >  . oraenv
ORACLE_SID = [oracle] ? PROD
UNIX:~/dba/log > rman target / nocatalog

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 26 06:46:58 2009

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

connected to target database: PROD (DBID=2706003900)
using target database control file instead of recovery catalog

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD 2706003900       CURRENT 1          07-FEB-07

RMAN> exit

Now, having logged into the recovery catalog database as the recovery catalog owner and running a couple of queries shown below will give us some background information on the target database DBID and the incarnation id etc.

SQL> select dbinc_key,db_key,db_name from RMAN.dbinc where db_name='PROD';

 DBINC_KEY     DB_KEY DB_NAME  
---------- ---------- -------- 
    875312     875311 PROD     


SQL> select DBID, NAME, DBINC_KEY, RESETLOGS_CHANGE#, RESETLOGS_TIME 
  2  from rc_database_incarnation where dbid=2706003900;

      DBID NAME      DBINC_KEY RESETLOGS_CHANGE# RESETLOGS
---------- -------- ---------- ----------------- ---------
2706003900 PROD     875312                     1 07-FEB-07
2706003900 PROD2    9179422           6.2038E+12 24-MAY-09

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

Session altered.

SQL>  select DBID, NAME, DBINC_KEY, RESETLOGS_CHANGE#, RESETLOGS_TIME
  2   from rc_database_incarnation where dbid=2706003900;

      DBID NAME      DBINC_KEY RESETLOGS_CHANGE# RESETLOGS_TIME
---------- -------- ---------- ----------------- --------------------
2706003900 PROD      875312                    1 07-FEB-2007 15:49:16
2706003900 PROD2    9179422           6.2038E+12 24-MAY-2009 22:25:13

The database PROD with the DBID (2706003900) and DBINC_KEY (875312) has to be reset in the recovery catalog and re-registered for its backups to resume.

UNIX:~/dba/log > rman target / catalog rman_user/rman_pass@rcat.acme.com

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 26 07:00:56 2009

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

connected to target database: PROD (DBID=2706003900)
connected to recovery catalog database

RMAN> RESET DATABASE TO INCARNATION 875312;

database reset to incarnation 875312

RMAN> register database;

starting full resync of recovery catalog


full resync complete
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of register command on default channel at 05/26/2009 07:40:05
RMAN-20002: target database already registered in recovery catalog

RMAN>
RMAN>

It should have rather been a "resync catalog" than "register database" to suppress the above error.After this, the backup ran to success.

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN    Reset Time
------- ------- -------- ---------------- ---     ----------   ----------
875311  875312  PROD     2706003900       CURRENT 1             07-FEB-07
875311  9179422 PROD2    2706003900       ORPHAN  6203845546338 24-MAY-09

RMAN> exit


Recovery Manager complete.

DBMS_METADATA.GET_DDL

Oracle introduced the usage of DBMS_METADSATA package's GET_DDL function to retrieve the DDL of a particular object type from 9i onwards. It takes two/three arguments depending on the type of object you want to retrieve the DDL for.

GENERAL USAGE

set long 300000 pages 0 lines 140

SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OWNER') FROM DUAL;

The 'OWNER' argument is sometimes optional if you are connected as the same user who owns the object you are trying to retreive the DDL for. Pasted below are the statements to generate the DDL for most of the object types.

select dbms_metadata.get_ddl('CLUSTER','C_MLOG#','SYS') from dual;

select dbms_metadata.get_ddl('CONTEXT','LT_CTX') from dual;

select dbms_metadata.get_ddl('DB_LINK','PROD.WORLD','ADAM') from dual;

select dbms_metadata.get_ddl('DB_LINK','TEST.WORLD','PUBLIC') from dual;

select dbms_metadata.get_ddl('FUNCTION','TO_DATE_FUNC','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','REPCAT$_REPCOLUMN_PK','SYSTEM') from dual;

select dbms_metadata.get_ddl('JAVA_SOURCE','java_util','ADAM') from dual

select dbms_metadata.get_ddl('JAVA_SOURCE','/6c363944_Dumper','SYS') from dual

select dbms_metadata.get_ddl('LIBRARY','UTL_SMT_LIB','SYS') from dual;

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','BS_TAB_MV','SYS') from dual;

select dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG','MLOG$_BS_TABLE','SYS') from dual;

select dbms_metadata.get_ddl('OPERATOR','OLAP_EXPRESSION') from dual;

select dbms_metadata.get_ddl('OPERATOR','OLAP_EXPRESSION','SYS') from dual;

select dbms_metadata.get_ddl('PACKAGE','XMLPARSER') from dual;

select dbms_metadata.get_ddl('PACKAGE','XMLPARSER','SYS') from dual;

select dbms_metadata.get_ddl('PACKAGE_BODY','STATSPACK','PERFSTAT') from dual;

select dbms_metadata.get_ddl('PROCEDURE','ORA$_SYS_REP_AUTH','SYSTEM') from dual;

select dbms_metadata.get_ddl('SEQUENCE','STATS$SNAPSHOT_ID','PERFSTAT') from dual;

select dbms_metadata.get_ddl('SYNONYM','/2fddc42_paintARGB_PRE_ONTO_S5','PUBLIC') from dual;

select dbms_metadata.get_ddl('TABLE','SQLEXPERT_PLAN1','SYSTEM') from dual;

select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

select dbms_metadata.get_ddl('TRIGGER','DEF$_PROPAGATOR_TRIG','SYSTEM') from dual;

select dbms_metadata.get_ddl('TYPE','XMLSEQ_IMP_T','SYS') from dual;

select dbms_metadata.get_ddl('TYPE_BODY','ORACLE_LOADER','SYS') from dual;

select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION','SYS') from dual;

select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION') from dual;

select dbms_metadata.get_ddl('PACKAGE','DBMS_METADATA','SYS') from dual;