Oracle DBA: May 2013

Monday, 20 May 2013

Upgrade oracle database 10gR2 to 11gR2

This is just basic steps for oracle database upgradation. In real environment some other pre-requisite checks and changes will be there.

The oracle 10g database details

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

manudb

SQL> select banner from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod

PL/SQL Release 10.2.0.2.0 - Production

CORE 10.2.0.2.0 Production

TNS for Linux: Version 10.2.0.2.0 - Production

NLSRTL Version 10.2.0.2.0 - Production

SQL>

Install the oracle 11g software

Install the oracle 11gR2 software in an oracle_home seperate from 10gR2.

Here the 10g oracle_home is /u01/app/oracle/product/10.2.0/db_1 and the new installed 11g oracle_home is /u01/app/oracle/product/11.2.0/db_1.

Run the pre-upgrade scripts

copy the utlu112i.sql script from oracle 11g $ORACLE_HOME/rdbms/admin/ to /tmp directory and

run the script.

SQL> @/tmp/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 05-18-2013 22:04:27

**********************************************************************

Database:

**********************************************************************

--> name: MANUDB

--> version: 10.2.0.2.0

--> compatible: 10.2.0.1.0

--> blocksize: 8192

--> platform: Linux IA (32-bit)

--> timezone file: V2

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 734 MB

.... AUTOEXTEND additional space required: 254 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 513 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 445 MB

.... AUTOEXTEND additional space required: 215 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 61 MB

.... AUTOEXTEND additional space required: 35 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]

**********************************************************************

--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"

--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"

--> core_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"

**********************************************************************

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

--> Data Mining [upgrade] VALID

--> Expression Filter [upgrade] VALID

--> Rule Manager [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 10.2.0.2.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: --> Database contains schemas with objects dependent on network packages.

.... Refer to the Upgrade Guide for instructions to configure Network ACLs.

.... USER SYSMAN has dependent objects.

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

.... 20 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.

Check the recommendations and warnings and do the necessary actions.

SQL> exec dbms_stats.gather_schema_stats('sys');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('sysman')

PL/SQL procedure successfully completed.

SQL> PURGE DBA_RECYCLEBIN

2 ;

DBA Recyclebin purged.

SQL>

Copy the spfile and password file.

Copy the spfilemanudb.ora and orapwmanudb file from oracle 10g $ORACLE_HOME/dbs to 11g $ORACLE_HOME/dbs.

Restart the oracle database manudb from oracle 11g environment.

shutdown the oracle database manudb and start it from the oracle 11g environment.

[oracle@test1 bin]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

[oracle@test1 bin]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 19 01:06:25 2013

Enter user-name: /as sysdba

Connected to an idle instance.

SQL>

upgrade the database

startup the database in upgrade mode and run the catupgrd.sql script.

SQL> startup

ORACLE instance started.

Total System Global Area 435224576 bytes

Fixed Size 1337044 bytes

Variable Size 222300460 bytes

Database Buffers 205520896 bytes

Redo Buffers 6066176 bytes

Database mounted.

Database opened.

SQL>

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

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

SQL> .

31 /

PL/SQL procedure successfully completed.

SQL>

SQL>

SQL> /*****************************************************************************/

SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!!

SQL> */

SQL> /*****************************************************************************/

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

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>#######################################################################

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

DOC>#

SQL>

SQL> Rem Set errorlogging off

SQL> SET ERRORLOGGING OFF;

SQL>

SQL> Rem *********************************************************************

SQL> Rem END catupgrd.sql

SQL> Rem *********************************************************************

SQL>

SQL>

The database upgradation is completed.

Startup the database

SQL> startup

ORACLE instance started.

Total System Global Area 435224576 bytes

Fixed Size 1337044 bytes

Variable Size 222300460 bytes

Database Buffers 205520896 bytes

Redo Buffers 6066176 bytes

Database mounted.

Database opened.

SQL>

Run the post upgrade scripts

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

SQL> exec dbms_stats.gather_fixed_objects_stats; -- generate fixed object statistics

PL/SQL procedure successfully completed.

SQL>

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

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2013-05-19 13:29:32

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 2013-05-19 13:33:27

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

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

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

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

PL/SQL procedure successfully completed.

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

Oracle Database 11.2 Post-Upgrade Status Tool 05-19-2013 13:36:07

Component Status Version HH:MM:SS

Oracle Server

. VALID 11.2.0.1.0 00:25:04

JServer JAVA Virtual Machine

. VALID 11.2.0.1.0 00:08:17

Oracle Workspace Manager

. VALID 11.2.0.1.0 00:01:12

OLAP Analytic Workspace

. VALID 11.2.0.1.0 00:00:50

OLAP Catalog

. VALID 11.2.0.1.0 00:01:18

Oracle OLAP API

. VALID 11.2.0.1.0 00:01:36

Oracle Enterprise Manager

. VALID 11.2.0.1.0 00:12:51

Oracle XDK

. VALID 11.2.0.1.0 00:02:24

Oracle Text

. VALID 11.2.0.1.0 00:01:37

Oracle XML Database

. VALID 11.2.0.1.0 00:06:00

Oracle Database Java Packages

. VALID 11.2.0.1.0 00:01:06

Oracle Multimedia

. VALID 11.2.0.1.0 00:08:02

Spatial

. VALID 11.2.0.1.0 00:08:11

Oracle Expression Filter

. VALID 11.2.0.1.0 00:00:32

Oracle Rule Manager

. VALID 11.2.0.1.0 00:00:17

Gathering Statistics

. 00:09:24

Total Upgrade Time: 01:28:55

PL/SQL procedure successfully completed.

SQL>

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

Oracle Database 11.1 Post-Upgrade Invalid Objects Tool 05-19-2013 13:40:14

This tool lists post-upgrade invalid objects that were not invalid

prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).

