Friday 5 May 2017

Upgrade Database from 11g to 12c

Upgrade Database From 11g To 12c Manually
A database can be upgrade either using DBUA or manual method. But for major production databases , it is better to do it manual, so that troubleshooting will be easy. Here we will provide steps for upgrading from 11gr2 to 12c database manually and DBUA silent method

Current ORACLE_HOME= /u01/app/oracle/product/11.2.0.4
Target ORACLE_HOME=/u01/app/oracle/product/12.1.0.2

 PRECHECKS:

·         Check for the Invalid Components in the database using query DBA_REGISTRY. It list the components’ status. The status for all components must be valid before attempting the upgrade.


SQL> SELECT comp_name , version, status  FROM   dba_registry;

·         Find out if there are invalid and duplicate objects owned by the SYS and SYSTEM users. We find and fix invalid and duplicate objects as illustrated below.


SQL> SELECT DISTINCT object_name, object_type, owner  FROM dba_objects WHERE  status=’INVALID’  ORDER BY owner,object_name, object_type;
 It’s important to note that you do not start the database upgrade when there are invalid data dictionary objects. To recompile invalid objects, connect as SYSDBA and run the supplied script ‘utlrp.sql’ as shown below.
                         SQL> @?/rdbms/admin/utlrp.sql

·         Before starting the upgradation process, take the complete backup of database
Take cold backup of 11.2.0.4 database:
$ mkdir -p /u01/ test11g12c /coldbkp_prod
$ sqlplus / as sydba
SQL> select name from v$datafile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;
SQL> shutdown
$  cp  <all datafile names>  <all controlfile>  <all redolog files>  /u01/test11g12c/coldbkp_prod/.

·         Stop the Listener
$ lsnrctl stop  <listener name>
$ lsnrctl stop listtest11g12c

UPGRADATION PROCEDURE

After checking all the prerequisites follow the below procedure to upgrade the database.

·         The sql script has been replaced with the preupgrd.sql script. The new Pre-Upgrade Information tool provides fix-up scripts with the ability to fix and address any issues identified by the pre-upgrade check script
 Copy the preupgrade.sql script from the new 12c home to present working directory.
$ cp /u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql  /u01/test11g12c/.
$ export ORACLE_HOME= /u01/app/oracle/product/11.2.0.4
SQL> @/u01/test11g12c/preupgrd.sql

·         The above script will give you the requirements to upgrade the database to new version. Perform any manual fix steps identified by the pre upgrade script or you can run preupgrade_fixups.sql. It is a fix up script that should be run before the upgrade but the manual fix procedure is recommended. To run preupgrade_fixups.sql use

SQL>  @/u01/app/oracle/product/11.2.0.4/cfgtoollogs/test11g12c/preupgrade/preupgrade_fixups.sql

·         Copy the parameter and password files from the old home to the new 12c home.

$ cp  /u01/app/oracle/product/11.2.0.4/dbs/inittest11g12c.ora /u01/app/oracle/product/12.1.0.2/dbs/inittest11g12c.ora
$ cp /u01/app/oracle/product/11.2.0.4/dbs/orapwtest11g12c  /u01/app/oracle/product/12.1.0.2/dbs/orapwtest11g12c

·         If the “preupgrade.log” file contains references to deprecated initialization parameters like changing the SGA size, make the suggested changes to the parameters now before continuing by editing parameters in the parameter file,

 $ vi inittest11g12c.ora

·         Set the environment is to new 12c home and run the environment file.

$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2

·         By connecting to SQL*PLUS client start the database in upgrade mode,

$sqlplus / as sysdba
SQL> STARTUP UPGRADE

·         The previous upgrade script catupgrd.sql has been replaced with the new catctl.pl Parallel Upgrade Utility script which provides the ability to run upgrade scripts in parallel taking advantage of spare CPU capacity which can potentially reduce upgrade times. Run the new Parallel Upgrade Utility (catctl.pl).

$   cd $ORACLE_HOME/rdbms/admin
$   $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

·         In case of a container 12.1.0.1 source database with one or more pluggable databases, it should be run for CDB$ROOT, PDB$SEED and also each of the pluggable database.

 SQL>alter session set container = CDB$ROOT
 SQL> spool c:\temp\upgrade.log
 SQL> @catupgrd.sql PARALLEL=NO
 SQL>alter session set container = PDB$SEED
SQL> @catupgrd.sql PARALLEL=NO

