Principal Performance Engineering, Database, SQL, PLSQL and Fusion Middleware Learnings

15 years 3 months + of experience in database administration, performance engineering and software cost optimization. Expert in architecture of large scale product, service features in product developments and several POCs executions. Specialty in Database administration, Database Performance, SQL, PLSQL, Shell, Simple solutions for Designing Performance engineering solutions, actively working on Software Cost Optimization in CLOUD. Always ready for new challenges with simple solutions

Sunday, December 20, 2020

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

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

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> @?/rdbms/admin/utl32k.sql

Session altered.

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>#

no rows selected

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>#

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.

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.

0 rows created.

no rows selected

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>#

PL/SQL procedure successfully completed.

Commit complete.

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$ 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

Enter password:

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

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.

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>#

no rows selected

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>#

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.

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.

0 rows created.

no rows selected

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>#

PL/SQL procedure successfully completed.

Commit complete.

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.

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>#

no rows selected

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>#

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.

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.

0 rows created.

no rows selected

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>#

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Session altered.

SQL> alter session set container=MC1F6791;

Session altered.

SQL> @?/rdbms/admin/utl32k

Session altered.

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>#

no rows selected

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>#

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.

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.

0 rows created.

no rows selected

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>#

PL/SQL procedure successfully completed.

Commit complete.

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

----------

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>

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.

Thursday, December 3, 2020

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;

Principal Performance Engineering, Database, SQL, PLSQL and Fusion Middleware Learnings

Sunday, December 20, 2020

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

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

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

Thursday, December 3, 2020

Oracle Database schema tables rows count

Sites I often visit...

Popular Posts

Blog Archive

Recent Posts

Visits

Labels

Featured Post

BACKUP AND RESTORE DATABASE WITH RMAN IN ORACLE