Owner Object Name Object Type

PL/SQL procedure successfully completed.

SQL>

Restart again the database in upgrade mode and execute the below procedure

SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup upgradeORACLE instance started.Total System Global Area 435224576 bytesFixed Size 1337044 bytesVariable Size 222300460 bytesDatabase Buffers 205520896 bytesRedo Buffers 6066176 bytesDatabase mounted.Database opened.

SQL> exec dbms_dst.begin_upgrade(new_version => 11);PL/SQL procedure successfully completed.

Startup the database in normal mode

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 435224576 bytes

Fixed Size 1337044 bytes

Variable Size 222300460 bytes

Database Buffers 205520896 bytes

Redo Buffers 6066176 bytes

Database mounted.

Database opened.

SQL> declare num_of_failures number;

begin

dbms_dst.upgrade_database(num_of_failures);

dbms_output.put_line(num_of_failures);

dbms_dst.end_upgrade(num_of_failures);

dbms_output.put_line(num_of_failures);

end;

/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

Now the database is completely upgraded to oracle 11gR2 and all the post upgradation scripts has been successfully executed.

Upgraded database status

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

manudb

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

SQL> select VERSION from dba_registry;

VERSION

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

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

11.2.0.1.0

17 rows selected.

SQL>

Sunday, 19 May 2013

Applying oracle patchset to upgrade from oracle 10.2.0.1 to 10.2.0.2

Applying oracle patch set to upgrade from 10.2.0.1 to 10.2.0.2

Database to be upgraded

SQL>select comp_name, version, status from sys.dba_registry;SQL> SQL> SQL> SQL> SQL>

COMP_NAME VERSION STATUS

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

Oracle Database Catalog Views 10.2.0.1.0 VALID

Oracle Database Packages and Types 10.2.0.1.0 VALID

Oracle Workspace Manager 10.2.0.1.0 VALID

JServer JAVA Virtual Machine 10.2.0.1.0 VALID

Oracle XDK 10.2.0.1.0 VALID

Oracle Database Java Packages 10.2.0.1.0 VALID

Oracle Expression Filter 10.2.0.1.0 VALID

Oracle Data Mining 10.2.0.1.0 VALID

