Month: October 2015

ORA-28405 Cannot Grant Secure Role To Another Secure Role

arcsdegeoLeave a comment

In Oracle Database releases 11.2.0.4 and 12c this error (ORA-28405) will be generated, the reason behind that is that Oracle imposed extra restrictions when you grant secure OR password protected role to a normal role OR (password protected role).

****** To simulate this:

connect / as sysdba

SQL>create role RL1 identified by hello23;

SQL>grant create session to RL1;

SQL>create role RL2 identified by bye246;

SQL>grant create session to RL2;

SQL>grant RL1 to RL2;

ORA-28405 Cannot Grant Secure Role To Another Secure Role

There is a workaround solution which is to set parameter _grant_secure_role = true

SQL> alter system set “_grant_secure_role”=true scope=spfile;

Advertisement

ORA-00283: recovery session canceled due to errors for standby database

arcsdegeoLeave a comment

I received the below errors while trying to recovery one of my standby databases that i am supporting:

SQL> RECOVER AUTOMATIC STANDBY DATABASE;

ORA-00283: recovery session canceled due to errors

ORA-00333: redo log read error block 104448 count 2048

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL> shutdown immediate;

SQL> startup mount;

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

ORA-01153: an incompatible media recovery is active

*** solution:

SQL>STARTUP NOMOUNT

SQL>ALTER DATABASE MOUNT STANDBY DATABASE;

 

SQL> SELECT open_mode,database_role FROM v$database;

OPEN_MODE            DATABASE_ROLE

——————– —————-

MOUNTED              PHYSICAL STANDBY

SQL> RECOVER AUTOMATIC STANDBY DATABASE;

How to roll back (Revert) Oracle 12c database patch (PSU) – (de-install PSU)

arcsdegeo2 Comments

Sometimes you need to roll back the applied Oracle database PSU (Patch Set Update) for whatever the reason is. So you need to know how to de-install the patch applied in quick and swift way especially if you are doing it in production environment.

On the other hand, a DBA shouldn’t resort to this unless there is a big impact on production applications since in a controlled IT change environments patches are usually applied first in test, QA, and then production and tested during these stages.

Also, another important information to mention is that Oracle is now releasing 2 patches ( 1 database, and 1 for JAVA component) every quarter. And the Java Component patch sometimes could lead to application problems with specific vendors so you need to be careful.

The following is general overview procedure in how to roll back a database patch (either for database or Java component), in my example I am referring to JULY 2015 Oracle release quarterly security patch. It’s very important that you read the (readme.html) accompanied with the patches downloaded.

1.de-installing the database patch:

  • Shtudwon the database and the listener of your database using the commands:

lsnrctl stop LISTENER_TESTDB

sqlplus ‘/as sysdba’

SQL> shutdown immediate

**** Then run the Opatch utility to roll back the applied patch:

cd /app/oracle/downloaded_patch/july2015/20831110

opatch rollback -id 20831110

You should receive message similar “OPatch completed successfully”

If you have warning then you can check the logs in the directory specified in the output.

  • Now the Post De-instillation will take place:

sqlplus ‘/as sysdba’

startup

cd $ORACLE_HOME/OPatch

./datapatch –verbose

*** To verify that the patch is rolled back successfully:

select * from dba_registry_sqlpatch where PATCH_ID=20831110;

rollback_patch_db

Important Remark:

My recommendation is to use utilrip after that  to re-compile all database objects:

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Also checking that all database components are vaild post de-instillation:

Select * from dba_registry;

lsnrctl start LISTENER_TESTDB

********************************************************************

2.de-installing the Java patch:

  • Shtudwon the database and the listener of your database using the commands:

lsnrctl stop LISTENER_TESTDB

SQL> shutdown immediate

*** Then run the Opatch utility to roll back the applied patch:

cd /app/oracle/downloaded_patch/july2015/JAVA/21068507

opatch rollback -id 21068507

  • Now the Post De-instillation will take place:

sqlplus ‘/as sysdba’

SQL> startup upgrade ;

SQL>exit

cd $ORACLE_HOME/OPatch

./datapatch –verbose

You will receive a message similar to this:

Patch 21068507 rollback: SUCCESS

SQL> shutdown immediate;

SQL> startup

Don’t forget to startup the listener:

lsnrctl start LISTENER_TESTDB

*** To verify that the patch is rolled back successfully:

select * from dba_registry_sqlpatch where PATCH_ID=21068507;

rollback_patch_db_java

Important Remark:

My recommendation is to use utilrip after that re-compile all database objects:

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Also checking that all database components are valid post de-instillation:

Select * from dba_registry;

I hope this would help…..

New compression algorithm parameter in datapump utility for 12c : COMPRESSION_ALGORITHM

arcsdegeoLeave a comment

A new “compression algorithm” parameter has been introduced in 12c release for export datapump utility, the parameter is: COMPRESSION_ALGORITHM .

There are three modes for this new compression: basic, low, medium, high

The default mode is “basic”.

** Here i am going to explore exporting the same schema in 3  different ways for testing purpose from a 12c database:

Exporting the schema using datapump command with “no compression”:

$ORACLE_HOME/bin/expdp “‘/as sysdba ‘”  DIRECTORY=DUMP_DIR  SCHEMAS=TEST_SCHEMA DUMPFILE=DUMP_NO_COMPRESSION.dmp  logfile=DUMP_NO_COMPRESSION_EXP.log logtime=all

Exporting the schema using datapump command using the new compression parameter in “HIGHT” mode:

$ORACLE_HOME/bin/expdp “‘/as sysdba ‘”  DIRECTORY= DUMP_DIR  SCHEMAS= TEST_SCHEMA DUMPFILE=DUMP_HIGHT_COMPRESSION.dmp COMPRESSION=ALL COMPRESSION_ALGORITHM=HIGH logfile=DUMP_HIGH_COMPRESSION_EXP.log logtime=all

Exportng the schema using datapump command using the new compression parameter in “MEDUIM” mode:

$ORACLE_HOME/bin/expdp “‘/as sysdba ‘”  DIRECTORY= DUMP_DIR  SCHEMAS= TEST_SCHEMA DUMPFILE=DUMP_MEDIUM_COMPRESSION.dmp COMPRESSION=ALL COMPRESSION_ALGORITHM=MEDIUM logfile=DUMP_MEDIUM_COMPRESSION_EXP.log logtime=all

The conclusion is the following:

  • Dump file generated with “no compression” size for the schema was around 13.41 GB
  • Dump file generated using the “high compression” size was around 6.88 GB
  • Dump file generated using “medium compression” size was around 7.31 GB

Important remark: the normal export took around 7 minutes to finish however the highly compressed export process took around 1 hour !!!

While using medium compression algorithm it took 16 minutes.

This was performed on IBM AIX environment for Oracle 12.1.0.2, of course the “server resources” is a factor while performing compression in addition to the “data types” of the schema exported is another factor to be considered.

—————-