Friday, December 12, 2014

Oracle EBS 12.2.3 Installation


Oracle E-Business Suite Release 12.2.3 Readme (Doc ID 1586214.1 )
Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1)
Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Doc ID 1617461.1)
Oracle E-Business Suite 12.2 Patching Technology Components Guide (Doc ID 1355068.1)

Oracle E-Business Suite Release Notes, Release 12.2 (Doc ID 1320300.1)
Oracle E-Business Suite Release 12.2 Technology Stack Documentation Roadmap (Doc ID 1934915.1)
Oracle E-Business Suite Technology Stack and Applications DBA Release Notes for Release 12.2.3 (Doc ID 1606170.1)

Wednesday, July 16, 2014

Oracle EBS R12 DB Tier Cloning Issue Resolution


Oracle EBS R12 DB Tier Cloning was NOT successful. But Database was mounted. Then following can be attempted to recover and startup the database.

1. Try to open the database using resetlogs option.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/TEST/db/apps_st/data/system01.dbf'


2. If similar error comes like above, Shutdown immediate     

SQL> Shutdown immediate

3. Remark the parameter in initTEST.ora:  

 UNDO_MANAGEMENT=AUTO
 UNDO_TABLESPACE=APPS_UNDOTS

4. Add the parameter in initTEST.ora :         

    UNDO_MANAGEMENT=MANUAL
    _ALLOW_RESETLOGS_CORRUPTION = TRUE
    _ALLOW_ERROR_SIMULATION = TRUE

5. Startup database with Manual undo management.using new initTEST.ora      

sqlplus '/as sysdba'

SQL> startup mount pfile=initTEST.ora

SQL> recover database using backup controlfile until cancel;

SQL> alter database open resetlogs;

6. Create new UNDO Tablespace  

SQL> Create UNDO tablespace NEW_UNDOTS datafile '/u01/TEST/db/apps_st/data/undo01.dbf' size 2048M;

7. Take offline the OLD Undo Tablespace :  
 
SQL> alter tablespace APPS_UNDOTS offline;

8. Take online the NEW Undo Tablespace :  

SQL> alter tablespace NEW_UNDOTS online;

9. Shutdown the database :      

SQL> shutdown immediate;

10. Edit the initTEST.ora :  

    + Remark the parameter :

    UNDO_MANAGEMENT=MANUAL
    _ALLOW_RESETLOGS_CORRUPTION = TRUE
    _ALLOW_ERROR_SIMULATION = TRUE

    + Add and edit the parameter :

    UNDO_MANAGEMENT=AUTO
    UNDO_TABLESPACE=NEW_UNDOTS 

11. Startup the database :

SQL> startup

12. Set the  default Undo tablespace as NEW_UNDOS

SQL> alter system set undo_tablespace=NEW_UNDOTS;

13. Then we can drop the OLD Undo tablespace :   

SQL> drop tablespace APPS_UNDOTS including contents and datafiles;

14. Also reuse the existing TEMP tablespace tempfiles or create a new TEMP tablespace.

Tuesday, June 3, 2014

Oracle EBS 12.1.3 - Database Upgrade from 11.1.0.7 to 11.2.0.4

Steps in the following metalink documents should be followed to do the database upgrade from 11.1.0.7 to 11.2.0.4.

Interoperability Notes EBS 12.0 and 12.1 with Database 11gR2 (Doc ID 1058763.1)
Complete Checklist to Upgrade the Database to 11gR2 using DBUA (Doc ID 870814.1)

Tuesday, March 11, 2014

Issue Resolution for ORA-00020: maximum number of processes (%s) exceeded

When ORA-00020: maximum number of processes (%s) exceeded occurred in a database and also you are NOT able to connect to the database as sys even to increase the processes parameter.
In that scenario following will work to resolve the same.

1. Instead of logging to the database via sys user as sqlplus / as sysdba you can use the following command.
sqlplus -prelim "/ as sysdba"

2.  Then you can shutdown the database using shutdown abort
SQL> shutdown abort

3. Then connect to the database via sys user as follows.
sqlplus / as sysdba

4. Then mount the database and increase the processes parameter as required.
SQL> startup mount

5. Then open the database.
SQL> alter database open

Friday, January 17, 2014

Oracle EBS R12: Creating New Branch on New Bank Errors with ORA-06508: Issue Resolution

Oracle Payables – Version: 12.1.1 and later   [Release: 12.1 and later ]
Information in this document applies to any platform.
ARHRELTS.pls 120.2
ARHRELTB.pls 120.4
ARH2RGVB.pls
ARH2RGVS.pls
HZ_RELATIONSHIPS_PKG
HZ_REGISTRY_VALIDATE_V2PUB

In Payables, when trying to save a new bank branch on a newly created bank, users are getting the following online error message:The following SQL error occurred: ORA-06508: PL/SQL: could not find program unit being called.


Payables Manager > Setup: Payment Banks and Bank Branches
Bank Branches Tab > Create
Enter Bank Name, Number and Country
Branch Options > Create New Branch
Enter Branch Name and Branch Type
Click on Save and Next or Finish

The following SQL error appears

ORA-06508: PL/SQL: could not find program unit being called.
Log shows that HZ_RELATIONSHIPS_PKG is the missing program.

Solution

1. Execute HZ_RELATIONSHIPS_PKG to create the database package

       SQL>@ARHRELTS.pls
       SQL>@ARHRELTB.pls