Oracle Text 10.2.0.1.0 VALID

Oracle XML Database 10.2.0.1.0 VALID

Oracle Rules Manager 10.2.0.1.0 VALID

Oracle interMedia 10.2.0.1.0 VALID

OLAP Analytic Workspace 10.2.0.1.0 VALID

Oracle OLAP API 10.2.0.1.0 VALID

OLAP Catalog 10.2.0.1.0 VALID

Spatial 10.2.0.1.0 VALID

Oracle Enterprise Manager 10.2.0.1.0 VALID

17 rows selected.

SQL>

Stop the dbconsole

[oracle@test1 ~]$ emctl stop dbconsole

TZ set to Asia/Calcutta

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

http://test1.localdomain:5500/em/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Database Control ...

... Stopped.

Stop the listener

[oracle@test1 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 16-MAY-2013 22:22:55

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

The command completed successfully

[oracle@test1 ~]$

Backup the ORACLE_HOME and database files

Here I have copied the oracle_home and database files as compressed tar files as shown below. All the database files are stored in the same location.

[oracle@test1 oradata]$ pwd

/u01/app/oracle/oradata

[oracle@test1 oradata]$ tar -czf /home/oracle/manudb.tar.gz manudb

[oracle@test1 oradata]$

[oracle@test1 oradata]$ cd /u01/app/oracle/product/10.2.0/

[oracle@test1 10.2.0]$ ls

db_1

[oracle@test1 10.2.0]$ tar czf /home/oracle/ora10ghome.tar.gz db_1

[oracle@test1 10.2.0]$

Download the oracle pathset and install

Download the oracle patchset p4547817_10202_LINUX.zip and unzip it. The folder named Disk1 will be created.

[oracle@test1 Desktop]$ unzip p4547817_10202_LINUX.zip

[oracle@test1 Desktop]$cd Disk1/

[oracle@test1 Disk1]$./runInstaller

Click next

Check the oracle_home details and click next

Verify the summary details and click on install

Installation in progress

A popup window to run root.sh script will appear. Open a new terminal and log in as root user and execute the root.sh script. After completed the execution, click ok.

Take a backup of dbhome, oraenv and coraenv directories as executing root.sh script will override the files in those directories.

[oracle@test1 Disk1]$ cd $ORACLE_HOME/bin

[oracle@test1 bin]$ mv dbhome dbhome_10201

[oracle@test1 bin]$ mv oraenv oraenv_10201

[oracle@test1 bin]$ mv coraenv coraenv_10201

[oracle@test1 bin]$

[root@test1 ~]# /u01/app/oracle/product/10.2.0/db_1/root.sh

Running Oracle10 root.sh script...

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

[root@test1 ~]#

End of installation, click exit button

Now we have applied the patch set to ORACLE_HOME and then we need to upgrade the databases as well.

Start the database in upgrade mode.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 436207616 bytes

Fixed Size 1261284 bytes

Variable Size 130023708 bytes

Database Buffers 301989888 bytes

Redo Buffers 2932736 bytes

Database mounted.

Database opened.

SQL>

Run the preupgrade script

Run the utlu102i.sql script to check whether the database is good for upgrade. Do the recommended changes before upgrading the database.

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

Oracle Database 10.2 Upgrade Information Utility 05-16-2013 23:17:55

**********************************************************************

Database:

**********************************************************************

--> name: MANUDB

--> version: 10.2.0.1.0

--> compatible: 10.2.0.1.0

--> blocksize: 8192

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 488 MB

.... AUTOEXTEND additional space required: 8 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 230 MB

.... AUTOEXTEND additional space required: 0 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 58 MB

.... AUTOEXTEND additional space required: 38 MB

**********************************************************************

Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************

WARNING: --> "streams_pool_size" needs to be increased to at least 50331648

WARNING: --> "session_max_open_files" needs to be increased to at least 20

**********************************************************************

Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************

-- No renamed parameters found. No changes are required.

**********************************************************************

Obsolete/Deprecated Parameters: [Update Oracle Database 10.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 Java Packages [upgrade] VALID

--> Oracle Text [upgrade] VALID

--> Oracle XML Database [upgrade] VALID

--> Oracle Workspace Manager [upgrade] VALID

--> Oracle Data Mining [upgrade] VALID

--> OLAP Analytic Workspace [upgrade] VALID

--> OLAP Catalog [upgrade] VALID

--> Oracle OLAP API [upgrade] VALID

--> Oracle interMedia [upgrade] VALID

--> Spatial [upgrade] VALID

--> Expression Filter [upgrade] VALID

--> EM Repository [upgrade] VALID

--> Rule Manager [upgrade] VALID

**********************************************************************

Miscellaneous Warnings

**********************************************************************

WARNING: --> Database contains INVALID objects prior to upgrade.

.... USER PUBLIC has 26 INVALID objects.

.... USER SYSMAN has 1 INVALID objects.

.... USER SYS has 10 INVALID objects.

.... USER WMSYS has 5 INVALID objects.

PL/SQL procedure successfully completed.

SQL> spool off

SQL>

Run the upgrade script

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

TIMESTAMP

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

COMP_TIMESTAMP RUL 2013-05-17 00:08:03

DBUA_TIMESTAMP RUL VALID 2013-05-17 00:08:03

TIMESTAMP

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

COMP_TIMESTAMP UPGRD_END 2013-05-17 00:08:03

Oracle Database 10.2 Upgrade Status Utility 05-17-2013 00:08:03

Component Status Version HH:MM:SS

Oracle Database Server VALID 10.2.0.2.0 00:17:54

JServer JAVA Virtual Machine VALID 10.2.0.2.0 00:06:28

Oracle XDK VALID 10.2.0.2.0 00:00:45

Oracle Database Java Packages VALID 10.2.0.2.0 00:00:32

Oracle Text VALID 10.2.0.2.0 00:00:24

Oracle XML Database VALID 10.2.0.2.0 00:00:58

Oracle Data Mining VALID 10.2.0.2.0 00:00:26

OLAP Analytic Workspace VALID 10.2.0.2.0 00:00:34

OLAP Catalog VALID 10.2.0.2.0 00:01:11

Oracle OLAP API VALID 10.2.0.2.0 00:01:35

Oracle interMedia VALID 10.2.0.2.0 00:03:39

Spatial VALID 10.2.0.2.0 00:00:53

Oracle Expression Filter VALID 10.2.0.2.0 00:00:18

Oracle Enterprise Manager VALID 10.2.0.2.0 00:01:02

Oracle Rule Manager VALID 10.2.0.2.0 00:00:11

Total Upgrade Time: 00:36:58

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

DOC>

DOC> The above PL/SQL lists the SERVER components in the upgraded

DOC> database, along with their current version and status.

DOC>

DOC> Please review the status and version columns and look for

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

DOC> file, or any components are not VALID or not the current version,

DOC> consult the Oracle Database Upgrade Guide for troubleshooting

DOC> recommendations.

DOC>

DOC> Next shutdown immediate, restart for normal operation, and then

DOC> run utlrp.sql to recompile any invalid application objects.

DOC>

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

DOC>#

SQL>

Run the post upgrade script.

The invalid objects after upgradation needs to be compiled. Run the utlrp.sql script for recompiling the invalid objects.

First shutdown the database, startup in normal mode and execute the utlrp.sql script.

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

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2013-05-17 00:13:58

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>#

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END 2013-05-17 00:14:58

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

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

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

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

Now the whole database and the oracle software is upgraded to 10.2.0.2 .

SQL>select comp_name, version, status from sys.dba_registry;

SQL> SQL> SQL> SQL> SQL>

COMP_NAME VERSION STATUS

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

Oracle Database Catalog Views 10.2.0.2.0 VALID

Oracle Database Packages and Types 10.2.0.2.0 VALID

Oracle Workspace Manager 10.2.0.1.0 VALID

JServer JAVA Virtual Machine 10.2.0.2.0 VALID

Oracle XDK 10.2.0.2.0 VALID

Oracle Database Java Packages 10.2.0.2.0 VALID

Oracle Expression Filter 10.2.0.2.0 VALID

Oracle Data Mining 10.2.0.2.0 VALID

Oracle Text 10.2.0.2.0 VALID

Oracle XML Database 10.2.0.2.0 VALID

Oracle Rule Manager 10.2.0.2.0 VALID

Oracle interMedia 10.2.0.2.0 VALID

OLAP Analytic Workspace 10.2.0.2.0 VALID

Oracle OLAP API 10.2.0.2.0 VALID

OLAP Catalog 10.2.0.2.0 VALID

Spatial 10.2.0.2.0 VALID

Oracle Enterprise Manager 10.2.0.2.0 VALID

SQL>

Friday, 10 May 2013

Active database duplication in oracle 11gR2

Here I am duplicating an oracle database to a new server with out using any backups. The duplication takes place by connecting directly to the source database. This feature is newly introduced in oracle 11gR2.

SOURCE SERVER

The source database should be in archive log mode for the active duplication to work. Check the archive log mode of the source database.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SQL>

Add the following entry to $ORACLE_HOME/network/admin/tnsnames.ora file in the source server.

TESTDB_DESTINATION =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.193)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(ORACLE_SID = testdb)

)

