ORA-28017: The password file is in the legacy format.

Today I was trying to change password for one of my 12c database and got below error :-

SQL> alter user dbsnmp identified by "*************"
*
ERROR at line 1:
ORA-28017: The password file is in the legacy format.

Cause :- 

Looks like update to password file failed as the file format is wrong. 

Administrative privileges like sysbackup, syskm, sysdg cannot be granted unless the password file is in 12c Format

Solution :-

This error generally comes when you migrate your database from 11g to 12c

Regeneration of password will resolve this error.

PFB steps :-

1. Check which users have access to password file before regenerating it.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

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

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

DBSNMP                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

2. Recreate your password file :-

==>orapwd file=$ORACLE_HOME/dbs/orapwamit entries=5 force=y

Enter password for SYS:

[Target_server][oracle][amit]

3. Now by default only sys have access to it

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

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

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

4. Grant sysdba privilege to DBSNMP user to have access to password file

grant sysdba to dbsnmp;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

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

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

DBSNMP                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

5. Now again go for the password change, this time it will be successful.

SQL> alter user dbsnmp identified by "*************";

User altered.

I hope this article helped you.

Thanks
Amit Rath

MRP0: Background Media Recovery terminated with error 1111

Today while troubleshooting a DR database sync scenario issue, I got below error in alert log file :-

MRP0: Background Media Recovery terminated with error 1111
Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit1_pr00_1890.trc:
ORA-01111: name for data file 34 is unknown - rename to correct file
ORA-01110: data file 34: '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034'
ORA-01157: cannot identify/lock data file 34 - see DBWR trace file
ORA-01111: name for data file 34 is unknown - rename to correct file
ORA-01110: data file 34: '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034'

Cause :-

Above issue can occur in DR database in below scenarios :-

1. Space issue in DR database and a datafile is added in primary database.
2. Standby file management is set to manual
3. DB_create_file_dest or DB_file_name_convert parameter is not set in Standby database

Troubleshoot :-

When I crosscheck the primary database and standby database sync details , I saw Redo Apply was stopped.

I crosschecked the alert log files for standby database and got above mentioned error in alert logfile.

Checked the datafile details in DR database :-

File_id       name
34             /opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034

Checked the Primary alert log file and got to know that a datafile addition activity happened in primary database.

Crosschecked all the above mentioned parameters and got to know that space exhausted in BCP database due to that Oracle created an Unknown file in ORACLE_HOME location due to which Redo Apply stopped.

Solution :-

Checked the added file detail in Production database :-

File_id       name
34            +DATA/amit/datafile/data.321.931222251

Login to Standby database server :-

1. As space was exhausted on Diskgroup level, added space by logging in to ASM instance
2. Login to DR database instance :-

a. Change the standby_file_management parameter to Manual

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO
SQL> alter system set standby_file_management=MANUAL sid='*';

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
SQL>
SQL> select name from v$datafile where file#=34;

NAME
--------------------------------------------------------------------------------
/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034

SQL> alter database create datafile '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034' as '+DATA' size 10g;

Database altered.

After creation of the datafile in DR database , check the file detail in DR database :-

File_id       name
34             +DATA/amit_dr/datafile/data.278.293456881

Check the redo apply status, if not started , start it manually using dgmgrl.

I hope this article helped you.

Thanks
Amit Rath

RMAN restore fails with RMAN-06023 but backups are available

Today while I was restoring one RMAN backup to a new host , I got very different error while restoring even though backup pieces are available and cataloged also. PFB error details :-

Error:-

creating datafile file number=1 name=/u01/oracle/amit/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/27/2015 03:19:59
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u01/oracle/amit/system01.dbf'

Checked backup was also available for datafile 1

RMAN> list backup of datafile 1;


List of Backup Sets

===================

.

.

   List of Backup Pieces for backup set 30299 Copy #1

    BP Key  Pc# Status      Piece Name

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

    30299   1   EXPIRED     /u00/oracle/admin/amit/backup/hot_amit_30306_1_20140701

  Backup Set Copy #2 of backup set 30299

  Device Type Elapsed Time Completion Time     Compressed Tag

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

  DISK        00:52:02     2015-07-27 03:18:48 YES        HOT_DISK_amit

    List of Backup Pieces for backup set 30299 Copy #2

    BP Key  Pc# Status      Piece Name

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

    30310   1   AVAILABLE   /ora_backup/test/amit/hot_amit_30306_1_20140701

Cause :-