2. Compile HZ_REGISTRY_VALIDATE_V2PUB

     Alter package APPS.HZ_REGISTRY_VALIDATE_V2PUB compile;
     Alter package APPS.HZ_REGISTRY_VALIDATE_V2PUB compile body;

3. Retest the issue.

Reference

R12: Creating New Branch on New Bank Errors with ORA-06508: PL/SQL: Could Not Find Program Unit Being Called; HZ_RELATIONSHIPS_PKG (Doc ID 1291931.1)

Monday, January 6, 2014

Oracle EBS R12 Concurrent Managers Issue Resolution - Execute adrelink

Problem Description -
"Scheduler/Prereleaser Manager" is showing status "System Hold, Fix Manager before resetting counters".

Problem Solution -
To implement the solution, please execute the following steps:

1. Stop all middle tier services including the concurrent managers.
Please make sure that no FNDLIBR, FNDSM, or any dead process is
running.

2. Stop the database.

3. Start the database.

4. Go to cd $FND_TOP/bin
$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"
$ adrelink.sh force=y link_debug=y "fnd FNDFS"
$ adrelink.sh force=y link_debug=y "fnd FNDCRM"
$ adrelink.sh force=y link_debug=y "fnd FNDSM"

5. Run the CMCLEAN.SQL script from the referenced note below (don't forget to commit).
Note 134007.1 CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables

6. Execute the following SQL:
select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where
CONCURRENT_QUEUE_NAME like 'FNDSM%';

7. Start the middle tier services including your concurrent manager.

8. Retest the issue.

Tuesday, December 10, 2013

Resolve Oracle Database 11g Installation Issue on RHEL 6

Below error comes when trying to install the Oracle Database 11g on RHEL 6.




There is a workaround to avoid this issue. Execute following command and start the database installation again.

$ export LD_BIND_NOW = 1

Tuesday, October 8, 2013

Oracle Database 12c Installation and Configurations

Oracle 12c Container & Pluggable Database Installation

All Oracle Database 12c installation options are very similar to the Oracle Database 11g installation other than the new concept of container & pluggable databases. To create the container database with pluggale databases in Oracle 12c following options can be used in the installation wizard or in DBCA.

The checkbox "Create As Container Database" has to be selected and the option "Create a Container Database with one or more PDBs" should be chosen with the number of pluggable databases and prefix of the pluggable database.


Important Queries for Administration


[oracle@prod admin]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Tue Oct 8 16:30:29 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

SQL> select con_id, banner from v$version;

    CON_ID
----------
BANNER
--------------------------------------------------------------------------------
         0
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

         0
PL/SQL Release 12.1.0.1.0 - Production

         0
CORE    12.1.0.1.0      Production

    CON_ID
----------
BANNER
--------------------------------------------------------------------------------
         0
TNS for Linux: Version 12.1.0.1.0 - Production

         0
NLSRTL Version 12.1.0.1.0 - Production


SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           READ WRITE


SQL> alter session set container = PDB1;

Session altered.

SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
PDB1

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         3 PDB1                           READ WRITE

SQL> alter session set container = PDB2;

Session altered.

SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
PDB2

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         4 PDB2                           READ WRITE

SQL> SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
  2  fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
  3  (df.total_space_mb - fs.free_space_mb) USED_SPACE_MB, fs.free_space_mb FREE_SPACE_MB,
  4  ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
  5  FROM (SELECT tablespace_name, SUM (bytes) TOTAL_SPACE,
  6  ROUND( SUM (bytes) / 1048576) TOTAL_SPACE_MB
  7  FROM dba_data_files GROUP BY tablespace_name) df,
  8  (SELECT tablespace_name, SUM (bytes) FREE_SPACE, ROUND( SUM (bytes) / 1048576) FREE_SPACE_MB
  9  FROM dba_free_space GROUP BY tablespace_name) fs
 10  WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY ROUND(100 * (fs.free_space / df.total_space),2);

TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB
------------------------------ ----------- ---------- --------------
USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
SYSTEM                           272629760   10354688            260
          250            10        3.8

SYSAUX                           639631360   32309248            610
          579            31       5.05

USERS                              5242880    4194304              5
            1             4         80


RMAN Backup

[oracle@prod admin]$ rman

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Oct 8 16:45:56 2013

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

RMAN> connect target /

connected to target database: TEST (DBID=2126431506)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_TEST.f'; # default

RMAN> backup database plus archivelog;

Starting backup at 08-OCT-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=359 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=828285237
input archived log thread=1 sequence=5 RECID=2 STAMP=828285267
input archived log thread=1 sequence=6 RECID=3 STAMP=828285630
input archived log thread=1 sequence=7 RECID=4 STAMP=828287384
input archived log thread=1 sequence=8 RECID=5 STAMP=828290783
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2013_10_08/o1_mf_annnn_TAG20131008T164623_957td82r_.bkp tag=TAG20131008T164623 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-OCT-13

Starting backup at 08-OCT-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/TEST/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/TEST/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/TEST/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/TEST/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2013_10_08/o1_mf_nnndf_TAG20131008T164625_957td9jb_.bkp tag=TAG20131008T164625 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/TEST/PDB1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/TEST/PDB1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/TEST/PDB1/PDB1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/E8389F6267BA2A3EE0431C6FA8C0FFB3/backupset/2013_10_08/o1_mf_nnndf_TAG20131008T164625_957tdrrc_.bkp tag=TAG20131008T164625 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u01/app/oracle/oradata/TEST/PDB2/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/TEST/PDB2/system01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/TEST/PDB2/PDB2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/E838A04FA7AD2A98E0431C6FA8C01C02/backupset/2013_10_08/o1_mf_nnndf_TAG20131008T164625_957tf804_.bkp tag=TAG20131008T164625 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/TEST/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/TEST/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/E83894A1E2CD234FE0431C6FA8C0EE1E/backupset/2013_10_08/o1_mf_nnndf_TAG20131008T164625_957tfq7k_.bkp tag=TAG20131008T164625 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 08-OCT-13

Starting backup at 08-OCT-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=6 STAMP=828290846
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2013_10_08/o1_mf_annnn_TAG20131008T164726_957tg6hz_.bkp tag=TAG20131008T164726 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-OCT-13

Starting Control File and SPFILE Autobackup at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/autobackup/2013_10_08/o1_mf_s_828290847_957tg7qs_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-OCT-13

RMAN>


Pluggable Database Cloning

SQL> conn / as sysdba
Connected.

SQL> alter pluggable database PDB2 close;

Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           MOUNTED

SQL> alter pluggable database PDB2 open read only;

Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           READ ONLY

SQL> create pluggable database PDB3 from PDB2 file_name_convert = ('PDB2','PDB3');

Pluggable database created.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           READ ONLY
         5 PDB3                           MOUNTED

SQL> alter pluggable database PDB3 open;

Pluggable database altered.

SQL>  select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           READ ONLY
         5 PDB3                           READ WRITE

SQL> alter pluggable database PDB2 close;

Pluggable database altered.

SQL>  select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           MOUNTED
         5 PDB3                           READ WRITE

SQL> alter pluggable database PDB2 open;

Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           READ WRITE
         5 PDB3                           READ WRITE

SQL>

Friday, September 13, 2013

RMAN Backup Restoration to Another Server on Windows 2008 R2

First, install the binaries using the option software only in the database installation wizard.

Then create a instance with the SID using oradim.

D:\KALIPTO\product\11.2.0\dbhome_1\BIN>oradim -new -sid KALIPTO

Then connect to rman and set the DBID as follows

D:\KALIPTO\product\11.2.0\dbhome_1\BIN>rman
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Sep 12 16:36:52 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN> set DBID=221460339

executing command: SET DBID

RMAN>

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\KALIPTO\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITKALIPTO.ORA'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2251280 bytes
Variable Size                 96470512 bytes
Database Buffers              54525952 bytes
Redo Buffers                   5414912 bytes

RMAN>

RMAN> restore spfile to pfile 'D:\KALIPTO\product\11.2.0\dbhome_1\database\initKALIPTO.ora' from 'D:\AML_BACKUP\AUTOBAK_CTRLFILEC-221460339-20130820-00';

Starting restore at 12-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP D:\AML_BACKUP\AUTOBAK_CTRLFILEC-221460339-20130820-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 12-SEP-13

RMAN>

RMAN> shutdown immediate

Oracle instance shut down

Then open pfile and create the directory structure as required to nomount database.

RMAN> startup force nomount

Oracle instance started

Total System Global Area   21378957312 bytes

Fixed Size                     2262600 bytes
Variable Size               8992590264 bytes
Database Buffers           12348030976 bytes
Redo Buffers                  36073472 bytes

Then restore the controlfile and mount database.

RMAN> restore controlfile from autobackup;

Starting restore at 12-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=303 device type=DISK

recovery area destination: D:\KALIPTO\fast_recovery_area
database name (or database unique name) used for search: KALIPTO
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130912
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130911
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130910
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130909
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130908
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130907
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130906
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/12/2013 16:41:31
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece


RMAN> restore controlfile from 'D:\AML_BACKUP\AUTOBAK_CTRLFILEC-221460339-20130820-00';

Starting restore at 12-SEP-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\KALIPTO\ORADATA\KALIPTO\CONTROL01.CTL
output file name=D:\KALIPTO\FAST_RECOVERY_AREA\KALIPTO\CONTROL02.CTL
Finished restore at 12-SEP-13

RMAN>

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

If the RMAN backup residing directory is different than primary database server, then following catalog command is required to specify the RMAN backup directory.

RMAN> catalog start with 'D:\AML_BACKUP\';

searching for all files that match the pattern D:\AML_BACKUP\

List of Files Unknown to the Database
=====================================
File Name: D:\AML_BACKUP\AUTOBAK_CTRLFILEC-221460339-20130820-00
File Name: D:\AML_BACKUP\AUTOBAK_CTRLFILEC-221460339-20130912-00
File Name: D:\AML_BACKUP\AUTOBAK_CTRLFILEC-221460339-20130912-01
File Name: D:\AML_BACKUP\DATAFILES_823945946_2797_1.BAK
File Name: D:\AML_BACKUP\DATAFILES_823947858_2798_1.BAK
File Name: D:\AML_BACKUP\DATAFILES_823949157_2799_1.BAK
File Name: D:\AML_BACKUP\DATAFILES_823950816_2800_1.BAK
File Name: D:\AML_BACKUP\O1_MF_NCNNF_TAG20130912T154025_9334ROHD_.BKP
File Name: D:\AML_BACKUP\O1_MF_NNSNF_TAG20130912T155610_9335P2VW_.BKP

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: D:\AML_BACKUP\AUTOBAK_CTRLFILEC-221460339-20130820-00
File Name: D:\AML_BACKUP\AUTOBAK_CTRLFILEC-221460339-20130912-00
File Name: D:\AML_BACKUP\AUTOBAK_CTRLFILEC-221460339-20130912-01
File Name: D:\AML_BACKUP\DATAFILES_823945946_2797_1.BAK
File Name: D:\AML_BACKUP\DATAFILES_823947858_2798_1.BAK
File Name: D:\AML_BACKUP\DATAFILES_823949157_2799_1.BAK
File Name: D:\AML_BACKUP\DATAFILES_823950816_2800_1.BAK
File Name: D:\AML_BACKUP\O1_MF_NCNNF_TAG20130912T154025_9334ROHD_.BKP
File Name: D:\AML_BACKUP\O1_MF_NNSNF_TAG20130912T155610_9335P2VW_.BKP

Then restore and recover database and open the database with resetting logs.

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

Monday, August 26, 2013

Oracle 11gR2 netmgr/netca issue resolution on Solaris 10 for Corrupted listener.ora file

Following error comes when starting the netmgr or netca.

bash-3.2$ netmgr
Warning: Cannot convert string "-hanyi-ming-medium-r-normal--*-140-*-*-m-*-big5- 1" to type FontStruct
Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException
at oracle.net.mgr.listener.ListenerComponent.loadData(Unknown Source)
at oracle.net.mgr.listener.ListenerComponent$ListenerCompDataSource.inte rnal_refreshData(Unknown Source)
at oracle.sysman.emSDK.client.dataComponent.dataDrivenTree.TreeDataSourc e.populateData(TreeDataSource.java:1039)
at oracle.sysman.emSDK.client.dataComponent.dataDrivenTree.TreeDataSourc e._initData(TreeDataSource.java:1083)
at oracle.sysman.emSDK.client.dataComponent.dataDrivenTree.TreeDataSourc e.initData(TreeDataSource.java:1056)
at oracle.sysman.emSDK.client.dataComponent.dataDrivenTree.TreeParentNod e.setExpanded(TreeParentNode.java:284)
at oracle.sysman.emSDK.client.dataComponent.dataDrivenTree.TreeParentNod e.setExpanded(TreeParentNode.java:242)
at oracle.ewt.dTree.DTreeButtonDecoration.processMouseEvent(Unknown Sour ce)
at oracle.ewt.dTree.DTreeStackingDecoration.processMouseEvent(Unknown So urce)
at oracle.ewt.dTree.DTree.processMouseEvent(Unknown Source)
at oracle.sysman.emSDK.client.dataComponent.dataDrivenTree.DataDrivenTre e.processMouseEvent(DataDrivenTree.java:1226)
at java.awt.Component.processEvent(Component.java:5282)
at java.awt.Container.processEvent(Container.java:1966)
at oracle.ewt.lwAWT.LWComponent.processEventImpl(Unknown Source)
at oracle.ewt.dTree.DTree.processEventImpl(Unknown Source)
at oracle.ewt.lwAWT.LWComponent.redispatchEvent(Unknown Source)
at oracle.ewt.lwAWT.LWComponent.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Component.java:3984)
at java.awt.Container.dispatchEventImpl(Container.java:2024)
at java.awt.Component.dispatchEvent(Component.java:3819)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212 )
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3889)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
at java.awt.Container.dispatchEventImpl(Container.java:2010)
at java.awt.Component.dispatchEvent(Component.java:3819)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchTh read.java:242)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThre ad.java:163)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)