TARGET SERVER

Create the necessary directories.

Here I have created the directories same as that of source server.

[oracle@test1 ~]$ mkdir -p /u01/app/oracle/oradata/testdb

[oracle@test1 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/testdb

[oracle@test1 ~]$ mkdir -p /u01/app/oracle/admin/testdb/adump

Create a pfile with the name inittestdb.ora

Create a pfile with only one parameter DB_NAME, rest of the parameters will be available from the spfile backup during duplication.

[oracle@test1 ~]$ cd $ORACLE_HOME/dbs

[oracle@test1 ~]$ cat inittestdb.ora

DB_NAME=testdb

[oracle@test1 ~]$

Start the duplicate database in nomount

[oracle@test1 ~]$ export ORACLE_SID=testdb

[oracle@test1 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 7 22:15:20 2013

Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1000189952 bytes

Fixed Size 1340832 bytes

Variable Size 570428000 bytes

Database Buffers 423624704 bytes

Redo Buffers 4796416 bytes

SQL>

Create a password file in $ORACLE_HOME/dbs

The password given should be same as that of the password file in the source server.

[oracle@test dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@test dbs]$ orapwd file=orapwtestdb password=oracle entries=10
[oracle@test dbs]$

Add the below entries to the $ORACLE_HOME/network/admin/tnsnames.ora file.

[oracle@test1 admin]$ cat tnsnames.ora

TESTDB_SOURCE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.192)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(ORACLE_SID = testdb)
)
)

