How to change max_string_size in oracle RAC container database and pluggable databases

 

Change max_string_size in oracle RAC container database and pluggable databases:

Steps: 

1: create a pfile from spfile.

2. set max_string_size=EXTENDED 

3.  set cluster_database=FALSE 

4. startup upgrade

5. @?/rdbms/admin/utl32k.sql - run it in CDB.

6. set cluster_database=TRUE

7. Restart CDB with srvctl commands and ensure all the instances are up and running.

8. Now we are done with the root container.

9. run utl32.sql in each PDB exactly the way we ran in CDB. please note PDB has to be closed and need to open in upgrade mode. 

10. Steps are given below, see the commands and their output carefully.




bash-4.2$ sqlplus "/ as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 20 20:09:13 2020

Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL> 

SQL> show parameter string_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_string_size                      string      STANDARD

SQL> alter system set max_string_size=EXTENDED scope=spfile sid='*';

System altered.

SQL> exit

srvctl stop database -d PRODEXA1 -o immediate




bash-4.2$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 20 20:18:41 2020

Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Enter password:

Connected to an idle instance.

SQL>  startup nomount;

ORACLE instance started.

Total System Global Area 8.1068E+10 bytes

Fixed Size                 30385984 bytes

Variable Size            1.1006E+10 bytes

Database Buffers         6.9793E+10 bytes

Redo Buffers              238047232 bytes

SQL> SQL> SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

SQL> SQL>

SQL>

SQL> alter system set cluster_database=FALSE scope=spfile sid='*';

System altered.

SQL>

SQL>  shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>

startup upgrade

ORACLE instance started.

Total System Global Area 8.1068E+10 bytes

Fixed Size                 30385984 bytes

Variable Size            1.1006E+10 bytes

Database Buffers         6.9793E+10 bytes

Redo Buffers              238047232 bytes

Database mounted.

Database opened.

SQL> 

SQL> show parameter string_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_string_size                      string      EXTENDED

SQL>

SQL>

SQL> @?/rdbms/admin/utl32k.sql

Session altered.

Session altered.

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform a "SHUTDOWN ABORT"  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database does not have compatible >= 12.0.0

DOC>

DOC>   Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Session altered.

Table created.

Table created.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

--------------------------------------------------------------------------------

12/20/2020 20:21:27.778469000

PL/SQL procedure successfully completed.

No errors.

Session altered.

Session altered.

Session altered.

0 rows created.

no rows selected

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if we encountered an error while modifying a column to

DOC>   account for data type length change as a result of enabling or

DOC>   disabling 32k types.

DOC>

DOC>   Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered. 

Session altered.

 

SQL> alter system set cluster_database=TRUE scope=spfile sid='*';

System altered.

SQL>

SQL> shutdown immediate;

bash-4.2$  srvctl stop database -d PRODEXA1 -o immediate

PRCC-1016 : PRODEXA1 was already stopped

bash-4.2$   srvctl start  database -d PRODEXA1

bash-4.2$  srvctl status  database -d PRODEXA1

Instance PRODEXA11 is running on node host1.my.domain.com

Instance PRODEXA12 is running on node host2.my.domain.com

bash-4.2$

bash-4.2$

bash-4.2$

bash-4.2$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 20 20:23:29 2020

Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL>

SQL>

SQL> show parameter cluster_database

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cluster_database                     boolean     TRUE 

SQL> show parameter max_string_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------ 

max_string_size                      string      EXTENDED

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       MOUNTED

         3 EXA                            MOUNTED

         4 MC1F6791                       MOUNTED

SQL>

SQL> alter pluggable database pdb$seed open upgrade;

Pluggable database altered.

SQL> alter session set container=PDB$SEED;

Session altered.

SQL>  @?/rdbms/admin/utl32k

Session altered.

Session altered.

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform a "SHUTDOWN ABORT"  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database does not have compatible >= 12.0.0

DOC>

DOC>   Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Session altered.

Table created.

Table created.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

--------------------------------------------------------------------------------

12/20/2020 20:24:43.903946000

PL/SQL procedure successfully completed.

No errors.

Session altered.

Session altered.

Session altered.

0 rows created.

no rows selected

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if we encountered an error while modifying a column to

DOC>   account for data type length change as a result of enabling or

DOC>   disabling 32k types.

DOC>

DOC>   Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered.

Session altered.

SQL>  alter session set container=cdb$root;

Session altered.

SQL> sho pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       MIGRATE    YES

         3 EXA                            MOUNTED

         4 MC1F6791                       MOUNTED

SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open read only;

Pluggable database altered.

SQL> sho pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 EXA                            MOUNTED

         4 MC1F6791                       MOUNTED

SQL> alter pluggable database EXA,MC1F6791 open upgrade;

Pluggable database altered.