When we restore using a backup controlfile and run the catalog command to catalog backup pieces, Oracle does two things, First it does an implicit crosscheck for files available in the recovery area and then it does an explicit crosscheck for the location you mentioned to catalog the pieces.

While doing an implicit catalog, if there is any file available(archive log, online log, any datafile) of different incarnation from the control file, Oracle register that file details to the controlfile and changes the incarnation value for the control file as per the available file in the Recovery area.

Now when we try to restore our datafiles from backup pieces, controlfile is not able to recognize the backup pieces as they are from different incarnation.

So we get "ORA-01110: data file 1: '/u01/oracle/amit/system01.dbf'" even if we have backup pieces available.

Solution :-

1. Clean up you recovery area before doing a catalog for backup pieces. Run a Crosscheck and delete expired backup details.
2. Disable Recovery area details before running catalog command, you can do it by removing db_recovery_file_dest and db_recovery_file_dest_size parameters in parameter file.

After doing any of the above try to restore the backup , it will complete without any issues.

I hope this article helped you

Thanks
Amit Rath

ORA-00060: deadlock detected while waiting for resource ( Deadlock due to Bitmap Indexes)

Yesterday I was testing the below scenario with development team using Bitmap indexes .

Concept :-

Generally Bitmap index are created in Data warehouse environments where database activity is mostly read and writing is significantly less.

Bitmap indexes are created on columns with less cardinality , so one row of a bitmap index points to multiple rowid's to the column in the table , irrespective of B-tree indexes which points to only one row id.

Now any DML on a column having bitmap index ends up locking many rows in the index.

Every record for a column having bitmap index, there will be a index record created for that which will point to the number of rows in that column having that value.

For Eg , Gender Column will have 2 distinct values, Male and Female. If we put a Bitmap index on Gender column then index created will have two index records, Male and Female , and each index records will have detail for all the column values.

If Column's 1st, 5th and 9th record has value as Male then the index record will have value '1' in 1st , 5th and 9th record and '0' in the other.

Same goes for the Female Index record also, if 2nd, 4th, 3rd and 6th have value as Female then the index record will have '1' in 2nd, 4th, 3rd and 6th record and 0 in other.

Especially concurrent single row DML operations goes for a deadlock.

What happens when we try to insert same value to a column having Bitmap index associated to it from different sessions. PFB detail:-

When we try to insert same value from different sessions, both session tries to aquire lock for the same region of Bitmap index to create a index record, resulting in Deadlock.

Below is a demo for that :-

SQL> create table test_bitmap ( name varchar(10),age number);

Table created.

SQL> create bitmap index test_bitmap_idx on test_bitmap(age);

Index created.

Session 1:-

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: rick
Enter value for age: 34
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('rick',34)

1 row created.

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: Ramse
Enter value for age: 24
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('Ramse',24)
Waiting ……………………


Session 2:-

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: Ramse
Enter value for age: 24
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('Ramse',24)

1 row created.

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: rick
Enter value for age: 34
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('rick',34)
insert into test_bitmap values ('rick',34)
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Generally this is not the case with normal index while doing insert or any DML.

Solution :-

Prefer not to do insert/update/delete on columns having Bitmap indexes, if Business requirement is there then remove the Bitmap index from that column.

I hope this article helped you.

Thanks
Amit Rath

ORA-65114: space usage in container is too high

Yesterday I was doing an import for a pluggable database and it ran out of space so I tried to resize one of the datafile and got below error while doing it :-

SQL> ALTER DATABASE DATAFILE '+DATA1/AMIT_CON/3933D120863A61B7E0531609900AE4EE/DATAFILE/AMIT.786.919009661' RESIZE 100g;
ALTER DATABASE DATAFILE '+DATA1/AMIT_CON/3933D120863A61B7E0531609900AE4EE/DATAFILE/AMIT.786.919009661' RESIZE 100g
*
ERROR at line 1:
ORA-65114: space usage in container is too high

Cause :-

This error generally comes when total size of PDB after resize of the datafile is more than the size you have allocated to a PDB while creating.

Solution :-

Change the allocation storage for a PDB and then try to resize the datafile.

Note :- while changing the storage for a PDB , check if space is available in ASM disks.


Allocation storage of a PDB can be changed by logging in to the PDB :-

SQL> alter session set container=AMIT;

Session altered.

Either change the allocation storage to a required value or change it to unlimited.

SQL> ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

Pluggable database altered.

OR

SQL> alter pluggable database storage (MAXSIZE 120G);

Pluggable database altered.

Now try to resize the datafile