This has caused because of corrupted listener.ora file.

Solution

1. First back up the Listener.ora file.
2. Check the listener running on by following command.
    ps -ef|grep tns
3. Kill the Listener Process and also listener.ora file.
    kill -9 <Process-ID>
4. Start the listener from LSNRCTL Utility without the listener.ora file.
5. Now the default listener with 1521 port will be started and Net Manger and Net CA should be   working fine.

Friday, August 2, 2013

Troubleshoot & Re-create Oracle Enterprise Manager Repository on MS Windows Server

Remove the following directories from your filesystem:

<ORACLE_HOME>\<hostname>_<db_unique_name>
<ORACLE_HOME>\oc4j\j2ee\OC4J_DBConsole_<hostname>_<db_unique_name>

$ sc delete DbConsoleOEM

$ cd <ORACLE_HOME>\bin
$ emca -repos drop or we can drop the Repository manually as follows.

Delete DBControl Repository Objects Manually:

Logon to SQL*PLUS as SYS user and execute the following:

SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'SYSMAN.MGMT_NOTIFY_QTABLE',force=>TRUE);

SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/

SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;

Create the DBControl Repository Objects and Configuration Files:

Set your environment variables:

$ set ORACLE_HOME=D:\app\Administrator\product\11.2.0\dbhome_1
$ set ORACLE_SID=OEM
$ set ORACLE_UNQNAME=<database unique name> --> you can get this value from SQL query (SQL> show parameter db_unique_name)
$ set ORACLE_HOSTNAME