SQL>  alter session set container=EXA;

Session altered.

SQL> @?/rdbms/admin/utl32k

Session altered.

Session altered.

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform a "SHUTDOWN ABORT"  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database does not have compatible >= 12.0.0

DOC>

DOC>   Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Session altered.

Table created.

Table created.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

--------------------------------------------------------------------------------

12/20/2020 20:26:16.600081000

PL/SQL procedure successfully completed.

No errors.

Session altered.

Session altered.

Session altered.

0 rows created.

no rows selected

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if we encountered an error while modifying a column to

DOC>   account for data type length change as a result of enabling or

DOC>   disabling 32k types.

DOC>

DOC>   Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered.

Session altered.

SQL> alter session set container=MC1F6791;

Session altered.

SQL> @?/rdbms/admin/utl32k

Session altered.

Session altered.

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform a "SHUTDOWN ABORT"  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database does not have compatible >= 12.0.0

DOC>

DOC>   Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Session altered.

Table created.

Table created.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

--------------------------------------------------------------------------------

12/20/2020 20:26:41.482322000

PL/SQL procedure successfully completed.

No errors.

Session altered.

Session altered.

Session altered.

0 rows created.

no rows selected

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if we encountered an error while modifying a column to

DOC>   account for data type length change as a result of enabling or

DOC>   disabling 32k types.

DOC>

DOC>   Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered.

Session altered.

SQL> SQL> SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

bash-4.2$  srvctl stop database -d PRODEXA1 -o immediate

bash-4.2$   srvctl start  database -d PRODEXA1

bash-4.2$   srvctl status  database -d PRODEXA1

Instance PRODEXA11 is running on node host1.my.domain.com

Instance PRODEXA12 is running on node host2.my.domain.com

bash-4.2$

connect to CDB SQL> alter pluggable database all close immediate instances=all; Pluggable database altered. SQL> alter pluggable database all open instances=all; Pluggable database altered. SQL>

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 34 into a target database with TSTZ version 32. and ORA-30094: failed to find the time zone data file for version 34 in $ORACLE_HOME/oracore/zoneinfo

 Error:
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 34 into a target database with TSTZ version 32.

SQL> SELECT version FROM v$timezone_file;

   VERSION

----------

        32

conn sys as sysdba 

SQL> ALTER SESSION SET CONTAINER=mypdb;

ALTER PLUGGABLE DATABASE mypdb CLOSE IMMEDIATE INSTANCES=ALL;

Session altered.

SQL>

 

Pluggable database altered.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> STARTUP UPGRADE;

Pluggable Database opened.

SQL> SET SERVEROUTPUT ON

SQL> EXEC DBMS_DST.begin_upgrade(34);

BEGIN DBMS_DST.begin_upgrade(34); END;

*

ERROR at line 1:

ORA-30094: failed to find the time zone data file for version 34 in

$ORACLE_HOME/oracore/zoneinfo

ORA-06512: at "SYS.DBMS_DST", line 84

ORA-06512: at "SYS.DBMS_DST", line 1237

ORA-06512: at line 1

SQL>

SQL>

Solution: Patch, patch, patch …

Note: Ensure that 19c Patch Set Update (PSU) 31281355 is already applied on the Oracle Database.

1. download the  DSTv34 Patch 29997937 . 

2. Unzip   Patch 29997937

3. Apply  Patch 29997937 using Opatch. 

Commands to apply patch and verification:

 opatch version

 opatch lsinventory

  opatch prereq CheckConflictAgainstOHWithDetail -ph ./

  opatch apply

  opatch lspatches

UPGRADE 19c 2 node RAC cluster from 19.3 to 19.8 with July 2020 RU

Environment: 19.3 2 node RAC cluster.

Latest OPatch:                      : p6880880_190000_Linux-x86-64.zip  -- to get OPatch Version: 12.2.0.1.23

GI Release Update (July 2020) : p31305339_190000_Linux-x86-64.zip

Database Release Update (July 2020) : p31281355_190000_Linux-x86-64.zip

RU: Release Updates

followed exactly what is given in READ-ME files however below errors are seen.

  Following active executables are used by opatch process :

[Dec 15, 2020 2:44:55 AM] [INFO]    Prerequisite check "CheckActiveFilesAndExecutables" failed.

                                    The details are:

                                    Following active executables are not used by opatch process :

                                    //...//u01/app/19.3.0/grid/lib/libclntsh.so.19.1

                                    //...//u01/app/19.3.0/grid/lib/libasmclntsh19.so

                                    Following active executables are used by opatch process :

[Dec 15, 2020 2:44:55 AM] [SEVERE]  OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

[Dec 15, 2020 2:44:55 AM] [INFO]    Finishing UtilSession at Tue Dec 15 02:44:55 PST 2020