SQL> ALTER DATABASE DATAFILE '+DATA1/AMIT_CON/3933D120863A61B7E0531609900AE4EE/DATAFILE/amit.786.919009661' RESIZE 100556633088;

Database altered.

I hope this article helped you.

Thanks
Amit Rath

ORA-31623: a job is not attached to this session via the specified handle

Today I was doing an import and got below error while import was initiating :-

Issue :-

Import: Release 11.2.0.4.0 - Production on Thu Aug 4 22:49:29 2016

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

Username: /as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

Cause :-

Above issue can be caused if any registry component is invalid or Oracle is not able to provide sufficient memory to Datapump job.

Solution :-

Crosschecked the DBA _REGISTRY , all components were in VALID state.

Checked the SGA component allocated to database and saw that only SGA_MAX_TARGET is mentioned, SGA_TARGET is 0 . PFB below :-

Datapump use streams pool memory parameter to allocate memory to jobs, if SGA is manually sized then allocate some memory to streams_pool_size

SQL> alter system set streams_pool_size=128M scope=both;

IF ASMM/AMM is used then perform below steps :-

SQL> show parameter sga

NAME                                 TYPE        VALUE

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

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 1280M

sga_target                           big integer 0

Increased the value of SGA target to 4GB

SQL> alter system set sga_max_size=4g scope=spfile;

System altered.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2260088 bytes

Variable Size            4043309960 bytes

Database Buffers          201326592 bytes

Redo Buffers               28884992 bytes

Database mounted.

Database opened.

SQL> alter system set sga_target=4g;

SQL> show parameter sga

NAME                                 TYPE        VALUE

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

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 4G

sga_target                           big integer 4G

Tried import after this and it completed without any errors.

I hope this article helped you

Thanks

Amit Rath

How to recreate Oracle Inventory

Today I was applying patch on one of my development server and I got below error :-

[oracle@omr oraInventory]$ ./opatch lsinvinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/product/database/11.2.0.4
Central Inventory : /opt/orainv/oraInventory
   from           : /opt/oracle/product/database/11.2.0.4/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /opt/oracle/product/database/11.2.0.4/cfgtoollogs/opatch/opatch2016-03-11_04-33-59AM_1.log

Lsinventory Output file location : /opt/oracle/product/database/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2016-03-11_04-33-59AM.txt

OPatch failed to locate Central Inventory.
Possible causes are: 
    The Central Inventory is corrupted
    The oraInst.loc file specified is not valid.
LsInventorySession failed: OPatch failed to locate Central Inventory.
Possible causes are: 
    The Central Inventory is corrupted
    The oraInst.loc file specified is not valid.


OPatch failed with error code 73

Cause :- 

Checked the Inventory location, /opt/orainv/oraInventory , it was empty, seems someone accidentally deleted it.

Solution :-

We have to recreate our Inventory with all the ORACLE_HOME details available in the server before this blunder happened.

To crosscheck , what all products are installed, query the oratab file :-

grid:/opt/grid/11.2.0.4:N
agent:/opt/oracle/product/agent/core/12.1.0.4.0:N
+ASM:/opt/grid/11.2.0.4:N
amit:/opt/oracle/product/database/11.2.0.4:N        

We can see that , we have GRID_HOME, ORACLE_HOME and AGENT installed in this server.

Recreating the Oracle Inventory :-

1. Attach Oracle Home

Oracle Home :- /opt/oracle/product/database/11.2.0.4
Oracle Home Name :- Db11g_home1

$ ./runInstaller -silent -attachHome ORACLE_HOME="/opt/oracle/product/database/11.2.0.4" -invPtrLoc /etc/oraInst.loc ORACLE_HOME_NAME="Db11g_home1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/orainv/oraInventory
'AttachHome' was successful.

$ ./opatch lsinventory -oh /opt/grid/11.2.0.4
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/grid/11.2.0.4
Central Inventory : /opt/orainv/oraInventory
   from           : /opt/grid/11.2.0.4/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.4.0
Log file location : /opt/grid/11.2.0.4/cfgtoollogs/opatch/opatch2016-03-10_23-34-48PM_1.log

List of Homes on this system:

  Home name= OraDb11g_home1, Location= "/opt/oracle/product/database/11.2.0.4"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

We have to attach grid home also.

2. Attach Grid Home to the Inventory

Grid Home :- /opt/grid/11.2.0.4
Grid Home Name :- 11g_gridinfrahome1

