Wednesday, May 20, 2015

Removing Oracle Components From Standared Edition Database

Oracle database components that are not supported on standard edition will get installed if the database was created using a template (e.g OLTP template). This can results in warnings during upgrades and at times issues with other oracle utilities such as export/import data pumps.
It may be helpful and could speed up the upgrade if these unsupported and unwanted (by the application DB is used for) components are removed before the upgrade. This post list steps for removing some of these components. It's best to take a full database backup before running these components removal scripts.
The database used is a 11.2.0.3 SE database created with the OLTP template. Currently all the components have the status valid.
--> 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
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
But if ultrp was run some of the unsupported components will become invalid.
1. Removing Application Express
@?/apex/apxremov.sql
2. Removing Multimeida (interMedia)
@?/ord/im/admin/imremov.sql
3. Removing Spatial
sqlplus "/as sysdba"
set pagesize 0
set feed off
spool dropsyn.sql
select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
spool off;
@dropsyn.sql
set feedback on

drop user mddata cascade;
drop user spatial_csw_admin_usr cascade;
drop user spatial_wfs_admin_usr cascade;
drop user MDSYS cascade;
4.Removing Expression Filter
@$ORACLE_HOME/rdbms/admin/catnoexf.sql


5. Removing OLAP components (also refer 1362752.1, 565773.1)
drop user olapsys cascade;
@?/rdbms/admin/utlrp
spool olapdrop.sql
select 'drop public synonym ' || synonym_name || ';' from dba_synonyms where owner='PUBLIC' and table_owner='OLAPSYS';
spool off
@olapdrop.sql
select * from sys.exppkgact$ where package = 'DBMS_AW_EXP' and  schema= 'SYS';
delete from sys.exppkgact$ where package = 'DBMS_AW_EXP' and schema= 'SYS';
commit;

select * FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP' AND SCHEMA= 'SYS';
create table exppkgact$_bck as select * from exppkgact$;
delete from exppkgact$ where package = 'DBMS_CUBE_EXP' and schema = 'SYS';
commit;

 drop package CWM2_OLAP_INSTALLER;
 @?/rdbms/admin/utlrp
At this stage Oracle database packages and types may be invalid. To fix this start the DB in restrict mode and run
 @?/rdbms/admin/catalog.sql
 @?/rdbms/admin/catproc.sql
 @?/rdbms/admin/utlrp.sql
6. Removing XDK for Java
 drop public synonym xslprocessor;
 @?/xdk/admin/rmxml.sql
7. Removing JServer
@?/javavm/install/rmjvm.sql
8. Removing Java packages
@?/rdbms/admin/catnojav.sql
9. Removing Oracle Warehouse Builder (OWB)
@?/owb/UnifiedRepos/clean_owbsys.sql
These are the only components removed as the application still uses wm_concat workspace manager could not be removed. But if required following script could be used to remove workspace manager
@$ORACLE_HOME/rdbms/admin/owmuinst.plb
Removing Oracle text may have consequences for expdp/impdp (not tested) so it was left. But if required this too could be removed with
 @?/ctx/admin/catnoctx.sql
 drop procedure sys.validate_context;
At this stage the OLAP components (only two will left in the dba_registry listing) may have status invalid. Other components will have status valid or removed. This doesn't affect the upgrade from 11.2.0.3 to 11.2.0.4. Once upgraded the OLAP components will have the status option_off.
 COMP_NAME                                STATUS
---------------------------------------- -------------
Oracle Enterprise Manager                VALID
Oracle XML Database                      VALID
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID
JServer JAVA Virtual Machine             REMOVED
Oracle XDK                               REMOVED
Oracle Database Java Packages            REMOVED
OLAP Analytic Workspace                  OPTION OFF
Oracle OLAP API                          OPTION OFF
Useful metalink note
Information On Installed Database Components and Schemas [ID 472937.1]

Friday, May 8, 2015

Adding Temp File on Standby

Creating a temporary tablespace on the primary of a physical data guard configuration only adds the tablespace entry on the standby, it doesn't create a temp file associated with the temporary tablespace.
Adding new temporary tablespace on primary.
create temporary tablespace temp2 tempfile '+data(tempfile)' SIZE 10M AUTOEXTEND ON NEXT 1M;
on standby the tablespace is listed in v$tablespace
SQL> select name from v$tablespace;

       TS# NAME