[Dec 15, 2020 2:44:55 AM] [INFO]    Log file location: //...//u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-12-15_02-41-24AM_1.log

bash-4.2$ /sbin/fuser //...//u01/app/19.3.0/grid/lib/libclntsh.so.19.1; /sbin/fuser //...//u01/app/19.3.0/grid/lib/libasmclntsh19.so

//...//u01/app/19.3.0/grid/lib/libclntsh.so.19.1: 13192m 13214m 13216m 13245m 13298m 13402m

//...//u01/app/19.3.0/grid/lib/libasmclntsh19.so: 13192m 13214m 13216m 13245m 13298m 13402m

bash-4.2$ ps -elf |egrep '13192 | 13214 | 13216 | 13245 | 13298 | 13402' |grep -iv grep                                                 4 S oracle   13192     1  0  80   0 - 421363 futex_ 01:28 ?       00:01:14 //...//u01/app/19.3.0/grid/bin/oraagent.bin

0 S oracle   13214     1  0  80   0 - 92685 poll_s 01:28 ?        00:00:43 //...//u01/app/19.3.0/grid/bin/mdnsd.bin

0 S oracle   13216     1  0  80   0 - 217186 poll_s 01:28 ?       00:01:39 //...//u01/app/19.3.0/grid/bin/evmd.bin

0 S oracle   13245     1  0  80   0 - 264129 hrtime 01:28 ?       00:00:48 //...//u01/app/19.3.0/grid/bin/gpnpd.bin

0 S oracle   13298 13216  0  80   0 - 111523 poll_s 01:28 ?       00:00:42 //...//u01/app/19.3.0/grid/bin/evmlogger.bin -o //...//u01/app/19.3.0/grid/log/[HOSTNAME]/evmd/evmlogger.info -l //...//u01/app/19.3.0/grid/log/[HOSTNAME]/evmd/evmlogger.log

4 S oracle   13402     1  0 -40   - - 674556 futex_ 01:28 ?       00:01:33 //...//u01/app/19.3.0/grid/bin/ocssd.bin

bash-4.2$

Any attempt to kill anyone of the above processes linked to said two lib* executable resulted two nodes get rebooted automatically even if we ran on single node... 

In my case: I followed 

Prerequisite check CheckActiveFilesAndExecutables failed on executable libclntsh.so.12.1 with opatch rollback (Doc ID 2391109.1)

and ensured all the cluster resources are relocated to another node on which patch is not being applied. 

thereafter I was able apply RU patch, first on Grid, and oracle home. on each of the nodes one after another.

 

Oracle Database schema tables rows count

 We do come across a requirement or want to know count of each and every table in a particular schema or group of schemas. 

what is simplest way of doing this?.  do we need query each and every table ?.. not required as tables are used for some other DMLs .. . we can create a job or run whenever we want to know the count of each and every table in a particular schema. The code given below can be modified to suit to user requirement. 

There are two ways, run code and wait for output or simply run a job that inserts a record in a table, query stats table to see the count . 

Result table can be truncated or measured to see how each table growth is in terms of rows count..  similar option can be applied to find schema size , perhaps once is a day or run once in a week and compare the database growth .. capacity planning.. etc.. many uses. .. 

 

 Create a table:

Create table STATS_TABLE

(TABLE_NAME VARCHAR2(100) not null,

SCHEMA_NAME VARCHAR2(100) not null,

RECORD_COUNT NUMBER(12),

CREATED DATE);

Option 1:

set serveroutput on;

DECLARE

    v_count        INTEGER;

    l_owner_name   VARCHAR2(30) := 'PRODADMIN';

    l_table_name   varchar2(100);

    l_sql_qry     VARCHAR2 (999);

BEGIN FOR r IN (

    SELECT

        table_name,

        owner

    FROM

        all_tables

    WHERE

        owner = l_owner_name

) LOOP 

l_table_name:=concat(concat(r.owner,'.'),r.table_name);

 l_sql_qry:= 'SELECT /*+ parallel */ count (*) FROM '||l_table_name;

    EXECUTE IMMEDIATE l_sql_qry INTO v_count;    

dbms_output.put_line(l_table_name ||' ' ||  v_count);

END LOOP; 

COMMIT;

END;

/

Option 2:

