Year: 2015

Oracle 11.2.0.4 error PLS-00306: wrong number or types of arguments in call to ‘SYNCRN’

arcsdegeoLeave a comment

After upgrading the database to 11.2.0.4 and applying the latest Patch Set Update(October 2015 PSU) (11.2.0.4.8) , a database account could not perform any update or delete transaction on a table (that has context index) and received the following errors:

 

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘SYNCRN’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

 

Solution:

There is a patch provided by Oracle to fix this OR perform the following “workaround” by recreating ctxsys.syncrn procedure:

SQL> sqlplus ‘/as sysdba’

SQL>alter session set current_schema=CTXSYS;

SQL>create or replace procedure syncrn (
  ownid IN binary_integer,
  oname IN varchar2,
  idxid IN binary_integer,
  ixpid IN binary_integer,
  rtabnm IN varchar2,
  srcflg IN binary_integer,
  smallr IN binary_integer
)
  authid definer
  as external
  name “comt_cb”
  library dr$lib
  with context
  parameters(
    context,
    ownid  ub4,
    oname  OCISTRING,
    idxid  ub4,
    ixpid  ub4,
    rtabnm OCISTRING,
    srcflg ub1,
    smallr ub1
);
/

// execute the following to recompile all database objects:

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

//Then check that your database components are “valid”:

SQL> select * from dba_registry;

//check if there is any invalid objects under CTXSYS:

SQL>select owner, count(*) from dba_objects where status <>’VALID’ group by owner order by 1;

 

Remark: to find the patch set update applied on your database use the following query:

select * from sys.registry$history;

Upgrading Oracle database from 11.2.0.3 to 11.2.0.4

arcsdegeoLeave a comment

The following is a generic procedure to upgrade from 11.2.0.3 to 11.2.0.4 . As many of you know that 11.2.0.3 release has been desupported and last PSU released was in JULY 2015. so you need to either upgrade to 12cR1 OR to 11.2.0.4 . The right move is of course to move forward to 12c however, some vendor applications are not certified  Yet 😦 !!!

So you will need to upgrade 11.2.0.4.

I hope this will help. Please note that the below procedure assumes that you have already installed 11.2.0.4 binaries successfully.

 

Procedure upgrade in Unix environment:

//create a directory called “upgrade_11gR4”

mkdir upgrade_11gR4

cd upgrade_11gR4

//now copy 2 scripts (utlu112i.sql) and (dbupgdiag.sql )

cp /oraclex/oradbp05/product/11.2.0.4/rdbms/admin/utlu112i.sql .

cp /oracle_software/database/dbupgdiag.sql .

//gather general database information

sql

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> set pages 300

SQL> set lines 250

SQL> col owner for a23;

SQL> col object_name for a35;

SQL> col object_type for a20;

SQL> col action for a15;

SQL> col comments for a40;

SQL> col comp_name for a40;

SQL> col version for a15;

 

SQL> spool before_upgrading_11gR4.log

SQL> SELECT NAME FROM V$DATABASE;

SQL> SELECT * from V$VERSION;

SQL> SELECT ACTION,VERSION,COMMENTS FROM SYS.REGISTRY$HISTORY;

SQL> SELECT COMP_NAME,VERSION,STATUS FROM DBA_REGISTRY;

SQL> select count(*) from dba_objects where  status = ‘INVALID’;

SQL> spool off

SQL>upgrade_information_gather.log

SQL>@utlu112i.sql

SQL>spool off

exit

//check the the log output and read it carefully

cat upgrade_information_gather.log

sql

SQL>alter session set nls_language=’American’;
SQL>@dbupgdiag.sql

//then check the log output for diagnostic information

//Purging the Database Recycle Bin

SQL> PURGE dba_recyclebin;

//Copy the LISTENER.ORA &  TNSNAMES.ORA & SQLNET.ORA from 11.2.0.3 directory to 11.2.0.4 directory

//shutdown the database

//change  your .profile to point to the new Oracle HOME binaries for 11.2.0.4

cd $ORACLE_HOME/rdbms/admin

sqlplus “/ as sysdba”

SQL>startup UPGRADE

SQL>@catupgrd.sql

//enter a directory where you want to log to be generated to

SQL> shutdown immediate;

sqlplus “/ as sysdba”

SQL>STARTUP

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL>set pages 300

SQL>set lines 250

SQL>col owner for a17;

SQL>col object_name for a35;

SQL>col object_type for a20;

SQL>Select owner,object_type,object_name from dba_objects where status=’INVALID’ ORDER BY 1,2,3;

SQL>@$ORACLE_HOME/rdbms/admin/catuppst.sql

 

//Run the following script to identify any new invalid objects due to the upgrade:

SQL>@$ORACLE_HOME/rdbms/admin/utluiobj.sql

 

//check your time zone

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’ ORDER BY PROPERTY_NAME;

//if the the time zone is 14 then you are OK…and your upgrade is DONE…congratulations 🙂

//if the time zone value is less than 14 then proceed with the following:

SQL>TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

SQL>TRUNCATE TABLE sys.dst$affected_tables;

SQL>TRUNCATE TABLE sys.dst$error_table;      

 

//Start upgrade window

SQL>EXEC DBMS_DST.BEGIN_UPGRADE(14);

//The message “An upgrade window has been successfully started.” will be seen

//check

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE ‘DST_%’

ORDER BY PROPERTY_NAME;

//restart the database

SQL>shutdown immediate;

SQL> startup;

SQL>set serveroutput on

VAR numfail number

BEGIN