---------- -----------------------------------
         0 SYSTEM
         1 SYSAUX
         2 UNDOTBS1
         3 TEMP
         4 USERS
         5 TEMP2
But there's no temp file associated with this tablespce
select ts#,name from v$tempfile;

       TS# NAME
---------- --------------------------------------------------
         3 +DG_DATA/ent11g2s/tempfile/temp.267.787771139
To add a temp file stop the log apply on standby and open the db in read only mode.
SQL> alter database open read only;

Database altered.

SQL>  alter tablespace temp2 add tempfile '+DG_DATA' size 10m;

Tablespace altered.

select ts#,name from v$tempfile;

       TS# NAME
---------- ---------------------------------------------_
         3 +DG_DATA/ent11g2s/tempfile/temp.267.787771139
         5 +DG_DATA/ent11g2s/tempfile/temp2.260.877620677


If the temporary tablesapce is dropped on the primary then both tablespace and temp file is dropped on the standby.
SQL> select name from v$tempfile;

NAME
----------------------------------------------
+DG_DATA/ent11g2s/tempfile/temp.267.787771139

SQL>  select name from v$tablespace;

NAME
-----------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
On alert log
Recovery deleting tempfile #2:'+DG_DATA/ent11g2s/tempfile/temp2.260.877620677'
Recovery dropped temporary tablespace 'TEMP2'
Useful Metalink Notes
DDL Operations (Alter and Drop) on Temporary Tablespace Hang [ID 1911167.1]
Drop Temp Tablespace Command Hangs [ID 214371.1]
Drop Of A Huge LMT Temporary Tablespace Hangs [ID 1070665.1]

Friday, May 1, 2015

Upgrading to 12.1.0.2 Using 11.2.0.4 Backups

This post list the steps for upgrading a 11.2.0.4 RAC database as a single instance 12.1.0.2 database using the backups of 11.2.0.4 DB. Only difference in this case to that of normal restore is, database is restored using the higher version (12.1.0.2) oracle binaries.
Following steps assume that a backup of the RAC database is taken and copied to server where 12.1.0.2 software is installed. A pfile that's compatible with a single instance database is created. These steps are similar to restoring a RAC database to single instance.
1. Set the Oracle home to 12c and oracle SID to the database to be restored
$ echo $ORACLE_HOME
/opt/app/oracle/product/12.1.0/dbhome_2

$ export ORACLE_SID=std11g2
2. Start the DB using 12c binaries in nomount. Restore the control files and mount the database. At the same time update the pfile with control file details.
sqlplus  / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 27 11:36:25 2015

Connected to an idle instance.
SQL> startup nomount pfile='pfile.ora';
ORACLE instance started.

rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 27 11:36:57 2015

connected to target database: STD11G2 (not mounted)
RMAN> restore controlfile from '/home/oracle/backups/ctl2dq5eac3_1_1';

Starting restore at 27-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/oradata/STD11G2/controlfile/o1_mf_bmw4btg1_.ctl
output file name=/data/flash_recovery/STD11G2/controlfile/o1_mf_bmw4btpg_.ctl
Finished restore at 27-APR-15

RMAN> alter database mount;
3. Restore and recover the database.
run {
set newname for database to new;
restore database;
switch datafile all;
recover database;
}
4. Once the recovery has ended open the database in upgrade mode with resetlogs
RMAN> alter database open resetlogs upgrade;

using target database control file instead of recovery catalog

Statement processed
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-04023: Object SYS.STANDARD could not be validated or authorized
The warning and error could be ignored. Verify database is open in read write mode and instance status is migrate.
SQL> select open_mode from v$database;

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

SQL> select status from v$instance;

STATUS
------------
OPEN MIGRATE


5. Create a spfile from the pfile and then run the manual database upgrade from the 12c home. At the end of the upgrade database will be shutdown.
SQL> create spfile from pfile='/home/oracle/backups/pfile.ora';

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


Argument list for [catctl.pl]
SQL Process Count     n = 4
SQL PDB Process Count N = 0
Input Directory       d = 0
Phase Logging Table   t = 0
Log Dir               l = /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics
Script                s = 0
Serial Run            S = 0
Upgrade Mode active   M = 0
Start Phase           p = 0
End Phase             P = 0
Log Id                i = 0
Run in                c = 0
Do not run in         C = 0
Echo OFF              e = 1
No Post Upgrade       x = 0
Reverse Order         r = 0
Open Mode Normal      o = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0
Display Phases        y = 0
Child Process         I = 0