BEGIN

    dbms_scheduler.create_job('XXXXX_JOB_COUNT_TABLES_2', job_type => 'PLSQL_BLOCK', job_action => 'DECLARE     v_count        INTEGER;     l_owner_name   VARCHAR2(30) := ''PRODADMIN'';     l_table_name   varchar2(100);    l_sql_qry     VARCHAR2 (999);

BEGIN FOR r IN (    SELECT        table_name,        owner    FROM        all_tables    WHERE        owner = l_owner_name ) LOOP l_table_name:=concat(concat(r.owner,''.''),r.table_name);  l_sql_qry:= ''SELECT /*+ parallel */ count (*) FROM ''||l_table_name;   EXECUTE IMMEDIATE l_sql_qry INTO v_count;    INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED) VALUES (r.table_name,r.owner,v_count,SYSDATE);END LOOP;  COMMIT; END;'

    , number_of_arguments => 0, start_date => systimestamp,

                              repeat_interval => NULL, end_date => NULL, job_class => 'DEFAULT_JOB_CLASS', enabled => TRUE, auto_drop

                              => false,

                              comments => 'A Job that count table rows');

END;

/

ORA-22275: invalid LOB locator specified , do we really have LOB columns in table

 ORA-22275: invalid LOB locator specified , do we really have LOB columns in table:

I came across a customer problem that query returning ORA-22275: invalid LOB locator specified error  from application logs when they ran a query that has join conditions with just two tables . none of the tables in the query have LOB columns, it all number, char , varchar2 data type columns.

the interesting part is the same query get executed from sqlplus command prompt. sql developer and other tool but not from JDBC connection made applications. the query gave same error when running from OEM worksheet too.

 

SELECT

     ..

    cmtf.t_context,

    ..

FROM

    table1       cmt

    LEFT OUTER JOIN 

    table1_fld   cmtf ON cmt.per_name = cmtf.per_name

WHERE

    cmt.per_name = 'MAX RANO KING'

ORDER BY

    cmtf.seq_num,

    cmtf.LOC_NAME; 

there may some other methods to investigate but the method I followed is :

1. check table have lob columns, none of them have lob columns

2. t_context column in both tables has VARCHAR2(4000)

column that has VARCHAR2(4000) really creates the issue???  perhaps testing I did made me to believe is YES. read on How

I ran this query to see what session NLS parameter get set from calling environment.

SELECT DB.PARAMETER, DB.VALUE "DATABASE", I.VALUE "INSTANCE", S.VALUE "SESSION" FROM   NLS_DATABASE_PARAMETERS DB, NLS_INSTANCE_PARAMETERS I, NLS_SESSION_PARAMETERS S WHERE  DB.PARAMETER=I.PARAMETER(+) AND DB.PARAMETER=S.PARAMETER(+) and db.parameter ='NLS_LENGTH_SEMANTICS' ORDER BY DB.PARAMETER;

SQL command prompt, sql developer and other GUI tools NLS_LENGTH_SEMANTICS=BYTE and query get executed successfully whereas in OEM worksheet (perhaps the same case in application session too), the  NLS_LENGTH_SEMANTICS was set to CHAR.

I don't see any other problem and any other difference. 

Does it really matter if even data in that column not having length of size specified at column level?.

is it the inconsistency in storage semantics or NLS settings that we should address in application ?. 
is it because of session nls parameters not compatible with nls database parameters or instance nls parameters ? this seems to make to believe the case YES. 

however more to come on this as when I found more on this. 

any comments and inputs or help is welcome. thanks. 

Configuring Local Undo AND Configuring Shared Undo

Configuring Local Undo:

Shtudown the instance:

SQL> shutdown immediate;

SQL> startup upgrade;

SQL> show con_name

CON_NAME

-----------------------------

CDB$ROOT

Change the Undo mode to “Local Undo”:

SQL> alter database local undo on;

Reboot the instance:

SQL> shutdown immediate;

SQL> startup;

Verify that the Local Undo is now used:

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'

PROPERTY_NAME PROPERTY_VALUE

-------------------- ---------------

LOCAL_UNDO_ENABLED TRUE

Configuring Shared Undo

Shutdown the instance:

SQL> shutdown immediate;

SQL> startup upgrade;

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

Change the Undo mode to “Shared Undo”:

SQL> alter database local undo off;

Reboot the instance:

SQL> shutdown immediate;

SQL> startup;

Verify that the new Undo mode is now used:

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'

PROPERTY_NAME PROPERTY_VALUE

-------------------- ---------------

LOCAL_UNDO_ENABLED FALSE

How to export and import schema stats from one database to another database in oracle

How to import schema stats from one database to another database in oracle:

It is not just importing schema stats from one database to another database, the one of the problem I have come across is export delay or import delay when export or importing whole schema stats with including statistics. 

I found this is useful when making my export and imports faster however the solution is FYI only, you can test and see it yourself.

The steps in exporting and importing schema stats  in summary:

Create the STATS table.

Export the statistics to the STATS table.

Export the STATS table using export(exp) or datapump(expdp)

Transfer the dump file to the destination database.

Import the STATS table to the destination database.

In detail:

Please note to generate stats on the schema as you see fit prior to export and import just in case.

