Upgrade Oracle database from lower to upper version using DBUA utility

Execution plan for Up-gradation from 11.2.0.1,11.2.0.3 to 11.2.0.4 version using DBUA

Example show us the Upgradation of 11.2.0.1 to 11.2.0.4 version and same steps used to upgrade from 11G to 12C with non container database. For move to pluggable first you need to upgrade into 12C Version then you can move to pluggable database.

General Steps to Upgrade database with DBUA utility:

Note: Took the Pre-Upgrade SQL (UTLUXXXi.sql) from new version home present in RDMBS Folder and run on database which going to upgrade. It will give you report for upgrade. Example show for 11g to 12c upgrade download: Preupgrade
Important Note:Checked and performed the steps found in Pre-Upgrade log file as attached.

Steps for taking care of Upgrade Process if need to rollback:

1. Source database give following command 11g

GRANT ADMINISTER DATABASE TRIGGER to MDSYS;

2. Backup the old listener files.
Copy the network folder present in dbhome folder of Oracle

3. Set Old Oracle home and path variables

set ORACLE_HOME=E:\oracle\11.2.0\dbhome_1
set oracle_sid=IC
set PATH=E:\oracle\11.2.0\dbhome_1\bin;%PATH%

4. Took backup of database through RMAN Utility

run {
allocate channel ch00 type disk;
allocate channel ch01 type disk;
allocate channel ch02 type disk;
backup format 'F:\upgradebackupIC\rman\full_db_%t_%sp%p' filesperset 10 database plus archivelog;
release channel ch00;
release channel ch01;
release channel ch02;
allocate channel ch00 type disk;
backup format 'F:\upgradebackupIC\rman\cntrl_%s_%p_%t' CURRENT CONTROLFILE;
backup format 'F:\upgradebackupIC\rman\spfile_%s_%p_%t' spfile;
release channel ch00;
}

5. Check the Invalid objects

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

6. Check the objects count of user schema mainly:

select owner,object_type,count(*) from dba_objects group by owner,object_type order by owner,object_type ;

7. Run the UTLRP utility before upgrade if invalid objects found, note down again:

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

8. Check time zone if its less than 14 need to modified
Note: The changes might affect existing data of the TIMESTAMP WITH TIME ZONE data type

SELECT version FROM v$timezone_file;
-- check also
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

9: Check aud$ table before upgraded

select count(*) from sys.aud$;
Select count(*) from sys.fga_log$;

If you need audit Do the following s:
1. Export AUD$ data and/or FGA_LOG$ data from the source database
2. Clean up AUD$ and/or FGA_LOG$
3. Re-run catupgrd.sql
4. Import AUD$ data and/or FGA_LOG$ data into 11.2.0.1

truncate table sys.aud$;
truncate table sys.fga_log$

10: Execute the stats script

$ sqlplus "/as sysdba"
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

11: Take backup of RMAN Configuration

RMAN> Show all;

12: Check all the user list with password or expiry

set pages 999
set line 999
col account_status for a20
col username for a20
select username,account_status,expiry_Date,profile from dba_users;

13: Check profiles setting

set line 999
set pages 999
col profile for a20
col resource_name for a25
col resource for a10
col limit for a10
select * from dba_profiles order by 1,2;

14: Get all password of users;

select 'Alter user '||a.username||' identified by values '''||b.password||''' Default tablespace '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE '
||TEMPORARY_TABLESPACE||' '||case when account_status= 'OPEN' then ';' else 'LOCKED;' end from dba_users a,sys.user$ b where b.name = a.username;

15: Check local listener parameter is source database

Show parameter local

16: Backup of Pfile

create pfile='F:\upgradebackupIC\pfile.txt' from spfile;

17: Check Hidden parameter & remove it from spfile.

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';

Note: Remove the parameter from pfile and restart the database

18: Purging Recyclebin

PURGE DBA_RECYCLEBIN;

19: Took complete backup of database before dbua

Cold backup + Expdp backup full + RMAN Backup

20: Take snapshot of the services.msc

21: Check the registry$history table

col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select action_time,action,version,comments from registry$history;

22. Check the dba registory componenet:

col comp_id for a10
col version for a10
col status for a7
col comp_name for a50
select comp_id,comp_name,version,status from dba_registry;

23: Run the DBUA utility from new home
Set the PATH variable for new Oracle 11204 home, then run the DBUA Utility from new home.

24. Follows the steps performed in DBUA , it will take around 30 min to 1 hour depend upon system configuration and database size.

25. Check the Invalid objects

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

otherwise run utlrp.sql

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

26. Match the objects count for users schemas.

select owner,object_type,count(*) from dba_objects group by owner,object_type order by owner,object_type ;

27. Check time zone

SELECT version FROM v$timezone_file;

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

28 Import the aud$ backup and match the aud$ table

select count(*) from sys.aud$;
Select count(*) from sys.fga_log$;

29. Replace the listener.ora , tnsnames.ora and sqlnet.ora from backup Or Create new listener

30: Check the environment variable for windows

31: Match all the user list with password or expiry

set pages 999
set line 999
col account_status for a20
col username for a20
select username,account_status,expiry_Date,profile from dba_users;

32: Match profiles setting

set line 999
set pages 999
col profile for a20
col resource_name for a25
col resource for a10
col limit for a10
select * from dba_profiles order by 1,2;

33: Check local listener parameter is source database

Show parameter local

34: Check Hidden parameter

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';

35: Check Window Scheduler for backup or Services in windows

36: Took complete backup of database after database upgrade
Cold backup + Expdp backup full + RMAN Backup

37: Take snapshot of the services.msc

38: check the registry$history table

col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select action_time,action,version,comments from registry$history;

39. Check the dba_registry component:

col comp_id for a10
col version for a10
col status for a7
col comp_name for a50
select comp_id,comp_name,version,status from dba_registry;

40. Check archive log and enable it after dbua it is in disable state:

archive log list;
---Enable archiving
startup mount
alter database archivelog
alter database open;

41. Check the dump file destination in parameter for alert log & change it to new home:

show parameter diag

42. Checked all service is running from new oracle home binaries.

43. Set the RMAN configuration got in show all command.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s