catctl.pl version: 12.1.0.2.0
Oracle Base           = /opt/app/oracle

Analyzing file catupgrd.sql
Log files in /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics
catcon: ALL catcon-related output will be written to /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_catcon_3508.lst
catcon: See /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd*.log files for output generated by scripts
catcon: See /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_*.lst files for spool files, if any
Number of Cpus        = 4
SQL Process Count     = 4

------------------------------------------------------
Phases [0-73]         Start Time:[2015_04_27 12:23:18]
------------------------------------------------------
Serial   Phase #: 0 Files: 1     Time: 111s
Serial   Phase #: 1 Files: 5     Time: 60s
Restart  Phase #: 2 Files: 1     Time: 0s
Parallel Phase #: 3 Files: 18    Time: 15s
Restart  Phase #: 4 Files: 1     Time: 0s
Serial   Phase #: 5 Files: 5     Time: 25s
Serial   Phase #: 6 Files: 1     Time: 18s
Serial   Phase #: 7 Files: 4     Time: 11s
Restart  Phase #: 8 Files: 1     Time: 0s
Parallel Phase #: 9 Files: 62    Time: 44s
Restart  Phase #:10 Files: 1     Time: 0s
Serial   Phase #:11 Files: 1     Time: 19s
Restart  Phase #:12 Files: 1     Time: 0s
Parallel Phase #:13 Files: 91    Time: 11s
Restart  Phase #:14 Files: 1     Time: 0s
Parallel Phase #:15 Files: 111   Time: 21s
Restart  Phase #:16 Files: 1     Time: 0s
Serial   Phase #:17 Files: 3     Time: 1s
Restart  Phase #:18 Files: 1     Time: 0s
Parallel Phase #:19 Files: 32    Time: 25s
Restart  Phase #:20 Files: 1     Time: 0s
Serial   Phase #:21 Files: 3     Time: 8s
Restart  Phase #:22 Files: 1     Time: 0s
Parallel Phase #:23 Files: 23    Time: 100s
Restart  Phase #:24 Files: 1     Time: 0s
Parallel Phase #:25 Files: 11    Time: 45s
Restart  Phase #:26 Files: 1     Time: 1s
Serial   Phase #:27 Files: 1     Time: 0s
Restart  Phase #:28 Files: 1     Time: 0s
Serial   Phase #:30 Files: 1     Time: 0s
Serial   Phase #:31 Files: 257   Time: 25s
Serial   Phase #:32 Files: 1     Time: 0s
Restart  Phase #:33 Files: 1     Time: 0s
Serial   Phase #:34 Files: 1     Time: 5s
Restart  Phase #:35 Files: 1     Time: 0s
Restart  Phase #:36 Files: 1     Time: 0s
Serial   Phase #:37 Files: 4     Time: 60s
Restart  Phase #:38 Files: 1     Time: 0s
Parallel Phase #:39 Files: 13    Time: 61s
Restart  Phase #:40 Files: 1     Time: 1s
Parallel Phase #:41 Files: 10    Time: 7s
Restart  Phase #:42 Files: 1     Time: 0s
Serial   Phase #:43 Files: 1     Time: 7s
Restart  Phase #:44 Files: 1     Time: 0s
Serial   Phase #:45 Files: 1     Time: 6s
Serial   Phase #:46 Files: 1     Time: 0s
Restart  Phase #:47 Files: 1     Time: 0s
Serial   Phase #:48 Files: 1     Time: 108s
Restart  Phase #:49 Files: 1     Time: 0s
Serial   Phase #:50 Files: 1     Time: 38s
Restart  Phase #:51 Files: 1     Time: 0s
Serial   Phase #:52 Files: 1     Time: 0s
Restart  Phase #:53 Files: 1     Time: 1s
Serial   Phase #:54 Files: 1     Time: 106s
Restart  Phase #:55 Files: 1     Time: 0s
Serial   Phase #:56 Files: 1     Time: 75s
Restart  Phase #:57 Files: 1     Time: 0s
Serial   Phase #:58 Files: 1     Time: 1s
Restart  Phase #:59 Files: 1     Time: 0s
Serial   Phase #:60 Files: 1     Time: 0s
Restart  Phase #:61 Files: 1     Time: 0s
Serial   Phase #:62 Files: 1     Time: 17s
Restart  Phase #:63 Files: 1     Time: 0s
Serial   Phase #:64 Files: 1     Time: 2s
Serial   Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LD_LIBRARY_PATH; LIBPATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LIBPATH; LD_LIBRARY_PATH_64=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export DYLD_LIBRARY_PATH; /opt/app/oracle/product/12.1.0/dbhome_2/perl/bin/perl -I /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin -I /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/../../sqlpatch /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_datapatch_upgrade.log 2> /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_datapatch_upgrade.err
returned from sqlpatch
    Time: 44s