$ emca -config dbcontrol db -respos create

Edit <ORACLE_HOME>/sysman/admin/supported.tz file  i.e added Asia/Colombo under +5:30 GMT

Edit  <ORACLE_HOME>/<Hostname_DB>/sysman/config/emd.properties file to Asia/Colombo

$ set TZ=Asia/Colombo
$ emctl config agent getTZ
$ emctl config agent updateTZ
$ emctl resetTZ agent
$ emctl stop dbconsole
$ emctl start dbconsole

Friday, July 19, 2013

Database Opatch

Commands for Opatch

opatch version
opatch apply
opatch lsinvetory
opatch lsinventory -all

Opatch application specific Notes.

How To Download And Install The Latest OPatch Version (Doc ID 274526.1)
OPatch - Where Can I Find the Latest Version of OPatch? [Video] (Doc ID 224346.1)
OPatch Permissions Problem on Windows 2008 64-bit [ID 791005.1]
Write Errors, or Files In Use, or NoServicesForProcessException During Patch Installation (Doc ID 294350.1)



Wednesday, July 17, 2013

Oracle Database 12c Introduction - Webcast Q & A



What is meant by pluggable databases?

With Multitenant you can have multiple pluggable databases - PDBs ("tenants") plugged into a single multitenant container database (CDB). Consolidate to support more applications per server. Manage many as one. Move PDBs between CDB to move between SLA tiers - unprecedented agility. No application changes required. More information upcoming in webcast.


