Oracle 12c

Oracle October 2015 CPU – quick and dirty install on 11.2.0.4 and 12.1.0.2 4

This entry was posted in AIX OPatch Oracle Oracle 11g Oracle 12c PSU Security on by

[Update 17.11.2015 – Thanks Lars Johan Ulveseth for the extra testing and comment – see below. In fact I’ve only tested the PSU installation on a non-CDB 12c database. There is a missing step, added now, when you are in a multitenant configuration with pluggable databases. Script updated based on Lars comments.]

Following my January post, here are basic instructions to install the latest Oracle security patch. While one should always read the documentation, below you can find how to install CPU Oct 2015  on single instance. Please count about 5 minutes downtime on your database (a bit more if it is the first time you are doing this).

If you are single instance database (no RAC) and either 11.2.0.4 or 12.1.0.2 here are the quick summary of the installation. Tested on Linux and AIX:

Oracle 12.1.0.2 – October 2015 CPU installation

OPatch: You need to update the OPatch tool, minimum version is 12.1.0.1.7. Direct download from Oracle support.

Bundle 12.1.0.2 – Patch 21520444 – Combo OJVM PSU 12.1.0.2.5 and Database PSU 12.1.0.2.5 for UNIX

  • Patch 21359755 – Database Patch Set Update 12.1.0.2.5 (Oct2015) –> RAC-Rolling Installable
  • Patch 21555660 – Oracle JavaVM Component 12.1.0.2.5 Database PSU (OCT2015) –> Non RAC-Rolling Installable
export PATCH_LOC="/tmp"

1. Download + Upgrade OPatch (Opatch download direct link)

unzip ${PATCH_LOC}/p6880880_121010_Linux-x86-64.zip -d $ORACLE_HOME

2. Stop database+listeners+agent

3. Apply DB PSU (21359755 ) but do NOT run DB PSU post install steps

cd ${PATCH_LOC}
unzip p2*
cd 2*
cd 21359755/
$ORACLE_HOME/OPatch/opatch apply

3. Apply OJVM PSU patch

cd ../21555660/
$ORACLE_HOME/OPatch/opatch apply

4. Restart database [and open pluggable databases] in upgrade mode

SQL> startup upgrade;
SQL> alter pluggable database all open upgrade;

5. Run post install steps

cd $ORACLE_HOME/OPatch
./datapatch -verbose

6. Restart database [and open pluggable databases] in normal mode

SQL> shutdown;
SQL> startup;
SQL> alter pluggable database all open;





Oracle 11.2.0.4 – October 2015 CPU installation


Bundle 11.2.0.4 – Patch 21744335 – Combo OJVM PSU 11.2.0.4.5 and Database SPU 11.2.0.4 (CPUOct2015)


    

  • Patch 21352646 – Database Security Patch Update 11.2.0.4.0 (CPUOCT2015) –> RAC-Rolling Installable
    • 

  • Patch 21555791 – Oracle JavaVM Component 11.2.0.4.5 Database PSU (OCT2015) –> Non RAC-Rolling Installable
    • 



export PATCH_LOC="/tmp"
cd ${PATCH_LOC}
unzip p2*.zip
cd 2*



1. Shutdown databases and services

2. Apply DB PSU (21352646) but do NOT run DB PSU post install steps


cd 21352646
$ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate



3. Apply OJVM PSU patch


cd ../21555791/
$ORACLE_HOME/OPatch/opatch apply



4. Run the OJVM PSU post install steps followed by the DB PSU (or equivalent) post install steps.


cd $ORACLE_HOME/sqlpatch/21555791
sqlplus / as sysdba 



SQL> startup upgrade
SQL> @postinstall.sql
SQL> shutdown immediate
SQL> exit;



cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba



SQL> startup
SQL> @catbundle.sql cpu apply
SQL> @utlrp.sql
SQL> exit;



 


    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
Oracle 12.1.0.2 – ORA-600 [QERHJDISABLEHJCBK: NOT A HASH JOIN] on UPDATE
This entry was posted in  bug  Oracle  Oracle 12c  on  by  
        

        
Surprises happen when you go to the latest version of Oracle without deep testing.

My client just hit the bug:

Bug 20513930 : UPDATE FAILS WITH [QERHJDISABLEHJCBK: NOT A HASH JOIN]


Basically the client was getting a ORA-600 [QERHJDISABLEHJCBK: NOT A HASH JOIN] when doing a certain query. Looking at it, it was an UPDATE of a view. This view had a “instead of update” trigger, which was doing several actions. After enabling trace on the session, the bug does not happen! 


Fortunately someone had already open the bug at Metalink. The workaround is to disable the new “groundbreaking feature” (dixit Oracle) called Adaptive Plans. 