you may use code like this or can be added to more options.. 

begin

    dbms_stats.gather_schema_stats('MYSCHEMA_DEV');

end;

/

1. Create the STATS table.

begin

    dbms_stats.create_stat_table( ownname => 'MYSCHEMA_DEV', stattab => 'exp_stats');

end;

/

2. Export the statistics to the STATS table.

begin

    dbms_stats.export_schema_stats( ownname => 'MYSCHEMA_DEV', stattab => 'exp_stats');

end;

/

3. Export the STATS table using export(exp) or datapump(expdp)

this can be along with schema export or only stats table can be exported

1. expdp command.

2. dbms_datapump API procedures. 

expdp SCHEMAS=MYSCHEMA_DEV DIRECTORY=dumps parallel=24 DUMPFILE=MYSCHEMA_DEV_%U.dmp LOGFILE=MYSCHEMA_DEV_exp.log COMPRESSION=ALL CLUSTER=N METRICS=Y LOGTIME=ALL  EXCLUDE=STATISTICS

4. Transfer the dump file to the destination database.

use sftp or winscp whatever you think is fit.

5. Import the STATS table to the destination database.

begin

    dbms_stats.import_schema_stats(ownname => 'MYSCHEMA_DEV', stattab => 'exp_stats');

end;

/

Hope this helps... please feel free to comment .. thanks. 

How to truncate reference partitioning table in oracle database

How to truncate reference partitioning table in oracle database

there is no option to truncate reference partitioning tables in oracle database unless FK tables delete rule is set CASCADE. it is tested in 12.2

how do we get rid of PK table while keeping FK table exists , It depends on the way the tables are got created.

Possible ways:

1. use delete
2. drop FK tables, truncate PK tables since FK delete rule is 'NO ACTION'
3. truncate parent table would be successful only when FK tables delete rule is set to CASCADE.
4. TRUNCATE table partition (required partitions ) with cascade option.

Here it is the demo when FK tables delete rule is NO ACTION

SQL> create table p_emp(
   empno      number  primary key,
   job        varchar2(20),
   sal        number(7,2),
   deptno     number(2)
   )
   partition by list(job)
   ( partition p_job_dba values ('BA'),
     partition p_job_mgr values ('GR'),
     partition p_job_vp  values ('VP')
   );

SQL>  
Table created.

 create table r_emp
 (
 ename      varchar2(10),
 emp_id     number  primary key,
 empno      not null,
 constraint fk_empno foreign key(empno)
    references p_emp(empno)
 )
 partition by reference (fk_empno)
 /


SQL>  

Table created.

SQL> truncate table p_emp cascade;
truncate table p_emp cascade
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL> delete from p_emp;

0 rows deleted.

SQL> truncate table r_emp;

Table truncated.

SQL>  truncate table p_emp cascade;
 truncate table p_emp cascade
                *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL>


Here it is the demo when FK tables delete rule is CASCADE





 SQL> create table p_emp(
    empno      number  primary key,
    job        varchar2(20),
    sal        number(7,2),
    deptno     number(2)
    )
    partition by list(job)
    ( partition p_job_dba values ('BA'),
      partition p_job_mgr values ('GR'),
      partition p_job_vp  values ('VP')
    );

Table created.



SQL>
 create table r_emp
 (
 ename      varchar2(10),
 emp_id     number  primary key,
 empno      not null,
 constraint fk_empno1 foreign key(empno)
    references p_emp(empno) on delete cascade
 )
 partition by reference (fk_empno1)
 /

SQL>   
Table created.

SQL> SQL>
SQL>  truncate table p_emp cascade;

Table truncated.

SQL>


 

How to check FK delete rule:

the below query help us to know the delete rule of FK tables FK constraints.

when delete rule is not set :

select table_name, delete_rule
from user_constraints
where table_name like 'TABLE_NAME_YOURS%'
and constraint_type = 'R';

TABLE_NAME                     DELETE_RULE
------------------------------ ---------
TABLE_NAME_YOURS_CHAR        NO ACTION
TABLE_NAME_YOURS_LOG         NO ACTION
TABLE_NAME_YOURS_LOG_P       NO ACTION


when delete rule is set

TABLE_NAME                     DELETE_RULE
------------------------------ ---------
TABLE_NAME_YOURS_CHAR          CASCADE
TABLE_NAME_YOURS_LOG           CASCADE
TABLE_NAME_YOURS_LOG_P         CASCADE

4.
SQL> ALTER TABLE xxxx TRUNCATE PARTITION P2019xxx      UPDATE INDEXES;

Table truncated.

SQL>

Errors in 19c rac cluster *root.sh script execution during GRID installation.

Errors in 19c rac cluster *root.sh script execution during GRID installation.