When is 12c going to be available/supported on the exadata systems?

Oracle Database 12.1 is supported on Exadata. The minimum Exadata storage server software version required to run an Oracle 12.1 database is 11.2.3.2.1. You can also run a mix of 12.1 and 11.2 databases on Exadata. All features of the Oracle Database 12.1, including the multitenant pluggable databases are supported on Exadata. However, please note that the 12.1 smart scans will not filter rows in the Exadata storage and the 12.1 IORM plans will not be enforced in the storage. These limitations will be removed in a future Exadata storage server software version 12.1.1.1.0. Please look at the MOS note 1537407.1 for details. All other Exadata storage features like HCC, Smart Flash Cache, Smart Logging work as before.

12c pluggable databases, what that mean?

Stay tuned. Details will be explained in a few minutes. Also, much more info at: http://www.oracle.com/multitenant explain what multitenant means
With Multitenant you can have multiple pluggable databases - PDBs ("tenants") plugged into a single multitenant container database (CDB). Consolidate to support more applications per server. Manage many as one. Move PDBs between CDB to move between SLA tiers - unprecedented agility. No application changes required. More information upcoming in webcast.

Can migration be done one pluggable to another pluggable set?

Yes, migration between multitenant container databases (CDB) is supported. This gives us great agility, for example in migration between SLA tiers.

How SGA would be managed with mutiple pluggable database ?

The SGA is shared across the entire CDB. This shared overhead allows us to achieve maximum consolidation density.

Is pluggable database a chargeable option, and if so can we deploy 12c without using PDB?

Yes, Oracle Multitenant is a licensed option for Oracle Database 12c Enterprise Edition. However, you can deploy Oracle Database 12c in the so-called non-CDB architecture, just as before.

Tom, currently I get 50% compression for compress for OLTP in 11gR2. Will we get better compression in 12c like RainStor products?

The core algorithms for OLTP Table Compression - now called Advanced Row Compression - haven't changed from 11g to 12c, so I don't expect that your compression ratios will change. However, you have options in 11g and 12c to increase compression, by using Oracle storage - Exadata, ZFSSA, or Pillar Axiom - which allows you to use Hybrid Columnar Compression (HCC). You can estimate the HCC compression ratios you will get even if you don't have Oracle storage by using the compression advisor.

Also, in 12c you can use Heat Map and Automatic Data Optimization to automatically move data to desired storage tiers and compression levels, with no downtime and no DBA scripting.

Is Oracle Multitenant option an additional licence cost?
Oracle Multitenant is a new option with Oracle Database 12c. The single tenant configuration - one PDB per CDB is a no cost configuration. Two or more PDBs per CDB require the Multitenant Option license.

how much data 12c can store?>500GB
12c can store as much data as 11g - depending on your block size, the maximum size of a single Oracle 12c db can range into multiple petabytes...

is there a limit on # of pluggable databases per container?
We support up to 252 PDBs per CDB.

Is "Heat Map and Automatic Data Optimization" a feature or an option?
Heat Map and Automatic Data Optimization are features of Oracle Database 12c that are part of the Advanced Compression Option.

I have download OWB but it is not working with 12c

Try looking at the posts on the OWB Blog: https://blogs.oracle.com/warehousebuilder/entry/oracle_database_12c and I would suggest posting question on the OWB forum   https://forums.oracle.com/community/developer/english/business_intelligence/data_warehousing/warehouse_builder

Do we gain any PDB benefits by using the no cost one PDB per one CDB configuration, or does it just increase complexity?

No, this configuration simplifies patch application. Patch CDB2 and migrate PDB from CDB1 to CDB2 to apply the patch.

Is there any plan to extend HCC beyond Exadata,Pillar Axiom and ZFS on commodity hardware?

There are no plans to extend support for HCC beyond the existing Oracle storage platforms.

If one unplugs a DB and plugs into a different CDB can one still have access to the DB during the move of the DB from one CDB to another?

No. The database is inaccessible from an application perspective while unplugged.

Is Licensing impacted due to Container/tenant  databases?

Oracle Multitenant is a new licensed option with Oracle Database 12c.

In Oracle 12c is it still available Tablespace Encryption?

Yes. Transparent Data Encryption is available in Oracle Database 12c. This enables you to do both tablespace and column level encryption.

Do DB links work in the same manner with 12c CDB/PDB as prior versions?

Functionally, DB links between PDBs work just as they did between databases in prior versions of Oracle. Some perfrormance enhancements have been implemented for DB Links between PDBs in a single CDB.