$ ./runInstaller -silent -attachHome ORACLE_HOME="/opt/grid/11.2.0.4" -invPtrLoc /etc/oraInst.loc ORACLE_HOME_NAME="11g_gridinfrahome1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/orainv/oraInventory
'AttachHome' was successful.

3. Attach Agent Home to the Inventory

Agent Home :- /opt/oracle/product/agent/core/12.1.0.4.0
Agent Home Name :- agent12c1

$ ./runInstaller -silent -attachHome ORACLE_HOME="/opt/oracle/product/agent/core/12.1.0.4.0" -invPtrLoc /etc/oraInst.loc ORACLE_HOME_NAME="agent12c1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/orainv/oraInventory
'AttachHome' was successful.

All Homes present in the server added to the Central Inventory.

I hope this article helped you.

Thanks
Amit Rath

ORA-16700: the standby database has diverged from the primary database

Yesterday I faced below error in my standby database :-

Problem :-

DGMGRL> show database amit_dr

Database - amit_dr

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      148.00 KByte/s
  Real Time Query: OFF
  Instance(s):
   amit_dr

  Database Error(s):
    ORA-16700: the standby database has diverged from the primary database
    ORA-16766: Redo Apply is stopped

Database Status:
ERROR

Cause :- 

This happened because when due to an incomplete recovery in primary database, it has to be opened using resetlogs option and incarnation of primary and standby becomes different due to resetlogs option.

we will get below error in our standby database alert logfile

MRP0: Incarnation has changed! Retry recovery...

Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit_pr00_17847.trc:

ORA-19906: recovery target incarnation changed during recovery

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Fri Jul 24 05:58:48 2015

Archived Log entry 113 added for thread 1 sequence 1 ID 0xf0d327b6 dest 1:

Recovered data files to a consistent state at change 15883769

Fri Jul 24 05:58:48 2015

 started logmerger process

Fri Jul 24 05:58:48 2015

Managed Standby Recovery starting Real Time Apply

Warning: Recovery target destination is in a sibling branch

of the controlfile checkpoint. Recovery will only recover

changes to datafiles.

Datafile 1 (ckpscn 15883769) is orphaned on incarnation#=5

MRP0: Detected orphaned datafiles!

Solution :-

We do not have to rebuild our standby, just move standby database using flashback to a previous SCN value when the incarnation of primary and standby was same. Start the MRP manually or bounce the configuration in DG broker.

SQL> flashback database to scn 15883324;

Flashback complete.

Details from alert log on standby :-

flashback database to scn 15883324

Flashback Restore Start

Flashback Restore Complete

Flashback Media Recovery Start

Fri Jul 24 06:06:06 2015

Setting recovery target incarnation to 5

 started logmerger process

Parallel Media Recovery started with 12 slaves

Fri Jul 24 06:06:06 2015

Media Recovery Log +AMIT_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789

Incomplete Recovery applied until change 15883325 time 07/24/2015 05:52:43

Flashback Media Recovery Complete

Setting recovery target incarnation to 6

Completed: flashback database to scn 15883324

Attempt to start background Managed Standby Recovery process (dr3fia)

Fri Jul 24 06:10:35 2015

MRP0 started with pid=49, OS id=14805

MRP0: Background Managed Standby Recovery process started (dr3fia)

 started logmerger process

Fri Jul 24 06:10:40 2015

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 12 slaves

Media Recovery start incarnation depth : 1, target inc# : 6, irscn : 15883360

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log +AMIT_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

DGMGRL> show configuration

Configuration - amit_dr

  Protection Mode: MaxPerformance

  Databases:

    amit  - Primary database

    amit_dr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

I hope this article helped you.

Thanks

Amit Rath

ORA-25191: cannot reference overflow table of an index-organized table

Yesterday I faced below issue when I was trying to grant some privileges to a table :-

grant select,insert,update,delete on AMIT.SYS_IOT_OVER_14456 TO TEST;
                                             *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

Issue :-

This issue comes when we deal with Index Organized tables(IOT).

Solution :-

Issue this statement against the Parent Index Organized table containing the specified overflow table.

SQL> select IOT_NAME from dba_tables where owner='AMIT' and table_name='SYS_IOT_OVER_14456';

IOT_NAME

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

AMIT_TABLE

SQL> grant select,insert,update,delete on AMIT.AMIT_TABLE to TEST;

Grant succeeded.

Any operation related to Index Oraganized table has to be done by above method else it will give ORA-25191.

I hope this article helped you.

Thanks

Amit Rath

ORA-01722 during upgrade 10.2 to 11.2