1. run(before gird install)  runcluvfy.sh stage -pre crsinst -n  node1, node2 and ensure output is endup with all r in PASSED state.

Error seen in log file:

  2020-02-13 20:12:33: Invoking "/u10/app/19.3.0/grid/bin/cluutil -ckpt -global -oraclebase /u10/app/oracle -chkckpt -name ROOTCRS_FIRSTNODE -status"

2020-02-13 20:12:33: trace file=/u10/app/oracle/crsdata/nodemachine_1/crsconfig/cluutil9.log

2020-02-13 20:12:33: Running as user oracle: /u10/app/19.3.0/grid/bin/cluutil -ckpt -global -oraclebase /u10/app/oracle -chkckpt -name ROOTCRS_FIRSTNODE -status

2020-02-13 20:12:33: Removing file /tmp/C3gYWRsAf7

2020-02-13 20:12:33: Successfully removed file: /tmp/C3gYWRsAf7

2020-02-13 20:12:33: pipe exit code: 0

2020-02-13 20:12:33: /bin/su successfully executed

2020-02-13 20:12:33: FAIL

2020-02-13 20:12:33: The 'ROOTCRS_FIRSTNODE' status is FAILED

2020-02-13 20:12:33: Global ckpt 'ROOTCRS_FIRSTNODE' state: FAIL

2020-02-13 20:12:33: First node operations have not been done, and local node is installer node.

2020-02-13 20:12:33: Local node: nodemachine_1 is the first node.

2020-02-13 20:12:33: ORACLE_BASE is shared: 0

2020-02-13 20:12:33: Invoking "/u10/app/19.3.0/grid/bin/cluutil -ckpt -global -oraclebase /u10/app/oracle -writeckpt -name ROOTCRS_FIRSTNODE -state FAIL -nodelist nodemachine_1,nodemachine_2 -transferfile"

2020-02-13 20:12:33: trace file=/u10/app/oracle/crsdata/nodemachine_1/crsconfig/cluutil10.log

2020-02-13 20:12:33: Running as user oracle: /u10/app/19.3.0/grid/bin/cluutil -ckpt -global -oraclebase /u10/app/oracle -writeckpt -name ROOTCRS_FIRSTNODE -state FAIL -nodelist nodemachine_1,nodemachine_2 -transferfile

2020-02-13 20:12:36: Removing file /tmp/PIG92egVCe

2020-02-13 20:12:36: Successfully removed file: /tmp/PIG92egVCe

2020-02-13 20:12:36: pipe exit code: 0

2020-02-13 20:12:36: /bin/su successfully executed

2020-02-13 20:12:36:

2020-02-13 20:12:36:  "-ckpt -global -oraclebase /u10/app/oracle -writeckpt -name ROOTCRS_FIRSTNODE -state FAIL -nodelist nodemachine_1,nodemachine_2 -transferfile" succeeded with status 0.

2020-02-13 20:12:36: succeeded to write global ckpt 'ROOTCRS_FIRSTNODE' with status 'FAIL'

[root@nodemachine_1 crsconfig]#

running command what root.sh attempting to do manually also gave same error..

Solution:

Issue with  IPMI Management Network configuration.. 

1. aborted the grid install  .

2. deinstalled the grid (whatever portion installed)

3. restarted with grid install without IPMI option.

and found grid install done correctly.. 

There will be another post on IPMI configuration.

what is IPMI?

For now :

IPMI provides a set of common interfaces to computer hardware and firmware that system administrators can use to monitor system health and manage the system. more details in coming blogs... 

oracle online document is always best 
https://docs.oracle.com/en/database/oracle/oracle-database/19/cwsol/enabling-intelligent-platform-management-interface-ipmi.html#GUID-96249852-936C-480C-A166-6926A654F014

How to connect database using JDBC connect string

How to connect database using JDBC connect string:

As these days, databases are on cloud and at times the only way seems to connect database using JDBC url format.

it is very simple:

 scan name:
 sqlplus 'user1/user1@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.mydomain.myurl)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PDB19C1)))'


these days scan is default option..

if there are people who want list each node name in connection string or some kind of load balancing IS ON is enabled and still want to connect the way we connect  as shown above. the answer is simple and it is below


sqlplus 'user1/user1@(DESCRIPTION = (LOAD_BALANCE = on) (ADDRESS = (PROTOCOL = TCP)(HOST = hostname1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = hostname2)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = hostname3)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = hostname4)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydatabase)))'

How to Calculate OS CPU Utilization From Oracle Database AWR report.

How to Calculate OS CPU Utilization From Oracle Database AWR report.:

please note: the calculation formulas given here are the formulas i got from various sources on reading AWRs (in process of learning AWR myself).. the credit goes to them who could find time to share their knowledge.. many authors ...many blogs on awr analysis.