TESTDB_DESTINATION =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.193)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(ORACLE_SID = testdb)
)
)

Add the below entry to the $ORACLE_HOME/network/admin/listener.ora file.

[oracle@test1 admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.193)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = testdb)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = testdb)

)

[oracle@test1 admin]$

Restart or reload the listener after it's configuration

Connect to target and auxiliary database using rman

Open another terminal and connect to the target and auxiliary database using rman. Use the respective connect descriptors for both source and target databases.

[oracle@test1 backup]$ rman target sys/oracle@testdb_source auxiliary sys/oracle@testdb_destination

Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 10 20:07:35 2013

connected to target database: TESTDB (DBID=2596658962)

connected to auxiliary database (not started)

RMAN>

Duplicate the database

RMAN> duplicate database to testdb from active database spfile nofilenamecheck;

Starting Duplicate Db at 10-MAY-13

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora' auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora' ;

sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora''";

}

executing Memory Script

Starting backup at 10-MAY-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=39 device type=DISK

Finished backup at 10-MAY-13

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora''

contents of Memory Script:

{

sql clone "alter system set db_name =

''TESTDB'' comment=

''duplicate'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set db_name = ''TESTDB'' comment= ''duplicate'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1000189952 bytes

Fixed Size 1340832 bytes

Variable Size 591399520 bytes

Database Buffers 402653184 bytes

Redo Buffers 4796416 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''TESTDB'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''TESTDB'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format '/u01/app/oracle/oradata/testdb/control01.ctl';

restore clone controlfile to '/u01/app/oracle/flash_recovery_area/testdb/control02.ctl' from

'/u01/app/oracle/oradata/testdb/control01.ctl';

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''TESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''TESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1000189952 bytes

Fixed Size 1340832 bytes

Variable Size 591399520 bytes

Database Buffers 402653184 bytes

Redo Buffers 4796416 bytes

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_testdb.f tag=TAG20130510T201646 RECID=3 STAMP=815084233

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:51

Finished backup at 10-MAY-13

Starting restore at 10-MAY-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 10-MAY-13

database mounted

RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT

contents of Memory Script:

{

set newname for datafile 1 to

"/u01/app/oracle/oradata/testdb/system01.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/testdb/sysaux01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/testdb/undotbs01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/testdb/users01.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/testdb/example01.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/u01/app/oracle/oradata/testdb/system01.dbf" datafile

2 auxiliary format

"/u01/app/oracle/oradata/testdb/sysaux01.dbf" datafile

3 auxiliary format

"/u01/app/oracle/oradata/testdb/undotbs01.dbf" datafile

4 auxiliary format

"/u01/app/oracle/oradata/testdb/users01.dbf" datafile

5 auxiliary format

"/u01/app/oracle/oradata/testdb/example01.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/testdb/system01.dbf

output file name=/u01/app/oracle/oradata/testdb/system01.dbf tag=TAG20130510T201807

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:27

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/testdb/sysaux01.dbf

output file name=/u01/app/oracle/oradata/testdb/sysaux01.dbf tag=TAG20130510T201807

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:21

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/testdb/example01.dbf

output file name=/u01/app/oracle/oradata/testdb/example01.dbf tag=TAG20130510T201807

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:36

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/testdb/undotbs01.dbf

output file name=/u01/app/oracle/oradata/testdb/undotbs01.dbf tag=TAG20130510T201807

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:44

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/testdb/users01.dbf

output file name=/u01/app/oracle/oradata/testdb/users01.dbf tag=TAG20130510T201807

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04

Finished backup at 10-MAY-13

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like "/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_10/o1_mf_1_13_8rt3c9o7_.arc" auxiliary format

"/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_10/o1_mf_1_13_%u_.arc" ;

catalog clone recovery area;

switch clone datafile all;

}

executing Memory Script

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=13 RECID=7 STAMP=815085510

output file name=/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_10/o1_mf_1_13_0uo9adu9_.arc RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:15

Finished backup at 10-MAY-13

searching for all files in the recovery area

List of Files Unknown to the Database

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

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_10/o1_mf_1_13_0uo9adu9_.arc

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_07/o1_mf_1_1_8rldm7gx_.arc

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_09/o1_mf_1_12_0no9822u_.arc

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_1_8rqqy1qx_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_2_8rqr6nck_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_3_8rgb8ovq_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_3_8rhrjzbh_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_1_8rhrfoj0_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_2_8rgb8fsy_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_3_8rqrlvf8_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_1_8rgb8bk5_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_2_8rhrhtl4_.log

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_10/o1_mf_1_13_0uo9adu9_.arc

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_07/o1_mf_1_1_8rldm7gx_.arc

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_09/o1_mf_1_12_0no9822u_.arc

List of Files Which Where Not Cataloged

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

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_1_8rqqy1qx_.log

RMAN-07518: Reason: Foreign database file DBID: 2598336272 Database Name: TESTDB

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_2_8rqr6nck_.log

RMAN-07518: Reason: Foreign database file DBID: 2598336272 Database Name: TESTDB

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_3_8rgb8ovq_.log

RMAN-07518: Reason: Foreign database file DBID: 2598027485 Database Name: TESTDB

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_3_8rhrjzbh_.log

RMAN-07518: Reason: Foreign database file DBID: 2598074574 Database Name: TESTDB