About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Tuesday, December 18, 2012

ORA-39142: incompatible version number 3.1 in dump file "/amit/amit.dmp"

Few days ago I was trying to import some tables from one database to another through datapump utility and I faced below mentioned errors :-

bash-3.2$ impdp amit/amit directory=datapump dumpfile=EXPDP_AMIT_TABLES.DMP remap_schema=source:amit

Import: Release 11.1.0.7.0 - 64bit Production on Tuesday, 18 December, 2012 10:14:40

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/amit/amit.dmp"

When I searched regarding this error, I find out that I was trying to transfer data from a higher database version to a lower database version. PFB details of DB version's :-

Oracle Database 11g Enterprise edition Release 11.2.0.1.0 ----- Windows

Oracle Database 11g Enterprise edition Release 11.1.0.7.0 ------ Aix

This problem is not related to different platforms , its due to different version numbers

Solution :-

1. Export the required tables in the source database having higher version number by using version=11.1

2. Now import the required tables in target database and its completes without any errors.

Export :-

expdp amit/amit directory=datapump dumpfile=EXPDP_AMIT_TABLES.DMP logfile=EXPDP_AMIT_TABLES.log version=11.1 tables=A,B,C compression=all

Import :-

impdp amit/amit directory=datapump dumpfile=EXPDP_AMIT_TABLES.DMP remap_schema=source:amit 

It will import the tables without any error.

I hope this article helped you.

Regards,
Amit Rath

Monday, December 17, 2012

Temporary Tablespaces in Oracle Database

What are Temporary Tablespace

Temporary Tablespace contains transient data that persists only for the duration of the session.

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation.

Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

Note:- A temporary tablespace does not contain permanent objects and therefore doesn't need to be backed up.

How to create a temporary tablespace

SQL> create temporary tablespace temp1 tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP01.dbf' size 100m;

Tablespace created.

Default Temporary Tablespace

Default Temporary tablespace can be defined at the database creation time or by issuing an "ALTER DATABASE"

SQL> alter database default temporary tablespace temp1;

Database altered.


Restrictions :-
1. Default Temporary tablespace cannot be dropped till you create another one.
2. Default Temporary tablespace cannot be taken off-line.

If you define a default temporary tablespace , it's automatically assigned to users.

How to find default temporary tablespace of database


SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE          DESCRIPTION
------------------------------                           ------------------------------           ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                               Name of default temporary tablespace

How to change default Temporary Tablespace

1. create a new temporary tablespace

SQL> create temporary tablespace temp1 tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP01.dbf' size 100m;

2. Assign new temporary tablespace as the default temporary tablespace.


SQL> alter database default temporary tablespace temp1;

Database altered.

3. Drop the OLD default temporary tablespace


SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

4. Check that default temporary tablespace changed or not


SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE         DESCRIPTION
------------------------------                           ------------------------------         ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP1                           Name of default temporary tablespace

What are Temp Files

Details of Temp files in the database are not recorded in the control file, means we can recreate them whenever we restore the database or after deleting them accidently.

We cannot remove datafiles from a tablespace till we won't drop the entire tablespace. But we can remove tempfiles from a database :-

How to remove tempfiles from a database :-


SQL> alter database tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP02.DBF' drop including datafiles;

Database altered.

How to add tempfiles to a database:-

SQL> alter tablespace temp add tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP02.DBF' size 100m;

Getting Usage Details of Temp tablespace in a database :-

##################### RAC Database ###############################

SQL> Select INST_ID,TABLESPACE_NAME,sum(BYTES_USED/1024/1024/1024),sum(BYTES_FREE/1024/1024/1024) from gv$temp_space_header group by INST_ID,TABLESPACE_NAME;

##################### StandAlone Database ###############################

SQL> Select TABLESPACE_NAME,sum(BYTES_USED/1024/1024/1024),sum(BYTES_FREE/1024/1024/1024) from v$temp_space_header group by TABLESPACE_NAME;

######################################################################

SQL> Select * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                TABLESPACE_SIZE     ALLOCATED_SPACE      FREE_SPACE
------------------------------                   ---------------                     ---------------                     ----------
TEMP                                       104857600                     8388608                          103809024

########################## INSTANCE WISE ##############################


 SQL> select tablespace_name,
 (free_blocks*8)/1024/1024 FreeSpaceGB,
 (used_blocks*8)/1024/1024 UsedSpaceGB,
 (total_blocks*8)/1024/1024 TotalSpaceGB,
 i.instance_name,i.host_name
 from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
 i.inst_id=ss.inst_id;


TABLESPACE_NAME          FREESPACEGB     USEDSPACEGB    TOTALSPACEGB     INSTANCE_NAME    HOST_NAME
------------------------------            -----------                   -----------                  ------------                    ----------------               ------------------------------
TEMP                                 .007                       .000                      .007                           orcl                          AMIT-PC



I hope this article hepled you.

Regards,
Amit Rath




Friday, October 26, 2012

How to enable Flashback in oracle database 11g

Flashback in Oracle Database

Flashback technology is a set of features in Oracle database that make your work easier to view past states of data or to move your database objects to a previous state without using point in time media recovery.

View past states of data or move database objects to previous state means you have performed some operations like  DML + COMMIT and now you want to rollback that operation, this can be done easily through FLASHBACK technology without using point in time media recovery.

How to enable FLASHBACK in Oracle Database 11G R1 and below versions

1. Database has to be in ARCHIVELOG mode.
     To change ARCHIVE mode refer to -- Change ARCHIVE mode of database

2. Flash Recovery Area has to be configured. To configure PFB steps :-