·         Startup the upgraded database and run the “utlu121s.sql” script to check the summary of the upgrade results.


 SQL> @?/rdbms/admin/utlu121s.sql 
If we had no errors, the “catuppst.sql” script would have been run as part of the upgrade. if you  have an  errors, you need to run it manually.
SQL> @?/rdbms/admin/catuppst.sql

·         If the “postupgrade_fixups.sql” file contained any recommendations, run it now
SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
·          You can do this by manually as follows,
Check for the invalid objects by
SQL> SELECT DISTINCT object_name, object_type, owner  FROM dba_objects  WHERE  status=INVALID’  ;
SQL> select count(*) from dba_objects where status like %INVALID%;

If there are any invalid objects run the below script to validate the invalid objects,
Recompile invalid objects.
SQL> @?/rdbms/admin/utlrp.sql

Check for newly invalid objects.
SQL> @?/rdbms/admin/utluiobj.sql

Run again to check the final outcome of the upgrade.
SQL> @?/rdbms/admin/utlu121s.sql

After running above step again check the invalid objects.
·         Now the upgraded database is available for use and you must alter any script or profile files that contain references to the ORACLE_HOME environment variable.

SQL> Startup
SQL> SELECT name, open_mode FROM v$database;
SQL> select banner from v$version
SQL> select version, comp_id, comp_name, status from dba_registry;

This completes the upgradation procedure.

Upgrade Database From 11g To 12c DBUA Silent mode
PRECHECKS:
Delete the Database Recycle Bin
The recycle bin must be purged before starting the database upgrade. Use the command below to empty the database recycle bin for all users on the source database.

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL>
Invalid and Duplicate Objects
Here, we find out if there are invalid and duplicate objects owned by the SYS and SYSTEM users. We find and fix invalid and duplicate objects as illustrated below.

SQL> SELECT DISTINCT object_name, object_type, owner
   FROM   dba_objects
   WHERE  status='INVALID'
   ORDER BY owner, object_name, object_type;

no rows selected

SQL>

It's important to note that you do not start the database upgrade when there are invalid data dictionary objects. To recompile invalid objects, connect as SYSDBA and run the supplied script 'utlrp.sql' as shown below.

SQL> @?/rdbms/admin/utlrp.sql
Invalid Components
Query DBA_REGISTRY to list the components' status. The status for all components must be valid before attempting the upgrade. Also, the DBUA pre-upgrade script reports installed components' status.

SQL> SELECT comp_name component, version, status
   FROM   dba_registry;

Database Pre-Upgrade
·        
The sql script has been replaced with the preupgrd.sql script. The new Pre-Upgrade Information tool provides fix-up scripts with the ability to fix and address any issues identified by the pre-upgrade check script
 Copy the preupgrade.sql script from the new 12c home to present working directory.
$ cp /u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql  /u01/test11g12c/.
$ export ORACLE_HOME= /u01/app/oracle/product/11.2.0.4
SQL> @/u01/test11g12c/preupgrd.sql

·         The above script will give you the requirements to upgrade the database to new version. Perform any manual fix steps identified by the pre upgrade script or you can run preupgrade_fixups.sql. It is a fix up script that should be run before the upgrade but the manual fix procedure is recommended. To run preupgrade_fixups.sql use
SQL>  @/u01/app/oracle/product/11.2.0.4/cfgtoollogs/test11g12c/preupgrade/preupgrade_fixups.sql
Upgradation method
$ /u01/app/oracle/product/12.1.0.2/db_1/bin/dbua -silent \
> -sid test11g12c \
> -oracleHome @/u01/app/oracle/product/11.2.0.4/ \
> -diagnosticDest /u01/app/oracle \
> -autoextendFiles \
> -recompile_invalid_objects true \
> -degree_of_parallelism 2 \
> -upgradeTimezone \
> -emConfiguration NONE \
> -keepHiddenParams \
> -gatheringStatistics \
> -createGRP true \
> -upgrade_parallelism 2


After the script completed complete all post check which is same as manual method





Friday 21 February 2014

Most Common Oracle Errors with Solution

Error Code

ORA-00001 

Error Message

ORA-00001: unique constraint (constraint_name) violated

Error Reason

An INSERT or UPDATE statement execution which violating the unique key constraint on the Table

Error Resolution

1)Drop The unique Constraint

 

2)Allow duplicates by droping the constraint

Error Code

ORA-00054

Error Message