DBMS_DST.UPGRADE_DATABASE(:numfail,

parallel => TRUE,

log_errors => TRUE,

log_errors_table => ‘SYS.DST$ERROR_TABLE’,

log_triggers_table => ‘SYS.DST$TRIGGER_TABLE’,

error_on_overlap_time => FALSE,

error_on_nonexisting_time => FALSE);

DBMS_OUTPUT.PUT_LINE(‘Failures:’|| :numfail);

END;

/

SQL> VAR fail number

BEGIN

DBMS_DST.END_UPGRADE(:fail);

DBMS_OUTPUT.PUT_LINE(‘Failures:’|| :fail);

END;

/

//Output that will be seen: “An upgrade window has been successfully ended”  “Failures:0”

ACL (Access Control List) in Oracle 12c

arcsdegeoLeave a comment

The following points should be in consideration while upgrading your Oracle database to 12c:

  • Starting with Oracle Database 12c, the access control of the UTL packages (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR) is implemented using Oracle Database Real Application Security and does not require Oracle XML DB.

 

  • existing ACL in XDB of your database will be migrated during the upgrade to 12c .

 

  • DBMS_NETWORK_ACL_ADMIN has been deprecated.

 

  • Existing ACL’s in your database will be migrated and “re-named” after the upgrade , the following query will list the old and the new name of the ACL:

           SQL> select * from DBA_ACL_NAME_MAP ;

  • take the backup of the following tables just in case ACL’s were not migrated successfully:

 SQL> create table TESTSCHEMA.DBA_NETWORK_ACLS as select * from DBA_NETWORK_ACLS;

SQL> create table TESTSCHEMA.DBA_NETWORK_ACL_PRIVILEGES as select * from DBA_NETWORK_ACL_PRIVILEGES;

Restore table point in time recovery in Oracle 12c

arcsdegeo4 Comments

One of the new exciting features of Oracle 12c is the ability to restore a Table using RMAN which was not possible in previous Oracle releases.

To implement this there are pre-requisites:

  • The database should be in Archive log mode, and read-write mode.
  • You need to have successful RMAN backup of the table existed at the point of time you need to recover to.

Let us proceed in testing:

I will create a new dummy table based on actual table existing in the database:

SQL> create table TESTSCHEMA.DUMMY_TABLE as select * from TESTSCHEMA.EMPLOYEE_INFO;

Now check the current SCN number and use it for the command later on:

SQL> select dbms_flashback.get_system_change_number from dual;

OR use this query:

SQL> select current_scn from v$database;

OR check the SCN generated based on the tablespace backup:

RMAN> list backup;

list backups rman

OR

Use the following command to check the backup of the data file associated with the tablespace:

This query will report the FILE_ID number that can be used with the below RMAN command to get the SCN for the tablespace:

select TABLESPACE_NAME,FILE_ID from dba_data_files;

then,

rman target /

RMAN> LIST BACKUP OF DATAFILE 7;

datafile backup command

***Before Dropping the table make sure you have a valid successful backup:

RMAN> backup database;

Then drop the newly created table:

SQL>Drop table TESTSCHEMA.DUMMY_TABLE;

Command to recover the table:

recover table ‘TESTSCHEMA’.’DUMMY_TABLE’ until scn 161451617 auxiliary destination ‘/oracle/app/’;

You need to wait as it will take time with many messages that is going to be generated….

Important Remark: you will notice messages about AUX Database being shutdown and startup, DON’T PANIC (this is not the database you are working on it, from behind the scene an auxiliary database is being created while table restoration is taking place).

You can safely remove the files generated under the “auxiliary destination” directory.

****The following are another alternatives for the same action:

A. You can also recover the table to be exported as a dump file (where you can import the table later on ) :

recover table ‘ TESTSCHEMA ‘. ‘DUMMY_TABLE’ until scn 161773617 auxiliary destination ‘/oracle/app/’ DATAPUMP DESTINATION ‘/oracle/export/’ DUMP FILE ‘table_recovery_dump.dmp’ notableimport;

table recovery dump

B. You can recover the table to be renamed to a different name while recovered:

recover table ‘TESTSCHEMA’.’DUMMY_TABLE’ until scn 161773617 auxiliary destination ‘/oracle/app/’ remap table TESTSCHEMA.DUMMY_TABLE:DUMMY_TABLE_TEMP;

Oracle 12c new datapump parameters: views_as_tables & logtime

arcsdegeoLeave a comment

Here I am exploring two new Oracle datapump parameters that have been introduced in Oracle 12cR1 (12.1.0.2). They are: views_as_tables & logtime.

views_as_tables : will allow you to export the specified schema views as tables.

Logtime: will provide timestamp for each operation being performed while pump operations is running (in my opinion: very useful).

Export datapump command using both parameters example:

nohup $ORACLE_HOME/bin/expdp “‘/as sysdba ‘”  DIRECTORY=PUMP_DIR DUMPFILE=test_view_as_table.dmp  views_as_tables=SM_DEV.ANAL_COMP_VIEW logfile=export_table_as_view.log logtime=all &

***** If you face the below error while performing expdp with “logtime” parameter:

UDE-31623: operation generated ORACLE error 31623

ORA-31623: a job is not attached to this session via the specified handle

ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3905

ORA-06512: at “SYS.DBMS_DATAPUMP”, line 5203

ORA-06512: at line 1

***** To resolve this problem I have checked the following:

1.I have checked that the streams parameter and it was beyond 40M which is OK.

show parameter streams

  1. Then, I have checked that all my database components are valid using the query:

select * from dba_registry;

3. The final resolution was to unset NLS_LANG environment variable.

unset nls_lang

Which solved my error problems!!!

To return back to our initial thread, When you check the content of the log file the time stamps are included which is useful to check the process that is consuming more time.

here is an example of export logfile with time stamp dates added:

export logtime date stamp

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;

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.

—————-