Yesterday I was trying to upgrade my 10.2 database to 11.2 version in new host and I got below error when I ran catupgrd.sql to upgrade it:-

Issue :-

SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
                 *
ERROR at line 1:
ORA-01722: invalid number

Cause :-

I checked the Timezone value in registry$database table, I got below value

SQL> col PLATFORM_NAME for a30
SQL> set lin 400
SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------                ------------------------------   ----------
         13               Linux x86 64-bit

TZ_Version coloumn was blank, so was getting ORA-01722: invalid number error

Solution :-

Update the Tz_version column of registry$database table with 11.2 version details :-

SQL> create table registry_backup as select * from registry$database;

SQL> INSERT into registry$database (platform_id, platform_name, edition, tz_version) VALUES ((select platform_id from v$database), (select platform_name from v$database),NULL,(select version from v$timezone_file));

1 row created.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit
         13             Linux x86 64-bit                                                                 14

SQL> delete from sys.registry$database where TZ_VERSION is NULL;

1 row deleted.

SQL> select * from registry_backup;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit

SQL> commit;

Commit complete.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit                                                                14

Timezone issue resolved with above solution.

Again I ran catupgrd.sql and now this time I got a new error :-

Issue :-

SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
                 *
ERROR at line 1:
ORA-01722: invalid number

Cause :-

We have to disable the data vault option before running the upgrade and enable it once donewith upgrade

Solution :-

==>chopt disable dv

Writing to /opt/oracle/product/database/11.2.0.4/install/disable_dv.log...
/usr/bin/make -f /opt/oracle/product/database/11.2.0.4/rdbms/lib/ins_rdbms.mk dv_off ORACLE_HOME=/opt/oracle/product/database/11.2.0.4
/usr/bin/make -f /opt/oracle/product/database/11.2.0.4/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/opt/oracle/product/database/11.2.0.4

Now I ran the Catupgrd.sql and it ran without any issues and upgrade got successfully completed.

After the upgrade was done , I enable the data vault option by running below command :-

==>chopt enable dv

I hope this article helped you 

Regards,
Amit Rath

ORA-39083 object type type failed to create with error impdp

Today I was trying to import a schema dump using datapump utility and I got below mentioned error during the import :-

Error Message:
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:

CREATE TYPE "AMIT"."TEST_TYPE"   OID '8E21EF4C1F6EF167E04011AC99643EB0' IS TABLE OF VARCHAR2(40);

Cause :- 

Object Identifier has to be unique in a database. Above statement uses OID of old schema to create object in new schema.

Solution :-

1. Rerun the failed sql like below 

CREATE TYPE "AMIT"."TEST_TYPE"  TABLE OF VARCHAR2(40);

2. Best Solution is to use transform=oid:n in impdp statement.

I hope this article helped you

Regards,
Amit Rath

ORA-39700: database must be opened with UPGRADE option

Today I faced below error while restoring my current database to a new server. PFB :-

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Process ID: 21426

Session ID: 133 Serial number: 23

Cause :-

My current database version is 11.2.0.3.0

In new server database version is 11.2.0.4.0

So when I restored 11.2.0.3 backup to 11.2.0.4 database , it got successfully retored . But when I was trying to open the database got above error.

Solution :-

As in new server database version is higher(11.2.0.4.0), so we have to upgrade our restored database(11.2.0.3.0) to same higher version. As new server Oracle Home is at higher version, so dictionary of restored database also has to be at the same level.

Follow the below steps to start your database successfully in above kind of situations :-

$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 18 03:36:30 2014

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

Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup upgrade pfile='/ora_backup/amit/restore_test/initAMIT.ora'

ORACLE instance started.

Total System Global Area 1060585472 bytes

Fixed Size                  2260000 bytes

Variable Size             633340896 bytes

Database Buffers          402653184 bytes

Redo Buffers               22331392 bytes

Database mounted.

Database opened.

SQL>

SQL> @?/rdbms/admin/catupgrd.sql

DOC>#######################################################################

DOC>#######################################################################

DOC>

DOC>   The first time this script is run, there should be no error messages

DOC>   generated; all normal upgrade error messages are suppressed.

DOC>

DOC>   If this script is being re-run after correcting some problem, then

DOC>   expect the following error which is not automatically suppressed:

DOC>

DOC>   ORA-00001: unique constraint (<constraint_name>) violated

DOC>              possibly in conjunction with

DOC>   ORA-06512: at "<procedure/function name>", line NN

DOC>

DOC>   These errors will automatically be suppressed by the Database Upgrade