When we have multiple PDB under 1 CDB, are there any PGA/SGA settings where I control the amount of memory to allocate to a particular PDB?

No. All the PDBs share, and compete for, the same SGA. This is just the same as when customers do schema-based consolidation.

When upgrading, an existing database becomes a PDB. Right?

Not exactly. Upgrade to Oracle Multitenant is a 2-stage process.
1. Upgrade to Oracle Database 12c - non-CDB (the old architecture).
2. Adopt the non-CDB as a PDB. Step 2 is optional.

Does the base architecture will remain same or changed in 12c ?

We recommend the "Oracle Multitenant" technical whitepaper on OTN. An internet search for "Customer challenges addressed by Oracle Multitenant" finds it immediately. Despite what this section heading implies for the approach, the paper is very solidly technical. It aims for precision and completeness -- and not at all for brevity.

what is the main objective of Pluggable database concept..??

Reduce CapEx, Reduce OpEx, Increase Agility, No application changes required.

Can I migrate and run an 11gR2 DB into a Pluggable DB on 12c?  Without change?

Yes. This is simple. You upgrade in place to a 12.1.0.1 non-CDB and then plug that in as a PDB. Then you run a single post-plug step. The time time takes is independent of the size of the former non-CDB.

Can you have different database Oracle versions within the same tenant?

The benefit of Oracle Multitenant is the flexibility and agility of consolidating the container database management system and abstracting that from the data contained in pluggable databases.  This consolidation is enabled by ensuring that all pluggable databases plugged into a container database to be on the same version/patchset level.

Is there a 12c equivalent of grid Infrastructure?

There is Oracle Grid Infrastructure 12c with new features. Please, see: http://www.oracle.com/technetwork/products/cloud-storage/index-100339.html

How will the patching work when you have pluggable databases? Do they all have to have the same patch set applied?

The patch set level applies at the CDB level. All PDBs plugged into the CDB will be at the same patch set level. The beauty is that the patch only needs to be applied once - at the CDB level - and all tenants benefit. If you want to migrate individual PDBs to a new patch set level, use 2 CDBs, one at patch level x, the other at level x 1. Apply patches to individual PDBs by unplugging them from one CDB and plugging into the other one.

what is a typical size of an unplugged database, I would assume it is more than just an export.  Is an unplugged database an improvment upon typical backup/recovery functionality?

A pluggable database consists of a set of schemas and tablespaces which contain the data and the meta information for this data that is in the data dictionary.  All this is packaged together in a neat bundle so it can be moved (uplugged from one container database and plugged into another) and backed up and recovered independenly of other pluggable databases.   We expect most customers will backup whole container databases rather than each pluggable database separately because it is much easier to do this.   A major benefit of pluggable database is reduced management costs.

If CBD fails then all PBD fails then it will be single point of failure, so how can we deal with it. keep apart about HA for CBD

Active Data Guard or Data Guard provide the mechanism for preventing a CDB from being a potential for a single point of failure - each will maintain a synchronized replica of the CDB on seperate system and storage.  There are also management efficiencies.  Where before, if you had 50 independent databases, you would have 50 standbys to manage.  With Oracle Multitenant, you have a single primary (the CDB) and a single standby to manage.  For more on Data Guard see: http://www.oracle.com/technetwork/database/availability/active-data-guard-wp-12c-1896127.pdf.  If you wish to do granular replication at the level of an individual pdb, then GoldenGate provides that answer: http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

Is GoldenGate a feature of 12c by default for replication?

Active Data Guard, Data Guard, and GoldenGate are all replication options for 12c.  The first two provide simple, complete, one way physical replication with target able to be open read-only.  The later provides advanced logical replication (subsets, bi-directional, transformations, many-to-one, etc) with target open read-write.  See:http://www.oracle.com/us/products/database/options/active-data-guard/overview/index.html and http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

Can one CDB have primary PDB and its corresponding standby PDB?

Data Guard in 12c Release 1 works with Container Databases.   So all the Pluggable Databases in a Container Database will be protected by Data Guard,  When you add Pluggable Databases to a container database it is automatically protected by Data Guard which is a nice benefit.

what is the backup & recovery of CDB & PDB's ??

We expect you to run scheduled backups for the whole CDB to win the "manage as one" benefit. But you'll probably want to do point-in-time-recovery for a single PDB. So, yes, PDB PITR is supported!

Would each plugable database has their own undo and temp tablespaces ? or they share the same undo & temp tablespaces ?

Redo and undo are shared across the entire CDB.

is it possbile to backup PDB separatly and recover it?

Yes. We do support PDB PITR!

if we are going to have one CDB with many PDB, with just one SGA, does it means we will have more performance issue, contention, and etc.

Oracle Enterprise Manager 12c works in concert with Oracle Database 12c to ensure the highest performance and most optimal resource management.  To learn more, visit http://www.oracle.com/us/products/enterprise-manager/index.html

In 12c CDB we are sharing undo & redo log files among CDB & PBD...what if current redo logfile get lost due to catastropic failure, in that case all the pluggable database will be unavailable. Isn't a drawback of 12c CDB feature?

You can protect a CDB against catastropic failures with Data Guard.   All the PDBs in the CDB are protected.   If you create new PDBs in the CDB they are automatically protected as well.   This reduces management costs significantly because you don't need to setup Data Guard or other facilities like RAC for every new database to give it good HA protections