ORA-00054: resource busy and acquire with NOWAIT specified

Error Reason

An insert or update or select command against a LOCK Table

Error Resolution

1)Try to execute the statment after sometime

 

2)Execute the command with a NOWAIT commands

Error Code

ORA-00257

Error Message

ORA-00257: archiver is stuck. CONNECT INTERNAL only, until freed

Error Reason

Error occurs at the archival of the redo logs

Error Resolution

1)Check the storage space availabilty

 

2)Check the initialization paramter ARCHIVE_LOG_DEST

Error Code

ORA-00600

Error Message

ORA-00600

Error Reason

Internal Error generated by generic kernal code of the Oracle Software

Error Resolution

Should be reported to oracle support

Error Code

ORA-12154

Error Message

ORA-12154: TNS:could not resolve service name

Error Reason

This error occurs due to missing tnsnames.ora file or tnsname.ora file not configured properly .

Error Resolution

Reconfigure the tnsname.ora

Error Code

ORA-03113

Error Description

ORA-03113: end-of-file on communication channel

Error -Reason

An unexpected end of file occured During communication

Error Resolution

1)Check the network may be the network went down

 

2)check the server may be the server down

Error Code

ORA-00936

Error Description

ORA-00936: missing expression

Error -Reason

missing sysntax when executing SQL statments

Error Resolution

1) Check your statement sytax properly

Error Code

ORA-01017

Error Description

ORA-01017: invalid username/password; logon denied

Error -Reason

Login activity uisng worng database credentials

Error Resolution

1) Enter the login credentials properly

Error Code

ORA-01555

Error Description

ORA-01555: snapshot too old (rollback segment too small)

Error -Reason

Insufficient Rollback segments

Error Resolution

1) Make larger rollback segments

Error Code

ORA-04031

Error Description

ORA-04031: unable to allocate num bytes of shared memory

Error -Reason

More usage of shared memory than availabe

Error Resolution

1. Reduce the use of shared memory availabe.

2. Increase the SHARED_POOL_SIZE paramter

Error Code

ORA-01031

Error Description

ORA-01031: insufficient privileges

Error -Reason

Due to the lack of the privileges for the login user to thedatabase objects

Error Resolution

Contact the Oracle DBA for accessing privilages

Error Code

ORA-01000

Error Description

ORA-01000: maximum open cursors exceeded

Error -Reason

Each user is restricted in using the number of cursors at a time . You may get this error when your cursor limit exeeds the set parameter

Error Resolution

shut down Oracle database , increase the OPEN_CURSORS parameter in the initialization file, and restart Oracle database .

Error Code

ORA-12560

Error Description

ORA-12560: TNS:protocol adapter error

Error -Reason

This is an error related to the protocol adaptor

Error Resolution

Check the Protcol specification

Error Code

ORA-27101

Error Description

ORA-27101: shared memory realm does not exist

Error -Reason

Unable to locate the shared memory realm

Error Resolution

Make sure that ORACLE_SID and ORACLE_HOME are correct.

 

A tralling slash on the ORACLE_HOME can cause error

 

ORACLE_HOME=’/home/oracle/prod’ —- Correct

 

ORACLE_HOME=’/home/oracle/prod/’ —-Wrong

Error Code

ORA-04030

Error Description

ORA-04030: out of process memory

Error -Reason

This is a common error while running import . This error is related to RAM settings. If the server having low RAM and memeory parameters are configured with a high configuration causes this error

Error Resolution

The best solution is to increase the RAM of the server if your server running with a low RAM other option is to Increase pga_aggregate_target

Error Code

ORA-00020

Error Description

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

Error -Reason

Maximum number of processes are exceeded than the limit in oracle database

Error Resolution

Login as SYSDBA and kill the unwanted sessions

Error Code

ORA-04030

Error Description

ORA-04030: out of process memory

Error -Reason

This is a common error while running import . This error is related to RAM settings. If the server having low RAM and memeory parameters are configured with a high configuration causes this error

Error Resolution

The best solution is to increase the RAM of the server if your server running with a low RAM other option is to Increase pga_aggregate_target

Error Code

ORA-04030

Error Description

ORA-04030: out of process memory

Error -Reason

This is a common error while running import . This error is related to RAM settings. If the server having low RAM and memeory parameters are configured with a high configuration causes this error

Error Resolution

The best solution is to increase the RAM of the server if your server running with a low RAM other option is to Increase pga_aggregate_target