Oracle Learning: September 2012

Thursday, September 20, 2012

to check if oracle database is upgrade from 10g to 11g

Below script can the checked to see the database up gradation life cycle.

SQL> l

1* select * from DBA_REGISTRY_HISTORY

SQL> /

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS

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

16-NOV-09 04.22.18.257083 PM UPGRADE SERVER 11.1.0.7.0 Upgraded from 11.1.0.6.0

06-JUN-10 01.36.36.363629 PM APPLY SERVER 11.1.0.7 2 PSU 11.1.0.7.2

06-JUN-10 01.40.09.606668 PM CPU 6452863 view recompilation

25-MAY-11 05.59.56.069514 PM VIEW INVALIDATE 8289601 view invalidation

25-MAY-11 06.00.22.484425 PM UPGRADE SERVER 11.2.0.2.0 Upgraded from 11.1.0.7.0

25-MAY-11 07.45.23.622169 PM VIEW INVALIDATE 8289601 view invalidation

25-MAY-11 07.45.39.435819 PM UPGRADE SERVER 11.2.0.2.0 Upgraded from 11.1.0.7.0

7 rows selected.

SQL> SELECT version, prv_version,banner FROM sys.registry$ ;

VERSION PRV_VERSION BANNER

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

11.2.0.2.0 11.1.0.6.0 Oracle Database Catalog Views Release 11.2.0.2.0 - 64bit Production

11.2.0.2.0 11.1.0.7.0 Oracle Database Packages and Types Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Workspace Manager Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 JServer JAVA Virtual Machine Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle XDK Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Database Java Packages Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Expression Filter Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Text Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle XML Database Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Rules Manager Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Multimedia Release 11.2.0.2.0 - Development

VERSION PRV_VERSION BANNER

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

11.2.0.2.0 11.1.0.7.0 OLAP Analytic Workspace Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle OLAP API Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 OLAP Catalog Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Spatial Release 11.2.0.2.0 - Development

11.2.0.2.0 Oracle Enterprise Manager Release 11.2.0.2.0 - Development

3.2.1.00.12 3.0.1.00.12 Oracle Application Express Release 3.2.1.00.12 - Development

11.1.0.6.0 OWB Release 11.1.0.6.0 - Production

18 rows selected.

SQL>

Monday, September 10, 2012

RMAN DUPLICATE COMMAND PITFALL

· RMAN DUPLICATE: CONTROL FILE IS RESTORED NOT CREATED.

In Oracle Database 11.2, the RMAN 'Duplicate' command now RESTORES the backup controlfile to the auxiliary host rather than CREATE it. So we must ensure that there is a controlfile backup available to meet the duplicate time. Otherwise we may get the bellow error:

RMAN-03002: failure of Duplicate Db command at 07/23/2012 10:09:08

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06026: some targets not found - aborting restore

RMAN-06024: no backup or copy of the control file found to restore

Here's test to run before starting your duplication, to verify whether you have a valid controlfile backup:

RMAN> run {

set until time "to_date('2012 SEP 03 07:37','YYYY MON DD HH24:MI')";

restore controlfile preview;}

Note: change the 'SET UNTIL TIME' to meet your duplicate requirements.

· BCT(Block change tracking) issue when duplicating DB.

If BCT enable at production DB, we may get 'ORA-19755 Using RMAN Duplicate, Tries Open The Block Change Tracking File of Source DB'.

Workaround is to create a dummy file in same location as on Production to meet this requirement. We may also disable BCT for the time, duplicate process is active. Also check DOC ID: 1098638.1.

RMAN backup optimization

RMAN backup optimization is another feature to minimize backup size. Enabling this feature, RMAN BACKUP will skip backup of files that comes in certain condition or exactly identical to the file already backup up.

By Default it is set to OFF:

RMAN> SHOW BACKUP OPTIMIZATION;

RMAN configuration parameters for database with db_unique_name ORACLE are:

CONFIGURE BACKUP OPTIMIZATION OFF; # default

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters are successfully stored

RMAN>

Below are the criteria when RMAN check which files can be skipped from the backup.

In case of datafile RMAN compares the DBID, checkpoint SCN, creation SCN, and RESETLOGS SCN and time as a datafile already in a backup. If they are identical then skip to take backup again.

In case of archived redo log RMAN compares thread, sequence number, and RESETLOGS SCN and time.

In case of backupset RMAN compares recid and stamp.

Backup Optimization only applies to below rman commands:

BACKUP DATABASE

BACKUP ARCHIVELOG with ALL or LIKE options

BACKUP BACKUPSET ALL

Optimization feature can be override anytime by using FORCE option with backup command.

BACKUP DATABASE FORCE;

Log:

Starting backup at 08-SEP-12

current log archived

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3074.294.793279165;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3075.307.793284119;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3076.298.793286989;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3077.297.793289297;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3078.382.793291555;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3079.395.793293577;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3080.381.793295619;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3081.299.793297653;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3082.403.793299621;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3083.408.793301609;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3084.362.793303497;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3085.355.793305483;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3086.364.793307311;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3087.306.793309275;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3088.292.793311117;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3089.283.793312945;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3090.365.793314877;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3091.356.793316867;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3092.410.793317693;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3093.281.793319779;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3094.358.793322287;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3095.368.793325083;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3096.386.793326137;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3097.290.793328655;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3098.411.793332451;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3099.360.793338931;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3100.407.793343249;

Saturday, September 8, 2012

log evidence of master node in RAC environment

Another log evidence of master node in RAC environment

2012-09-06 18:27:20.452: [ CRSRES][1090042176][ALERT] Remote start for `******.******1.inst` failed on member `*******`

2012-09-06 18:27:20.453: [ CRSD][1092143424] SM: recovery in process: 8

2012-09-06 18:27:20.453: [ CRSEVT][1092143424] Do failover for: ********

2012-09-06 18:27:20.457: [ OCRMSG][1092143424]prom_rpc: CLSC recv failure..ret code 7

2012-09-06 18:27:20.457: [ OCRMSG][1092143424]prom_rpc: possible OCR retry scenario

2012-09-06 18:27:20.458: [ OCRMAS][1085839680]th_master:13: I AM THE NEW OCR MASTER at incar 14. Node Number 2

Thursday, September 6, 2012

ORA-20005: object statistics are locked

Today with an import script, we had received below error.

IMP-00017: following statement failed with ORACLE error 20005: IMP-00003: ORACLE error 20005 encountered IMP-00017: following statement failed with ORACLE error 20005: IMP-00003: ORACLE error 20005 encountered IMP-00017

When checking we find statistics are locked for those objects.

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked = 'ALL';

Solution is either to unlock the statistics the objects using dbms_stats package.

exec dbms_stats.unlock_schema_stats('schema_owner');

exec dbms_stats.unlock_table_stats('table_owner','table_name');

Else to prevent import (imp) from locking the table's statistics when importing a table without the rows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table's statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).

Oracle Learning