SQL> show parameter db_recovery_file_dest

NAME                                  TYPE           VALUE
------------------------------------       ----------- -       -----------------------------
db_recovery_file_dest             string
db_recovery_file_dest_size     big integer     0

Currently flashback is disabled. To enable :-

A. Set db_recovery_file_dest_size initialization parameter.

SQL> alter system set db_recovery_file_dest_size=2g;

System altered.

B. After db_recovery_file_dest_size parameeter has been set, create a location in OS where your FLASHBACK logs will be stored.

bash-3.2$ cd /orcl_db
bash-3.2$ mkdir FLASHBACK
bash-3.2$ pwd
/orcl_db/FLASHBACK

C. Now set db_recovery_file_dest initialization parameter.

SQL> alter system set db_recovery_file_dest='/orcl_db/FLASHBACK';    ##########For Standalone database##########

System altered.


SQL> alter system set db_recovery_file_dest='/orcl_db/FLASHBACK' sid='*';    ##########For RAC database##########

System altered.

SQL> show parameter db_recovery

NAME                                 TYPE           VALUE
------------------------------------      -----------         ------------------------------
db_recovery_file_dest            string           /orcl_db/FLASHBACK
db_recovery_file_dest_size     big integer    2G

3. Create an Undo Tablespace with enough space to keep data for flashback operations. More often users update the database more space is required.

4. By default automatic Undo Management is enabled, if not enable it. In 10g release 2 or later default value of UNDO management is AUTO. If you are using lower release then PFB to enable it:-

SQL> alter system set undo_management=auto scope=spfile;

System altered

5. Shut Down your database

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down

6. Startup your database in MOUNT mode

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.

7. Change the Flashback mode of the database

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL>alter database flashback ON;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database open;

Database altered.


FLASHBACK mode of the database has been enabled.

How to disable FLASHBACK in Oracle Database 11G R1 and below versions

1. Shut Down your database

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down

2. Startup your database in MOUNT mode

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>alter database flashback OFF;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO


SQL> alter database open;

Database altered.


FLASHBACK mode of the database has been disabled.


How to enable/disable FLASHBACK in Oracle Database 11G R2 and above versions.

From 11GR2 we donot have to bounce the database to alter flashback.


1. Database has to be in ARCHIVELOG mode.
     To change ARCHIVE mode refer to -- Change ARCHIVE mode of database

2. Flash Recovery Area has to be configured. To configure PFA steps.

3.  TO enable or disable flashback , we can change this while database is in open mode. PFB


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;

Database altered.

SQL> alter database flashback off;

Database altered.

I hope this article helped you.

Regards,
Amit Rath

Thursday, October 25, 2012

How to enable Archivelog mode in Oracle database 11g

Mode of Logging

There are two types of logging modes in Oracle database :-

1. ARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don't have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.

2. NOARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data.

How to check log mode in Oracle database 11g :-

[cognos@rac1 u02]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 25 23:03:44 2012

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

Enter user-name: /as sysdba

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

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /backup/orcl/
Oldest online log sequence     1
Current log sequence           1
SQL> select name,log_mode from v$database;

NAME      LOG_MODE
--------- -    -----------
ORCL      NOARCHIVELOG

Currently the ORCL database is in NOARCHIVELOG mode

To change the Oracle database in ARCHIVELOG mode. PFB below mentioned steps:-

1. If needed set the archive log destination where you want to save your archive logs whether to a single location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.

SQL> alter system set log_archive_dest_1='LOCATION=/u02/archive' scope=spfile;

System altered.

Note -- To change this parameter while database is open, your database has to run with SPFILE, if running through PFILE then shut down your database and make changes in your PFILE and then start the database in MOUNT mode using that changed PFILE

2. After this you need to shut down your database and start again in MOUNT mode

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/archive
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE
---------      ------------
ORCL      ARCHIVELOG

Database changed to ARCHIVELOG mode.

Note :- After you changed your database to ARCHIVELOG mode, take a backup of your database immediately because in recovery scenarios you can recover your database from the last backup taken in this mode.

#############################################################################

To change the Oracle database in NOARCHIVELOG mode. PFB below mentioned steps:-

1.Shutdown your running database.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2. Start your database in MOUNT mode.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/archive
Oldest online log sequence     1
Current log sequence           1
SQL> select name,log_mode from v$database;

NAME      LOG_MODE
---------      ------------
ORCL      NOARCHIVELOG

Database changed to NOARCHIVELOG mode.

I hope this article helped you.

Regards,
Amit Rath

Wednesday, October 24, 2012

ORA-00205: error in identifying control file

Loss of one control files or if any of the current control file is available:-

If you loss any one of the control file present in your database then you are not able to start your database you will get below mentioned error when trying to start your database :-

If database is not running and you try to start your database then loss of one controlfile will give you below error :-

ORA-00205: error in identifying control file

Below are the available actions :-

Copy the available control file to that destination where previously lost control file was available. Now open your database normally.
                                        OR
Remove the detail of the lost control file from the remaining available current controlfile and start the database normally.

If database is running then loss of one controlfile will suspend your work :-

SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: +AMIT_R1/orcl/controlfile/current.260.797540569'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

SQL> shu abort
ORACLE instance shut down.
SQL> exit

After shutting down do one of the below mentioned actions :- 

Copy the available control file to that destination where previously lost control file was available. 
                                          OR
Remove the detail of the lost control file from the remaining available current controlfile.

Now start your database :-


SQL> startup
ORACLE instance started.