Serial   Phase #:66 Files: 1     Time: 33s
Serial   Phase #:68 Files: 1     Time: 0s
Serial   Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LD_LIBRARY_PATH; LIBPATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LIBPATH; LD_LIBRARY_PATH_64=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export DYLD_LIBRARY_PATH; /opt/app/oracle/product/12.1.0/dbhome_2/perl/bin/perl -I /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin -I /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/../../sqlpatch /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_datapatch_normal.log 2> /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_datapatch_normal.err
returned from sqlpatch
 Time: 57s
Serial   Phase #:70 Files: 1     Time: 124s
Serial   Phase #:71 Files: 1     Time: 1s
Serial   Phase #:72 Files: 1     Time: 0s
Serial   Phase #:73 Files: 1 Use of uninitialized value $gsRTInclusion in concatenation (.) or string at catctl.pl line 1500.
    Time: 19s

------------------------------------------------------
Phases [0-73]         End Time:[2015_04_27 12:45:11]
------------------------------------------------------

Grand Total Time: 1315s

LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:
/opt/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/std11g2/upgrade/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:21m:55s]
Upgrade summary log showed the following.
Oracle Database 12.1 Post-Upgrade Status Tool           04-27-2015 12:50:10

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.1.0.2.0  00:11:25
JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:01:47
Oracle Real Application Clusters     OPTION OFF      12.1.0.2.0  00:00:01
Oracle Workspace Manager                  VALID      12.1.0.2.0  00:01:00
Oracle XDK                                VALID      12.1.0.2.0  00:00:38
Oracle XML Database                       VALID      12.1.0.2.0  00:01:46
Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:13
Final Actions                                                    00:01:16
Post Upgrade                                                     00:02:02
6. Start the database and verify the registry entries.
SQL>  select action_time,action,version,comments from registry$history;

ACTION_TIME                              ACTION                         VERSION                        COMMENTS
---------------------------------------- ------------------------------ ------------------------------ ----------------------------------------
22-JAN-15 05.21.55.225164 PM             APPLY                          11.2.0.4                       PSU 11.2.0.4.5
27-APR-15 12.40.55.455359 PM             VIEW INVALIDATE                                               view invalidation
27-APR-15 12.41.48.624963 PM             UPGRADE                        12.1.0.2.0                     Upgraded from 11.2.0.4.0


SQL> select comp_name,version from dba_registry;

COMP_NAME                                          VERSION
-------------------------------------------------- ------------------------------
Oracle XML Database                                12.1.0.2.0
Oracle Workspace Manager                           12.1.0.2.0
Oracle Database Catalog Views                      12.1.0.2.0
Oracle Database Packages and Types                 12.1.0.2.0
JServer JAVA Virtual Machine                       12.1.0.2.0
Oracle XDK                                         12.1.0.2.0
Oracle Database Java Packages                      12.1.0.2.0
Oracle Real Application Clusters                   12.1.0.2.0
7. Follow step 11 on Restore RAC DB Backup as a Single Instance DB post to carry out the clean up of additional thread and undo tablespaces.
8. If upgrade is satisfactory update the compatible parameter to 12c.

This conclude the upgrading to 12c using 11gR2 backups.

Useful metalink notes
RMAN Restore of Backups as Part of a Database Upgrade [ID 790559.1]

Related Posts
Upgrading RAC from 11.2.0.4 to 12.1.0.2 - Database
Upgrading 11.2.0.3 (11gR2) Database to 12.1.0.1 (12c) Using DBUA
Upgrading from 11.2.0.3 to 12.1.0.1 RAC
Upgrade Oracle Database 12c1 from 12.1.0.1 to 12.1.0.2
Upgrading 12c CDB and PDB from 12.1.0.1 to 12.1.0.2