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.
Smart way of Technology 