Total System Global Area 1.3700E+10 bytes
Fixed Size                  2161112 bytes
Variable Size            6878660136 bytes
Database Buffers         6811549696 bytes
Redo Buffers                7610368 bytes
Database mounted.
Database opened.

Loss of all control files

Loss of all controlfiles should never happen. We have to keep copies of all controlfiles in different locations, but there is still the possibility that we have to recover from losing all those copies. So "Prevention is better than recovery".

If we have lost all copies of current controlfile and we have a backup to recover controlfile from that , then our course of action depends on status of online log files and datafiles.

Online logs available :- 

A. If online logs are available contains redo data for recovery and datafiles are current then:-
1. Restore a backup controlfile from backup.
2. Perform complete recovery.(Specify the name of the REDO logs when asked for that)
3. Open the database with RESETLOGS option.

Note :- If online logs are available contains redo data for recovery and datafiles are not current then perform the same procedure for recovery from the loss of all controlfiles.

Online logs not available :- 

A. If online logs are not available and datafiles are current , then:-
1. Recreate the controlfile.
2. Mount the database
3. Open the database with RESETLOGS.

B. If online logs are not available and datafiles are not current then :-
1. Restore a backup controlfile.
2. Perform a Point-in-time recovery of the database.
3. Open the database with Resetlogs.

Eg :- If we loss all controlfiles then :-

When we tried to start the database we will get the following error :-