SQL> alter system set "_optimizer_adaptive_plans"=false;
System altered.



Hopefully there will be soon a patch for this problem.


    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
Oracle automatic maintenance, expdp and ORA-01466
This entry was posted in  Datapump  Oracle  Oracle 11g  Oracle 12c  on  by  
        

        
At a client request I’ve implemented a daily consistent export of few schemas, using something like:


expdp "/ as sysdba" SCHEMAS=user1 dumpfile=export_%1_%datetime%.dmp logfile=export_%1_%datetime%.log flashback_time=systimestamp



I was set to run at 22:00 every night.


Next day the log showed some errors like:


ORA-31693: Table data object "USER1"."TABLE_TEST" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed



Looking closer, it was due to the automatic statistics collection, done within the daily maintenance window of Oracle. The LAST_ANALYZED column of DBA_TABLES changed, as well as LAST_DDL_TIME on DBA_OBJECTS. I did not expect neither I see nowhere else complaining about this interference between the maintenance windows and a export data pump. Strange!


To avoid any problem in the future we decided to change the maintenance window time and lenght.

On Oracle 11g and 12c there is a window per day. So to change lets say to 19:00 and having a duration of 2 hours you do:


EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW','repeat_interval','freq=daily;byday=TUE;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW','repeat_interval','freq=daily;byday=FRI;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));



    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
Oracle January 2015 CPU – quick and dirty install on 11.2.0.4 and 12.1.0.2 1 
This entry was posted in  Oracle  Oracle 11g  Oracle 12c  PSU  Security  on  by  
        

        
While one should always read the documentation accompanying the patches, the new January 2015 (and in some extend also October 2014) are more complex to install with a java and a db components.


If you are single instance database (no RAC) and either 11.2.0.4 or 12.1.0.2 here are the quick summary of the installation. Tested on Linux and Solaris SPARC:


Oracle 12.1.0.2 – January 2015 CPU installation


Bundle 12.1.0.2 – Patch 20132434


    

  • Patch 19769480 – Database Patch Set Update 12.1.0.2.2 (Jan2015) –> RAC-Rolling Installable
    • 

  • Patch 19877336 – Oracle JavaVM Component 12.1.0.2.2 Database PSU (JAN2015) –> Non RAC-Rolling Installable
    • 



export PATCH_LOC="/tmp"



1. Download + Upgrade OPatch


unzip ${PATCH_LOC}/p6880880_121010_Linux-x86-64.zip -d $ORACLE_HOME



2. Stop database+listeners+agent


3. Apply DB PSU (19769480) but do NOT run DB PSU post install steps


cd ${PATCH_LOC}
unzip p2*
cd 2*
cd 19769480/
$ORACLE_HOME/OPatch/opatch apply



3. Apply OJVM PSU patch


cd ../19877336/
$ORACLE_HOME/OPatch/opatch apply



4. Restart database


5. Run post install steps


cd $ORACLE_HOME/OPatch
./datapatch -verbose





 


Oracle 11.2.0.4 – January 2015 CPU installation


 


Bundle 11.2.0.4 – Patch 20132517


    

  • Patch 19854503 – Database Security Patch Update 11.2.0.4.0 (CPUJAN2015) –> RAC-Rolling Installable
    • 

  • Patch 19877440 – Oracle JavaVM Component 11.2.0.4.2 Database PSU (JAN2015) –> Non RAC-Rolling Installable
    • 



export PATCH_LOC="/tmp"
cd ${PATCH_LOC}
unzip p20132517_112040_Linux-x86-64.zip
cd 2*



1. Shutdown databases and services

2. Apply DB PSU (19854503) but do NOT run DB PSU post install steps


cd 19854503
$ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate



3. Apply OJVM PSU patch


cd ../19877440/
$ORACLE_HOME/OPatch/opatch apply



4. For 11.2.0.3 and 11.2.0.4 run the OJVM PSU post install steps followed by the DB PSU (or equivalent) post install steps.


cd $ORACLE_HOME/sqlpatch/19877440
sqlplus / as sysdba



SQL> startup upgrade
SQL> @postinstall.sql
SQL> shutdown immediate
SQL> exit;



cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba



SQL> startup
SQL> @catbundle.sql cpu apply
SQL> @utlrp.sql
SQL> exit;



 


    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
Oracle 12.1 – Proxy only connect user property 1 
This entry was posted in  Oracle  Oracle 12c  Security  on  by  
        

        
This yet undocumented feature allows to define application schemas which can only be accessed through a proxy user. It makes a very useful to assure that no user connects directly to the application schema, even by knowing its password.


Here how it works:


SQL> CREATE USER app_user IDENTIFIED BY xyz;
User created.