When many databases are consolidated with PDBs, during patching, can I choose which database to patch because I don't have common down time window?

There are two options for applying patches with Oracle Multitenant. 
1. Apply patch at the CDB level. All PDBs will benefit.
2. Create a second CDB and patch that. When your application is ready to be patched, simply unplug the PDB fro the old CDB and move it to the patched CDB.

Is the command set for controlling the CDB able to shutdown all PDBs under a CDB?

The instance opens the CDB as a whole. You acn set the open mode (read-write, read-only, or "closed") for an individual PDB -- and you can make a different choice for this in each different RAC instance.

the solution he just described with no data loss for far async dataguard configs, is this different than traditional dataguard? Is there an additional cost?

the feature described is Far Sync.  It is a feature of Active Data Guard (so primary sending and the standby receiving redo from Far Sync needs to be licensed for Active Data Guard) - you don't need a seperate license for the server that Far Sync runs on.  It is very different in that the Far Sync instance has no data files, does not run recovery - it just forwards redo to a remote standby and enables zero data loss failover.  See more about Far Sync in the technical white paper: http://www.oracle.com/technetwork/database/availability/active-data-guard-wp-12c-1896127.pdf

Any improvements to DatagUARD and  Broker in  12C?

Very significant improvements, most notable is validate database command that automates extensive healt checks, particularly useful before executing switchovers.  Also there is the concept of resumable switchover, to gracefully handle any unexpected errors during role transtions, see more in the technical white paper http://www.oracle.com/technetwork/database/availability/active-data-guard-wp-12c-1896127.pdf and on OTN at http://www.oracle.com/goto/dataguard

Does the new synchronous dataguard feature has distance limitation?

there is always a practical distance limitation between source and a synchronous destination.  Fast Sync will extend that distance by taking standby disk i/o out of the round-trip time.  Far Sync makes the distance between primary and standby unlimited for the purpose of performing a zero data loss failover to a remote standby - but the Far Sync instance will still have the usual distance considerations between it and the source (since it is like any other synchronous destination).

any enhancements to RMAN in oracle 12c?

Yes, look at the 12c RMAN Users Guide, "New Features & Changes". Table recovery from backup, cross-platform backup & restore, pluggable database backup & recovery are some of the new features.

Tuesday, June 4, 2013

Oracle Database Optimal Undo_retention time and Undo Size

Optimal undo_retention time

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
    SUBSTR(e.value,1,25)    "UNDO RETENTION [Sec]",
    ROUND((d.undo_size / (to_number(f.value) *
    g.undo_block_per_sec)))"OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))undo_block_per_sec
       FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';

Optimal Undo Size

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
      v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';

Tuesday, April 30, 2013

Create Manual Snapshots and Generate AWR Report for Database

Create Manual Snapshot

Login to the database using SYS user.

Then, execute the following command to create a manual snapshot.
EXECUTE dbms_workload_repository.create_snapshot();

Then wait for 30 min ( or required time period ) and create another manual snapshot by following command.
EXECUTE dbms_workload_repository.create_snapshot();
 
Then generate the AWR between those two manual snapshots.

Generating an AWR Report

The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.

To generate an AWR report for a range of snapshots:

    At the SQL prompt, enter:

    @$ORACLE_HOME/rdbms/admin/awrrpt.sql

    Specify whether you want an HTML or a text report:
    Enter value for report_type: text

    Specify the number of days for which you want to list snapshot Ids.

    Enter value for num_days: 2

    A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

    Specify a beginning and ending snapshot ID for the workload repository report:

    Enter value for begin_snap: 150
    Enter value for end_snap: 160

    In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

    Enter a report name, or accept the default report name:

    Enter value for report_name:
    Using the report name awrrpt_1_150_160

    In this example, the default name is accepted and an AWR report named awrrpt_1_150_160 is generated.

Wednesday, April 24, 2013

EBS R12 Cloning Issue Resolution on AIX / LINUX

Perl lib Version Conflict Resolution

While doing cloning 11gR2 database, the following error occurs in ApplyDBTier log:
Perl lib version (v5.8.5) doesn't match executable version (v5.10.0) at /usr/lib/perl5/5.8.5/i386-linux-thread-multi/Config.pm line 32.

It can fixed by setting PERL5LIB as follows.

export PERL5LIB=/finsys/db/tech_st/11.2.0/perl/lib/5.10.0:/finsys/db/tech_st/11.2.0/perl/lib/site_perl/5.10.0:/finsys/db/tech_st/11.2.0/appsutil/perl

Rapid Clone Error : 11gR2 Database : Perl lib Version (v5.8.5) Doesn't Match Executable Version (v5.10.0) [ID 1276455.1]

RC-00118 Error Resolution

RC-00118: Error occurred during creation of database
Raised by oracle.apps.ad.clone.ApplyDatabase
StackTrace:
java.lang.Exception: Control file creation failed
        at oracle.apps.ad.clone.ApplyDatabase.doConf(ApplyDatabase.java:635)
        at oracle.apps.ad.clone.ApplyDatabase.doApply(ApplyDatabase.java:473)
        at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:366)
        at oracle.apps.ad.clone.ApplyDBTier.(ApplyDBTier.java:110)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)
        at java.lang.Thread.run(Unknown Source)

Change value of context variable s_db_listener in context file to the required target value (eg: TEST) and run autoconfig it will complete successful.


Cloning Error with ORA-01194 and RC-00118 can be resolved by following Oracle Note.