TOTAL CPU= NUM_CPUS*ELAPSED_TIME*60
TOTAL CPU= 8*60*60 = 28800 seconds ( 8 is cpu core box, 60 is elaspsed time)

DB cpu : 405.14 seconds
DB time: 885.6  seconds

‭45% cpu usage of DB time,. TOTAL CPU available in the system is 28800 seconds. it means actual cpu usage is 1.4 % usage.
----------------

BUSY_TIME 65,579
IDLE_TIME 2,804,991

% BUSY TIME= {BUSY_TIME/ (BUSY_TIME+IDLE_TIME)}*100                           = {5788280/8365936}*100
select (65579/(65579+2804991))*100 from dual; 2.28452885663822864448524160706758588016

This means that the system was overall 2.3% was busy utilizing the CPU.

BUSY_TIME=SYS_TIME+USER_TIME

SYS_TIME :12,835
USER_TIME : 52,272
select 12835+52272  from dual; -- 65107
calculate % of SYS_TIME and % of USER_TIME.:
% SYS_TIME = (SYS_TIME/BUSY_TIME)*100 - select (12835/65107)*100 from dual; 19.8% system time usage.
%USER_TIME = (USER_TIME/BUSY_TIME)*100 - select (52272/65107)*100 from dual; 80.3%
this 80.3% is actually 80.3% of %BUSY_TIME ie 2.3%.  -- this user time..
this 19.8% is actually 19.8% of %BUSY_TIME ie 2.3%. -- this is sys time..

SYS$USERS service is the default service name used when a user session is established without explicitly identifying its service name.

Service Statistics:
---------------------------
Service Name DB Time (s) DB CPU (s) Physical Reads (K) Logical Reads (K)
SYS$USERS 581 396 86 22,706
db_name  304 9 57 369
SYS$BACKGROUND 0 0 4 4,298

Major time spent in SYS$USERS category however the cpu used by database is very low.. need to check further based on issues we want to investigate.

Assumptions: single database is running on hosts.

[INS-40724] No locally defined network interface matches the SCAN subnet.

Errors while installing oracle 19c cluster - grid software installation..

[INS-40724] No locally defined network interface matches the SCAN subnet.

Issue: none of the locally defined network interfaces has a subnet matching the SCAN subnet.

Fixes:
1.  error entry in /etc/hosts file on RAC nodes.
2. defined network interfaces has a subnet matching the SCAN subnet i.e. the Public IP and SCAN VIPs should be in same subnet, and public IP should be primary IP on a NIC.

Recommendations:
1. The Public IP and SCAN VIPs should be in same subnet, and public IP should be primary IP on a NIC.
2.priv and public network subnet have to be different also do configure ssh passwordless connection for user if not done already.


you may check https://docs.oracle.com/en/database/oracle/oracle-database/19/cwlin/network-checklist-for-oracle-grid-infrastructure.html for more info on NIC configuration for 19c oracle RAC.

All suggestions and inputs are welcome in this regard. Thanks.

How to check Oracle Flex cluster and Flex ASM

Oracle FLEX ASM: The number of ASM instances which will be running in the cluster based on cardinality defined during the cluster configuration.

Flex Cluster need to be enabled for flex ASM.

The default cardinality is 3, can be changed with srvctl modify asm commands

Clients can connect to remote ASM instance either shared network or separate dedicated asm network.

Useful commands:
 srvctl status asm -detail

run at asmcmd command prompt:
showclsutermode
showclusterstate




Oracle Flex Cluster:
Oracle Flex Cluster is a type of cluster with Hub and leaf nodes. Hub nodes do have direct access to storage and its files (OCR, Voting disks, disk group files etc.)
.leaf nodes do not have direct access to shared storages. all leaf nodes communicatoin go via hub nodes. Flex Cluster need Flex ASM configured prior.

Useful Commands:
crsctl get node role config
crsctl get node role config -all
olsnodes  -n -s -a
crsctl get cluster mode status

Plug Single Instance PDB from non ASM CDB to ASM RAC CDB with wallet configuration

Plug Single Instance PDB to RAC CDB

Objective : clone pdb from one cdb to another cdb. DB version : 12.2
our source is non RAC file system CDB, our target is ASM RAC CDB.

One may wonder why did we do unplug and plug in database when we have a simple way of doing pdb clone with database link. yes, doing pdb clone copy with database link is simple but we could not bring PDB across all 4 nodes of RAC, we could bring cloned pdb on only one instance, bring it on all the nodes gave UNDO Tablespace related issues. In RAC, each instances has their own UNDO, in non RAC db, there is only one undo, PDB clone gave issues to me hence I did via unplug and plug method. please past the steps or method of pdb clone from non-rac file system to ASM RAC CDB with database link if anyone has done it.thanks.