SQL> GRANT CREATE SESSION TO app_user;
Grant succeeded.

SQL> ALTER USER app_user PROXY ONLY CONNECT;
User altered.

SQL> CREATE USER personal_user IDENTIFIED BY prx1;
User created.

SQL> ALTER USER app_user GRANT CONNECT THROUGH personal_user;
User altered.

SQL> CONNECT app_user/xyz;
ERROR:
ORA-28058: login is allowed only through a proxy

SQL> CONNECT personal_user[app_user]/prx1;
Connected.

SQL> SELECT user FROM dual;
USER
------------------------------
APP_USER



The information that app_user accepts to be connected only through proxy user can be seen at the new DBA_USERS column PROXY_ONLY_CONNECT.


As usual, the use of undocumented features are not supported by Oracle. The syntax to rollback the change is:


SQL> ALTER USER app_user CANCEL PROXY ONLY CONNECT;



    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
Add path to ASMCMD prompt
This entry was posted in  ASM  Oracle  Oracle 11g  Oracle 12c  RAC  on  by  
        

        
Just discovered this morning how to show the path on the ASMCMD tool and could not be easier:


anjovm2:/home/grid:+ASM2 $ asmcmd -p
ASMCMD [+] > cd dgdata
ASMCMD [+dgdata] > cd db12c
ASMCMD [+dgdata/db12c] > ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfiledb12c.ora
ASMCMD [+dgdata/db12c] > cd datafile
ASMCMD [+dgdata/db12c/datafile] >



    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
ORA-17628: Oracle error 19505 returned by remote Oracle server
This entry was posted in  DataGuard  Oracle  Oracle 11g  Oracle 12c  on  by  
        

        
Today I was doing a procedure for duplicating databases at a client environment. One error that pop up time to time was the one below:


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/13/2014 18:49:01
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on t1 channel at 05/13/2014 18:49:00
ORA-17628: Oracle error 19505 returned by remote Oracle server



At several websites you can see different causes:


– Control file location specification was wrong;


– db_file_name_convert were not specified;


– FRA diskgroup inexisting in the target;


– etc.


I had the following two cases on my experiments:


– destination database directory not existing on the DGDATA diskgroup (+DGDATA/<db_unique_name>)


– I did not connect to auxiliary using a standard network naming (I was using ezconnect).


    

    
                                    

                                
                                                            
                                                                    

                                        
    

              

          
Oracle DBA role granted privileges per version
This entry was posted in  Oracle  Oracle 10g  Oracle 11g  Oracle 12c  Security  on  by  
        

        

Below you can find the Oracle defaults

DBA role privileges per database version since Oracle 10.2. It is based on Enterprise Edition with Java and XML installed (on 12.1 you cannot anymore choose what to install).







On Oracle

12.1 there are 3 new roles : CAPTURE_ADMIN, EM_EXPRESS_ALL and

OPTIMIZER_PROCESSING_RATE. Comments about these new roles/privileges can be found below.





















admin_option











comment









CAPTURE_ADMIN





yes





>12.1





Provides

  the privileges necessary to create and manage privilege analysis policies.









DATAPUMP_EXP_FULL_DATABASE











>11.1















DATAPUMP_IMP_FULL_DATABASE











>11.1















DELETE_CATALOG_ROLE





yes





















EM_EXPRESS_ALL











>12.1





Enables

  users to connect to Oracle Enterprise Manager (EM) Express and use all the

  functionality provided by EM Express (read and write access to all EM Express

  features). The EM_EXPRESS_ALL role includes the EM_EXPRESS_BASIC role









EXECUTE_CATALOG_ROLE





yes





















EXP_FULL_DATABASE



























GATHER_SYSTEM_STATISTICS



























IMP_FULL_DATABASE



























JAVA_ADMIN



























JAVA_DEPLOY



























OPTIMIZER_PROCESSING_RATE











>12.1











Provides privileges to execute the

    GATHER_PROCESSING_RATE, SET_PROCESSING_RATE, and DELETE_PROCESSING_RATE

    procedures in the DBMS_STATS package. These procedures manage the

    processing rate of a system for automatic degree of parallelism (Auto DOP).

    Auto DOP uses these processing rates to determine the optimal degree of

    parallelism for a SQL statement.



















SCHEDULER_ADMIN





yes





















SELECT_CATALOG_ROLE





yes





















WM_ADMIN_ROLE



























XDBADMIN



























XDB_SET_INVOKER











>11.1















XDBWEBSERVICES











<10.2

















The query used is:





set pages 20 lines 200



col grantee for a10



col granted_role for a40



select * from dba_role_privs where grantee=’DBA’ order by 1,2;