Rapid Clone Failed While Running "Adcfgclone.pl dbTier": ORA-01194 and RC-00118 [ID 375389.1]

Important oracle docs for block corruption issues.

Failure to Create new Control File Migrating From 32-Bit 10gr1 To 64-Bit 10gr2 [ID 458401.1]
Resolving ORA-600[3020] Raised During Recovery [ID 361172.1]
Ora-600 [Kcrrge.Cannotread_inc.1] Report During Block Media Recovery [ID 742468.1]



Wednesday, April 17, 2013

Feature Availability by Oracle Database Edition

 Feature Availability for Oracle Database Editions
Feature/Option SE1 SE EE Notes
High Availability
       
Oracle Fail Safe
Y
Y
Y
Windows only
Oracle Data Guard—Redo Apply
N
N
Y
 
Oracle Data Guard—SQL Apply
N
N
Y
 
Oracle Data Guard—Snapshot Standby
N
N
Y
 
Oracle Active Data Guard
N
N
Y
Extra cost option
Rolling Upgrades—Patch Set, Database, and Operating System
N
N
Y
 
Online index rebuild
N
N
Y
 
Online index-organized table organization
N
N
Y
ALTER TABLE ... MOVE ONLINE operations
Online table redefinition
N
N
Y
Using the DBMS_REDEFINITION package
Duplexed backup sets
N
N
Y
 
Block change tracking for fast incremental backup
N
N
Y
 
Unused block compression in backups
N
N
Y
 
Block-level media recovery
N
N
Y
 
Lost Write Protection
N
N
Y
 
Parallel backup and recovery
N
N
Y
 
Tablespace point-in-time recovery
N
N
Y
 
Trial recovery
N
N
Y
 
Fast-start fault recovery
N
N
Y
 
Flashback Table
N
N
Y
 
Flashback Database
N
N
Y
 
Flashback Transaction
N
N
Y
 
Flashback Transaction Query
N
N
Y
 
Flashback Data Archive (Total Recall)
N
N
Y
Requires Oracle Advanced Compression option
Scalability
       
Oracle Real Application Clusters
N
Y
Y
Extra cost with EE, included with SE
Automatic Workload Management
N
Y
Y
Requires Oracle Real Application Clusters
Performance
       
Client Side Query Cache
N
N
Y
 
Query Results Cache
N
N
Y
 
PL/SQL Function Result Cache
N
N
Y
 
In-Memory Database Cache
N
N
Y
Extra cost option
SQL Plan Management
N
N
Y
 
Support for Oracle Exadata Storage Server
N
N
Y
 
Support for Oracle Exadata Storage Server Software
N
N
Y
 
Security
       
Oracle Advanced Security
N
N
Y
Extra cost option
Oracle Label Security
N
N
Y
Extra cost option
Virtual Private Database
N
N
Y
 
Fine-grained auditing
N
N
Y
 
Oracle Database Vault
N
N
Y
Extra cost option
Development Platform
       
SQLJ
Y
Y
Y
Requires Oracle Programmer
Oracle Developer Tools for Visual Studio .NET
Y
Y
Y
Windows only
Microsoft Distributed Transaction Coordinator support
Y
Y
Y
Windows only
Active Directory integration
Y
Y
Y
Windows only
Native .NET Data Provider—ODP.NET
Y
Y
Y
Windows only
.NET Stored Procedures
Y
Y
Y
Windows only
Manageability
       
Oracle Change Management Pack
N
N
Y
Extra cost option
Oracle Configuration Management Pack
N
N
Y
Extra cost option
Oracle Diagnostic Pack
N
N
Y
Extra cost option
Oracle Tuning Pack
N
N
Y
Extra cost option, also requires the Diagnostic Pack
Oracle Provisioning and Patch Automation Pack
N
N
Y
Extra cost option
Database Resource Manager
N
N
Y
 
Oracle Real Application Testing
N
N
Y
Extra cost option
VLDB, Data Warehousing, Business Intelligence
       
Oracle Partitioning
N
N
Y
Extra cost option
Oracle OLAP
N
N
Y
Extra cost option
Oracle Data Mining
N
N
Y
Extra cost option
Oracle Advanced Compression
N
N
Y
Extra cost option
Direct-Load Table Compression
N
N
Y
 
Bitmapped index, bitmapped join index, and bitmap plan conversions
N
N
Y
 
Parallel query/DML
N
N
Y
 
Parallel statistics gathering
N
N
Y
 
Parallel index build/scans
N
N
Y
 
Parallel Data Pump Export/Import
N
N
Y
 
Transportable tablespaces, including cross-platform
N
N
Y
Import of transportable tablespaces supported into SE, SE1, and EE
Summary management—Materialized View Query Rewrite
N
N
Y
 
Asynchronous Change Data Capture
N
N
Y
 
Integration
       
Basic Replication
Y
Y
Y
SE1/SE: read-only, updateable materialized view
Advanced Replication
N
N
Y
Multi-master replication
Oracle Streams
Y
Y
Y
SE1/SE: no capture from redo
Database Gateways
Y
Y
Y
Separate product license
Networking
       
Oracle Connection Manager
N
N
Y
 
Infiniband Support
N
N
Y
 
Content Management
       
Oracle Spatial and Graph
N
N
Y
Extra cost option
Semantic Technologies (RDF/OWL)
N
N
Y
Requires the Oracle Spatial and Graph option and the Oracle Partitioning option