DOC>   Assistant (DBUA) when it re-runs an upgrade.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

DOC>######################################################################

DOC>######################################################################

DOC>    The following statement will cause an "ORA-01722: invalid number"

DOC>    error if the user running this script is not SYS.  Disconnect

DOC>    and reconnect with AS SYSDBA.

DOC>######################################################################

DOC>######################################################################

DOC>#

no rows selected

DOC>######################################################################

DOC>######################################################################

DOC>    The following statement will cause an "ORA-01722: invalid number"

DOC>    error if the database server version is not correct for this script.

DOC>    Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and use

DOC>    a different script or a different server.

DOC>######################################################################

DOC>######################################################################

DOC>#

no rows selected

..

.

.

..

.

Oracle Database 11.2 Post-Upgrade Status Tool           10-18-2014 04:38:30

.

Component                               Current      Version     Elapsed Time

Name                                    Status       Number      HH:MM:SS

.

Oracle Server

.                                         VALID      11.2.0.4.0  00:44:37

Oracle Real Application Clusters

.                                       INVALID      11.2.0.4.0  00:00:04

Oracle Workspace Manager

.                                         VALID      11.2.0.4.0  00:00:50

Final Actions

.                                                                00:00:01

Total Upgrade Time: 00:45:35

PL/SQL procedure successfully completed.

SQL>

SQL> SET SERVEROUTPUT OFF

SQL> SET VERIFY ON

SQL> commit;

Commit complete.

SQL>

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> DOC

DOC>#######################################################################

DOC>#######################################################################

DOC>

DOC>   The above sql script is the final step of the upgrade. Please

DOC>   review any errors in the spool log file. If there are any errors in

DOC>   the spool file, consult the Oracle Database Upgrade Guide for

DOC>   troubleshooting recommendations.

DOC>

DOC>   Next restart for normal operation, and then run utlrp.sql to

DOC>   recompile any invalid application objects.

DOC>

DOC>   If the source database had an older time zone version prior to

DOC>   upgrade, then please run the DBMS_DST package.  DBMS_DST will upgrade

DOC>   TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped

DOC>   with Oracle.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

SQL>

SQL> Rem Set errorlogging off

SQL> SET ERRORLOGGING OFF;

SQL>

SQL> REM END OF CATUPGRD.SQL

SQL>

SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.

SQL> REM                This forces user to start a new sqlplus session in order

SQL> REM                to connect to the upgraded db.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

  
This script will run for almost an hour. kindly wait for script to complete.

Once the script completed , database is down as you can see above. Kindly start the database with normal option.

SQL> startup pfile='/ora_backup/amit/restore_test/initAMIT.ora'

 ORACLE instance started.

Total System Global Area 1060585472 bytes

Fixed Size                  2260000 bytes

Variable Size             633340896 bytes

Database Buffers          402653184 bytes

Redo Buffers               22331392 bytes

Database mounted.

Database opened.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

AMIT     READ WRITE

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

Run the utlrp script to check details regarding invalid objects.

SQL> @?/rdbms/admin/utlrp

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN  2014-10-18 05:18:42

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC>   objects in the database. Recompilation time is proportional to the

DOC>   number of invalid objects in the database, so this command may take

DOC>   a long time to execute on a database with a large number of invalid

DOC>   objects.

DOC>

DOC>   Use the following queries to track recompilation progress:

DOC>

DOC>   1. Query returning the number of invalid objects remaining. This

DOC>      number should decrease with time.

DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC>   2. Query returning the number of objects compiled so far. This number

DOC>      should increase with time.

DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC>   This script automatically chooses serial or parallel recompilation

DOC>   based on the number of CPUs available (parameter cpu_count) multiplied

DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC>   On RAC, this number is added across all RAC nodes.

DOC>

DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC>   recompilation. Jobs are created without instance affinity so that they

DOC>   can migrate across RAC nodes. Use the following queries to verify

DOC>   whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC>   1. Query showing jobs created by UTL_RECOMP

DOC>         SELECT job_name FROM dba_scheduler_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC>   2. Query showing UTL_RECOMP jobs that are running

DOC>         SELECT job_name FROM dba_scheduler_running_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END  2014-10-18 05:23:46

DOC> The following query reports the number of objects that have compiled

DOC> with errors.

DOC>

DOC> If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

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

                  0

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

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

                          0

Function created.

  

PL/SQL procedure successfully completed.

  

Function dropped.

  

PL/SQL procedure successfully completed.

Database opened successfully after upgrade.

I hope this article helped you.

Regards,

Amit Rath