SQL> startup
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> shu immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[cognos@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 24 19:02:28 2012

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

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 24-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

recovery area destination: +AMIT_R1
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +amit_r1/ORCL/AUTOBACKUP/2012_10_24/s_797530166.287.797530169 found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP +amit_r1/ORCL/AUTOBACKUP/2012_10_24/s_797530166.287.797530169
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+AMIT_R1/orcl/controlfile/current.260.797540569
output file name=+AMIT_R1/orcl/controlfile/current.261.797540569
Finished restore at 24-OCT-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


RMAN> recover database;

Starting recover at 24-OCT-12
Starting implicit crosscheck backup at 24-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
Crosschecked 34 objects
Finished implicit crosscheck backup at 24-OCT-12

Starting implicit crosscheck copy at 24-OCT-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-OCT-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +amit_r1/ORCL/AUTOBACKUP/2012_10_24/s_797530166.287.797530169

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file +AMIT_R1/orcl/onlinelog/group_2.265.790789025
archived log file name=+AMIT_R1/orcl/onlinelog/group_2.265.790789025 thread=1 sequence=14
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-OCT-12

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.
[cognos@rac1 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 24 19:07:46 2012

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

Enter user-name: /as sysdba

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

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Restore of database completed after loss of all control files but having onlinelog files available.

I hope this article helped you.

Regards,
Amit Rath

Saturday, October 20, 2012

How to clone a Oracle Database using RMAN

Cloning of Database includes below mentioned steps :-

1. Take a fresh backup of your OLD database of which you want to make a clone.

Refer to this article for taking RMAN consistent or Inconsistent backups :-
How to backup of Oracle Database using RMAN

2. Create a pfile for OLD database.

SQL> create pfile='/home/cognos/pfileorcl.ora' from spfile;

File created.

3. PFB contents of OLD database pfile . 

Modify the parameters in green colour of this pfile according to the New Database server.

*.audit_file_dest='/u04/app/cognos/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='+AMIT_R1/orcl/controlfile/current.261.790789011','+AMIT_R1/orcl/controlfile/current.260.790789011'
*.db_block_size=8192
*.db_create_file_dest='+AMIT_R1'
*.db_domain='com'
*.db_name='orcl'
*.db_recovery_file_dest='+AMIT_R1'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/u04/app/cognos'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.java_pool_size=64m

*.log_archive_dest_1='LOCATION=/backup/orcl/'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=1026870912
*.sga_target=1026870912
*.undo_tablespace='UNDOTBS1'


4. Move Backup pieces and pfile to new database server.

5. Use the above pfile to startup in nomount mode the NEW database. 
 
SQL> startup nomount pfile='/home/cognos/pfileorcl.ora'
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
SQL>


6. Restore control files of OLD database to new host database.

RMAN> restore controlfile from '/backup/BKPSETS_20OCT12_CONTROLFILE_0sno8cm4_1_1';

Starting restore at 20-OCT-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output file name=+EXAMPLE/orcl/controlfile/current.267.797196775
output file name=+
EXAMPLE/orcl/controlfile/current.268.797196777
Finished restore at 20-OCT-12



7. Mount your New Database.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


8. Catalog all Backup pieces of OLD database server to new database server.

RMAN> catalog backuppiece '/backup/BKPSETS_20OCT12_0ino8cf8_1_1','/backup/BKPSETS_20OCT12_0lno8cfa_1_1','/backup/BKPSETS_20OCT12_0kno8cf8_1_1','/backup/BKPSETS_20OCT12_0rno8cm2_1_1','/backup/BKPSETS_20OCT12_0sno8cm4_1_1';

cataloged backup piece
backup piece handle=/backup/BKPSETS_20OCT12_0ino8cf8_1_1 RECID=29 STAMP=797197934
cataloged backup piece
backup piece handle=/backup/BKPSETS_20OCT12_0lno8cfa_1_1 RECID=30 STAMP=797197935
cataloged backup piece
backup piece handle=/backup/BKPSETS_20OCT12_0kno8cf8_1_1 RECID=31 STAMP=797197935
cataloged backup piece
backup piece handle=/backup/BKPSETS_20OCT12_0rno8cm2_1_1 RECID=32 STAMP=797197935
cataloged backup piece
backup piece handle=/backup/BKPSETS_20OCT12_0sno8cm4_1_1 RECID=33 STAMP=797197935


 9. Run the restore command to restore the datafiles.

TO run restore command you need the details of the datafiles of OLD database.
Login into the OLD database and get below mentioned details:-

SQL> Select file_id,file_name from dba_data_files;

   FILE_ID FILE_NAME
---------- --------------------------------------------------------------------------------                  

         6 +AMIT_R1/orcl/datafile/amit.271.795743985
         5 +AMIT_R1/orcl/datafile/example.269.790789095
         4 +AMIT_R1/orcl/datafile/users.259.790788813
         3 +AMIT_R1/orcl/datafile/undotbs1.258.790788813
         2 +AMIT_R1/orcl/datafile/sysaux.257.790788811
         1 +AMIT_R1/orcl/datafile/system.256.790788811

6 rows selected.



Now run the resore command in NEW database host in RMAN prompt :-

RMAN> run
2> {
3> set newname for datafile 1 to '
+EXAMPLE';
4> set newname for datafile 2 to '+
EXAMPLE';
5> set newname for datafile 4 to '+
EXAMPLE';
6> set newname for datafile 3 to '+
EXAMPLE';
7> set newname for datafile 5 to '+
EXAMPLE';
8> set newname for datafile 6 to '+
EXAMPLE';
9> restore database;
10> switch datafile all;
11> }


The Command will give you below mentioned output:-

RMAN> run
 {
set newname for datafile 1 to '
+EXAMPLE';
 set newname for datafile 2 to '+
EXAMPLE';
 set newname for datafile 4 to '+
EXAMPLE';
 set newname for datafile 3 to '+
EXAMPLE';
 set newname for datafile 5 to '+
EXAMPLE';
 set newname for datafile 6 to '+
EXAMPLE';
 restore database;
 switch datafile all;
  }2> 3> 4> 5> 6> 7> 8> 9> 10> 11>

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to +EXAMPLE

channel ORA_DISK_1: restoring datafile 00004 to +EXAMPLE
channel ORA_DISK_1: reading from backup piece +AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.275.797192689
channel ORA_DISK_1: piece handle=+AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.275.797192689 tag=TAG20121020T182444
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to +EXAMPLE

channel ORA_DISK_1: restoring datafile 00006 to +EXAMPLE
channel ORA_DISK_1: reading from backup piece +AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.276.797192687
channel ORA_DISK_1: piece handle=+AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.276.797192687 tag=TAG20121020T182444
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +EXAMPLE

channel ORA_DISK_1: restoring datafile 00005 to +EXAMPLE
channel ORA_DISK_1: reading from backup piece +AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.274.797192711
channel ORA_DISK_1: piece handle=+AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.274.797192711 tag=TAG20121020T182444
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 20-OCT-12

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=797201985 file name=+
EXAMPLE/orcl/datafile/system.260.797201879
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=797201985 file name=+
EXAMPLE/orcl/datafile/sysaux.269.797201773
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=797201985 file name=+
EXAMPLE/orcl/datafile/undotbs1.268.797201697
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=797201986 file name=+
EXAMPLE/orcl/datafile/users.267.797201699
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=797201986 file name=+
EXAMPLE/orcl/datafile/example.261.797201879
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=797201986 file name=+
EXAMPLE/orcl/datafile/amit.256.797201773 

10. Recover the database

RMAN> recover database;

Starting recover at 20-OCT-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file +AMIT_R1/orcl/onlinelog/group_2.264.790789023
archived log for thread 1 with sequence 9 is already on disk as file +AMIT_R1/orcl/onlinelog/group_3.266.790789027
archived log for thread 1 with sequence 10 is already on disk as file +AMIT_R1/orcl/onlinelog/group_1.262.790789015
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /backup/BKPSETS_20OCT12_0rno8cm2_1_1
channel ORA_DISK_1: piece handle=/backup/BKPSETS_20OCT12_0rno8cm2_1_1 tag=TAG20121020T182818
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/backup/orcl/1_7_796679647.dbf thread=1 sequence=7
archived log file name=+AMIT_R1/orcl/onlinelog/group_2.264.790789023 thread=1 sequence=8
archived log file name=+AMIT_R1/orcl/onlinelog/group_3.266.790789027 thread=1 sequence=9
archived log file name=+AMIT_R1/orcl/onlinelog/group_1.262.790789015 thread=1 sequence=10
media recovery complete, elapsed time: 00:00:26
Finished recover at 20-OCT-12


Exit from RMAN and login with SQLPLUS

11. Rename the ONLINE redo logs file

 SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------      


+AMIT_R1/orcl/onlinelog/group_3.266.790789027
+AMIT_R1/orcl/onlinelog/group_3.267.790789029
+AMIT_R1/orcl/onlinelog/group_2.264.790789023
+AMIT_R1/orcl/onlinelog/group_2.265.790789025
+AMIT_R1/orcl/onlinelog/group_1.262.790789015
+AMIT_R1/orcl/onlinelog/group_1.263.790789019

6 rows selected.


Rename all logfiles :-

SQL> alter database rename file '+AMIT_R1/orcl/onlinelog/group_3.266.790789027' to '+EXAMPLE/fingate/redo03a.log';
Database altered.

SQL> alter database rename file '
+AMIT_R1/orcl/onlinelog/group_1.262.790789015' to '+EXAMPLE/fingate/redo01.log';
Database altered.

SQL> alter database rename file '
+AMIT_R1/orcl/onlinelog/group_1.263.790789019' to '+EXAMPLE/fingate/redo01a.log';
Database altered.

.
.
.
like this reaname all logfiles .

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------        

+EXAMPLE/fingate/redo01.log+EXAMPLE/fingate/redo01a.log+EXAMPLE/fingate/redo03a.log+EXAMPLE/fingate/redo03.log+EXAMPLE/fingate/redo4.log+EXAMPLE/fingate/redo4a.log

6 rows selected.


12. Open your clone database

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$database;

NAME
---------
ORCL


13. Rename Temp tablespace.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------  

+AMIT_R1/orcl/tempfile/temp.268.790789087

SQL> create temporary tablespace temp1 tempfile '+
EXAMPLE';

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------             

+EXAMPLE/orcl/tempfile/temp1.270.797205367

Cloning of ORCL database on a NEW HOST completed.

I Hope this article helped you.

Regards,
Amit Rath

RMAN backup of Oracle Database

Backing up Oracle Database using RMAN in background :-

HOT Backup( Database in Archive log mode) :-

1. Create a RCV file for backup.
[cognos@rac1 ~]$ vi compressed_bkp_20_OCT_12.rcv

contents inside this file :-

run
{
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate channel t5 type disk;
sql 'alter system archive log current';
backup as compressed backupset database plus archivelog delete input format '/backup/BKPSETS_20OCT12_%U' filesperset 8;
backup as compressed backupset format '/backup/BKPSETS_20OCT12_CONTROLFILE_%U' current controlfile;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
}
 

2. create a new file backup.

[cognos@rac1 ~]$ vi rman_hotbackup.sh

 contents inside this file 

 cd 
. ./.bash_profile
rman target / cmdfile=compressed_bkp_20_OCT_12.rcv log=backup_20_OCT_12.log << EOF
exit


3. Run this file from oracle user prompt to start backup
[cognos@rac1 ~]$ sh rman_hotbackup.sh >> rman_hotbackup.txt & 

COLD Backup( Database in NOArchive log mode) :- 

1. create a RCV file for backup.
[cognos@rac1 ~]$ vi compressed_bkp_20_OCT_12.rcv

contents inside this file :-

run
{
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate channel t5 type disk;
backup as compressed backupset database format '/backup/BKPSETS_20OCT12_%U' filesperset 8;
backup as compressed backupset format '/backup/BKPSETS_20OCT12_
CONTROLFILE_%U' current controlfile;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
}
 

2. create a new file backup.
[cognos@rac1 ~]$ vi rman_coldbackup.sh

 contents inside this file

 cd 

. ./.bash_profile
rman target / cmdfile=compressed_bkp_20_OCT_12.rcv log=backup_20_OCT_12.log << EOF
exit


3. Run this file from oracle user prompt to start backup
[cognos@rac1 ~]$ sh rman_coldbackup.sh >> rman_coldbackup.txt &

NOTE :-- For taking Cold backup(Consistent backup) through RMAN database must be in MOUNT mode.

Backup of only Archive logs through RMAN :-
  
1. Backup all Archive logs :-

[cognos@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 18:35:40 2012

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

connected to target database: ORCL (DBID=1318569746)

 RMAN> run
 {
 allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
 backup as compressed backupset archivelog all delete input format '/backup/archive/arc_%U';
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}


2. Backup all archivelogs between dates eg :- created more than 7 and less than 30 days ago.

RMAN> run
2> {
3> allocate channel t1 type disk;
4>allocate channel t2 type disk;
5>allocate channel t3 type disk;
6>allocate channel t4 type disk;
7> 

backup as compressed backupset
 archivelog from time 'SYSDATE-30' until time 'SYSDATE - 7' format '/backup/archive/arc_%U';
8>release channel t1;
9>release channel t2;
10>release channel t3;
11>release channel t4;

12 }

3. Backup archivelogs between log sequence eg:- from sequence 120 to 220

RMAN> run
2> {
3> allocate channel t1 type disk;
4>allocate channel t2 type disk;
5>allocate channel t3 type disk;
6>allocate channel t4 type disk;
7> 

backup as compressed backupset
 archivelog from logseq 120 until logseq 220 thread 1 format '/backup/archive/arc_%U';
8>release channel t1;
9>release channel t2;
10>release channel t3;
11>release channel t4;

12 }

I Hope this article helped you.

Regards,
Amit Rath

Sunday, October 14, 2012

ORA-01207: File is more recent than control file - old control file

Few days ago I faced this issue in my database, due to some problem in my ASM disks, my database closed abruptly and I was getting the following error when I was trying to open my database using STARTUP command.

Errors:-
===========
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '
+AMIT_R1/orcl/datafile/amit.271.795743985'
ORA-01207: file is more recent than control file - old control file.


Cause :-
===========
The information in this file is inconsistent with information from the control file.
The datafile header CHECKPOINT count is beyond the controlfile CHECKPOINT count.


Solution :-
=========== 
1. Mount the database.
 SQL> startup mount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
 

Database mounted. 

2. Recreate the control file of the database.
Please refer to 
http://amit7oracledba.blogspot.in/2012/10/how-to-recreate-control-file-in-oracle.html 
only to recreate your control file.


3. After creation of control file, database is in mount stage

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED


4. Recover the database.
SQL> recover database;

If it does recovery from the redo logs then from above command only your database will be recovered , but if the error is "control file is more recent than the file" then it asks you for archive logs to recover then you have to use below command 

SQL> recover database using backup controlfile until cancel;

it will ask you for archive logs, give the path of all archive logs which it needs until it gives you message, MEDIA RECOVERY COMPLETED.

5. Open the database.
SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE


 Database recovery complete.

I hope this article helped you.

Regards,
Amit Rath 


How to recreate a control file in Oracle Database

We should only recreate our control file when we are in certain circumstances :-

1. All copies of control files present in database are lost or corrupted.
2. We are restoring a backup in which control file is corrupted or missing.
3. We need to change a hard limit database parameter in the control file.
4. If we are moving our database to another server are files are present in different location.
5. Oracle customer support advices us to do so.

Recreating a control file of that database which is able to mount or open.

1. First we have to generate a ascii dump of the control file.

When database is mounted or open :-
SQL> alter database backup controlfile to trace;

          Database altered


Trace file will be generated in User_dump directory.
SQL> show parameter user_dump_dest

NAME                                  TYPE           VALUE
------------------------                  ---------          ----------------------------
user_dump_dest                  string            /u04/app/cognos/diag/rdbms/orc
                                                                 l/orcl/trace


Navigate to this directory and locate the latest trace file by using ls -ltr
[cognos@rac1 ~]$ cd /u04/app/cognos/diag/rdbms/orcl/orcl/trace/
[cognos@rac1 trace]$ ls -ltr


2. Create control file creation script from backup trace file :-

open the trace file named like _orc_1234.trc. It appears like an ordinary trace file but we are interested in the part having create control file script. Modify the trace file, delete every thing above the "CREATE CONTROLFILE" and after the "CHARACTER SET" option.

3. Shut down your database with immediate option.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


4. Startup the database in nomount mode.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
SQL>


5. Take the control file script and use it to create the control file of the database.
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+AMIT_R1/orcl/onlinelog/group_1.262.790789015',
    '+AMIT_R1/orcl/onlinelog/group_1.263.790789019'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '+AMIT_R1/orcl/onlinelog/group_2.264.790789023',
    '+AMIT_R1/orcl/onlinelog/group_2.265.790789025'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '+AMIT_R1/orcl/onlinelog/group_3.266.790789027',
    '+AMIT_R1/orcl/onlinelog/group_3.267.790789029'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+AMIT_R1/orcl/datafile/system.256.790788811',
  '+AMIT_R1/orcl/datafile/sysaux.257.790788811',
  '+AMIT_R1/orcl/datafile/undotbs1.258.790788813',
  '+AMIT_R1/orcl/datafile/users.259.790788813',
  '+AMIT_R1/orcl/datafile/example.269.790789095',
  '+AMIT_R1/orcl/datafile/amit.271.795743985'
CHARACTER SET AL32UTF8
;
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29

Control file created
.

Once the control file successfully created database is automatically mounted.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED


6. Once the database is mounted open the database with reset logs option.

SQL> alter database open resetlogs;

Database altered.


We have started the database with resetlogs so it's important to take a backup immediately.

7. After the database is open add the existing temp file

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+AMIT_R1/orcl/tempfile/temp.268.790789087' size 1429M REUSE;

Tablespace altered.


NOTE :- If we are using this control file creation script for a new database then some change have to be made in control file creation script, instead of "CREATE CONTROLFILE REUSE" we have to use "CREATE CONTROLFILE SET" and instead of "NORESETLOGS" we have to use "RESETLOGS".

Recreating a control file of that database which is not able to mount.

In this scenario when we donot have a control file then :-

1. Restore control file from backup.
                         OR
    Create a script of control file from beginning

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG

  Follow the format listing :
  1. location of redo logs.
  2. Location of Datafiles.
  3. Specify the Characterset.

Once all things are listed correctly, use this to receate your control file.

SQL> startup nomount;

Create your control file from the script created earlier.

I hope this article helped you.

Regards,
Amit Rath

Saturday, October 6, 2012

Oracle Database 11gR1 to 11gR2 Upgrade

Upgrading 11GR1 to 11GR2, PFB steps :-
  • Install 11gR2 software in a new Oracle home.
  • Run the pre-upgrade steps in current Oracle (11gR1) version and make the necessary modification  as suggested by pre upgrade utility.
  • Upgrade Oracle version to 11gR2 using manual upgrade method.
  • After upgrade process has completed, run the post-upgrade checks. 
  • Recreate the listener using the new Oracle version. 
  • Update the time zone to the appropriate version.
Install 11GR2 software only and install it in a new Oracle Home.

Preupgrade steps :- Run the utlu112i.sql in 11GR1 oracle home.

PFB Output of  utlu112i.sql script
[cognos@rac1 ~]$ sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 6 04:54:58 2012

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL> @/u04/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool    10-06-2012 04:55:38
.
**********************************************************************
Database:
**********************************************************************
--> name:          ORCL
--> version:       11.1.0.6.0
--> compatible:    11.1.0.0.0
--> blocksize:     8192
--> platform:      Linux IA (32-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1049 MB
.... AUTOEXTEND additional space required: 349 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 909 MB
.... AUTOEXTEND additional space required: 308 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 462 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 78 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Oracle Ultra Search          [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle Application Express   [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 11.1.0.6.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
....   SYSMAN
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 33 object(s).  It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command:  PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.


. PL/SQL procedure successfully completed.

  
Purge recyclebin of database.
 SQL> PURGE DBA_RECYCLEBIN;

           DBA Recyclebin purged.

Gather Schema and Database statistics
 SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

           PL/SQL procedure successfully completed.
 SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

           PL/SQL procedure successfully completed.

 SQL> exit

Remove Oracle EM database control.

[cognos@rac1 ~]$ export ORACLE_SID=orcl
[cognos@rac1 ~]$ emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Mar 7, 2012 12:20:43 PM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 7, 2012 12:20:53 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at E:\app\svijay7\cfgtoollogs\emca\test\emca_2012_03_07_12_20_43.log.
Mar 7, 2012 12:20:55 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 7, 2012 12:21:03 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 7, 2012 12:24:23 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 7, 2012 12:24:36 PM


Oracle EM successfully removed.

Shut Down your Oracle 11GR1 Database.
Copy your init.ora file of 11GR1 database to 11GR2 Oracle home dbs folder
Edit the Pfile by increasing java pool size to 64 MB, audit_file_dest, diagnostic_dest and db_recovery_file_dest parameters according to the new 11GR2 oracle home.

Upgrading Database to 11GR2 

Invoke sqlplus from 11GR2 Oracle Home

[cognos@rac1 ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 12:34:14 2012

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


Enter user-name: /as sysdba
Connected to an idle instance.


SQL> startup upgrade pfile='/u04/app/cognos/dbs/initpfile.ora';
ORACLE instance started.
Total System Global Area 602619904 bytes
Fixed Size 1334828 bytes
Variable Size 163578324 bytes
Database Buffers 432013312 bytes
Redo Buffers 5693440 bytes
Database mounted.
Database opened.


(If you are upgrading this in windows server then you will get below mentioned error)

SQL> spool F:\11gr2_upgrade.log     (remember this log file will be very large, can be in GB's)
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/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> Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
SELECT TO_NUMBER('MUST_BE_11_2') FROM v$instance
*
ERROR at line 1:
ORA-01722: invalid number
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Steps to recover from this error in windows server
a. Stop all Oracle Services

C:\Documents and Settings\amit>NET STOP OracleServiceorcl
The OracleServiceorcl service is stopping.
The OracleServiceorcl service was stopped successfully.


b. Delete Oracle service using ORADIM binary from which the database is upgraded

C:\Documents and Settings\amit>oradim -delete -SID orcl
Instance deleted.
C:\Documents and Settings\amit>ORADIM -NEW -SID orcl -INTPWD sys -STARTMODE manual
Instance created.

After this again run the below upgrade script :-
@\u02\app\cognos\product\11.2.0\dbhome_2\rdbms\admin\catupgrd.sql

If you are upgrading this in Unix server then you will get below mentioned Output

SQL> spool F:\11gr2_upgrade.log     (remember this log file will be very large, can be in GB's)
SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/catupgrd.sql


It will take more than an hour to finish the upgrade process and database will be in a shutdown stage. 

Post Upgrade steps:-

Start the database and run the post upgrade utility

[cognos@rac1 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 12:34:14 2012

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

Enter user-name: /as sysdba
Connected to an idle instance.


SQL> startup pfile='/u04/initorcl.ora'
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.
Database opened.


SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 06-10-2012 10:40:44
.
Component Status Version HH:MM:SS
.
Oracle Server                                                      . VALID 11.2.0.1.0 00:17:39  

JServer JAVA Virtual Machine                           . VALID 11.2.0.1.0 00:06:28
Oracle Workspace Manager                               . VALID 11.2.0.1.0 00:00:45
OLAP Analytic Workspace                                 . VALID 11.2.0.1.0 00:01:53
OLAP Catalog                                                    . VALID 11.2.0.1.0 00:01:58
Oracle OLAP API                                              . VALID 11.2.0.1.0 00:00:29
Oracle XDK                                                       . VALID 11.2.0.1.0 00:01:11
Oracle Text                                                        . VALID 11.1.0.6.0 00:00:00
Oracle XML Database                                      . VALID 11.2.0.1.0 00:03:52
Oracle Database Java Packages                      . VALID 11.2.0.1.0 00:00:47
Oracle Multimedia                                            . VALID 11.2.0.1.0 00:09:02
Spatial                                                               . VALID 11.2.0.1.0 00:05:45
Oracle Expression Filter                                   . VALID 11.2.0.1.0 00:00:13
Oracle Rule Manager                                        . VALID 11.2.0.1.0 00:00:10
Gathering Statistics. 00:04:29
Total Upgrade Time: 01:34:54
PL/SQL procedure successfully completed.


SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


Run catuppst.sql to perform post-upgrade actions.

SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/catuppst.sql

TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP   POSTUP_BGN 2012-10-06 12:40:50


PL/SQL procedure successfully completed.


This script will migrate the Baseline data on a pre-11g database
to the 11g database.

...                                       ...
... Completed Moving the Baseline Data    ...
...                                       ...
... If there are no Move BL Data messages ...
... above, then there are no renamed      ...
... baseline tables in the system.        ...
...                                       ...
...                                       ...
... Completed the Dropping of the         ...
... Renamed Baseline Tables               ...
...                                       ...
... If there are no Drop Table messages   ...
... above, then there are no renamed      ...
... baseline tables in the system.        ...
...                                       ...

PL/SQL procedure successfully completed.


0 rows created.


Commit complete.


Table created.


2 rows created.


1 row updated.


2 rows updated.


0 rows updated.


Table dropped.


Commit complete.


0 rows updated.


Commit complete.


0 rows updated.


Commit complete.


0 rows updated.


Commit complete.


0 rows created.


Commit complete.


0 rows created.


Commit complete.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP POSTUP_END 2012-10-06 12:41:03


Run the utlrp.sql script to recompile any invalid objects.

SQL> @/u02/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_BGN  2012-10-06 12:43:14

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  2012-10-06 12:46:49

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). 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


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


SQL> select count(*) from dba_objects where status='INVALID';
 


COUNT(*)
----------
          0


Recreate the Listener service 

Recreate the listener service from new oracle home.

Check the Current version of  the Oracle time Zone definitions in Upgraded database

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
         4


SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                              VALUE
------------------------------ 

------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION             4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE                      NONE



(if this is LOWER than 11 (for 11.2.0.1 ) or 14 (for 11.2.0.2 and 11.2.0.3) then it is *recommended* to upgrade the timezone version)

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.


SQL> exec DBMS_DST.BEGIN_PREPARE(11);


PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                              VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION             4
DST_SECONDARY_TT_VERSION      11
DST_UPGRADE_STATE                      PREPARE


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[cognos@rac1 ~]$
[cognos@rac1 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 13:03:59 2012

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

Enter user-name: /as sysdba

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

SQL> shu  immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


Update the Database DST version

SQL> startup upgrade pfile='/u04/initorcl.ora'
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> create spfile='+AMIT_R1' from pfile='/u04/initorcl.ora';

File created.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
 

PROPERTY_NAME                            VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION            4
DST_SECONDARY_TT_VERSION     11
DST_UPGRADE_STATE                    PREPARE

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
BEGIN DBMS_DST.BEGIN_UPGRADE(11); END;

*
ERROR at line 1:
ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1054
ORA-06512: at line 1


SQL> EXEC DBMS_DST.END_PREPARE;

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                          VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION   0
DST_UPGRADE_STATE                   NONE

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);

PL/SQL procedure successfully completed.


SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                           VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         11
DST_SECONDARY_TT_VERSION   4
DST_UPGRADE_STATE                  UPGRADE


-- Optionally you can check what user tables still need to be updated using DBMS_DST.UPGRADE_DATABASE 
-- BEGIN_UPGRADE upgrades system tables that contain TSTZ data and marks user tables (containing TSTZ data) with the UPGRADE_IN_PROGRESS property. 
-- even if this select gives no rows you still need to do to the rest of the steps
-- it simply gives an indication of how many user objects need to processed in the later steps

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

OWNER                              TABLE_NAME                                                      UPG
------------------------------    ------------------------------                                          ---
SYSMAN                             MGMT_PROV_NET_CONFIG                            YES
SYSMAN                             MGMT_PROV_IP_RANGE                                 YES
SYSMAN                             MGMT_PROV_SUITE_INST_MEMBERS          YES
SYSMAN                              MGMT_PROV_BOOTSERVER                           YES
SYSMAN                              AQ$_MGMT_NOTIFY_QTABLE_L                    YES
SYSMAN                              AQ$_MGMT_LOADER_QTABLE_S                   YES
SYSMAN                              AQ$_MGMT_LOADER_QTABLE_L                   YES
SYSMAN                              AQ$_MGMT_NOTIFY_QTABLE_S                     YES
SYSMAN                              MGMT_CONFIG_ACTIVITIES                           YES
SYSMAN                              MGMT_PROV_STAGING_DIRS                         YES
SYSMAN                              MGMT_PROV_OPERATION                              YES

OWNER                                TABLE_NAME                                                     UPG
------------------------------      ------------------------------                                         ---
SYSMAN                              MGMT_PROV_ASSIGNMENT                           YES
SYSMAN                              MGMT_PROV_CLUSTER_NODES                   YES
SYSMAN                              MGMT_PROV_RPM_REP                                  YES
SYSMAN                             MGMT_PROV_DEFAULT_IMAGE                    YES
IX                                       AQ$_STREAMS_QUEUE_TABLE_S                         YES
IX                                      AQ$_STREAMS_QUEUE_TABLE_L                         YES
IX                                      AQ$_ORDERS_QUEUETABLE_S                             YES
IX                                     AQ$_ORDERS_QUEUETABLE_L                             YES

19 rows selected.


These 19 tables need to be upgraded

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


[cognos@rac1 dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 13:24:53 2012

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

Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.
Database opened.
 

Previeous 19 tables will be upgraded now 

SQL>
SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.


SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
  2  DBMS_DST.UPGRADE_DATABASE(:numfail,
  3  parallel => TRUE,
  4  log_errors => TRUE,
  5  log_errors_table => 'SYS.DST$ERROR_TABLE',
  6  log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
  7  error_on_overlap_time => FALSE,
  8  error_on_nonexisting_time => FALSE);
  9  DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
 10  END;
 11  /
Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_SUITE_INST_MEMBERS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_STAGING_DIRS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_RPM_REP
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_OPERATION
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_NET_CONFIG
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_IP_RANGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_DEFAULT_IMAGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_CLUSTER_NODES
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_BOOTSERVER
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_L
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_L
Number of failures: 0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_S
Number of failures: 0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_L
Number of failures: 0
Table list: IX.AQ$_ORDERS_QUEUETABLE_S
Number of failures: 0
Table list: IX.AQ$_ORDERS_QUEUETABLE_L
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.


-- if there where no failures then end the upgrade.


SQL> VAR fail number
SQL> BEGIN
  2  DBMS_DST.END_UPGRADE(:fail);
  3  DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
  4  END;
  5  /
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.


SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;


PROPERTY_NAME                           VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         11
DST_SECONDARY_TT_VERSION   0
DST_UPGRADE_STATE                  NONE


SQL> SELECT * FROM v$timezone_file;

FILENAME                 VERSION
--------------------          ----------
timezlrg_11.dat           11

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production



######################### Upgrade to 11gR2 is complete ##########################

I hope this article helped you.

Regards,
Amit Rath