SQL> conn sys as sysdba
Enter password:


SQL> SQL>  alter session set container=EXP_IMP_PDB;

Session altered.

SQL> administer key management set keystore open identified by "password";

keystore altered.

SQL> administer key management  export encryption keys with secret "this is my secret password for the export" to '/tmp/mr/EXP_IMP_PDB.p12' identified by "password";

keystore altered.

SQL> conn sys as sysdba
Enter password:

Connected.
SQL> SQL> alter pluggable database EXP_IMP_PDB close immediate;

Pluggable database altered.

SQL>  ALTER PLUGGABLE DATABASE EXP_IMP_PDB UNPLUG INTO '/tmp/mr/EXP_IMP_PDB.xml';

Pluggable database altered.


Please Note:
Copy the files (XML +, wallet file, database files) to the target database host (you may use scp or zip or any other format to copy xml and all the datafiles of pluggable database that is being moved/cloned/made a copy from it)

-- move files to different RAC database host- move to any one of rac instance.

---- RAC CDB steps -- begin

create /tmp/mr/ folder , copy xml files and datafiles to this folder.

. oraenv
+ASM1

asmcmd
got to DATA, create folder structure to have the path

  +DATA/ORCL/88E8A407CFE57C1FE053A3A4580A3356/datafile

login into RAC CDB

  set serveroutput on;
 declare
  chk boolean := FALSE;
  begin
  chk :=DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>'/tmp/mr/EXP_IMP_PDB.xml',pdb_name=>'EXP_IMP_PDB');
  if chk then
  DBMS_OUTPUT.PUT_LINE('YES');
  else DBMS_OUTPUT.PUT_LINE('NO');
  end if;
  end;
  /


 create pluggable database EXP_IMP_PDB  using '/tmp/mr/EXP_IMP_PDB.xml' FILE_NAME_CONVERT=('/scratch/u01/app/oracle/oradata/ORCL/OMF/ORCL/','+DATA/ORCL/') copy;





alter pluggable database EXP_IMP_PDB open;



  SQL> alter pluggable database EXP_IMP_PDB open;

Warning: PDB altered with errors.

SQL>
SQL>
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='EXP_IMP_PDB';

NAME
--------------------------------------------------------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
EXP_IMP_PDB
Wallet Key Needed                                                ERROR
PDB needs to import keys from source.
PENDING

.....
9 rows selected.

SQL>  alter session set container=EXP_IMP_PDB;

Session altered.

SQL> administer key management set keystore open identified by "password";

keystore altered.

SQL> administer key management  import encryption keys with secret "this is my secret password for the export" from '/tmp/mr/EXP_IMP_PDB.p12' identified by "password"  with backup;


keystore altered.

SQL> SQL>


SQL> conn sys as sysdba
Enter password:


Connected.
SQL>  alter pluggable database EXP_IMP_PDB close immediate instances=all;

Pluggable database altered.

SQL>  alter pluggable database EXP_IMP_PDB open  instances=all;

Pluggable database altered.

---- RAC CDB steps -- end



--- plugin the pdb on same CDB in which unplug steps are done to make pdb same as what it was on same host ----------

  set serveroutput on;
 declare
  chk boolean := FALSE;
  begin
  chk :=DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>'/tmp/mr/EXP_IMP_PDB.xml',pdb_name=>'EXP_IMP_PDB');
  if chk then
  DBMS_OUTPUT.PUT_LINE('YES');
  else DBMS_OUTPUT.PUT_LINE('NO');
  end if;
  end;
  /


select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='EXP_IMP_PDB';


  drop pluggable database  EXP_IMP_PDB keep datafiles;





 Create pluggable database EXP_IMP_PDB using '/tmp/mr/EXP_IMP_PDB.xml' copy ;

 alter pluggable database EXP_IMP_PDB open;



  SQL> alter pluggable database EXP_IMP_PDB open;

Warning: PDB altered with errors.

SQL>
SQL>
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='EXP_IMP_PDB';

NAME
--------------------------------------------------------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
EXP_IMP_PDB
Wallet Key Needed                                                ERROR
PDB needs to import keys from source.
PENDING



SQL>  alter session set container=EXP_IMP_PDB;

Session altered.

SQL> administer key management set keystore open identified by "password";

keystore altered.

SQL> administer key management  import encryption keys with secret "this is my secret password for the export" from '/tmp/mr/EXP_IMP_PDB.p12' identified by "password"  with backup;


keystore altered.

SQL> SQL>


SQL> conn sys as sysdba
Enter password:


Connected.
SQL>  alter pluggable database EXP_IMP_PDB close immediate;

Pluggable database altered.

SQL>  alter pluggable database EXP_IMP_PDB open;

Pluggable database altered.