Saturday, 26 December 2020

Compile the invalid catalog and catproc in Container and PDB database

Doc ..

https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/administering-a-cdb-with-sql-plus.html#GUID-4A64CE26-DD34-4543-B08E-6B4C61A24BAB

Run catcon.pl to start utlrp.sql, and to recompile any remaining invalid objects.

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

--n parameter: is set to 1, so the script runs each PDB recompilation in sequence.

--e parameter: turns echo on.

--b parameter: Sets the log file base name. It is set to utlrp.

export PATH=$ORACLE_HOME/perl/bin:$PATH

for all the pdb!!

pwd

/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin

www

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp_catcon_32574.lst]

catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp*.log] files for output generated by scripts

catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp_*.lst] files for spool files, if any

catcon.pl: completed successfully

admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl --n 1 --e --b utlrp --d '''.''' utlrp.sql

catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp_catcon_5882.lst]

catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp*.log] files for output generated by scripts

catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp_*.lst] files for spool files, if any

catcon.pl: completed successfully

--incl_con 'PDB9' <<<< for only This pdbs

www

pwd

/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin

$ORACLE_HOME/perl/bin/perl catcon.pl --n 1 --e --incl_con 'PDB9' --b utlrp --d '''.''' utlrp.sql

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

output !!!

cat /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp0.log

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 26 13:13:50 2020

SQL> Connected.

SQL> 2

Session altered.

SQL> 2

Session altered.

SQL>

ALTER SYSTEM KILL SESSION '613,57640' force timeout 0 -- process 22678

SQL> SQL> 2

Session altered.

SQL> SQL>

SQL> 2

Session altered.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2

Session altered.

SQL>

NOW_CONNECTED_TO

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

==== Current Container = PDB9 Id = 3 ====

SQL>

NOW_CONNECTED_TO

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

==== Current Container = PDB9 Id = 3 ====

SQL> SQL> 2

CATCONSECTION

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

==== CATCON EXEC IN CONTAINERS ====

SQL>

BEGIN_RUNNING

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

==== @./utlrp.sql Container:PDB9 Id:3 20-12-26 01:13:50 Proc:0 ====

SQL>

BEGIN_RUNNING

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

==== @./utlrp.sql Container:PDB9 Id:3 20-12-26 01:13:50 Proc:0 ====

SQL> 2

Session altered.

SQL> 2

Session altered.

SQL> SQL> Rem

SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $

SQL> Rem

SQL> Rem utlrp.sql

SQL> Rem

SQL> Rem NAME

SQL> Rem utlrp.sql - Recompile invalid objects

SQL> Rem

SQL> Rem DESCRIPTION

SQL> Rem This script recompiles invalid objects in the database.

SQL> Rem

SQL> Rem When run as one of the last steps during upgrade or downgrade,

SQL> Rem this script will validate all remaining invalid objects. It will

SQL> Rem also run a component validation procedure for each component in

SQL> Rem the database. See the README notes for your current release and

SQL> Rem the Oracle Database Upgrade book for more information about

SQL> Rem using utlrp.sql

SQL> Rem

SQL> Rem Although invalid objects are automatically re-validated when used,

SQL> Rem it is useful to run this script after an upgrade or downgrade and

SQL> Rem after applying a patch. This minimizes latencies caused by

SQL> Rem on-demand recompilation. Oracle strongly recommends running this

SQL> Rem script after upgrades, downgrades and patches.

SQL> Rem

SQL> Rem NOTES

SQL> Rem * This script must be run using SQL*PLUS.

SQL> Rem * You must be connected AS SYSDBA to run this script.

SQL> Rem * There should be no other DDL on the database while running the

SQL> Rem script. Not following this recommendation may lead to deadlocks.

SQL> Rem

SQL> Rem MODIFIED (MM/DD/YY)

SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriate

SQL> Rem gviswana 06/12/03 - Switch default back to serial

SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning

SQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics

SQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc

SQL> Rem gviswana 06/25/02 - Add documentation

SQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial

SQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368

SQL> Rem rburns 11/12/01 - validate all components after compiles

SQL> Rem rburns 11/06/01 - fix invalid CATPROC call

SQL> Rem rburns 09/29/01 - use 9.2.0

SQL> Rem rburns 09/20/01 - add check for CATPROC valid

SQL> Rem rburns 07/06/01 - get version from instance view

SQL> Rem rburns 05/09/01 - fix for use with 8.1.x

SQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33

SQL> Rem skabraha 09/25/00 - validate is now a keyword

SQL> Rem kosinski 06/14/00 - Persistent parameters

SQL> Rem skabraha 06/05/00 - validate tables also

SQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.

SQL> Rem rshaikh 09/22/99 - quote name for recompile

SQL> Rem ncramesh 08/04/98 - change for sqlplus

SQL> Rem usundara 06/03/98 - merge from 8.0.5

SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).

SQL> Rem Mark Ramacher (mramache) was the original

SQL> Rem author of this script.

SQL> Rem

SQL>

SQL> Rem ===========================================================================

SQL> Rem BEGIN utlrp.sql

SQL> Rem ===========================================================================

SQL>

SQL> @@utlprp.sql 0

SQL> Rem

SQL> Rem NAME

SQL> Rem utlprp.sql - Recompile invalid objects in the database

SQL> Rem

SQL> Rem DESCRIPTION

SQL> Rem This script recompiles invalid objects in the database.

SQL> Rem

SQL> Rem This script is typically used to recompile invalid objects

SQL> Rem remaining at the end of a database upgrade or downgrade.

SQL> Rem

SQL> Rem Although invalid objects are automatically recompiled on demand,

SQL> Rem running this script ahead of time will reduce or eliminate

SQL> Rem latencies due to automatic recompilation.

SQL> Rem

SQL> Rem This script is a wrapper based on the UTL_RECOMP package.

SQL> Rem UTL_RECOMP provides a more general recompilation interface,

SQL> Rem including options to recompile objects in a single schema. Please

SQL> Rem see the documentation for package UTL_RECOMP for more details.

SQL> Rem

SQL> Rem INPUTS

SQL> Rem The degree of parallelism for recompilation can be controlled by

SQL> Rem providing a parameter to this script. If this parameter is 0 or

SQL> Rem NULL, UTL_RECOMP will automatically determine the appropriate

SQL> Rem level of parallelism based on Oracle parameters cpu_count and

SQL> Rem parallel_threads_per_cpu. If the parameter is 1, sequential

SQL> Rem recompilation is used. Please see the documentation for package

SQL> Rem UTL_RECOMP for more details.

SQL> Rem

SQL> Rem NOTES

SQL> Rem * You must be connected AS SYSDBA to run this script.

SQL> Rem * There should be no other DDL on the database while running the

SQL> Rem script. Not following this recommendation may lead to deadlocks.

SQL> Rem

SQL> Rem MODIFIED (MM/DD/YY)

SQL> Rem jmuller 12/09/14 - Fix bug 19728696 (sort of): clarify comments

SQL> Rem pyam 04/08/14 - 18478064: factor out to reenable_indexes.sql

SQL> Rem kquinn 11/01/11 - 13059165: amend 'OBJECTS WITH ERRORS' SQL

SQL> Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name

SQL> Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes

SQL> Rem not needed.

SQL> Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270

SQL> Rem cdilling 01/21/08 - add support for ORA-30552

SQL> Rem cdilling 08/27/07 - check disabled indexes only

SQL> Rem cdilling 05/22/07 - add support for ORA-38301

SQL> Rem cdilling 02/19/07 - 5530085 - renable invalid indexes

SQL> Rem rburns 03/17/05 - use dbms_registry_sys

SQL> Rem gviswana 02/07/05 - Post-compilation diagnostics

SQL> Rem gviswana 09/09/04 - Auto tuning and diagnosability

SQL> Rem rburns 09/20/04 - fix validate_components

SQL> Rem gviswana 12/09/03 - Move functional-index re-enable here

SQL> Rem gviswana 06/04/03 - gviswana_bug-2814808

SQL> Rem gviswana 05/28/03 - Created

SQL> Rem

SQL>

SQL> SET VERIFY OFF;

SQL>

SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2020-12-26 13:13:50

SQL>

SQL> DOC

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC> objects in the database. Recompilation time is proportional to the

DOC> number of invalid objects in the database, so this command may take

DOC> a long time to execute on a database with a large number of invalid

DOC> objects.

DOC>

DOC> Use the following queries to track recompilation progress:

DOC>

DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.

DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC> This script automatically chooses serial or parallel recompilation

DOC> based on the number of CPUs available (parameter cpu_count) multiplied

DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC> On RAC, this number is added across all RAC nodes.

DOC>

DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC> recompilation. Jobs are created without instance affinity so that they

DOC> can migrate across RAC nodes. Use the following queries to verify

DOC> whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

SQL>

SQL> DECLARE

2 threads pls_integer := &&1;

3 BEGIN

4 utl_recomp.recomp_parallel(threads);

5 END;

6 /

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END 2020-12-26 13:13:51

SQL>

SQL> Rem #(8264899): The code to Re-enable functional indexes, which used to exist

SQL> Rem here, is no longer needed.

SQL>

SQL> DOC

DOC> The following query reports the number of invalid objects.

DOC>

DOC> If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

SQL> select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status in (3,4,5,6);

OBJECTS WITH ERRORS

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

SQL>

SQL> DOC

DOC> The following query reports the number of exceptions caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC> Note: Typical compilation errors (due to coding errors) are not

DOC> logged into this table: they go into DBA_ERRORS instead.

DOC>#

SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;

ERRORS DURING RECOMPILATION

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

SQL>

SQL> Rem =====================================================================

SQL> Rem Reenable indexes that may have been disabled, based on the

SQL> Rem table SYS.ENABLED$INDEXES

SQL> Rem =====================================================================

SQL>

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

SQL> Rem

SQL> Rem $Header: rdbms/admin/reenable_indexes.sql /main/3 2015/02/04 13:57:27 sylin Exp $

SQL> Rem

SQL> Rem reenable_indexes.sql

SQL> Rem

SQL> Rem NAME

SQL> Rem reenable_indexes.sql - <one-line expansion of the name>

SQL> Rem

SQL> Rem DESCRIPTION

SQL> Rem <short description of component this file declares/defines>

SQL> Rem

SQL> Rem NOTES

SQL> Rem <other useful comments, qualifications, etc.>

SQL> Rem

SQL> Rem BEGIN SQL_FILE_METADATA

SQL> Rem SQL_SOURCE_FILE: rdbms/admin/reenable_indexes.sql

SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/reenable_indexes.sql

SQL> Rem SQL_PHASE: REENABLE_INDEXES

SQL> Rem SQL_STARTUP_MODE: NORMAL

SQL> Rem SQL_IGNORABLE_ERRORS: NONE

SQL> Rem SQL_CALLING_FILE: rdbms/admin/noncdb_to_pdb.sql

SQL> Rem END SQL_FILE_METADATA

SQL> Rem

SQL> Rem MODIFIED (MM/DD/YY)

SQL> Rem sylin 01/30/15 - bug20422151 - longer identifier

SQL> Rem surman 01/08/15 - 19475031: Update SQL metadata

SQL> Rem pyam 04/03/14 - Reenable indexes based on sys.enabled$indexes

SQL> Rem (formerly in utlprp.sql)

SQL> Rem pyam 04/03/14 - Created

SQL> Rem

SQL>

SQL> Rem

SQL> Rem Declare function local_enquote_name to pass FALSE

SQL> Rem into underlying dbms_assert.enquote_name function

SQL> Rem

SQL> CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2)

2 return varchar2 is

3 begin

4 return dbms_assert.enquote_name(str, FALSE);

5 end local_enquote_name;

6 /

Function created.

SQL> Rem

SQL> Rem If sys.enabled$index table exists, then re-enable

SQL> Rem list of functional indexes that were enabled prior to upgrade

SQL> Rem The table sys.enabled$index table is created in catupstr.sql

SQL> Rem

SQL> SET serveroutput on

SQL> DECLARE

2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

3 commands tab_char;

4 p_null CHAR(1);

5 p_schemaname dbms_id;

6 p_indexname dbms_id;

7 rebuild_idx_msg BOOLEAN := FALSE;

8 non_existent_index exception;

9 recycle_bin_objs exception;

10 cannot_change_obj exception;

11 no_such_table exception;

12 pragma exception_init(non_existent_index, -1418);

13 pragma exception_init(recycle_bin_objs, -38301);

14 pragma exception_init(cannot_change_obj, -30552);

15 pragma exception_init(no_such_table, -942);

16 type cursor_t IS REF CURSOR;

17 reg_cursor cursor_t;

19 BEGIN

20 -- Check for existence of the table marking disabled functional indices

22 SELECT NULL INTO p_null FROM DBA_OBJECTS

23 WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and

24 object_type = 'TABLE' and rownum <=1;

26 -- Select indices to be re-enabled

27 EXECUTE IMMEDIATE q'+

28 SELECT 'ALTER INDEX ' ||

29 local_enquote_name(e.schemaname) || '.' ||

30 local_enquote_name(e.indexname) || ' ENABLE'

31 FROM enabled$indexes e, ind$ i

32 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND

33 bitand(i.property, 16) != 0+'

34 BULK COLLECT INTO commands;

36 IF (commands.count() > 0) THEN

37 FOR i IN 1 .. commands.count() LOOP

38 BEGIN

39 EXECUTE IMMEDIATE commands(i);

40 EXCEPTION

41 WHEN NON_EXISTENT_INDEX THEN NULL;

42 WHEN RECYCLE_BIN_OBJS THEN NULL;

43 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;

44 END;

45 END LOOP;

46 END IF;

48 -- Output any indexes in the table that could not be re-enabled

49 -- due to ORA-30552 during ALTER INDEX...ENBLE command

51 IF rebuild_idx_msg THEN

52 BEGIN

53 DBMS_OUTPUT.PUT_LINE

54 ('The following indexes could not be re-enabled and may need to be rebuilt:');

56 OPEN reg_cursor FOR

57 'SELECT e.schemaname, e.indexname

58 FROM enabled$indexes e, ind$ i

59 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';

61 LOOP

62 FETCH reg_cursor INTO p_schemaname, p_indexname;

63 EXIT WHEN reg_cursor%NOTFOUND;

64 DBMS_OUTPUT.PUT_LINE

65 ('.... INDEX ' || p_schemaname || '.' || p_indexname);

66 END LOOP;

67 CLOSE reg_cursor;

69 EXCEPTION

70 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;

71 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;

72 WHEN OTHERS THEN CLOSE reg_cursor; raise;

73 END;

75 END IF;

77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';

79 EXCEPTION

80 WHEN NO_DATA_FOUND THEN NULL;

82 END;

83 /

PL/SQL procedure successfully completed.

SQL>

SQL> DROP function local_enquote_name;

Function dropped.

SQL> SET serveroutput off

SQL>

SQL> Rem =====================================================================

SQL> Rem Run component validation procedure

SQL> Rem =====================================================================

SQL>

SQL> SET serveroutput on

SQL> EXECUTE dbms_registry_sys.validate_components;

PL/SQL procedure successfully completed.

SQL> SET serveroutput off

SQL>

SQL> Rem ===========================================================================

SQL> Rem END utlrp.sql

SQL> Rem ===========================================================================

SQL>

END_RUNNING

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

==== @./utlrp.sql Container:PDB9 Id:3 20-12-26 01:13:53 Proc:0 ====

SQL>

END_RUNNING

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

==== @./utlrp.sql Container:PDB9 Id:3 20-12-26 01:13:53 Proc:0 ====

SQL> SQL>

SQL> 2

Session altered.

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

-d utlrp.sql <<<sql path

-b file_name in this case tmp1

-l /tmp dir

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d $ORACLE_HOME/rdbms/admin -l /tmp -b tmp1 utlrp.sql

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/tmp1_catcon_4317.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/tmp1*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/tmp1_*.lst] files for spool files, if any

catcon.pl: completed successfully

check log file ...

grep "Current Container =" /tmp/tmp1*.log

/tmp/tmp10.log:==== Current Container = CDB$ROOT Id = 1 ====

/tmp/tmp10.log:==== Current Container = PDB$SEED Id = 2 ====

/tmp/tmp10.log:==== Current Container = VIHAAN Id = 5 ====

/tmp/tmp11.log:==== Current Container = ANUJ Id = 3 ====

/tmp/tmp11.log:==== Current Container = PDB2 Id = 4 ====

/tmp/tmp11.log:==== Current Container = VIHAAN2 Id = 6 ====

=========

To run below sql to check invalid objects in all the pdbs

--- invalid.sql

set linesize 300 pagesize 300

col name for a10

col owner for a20

col object_type for a20

select t.con_id,p.name,t.owner,t.object_type,t.status,count(*) from cdb_objects t ,v$containers p

where p.con_id = t.con_id

and t.status = 'INVALID'

group by t.con_id,p.name,t.owner,t.object_type,t.status

order by 3;

-b file_name <<<<< invalid.sql

-l /tmp dir for log file

-d dir name for this file >>> invalid.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d /home/oracle -l /tmp -b invalid invalid.sql

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

Script Name

cat tablespace.sql

===

cat who.sql

alter session set nls_date_format='dd-mm-YYYY hh24:mi';

set linesize 300 pagesize 0 serveroutput on

column "db details" format a300

select

' Sysdate:-' || (select sysdate from dual) ||

' \SERVER_HOST:-' ||SYS_CONTEXT('USERENV', 'SERVER_HOST') ||

' \DB_UNIQUE_NAME:-' ||SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME') ||

' \INSTANCE_NAME:-' ||SYS_CONTEXT('USERENV', 'INSTANCE_NAME') ||

--' \SERVICE_NAME:-' ||SYS_CONTEXT('USERENV', 'SERVICE_NAME') ||

' \PLATFORM_NAME:-' ||(select platform_name from v$database) ||

' \INSTANCE NUMBER:-' ||INSTANCE_NUMBER ||

' \STARTUP_TIME:-' || STARTUP_TIME ||

' \STATUS:-' || STATUS ||

'\ CONTAINER NAME:- ' ||sys_context('userenv','con_name') ||

' \DATABASE ROLE:- ' ||sys_context('userenv','database_role') ||

' \CLIENT IP ADDRESS: ' ||sys_context('userenv','ip_address') ||

'\OS USER: ' ||sys_context('userenv','os_user') ||

SYS_CONTEXT('USERENV', 'DB_DOMAIN') "db details"

from gv$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME');

set pagesize 100

====

@/home/oracle/who.sql

set linesize 300

col NAME for a20

SELECT LTRIM (d.tablespace_name) as name,

NVL (ddf.BYTES - NVL(u.BYTES, 0), 0) as freeSp,

DECODE (d.CONTENTS, 'UNDO', NVL (TRUNC ((ddf.BYTES - NVL (u.BYTES, 0))/(ddf.bytes)*100,3),0), NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100,3), 0)) as avPct,

NVL (dfs.antall, 0) as chunks,

ddf.autoextend_flag as autoext,

NVL (TRUNC ((ddf.bytes)), 0) as maxSize,

NVL (TRUNC ( (ddf.BYTES - NVL (dfs.BYTES, 0))/(ddf.bytes)*100,3),0) as maxPct

FROM dba_tablespaces d,

(SELECT tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) maxbytes, COUNT (1) antall FROM dba_free_space GROUP BY tablespace_name) dfs,

(SELECT tablespace_name, SUM (BYTES) BYTES, SUM (maxbytes) maxbytes, COUNT (1) antall, DECODE (MAX (autoextensible),'YES', 'Y','N') autoextend_flag

FROM dba_data_files GROUP BY tablespace_name) ddf,

(SELECT tablespace_name, SUM (BYTES) BYTES FROM dba_undo_extents WHERE status <> ('EXPIRED') GROUP BY tablespace_name) u

WHERE d.tablespace_name = ddf.tablespace_name(+)

AND d.tablespace_name = dfs.tablespace_name(+)

AND d.tablespace_name = u.tablespace_name(+)

AND NOT (d.extent_management LIKE 'LOCAL'

AND d.CONTENTS LIKE 'TEMPORARY')

UNION ALL

SELECT LTRIM (d.tablespace_name) as name,

NVL (TRUNC (ddf.BYTES), 0) - NVL (TRUNC (dfs.BYTES), 0) as freeSp,

100 - NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100), 0) as avPct,

DECODE (NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100), 0),0, 1,100, 0) as chunks,

ddf.autoextend_flag as autoext,

NVL (TRUNC ((ddf.bytes)), 0) maxSize,

NVL (TRUNC (NVL (dfs.BYTES, 0) / (ddf.bytes)* 100,3),0) as maxPct

FROM dba_tablespaces d,

(SELECT tablespace_name, SUM (BYTES) BYTES, SUM (maxbytes) maxbytes, COUNT (1) antall, DECODE (MAX (autoextensible), 'YES', 'Y','N') autoextend_flag

FROM dba_temp_files GROUP BY tablespace_name) ddf,

(SELECT ss.tablespace_name, SUM ((ss.used_blocks * ts.BLOCKSIZE)) BYTES, MAX ((ss.used_blocks * ts.BLOCKSIZE)) maxbytes, COUNT (1) antall

FROM gv$sort_segment ss, SYS.ts$ ts WHERE ss.tablespace_name = ts.NAME GROUP BY ss.tablespace_name) dfs

WHERE d.tablespace_name = ddf.tablespace_name(+)

AND d.tablespace_name = dfs.tablespace_name(+)

AND d.extent_management LIKE 'LOCAL'

AND d.CONTENTS LIKE 'TEMPORARY'

ORDER BY 1

;

========

cd to this dir

[oracle@localhost admin]$ pwd

/u01/app/oracle/product/version/db_1/rdbms/admin

[oracle@localhost admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d /home/oracle -l /home/oracle -S -c 'ORCL' -b tablespace tablespace.sql

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/tablespace_catcon_9130.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/tablespace*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/tablespace_*.lst] files for spool files, if any

catcon.pl: completed successfully

[oracle@localhost admin]$

cat tablespace0.log <<<<< output file

Sysdate:-31-12-2022 09:11 \SERVER_HOST:-localhost \DB_UNIQUE_NAME:-orclcdb \INSTANCE_NAME:-orclcdb \PLATFORM_NAME:-Linux x86 64-bit \INSTANCE NUMBER:-1 \STARTUP_TIME:-31-12-2022 05:31 \STATUS:-OPEN\ CONTAINER NAME:- ORCL \DATABASE ROLE:- PRIMARY \CLIENT IP ADDRESS: \OS USER: oracle

NAME FREESP AVPCT CHUNKS A MAXSIZE MAXPCT

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

APEX_129159770360740 2686976 60.975 1 Y 2686976 39.024

SYSAUX 1258291200 5.072 3 Y 1258291200 94.927

SYSTEM 429916160 .853 2 Y 429916160 99.146

TEMP 37748736 100 1 Y 37748736 0

UNDOTBS2 38207488 72.875 21 Y 52428800 62.25

USERS 420741120 5.7 2 Y 420741120 94.299

6 rows selected.

SQL>

Upgrading The Time Zone File And Timestamp With Time Zone Data In A 12.2 Oracle Database

time zone upgarde ..

Time Zone upgrade performed through DBUA takes more time in databases that have large amount of data impacted by new TZ files (Doc ID 2259734.1)

Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)

on new home

$ find /u01ora/app/oracle/product/12.2 -name readme.txt -print

head -10 $ORACLE_HOME/oracore/zoneinfo/readme.txt

head -10 /u01ora/app/oracle/product/12.2/db_1/oracore/zoneinfo/readme.txt

Current Structure version: 3

Current Content Version :26

Content Version 26 <<<< our file version

status of the database

set pagesize 100 linesize 132

col PROPERTY_NAME format a30

col value format a20

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

PROPERTY_NAME VALUE

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

DST_PRIMARY_TT_VERSION 18 <<<<, current version is 18

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE <<< check this

******** Do not continue if the aove output is different*

select * from v$timezone_file;

FILENAME VERSION CON_ID

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

timezlrg_14.dat 18 0

Start a prepare window!!!!!!!!!!!!!!

alter session set "_with_subquery"=materialize;

alter session set "_simple_view_merging"=TRUE;

purge dba_recyclebin;

Session altered.

SQL>

Session altered.

SQL>

DBA Recyclebin purged.

SQL> set serveroutput on

SQL> exec DBMS_DST.BEGIN_PREPARE(26);

A prepare window has been successfully started.

PL/SQL procedure successfully completed.

set pagesize 100 linesize 132

col PROPERTY_NAME format a30

col value format a20

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

PROPERTY_NAME VALUE

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

DST_PRIMARY_TT_VERSION 18

DST_SECONDARY_TT_VERSION 26

DST_UPGRADE_STATE PREPARE

SQL> exec DBMS_DST.FIND_AFFECTED_TABLES;

PL/SQL procedure successfully completed.

SQL> select count(*) from sys.dst$affected_tables;

COUNT(*)

----------

SQL> select count(*) from sys.dst$error_table;

COUNT(*)

----------

if error fix first

fixed by looking at the documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/datetime-data-types-and-time-zone-support.html#GUID-6377A1C4-F76A-4C53-82CD-BF4AE6148D6D

end the prepare window

SQL> exec DBMS_DST.END_PREPARE;

A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

set pagesize 100 linesize 132

col PROPERTY_NAME format a30

col value format a20

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

PROPERTY_NAME VALUE

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

DST_PRIMARY_TT_VERSION 18

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE

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

Start an upgrade window:

SQL> shutdown immediate; !!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade; !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 838860800 bytes

Fixed Size 8798312 bytes

Variable Size 620760984 bytes

Database Buffers 205520896 bytes

Redo Buffers 3780608 bytes

Database mounted.

Database opened.

SQL> set serveroutput on

SQL> exec DBMS_DST.BEGIN_UPGRADE(26);

An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

set pagesize 100 linesize 132

col PROPERTY_NAME format a30

col value format a20

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;SQL> SQL> SQL> SQL> 2 3

PROPERTY_NAME VALUE

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

DST_PRIMARY_TT_VERSION 26

DST_SECONDARY_TT_VERSION 18

DST_UPGRADE_STATE UPGRADE <<<<<<

for info ...

col OWNER format a30

col TABLE_NAME format a30

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES;

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 838860800 bytes

Fixed Size 8798312 bytes

Variable Size 620760984 bytes

Database Buffers 205520896 bytes

Redo Buffers 3780608 bytes

Database mounted.

Database opened.

SQL> truncate table sys.dst$error_table;

Table truncated.

SQL> truncate table sys.dst$trigger_table;

Table truncated.

upgrade the TSTZ data in all user tables by invoking DBMS_DST.UPGRADE_DATABASE!!!!!!!!!!!

alter session set "_with_subquery"=materialize;

alter session set "_simple_view_merging"=TRUE;

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 => TRUE,

error_on_nonexisting_time => TRUE);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

END;

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"

Number of failures: 0

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"

Number of failures: 0

Table list: "DVSYS"."AUDIT_TRAIL$"

Number of failures: 0

Table list: "DVSYS"."SIMULATION_LOG$"

Number of failures: 0

Failures:0

PL/SQL procedure successfully completed.

SELECT * FROM sys.dst$error_table;

no rows selected

Finally, end the prepare window:

SQL>

BEGIN

DBMS_DST.END_UPGRADE(:numfail);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

END;

BEGIN

DBMS_DST.END_UPGRADE(:numfail);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

END;

/SQL> 2 3 4 5

An upgrade window has been successfully ended.

Failures:0

PL/SQL procedure successfully completed.

SQL> select * from v$timezone_file;

FILENAME VERSION CON_ID

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

timezlrg_26.dat 26 0

set pagesize 100 linesize 132

col PROPERTY_NAME format a30

col value format a20

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;SQL> SQL> SQL> SQL> SQL> 2 3

PROPERTY_NAME VALUE

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

DST_PRIMARY_TT_VERSION 26

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.

SQL> commit ;

Commit complete.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

26 <<<<<<<<<<<<Done !!!!!!!!!!!

Saturday, 12 December 2020

How to migrate an existing pre-12c database (non-CDB) to 12c CDB database?

How to migrate an existing pre-12c database (non-CDB) to 12c CDB database? ..

REFERENCES

NOTE:1935365.1 - Multitenant Unplug/Plug Best Practices

NOTE:1564657.1 - How to migrate an existing pre-12c database (non-CDB) to 12c CDB database?

Aim to convert tnoncdb database to tcdb (CDB)

1.Prepare the non-CDB environment for conversion.

SQL> def

DEFINE _DATE = "12-DEC-20" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "tnoncdb1" (CHAR) <<<< on

DEFINE _USER = "SYS" (CHAR)

SQL>

BEGIN

DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/tnoncdb.xml');

END;

SQL> SQL> 2 3 4

PL/SQL procedure successfully completed.

Check all datafile

______________________________________________________________________________________________________________________

12 Dec 2020 08:15:08

File Report (all physical files)

Data File Report (all physical files) irac01-tnoncdb

______________________________________________________________________________________________________________________

Tablespace Name / File Class Filename File Size MB Auto Next Max MB

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

SYSAUX +DATA/TNONCDB/DATAFILE/sysaux.684.1058938847 670 YES 10 32,768

SYSTEM +DATA/TNONCDB/DATAFILE/system.797.1058938803 820 YES 10 32,768

TEMP +DATA/TNONCDB/TEMPFILE/temp.633.1058939071 43 YES 1 32,768

UNDOTBS1 +DATA/TNONCDB/DATAFILE/undotbs1.486.1058938873 285 YES 5 32,768

UNDOTBS2 +DATA/TNONCDB/DATAFILE/undotbs2.359.1058939103 50 YES 25 32,768

USERS +DATA/TNONCDB/DATAFILE/users.386.1058938875 5 YES 1 32,768

[ BLOCK TRACKING]DISABLED

[ CONTROL FILE ] +DATA/TNONCDB/CONTROLFILE/current.784.1058939063 10

[ CONTROL FILE ] +DATA/TNONCDB/CONTROLFILE/current.889.1058939063 10

[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_1.630.1058939065 200 200

[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_1.672.1058939065 200 200

[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_2.362.1058939065 200 200

[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_2.366.1058939065 200 200

[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_3.586.1058939371 200 200

[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_3.636.1058939371 200 200

[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_4.646.1058939371 200 200

[ ONLINE REDO LOG ] +DATA/TNONCDB/ONLINELOG/group_4.653.1058939371 200 200

[ Spfile or Pfile]SPFILE +DATA/TNONCDB/PARAMETERFILE/spfile.626.1058939373

18 rows selected.

Creating user for testing .. We will check this user on pdb database ...

SQL> show user

USER is "ANUJ"

SQL> create table test as select sysdate vdate from dual ;

Table created.

SQL> select * from test ;

SQL> def

DEFINE _DATE = "12-DEC-20" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "tnoncdb1" (CHAR) <<<<< now shutdown this database .

[oracle@irac01 tnoncdb]$ srvctl stop database -d tnoncdb

[oracle@irac01 tnoncdb]$ srvctl status database -d tnoncdb

Instance tnoncdb1 is not running on node irac01

Instance tnoncdb2 is not running on node irac02

=======================================================

now on CDB database

oracle@irac01 ~]$ . oraenv

ORACLE_SID = [tcdb1] ?

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0/dbhome_1

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ibrac01 ~]$ sqlplus / as sysdba

SQL> show con_name;

CON_NAME

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

CDB$ROOT

SQL>

SET SERVEROUTPUT ON

DECLARE

compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/tnoncdb.xml')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10

11 /

YES

PL/SQL procedure successfully completed.

set pagesize 300

col cause for a20

col name for a20

col message for a75 word_wrapped

select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS

2 /

NAME CAUSE TYPE MESSAGE STATUS

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

TNONCDB Non-CDB to PDB WARNING PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING

TNONCDB Parameter WARNING CDB parameter sga_max_size mismatch: Previous 2G Current 1664M PENDING

TNONCDB Parameter WARNING CDB parameter sga_target mismatch: Previous 2G Current 1664M PENDING

TNONCDB Parameter WARNING CDB parameter pga_aggregate_target mismatch: Previous 7222M Current 554M PENDING

CREATE PLUGGABLE DATABASE tnoncdb USING '/tmp/tnoncdb1.xml';

CREATE PLUGGABLE DATABASE tnoncdb USING '/tmp/tnoncdb1.xml';SQL> SQL>

CREATE PLUGGABLE DATABASE tnoncdb USING '/tmp/tnoncdb1.xml'

ERROR at line 1:

ORA-65139: Mismatch between XML metadata file and data file +DATA/TNONCDB/DATAFILE/system.797.1058938803 for value of fileblocks (103680 in the plug XML file, 104960 in the data file)

check following metalink note ... for above error

ORA-65139: Mismatch Between XML Metadata File And Data File. Database Not Open Read Only (Doc ID 1963139.1)

The following resolves the issue:

-- Set the ORACLE_SID variable to noncdb.

-- Connect to the noncdb instance.

sqlplus / as sysdba

-- If the instance is up, shut it down first.

shutdown immediate

-- Start up the database in mount exclusive mode.

startup mount exclusive

-- Open the database in read-only mode.

alter database open read only;

exec dbms_pdb.describe(pdb_descr_file=>'/opt/oracle/oradata/noncdb1/noncdb1.xml');

BEGIN

DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/tnoncdb1.xml');

END;

SET SERVEROUTPUT ON

DECLARE

compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/tnoncdb1.xml')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

SET SERVEROUTPUT ON

DECLARE

compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/tnoncdb1.xml')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;SQL> SQL> SQL> 2 3 4 5 6 7 8

9 /

YES

PL/SQL procedure successfully completed.

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS;

NAME CAUSE TYPE MESSAGE STATUS

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

TNONCDB Non-CDB to PDB WARNING PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING

TNONCDB Parameter WARNING CDB parameter sga_max_size mismatch: Previous 2G Current 1664M PENDING

TNONCDB Parameter WARNING CDB parameter sga_target mismatch: Previous 2G Current 1664M PENDING

TNONCDB Parameter WARNING CDB parameter pga_aggregate_target mismatch: Previous 7222M Current 554M PENDING

SQL>

CREATE PLUGGABLE DATABASE tnoncdb USING '/tmp/tnoncdb1.xml';

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS;

NAME CAUSE TYPE MESSAGE STATUS

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

TNONCDB Non-CDB to PDB WARNING PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING

TNONCDB Parameter WARNING CDB parameter sga_max_size mismatch: Previous 2G Current 1664M PENDING

TNONCDB Parameter WARNING CDB parameter sga_target mismatch: Previous 2G Current 1664M PENDING

TNONCDB Parameter WARNING CDB parameter pga_aggregate_target mismatch: Previous 7222M Current 554M PENDING

SQL>

CREATE PLUGGABLE DATABASE tnoncdb USING '/tmp/tnoncdb1.xml';

Pluggable database created.

use convert parameter on prod <<<<<<<

===================================================================

create pluggable database exnoncdb
as clone
using '/opt/oracle/oradata/noncdb/noncdb.xml'
file_name_convert=('/opt/oracle/oradata/noncdb','/stage/oradata/exnoncdb')
copy;

==================================================

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 VIHAAN READ WRITE NO

4 TNONCDB MOUNTED

SQL> alter pluggable database TNONCDB open instances=all ;

Warning: PDB altered with errors.

alter pluggable database TNONCDB close instances=all ;

SQL> SELECT NAME,STATUS,MESSAGE FROM PDB_PLUG_IN_VIOLATIONS;

NAME STATUS

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

MESSAGE

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

TNONCDB RESOLVED

CDB parameter sga_max_size mismatch: Previous 2G Current 1664M

TNONCDB RESOLVED

CDB parameter sga_target mismatch: Previous 2G Current 1664M

TNONCDB RESOLVED

CDB parameter pga_aggregate_target mismatch: Previous 7222M Current 554M

NAME STATUS

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

MESSAGE

TNONCDB PENDING

PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 VIHAAN READ WRITE NO

4 TNONCDB READ WRITE YES

SQL> alter session set CONTAINER=TNONCDB;

Session altered.

Imp !!!!!!!!!!!!!!!!

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

Session altered.

SQL> set trimout ON

SQL> set trimspool ON

SQL> set underline "-"

SQL> set verify OFF

SQL> set wrap ON

SQL> set xmloptimizationcheck OFF

SQL>

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

SQL>

set pages 2000

column message format a50

column status format a9

column type format a9

column con_id format 9

column name format a8

select con_id, name, type, message, status from PDB_PLUG_IN_VIOLATIONS where status<>'RESOLVED'

order by name,time;

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

4 TNONCDB MOUNTED

SQL> alter pluggable database TNONCDB open instances=all ;

Pluggable database altered.

SQL> SELECT NAME,STATUS,MESSAGE FROM PDB_PLUG_IN_VIOLATIONS;

NAME STATUS MESSAGE

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

TNONCDB RESOLVED PDB plugged in is a non-CDB, requires noncdb_to_pd

b.sql be run.

1 row selected.

Imp!!!

select * from dba_objects where status <> 'VALID'; no rows selected

SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi';

Session altered.

SQL> select * from anuj.test;

VDATE

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

12-12-2020 08:17

1 row selected.

Check all file !!!!!!

Tablespace Name / File Class Filename File Size MB Auto Next Max MB

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

SYSAUX +DATA/TCDB/DATAFILE/sysaux.334.1058941695 660 YES 10 32,768

SYSTEM +DATA/TCDB/DATAFILE/system.645.1058941653 810 YES 10 32,768

TEMP +DATA/TCDB/TEMPFILE/temp.641.1058941913 40 YES 1 32,768

TNONCDB.SYSAUX +DATA/TCDB/B6431750F11C2822E05322F56A40F4C0/DATAFILE/sysaux.338.1058950367 680 YES 10 32,768

TNONCDB.SYSTEM +DATA/TCDB/B6431750F11C2822E05322F56A40F4C0/DATAFILE/system.647.1058950367 820 YES 10 32,768

TNONCDB.UNDOTBS1 +DATA/TCDB/B6431750F11C2822E05322F56A40F4C0/DATAFILE/undotbs1.330.105895036 890 YES 5 32,768

TNONCDB.UNDOTBS2 +DATA/TCDB/B6431750F11C2822E05322F56A40F4C0/DATAFILE/undotbs2.349.105895036 50 YES 25 32,768

TNONCDB.USERS +DATA/TCDB/B6431750F11C2822E05322F56A40F4C0/DATAFILE/users.348.1058950367 5 YES 1 32,768

UNDOTBS1 +DATA/TCDB/DATAFILE/undotbs1.625.1058941721 305 YES 5 32,768

UNDOTBS2 +DATA/TCDB/DATAFILE/undotbs2.474.1058942129 25 YES 25 32,768

USERS +DATA/TCDB/DATAFILE/users.644.1058941721 5 YES 1 32,768

VIHAAN.SYSAUX +DATA/TCDB/B643F68F44676828E05322F56A40DA6B/DATAFILE/sysaux.639.1058942807 500 YES 10 32,768

VIHAAN.SYSTEM +DATA/TCDB/B643F68F44676828E05322F56A40DA6B/DATAFILE/system.623.1058942807 260 YES 10 32,768

VIHAAN.UNDOTBS1 +DATA/TCDB/B643F68F44676828E05322F56A40DA6B/DATAFILE/undotbs1.624.105894280 100 YES 5 32,768

VIHAAN.UNDO_2 +DATA/TCDB/B643F68F44676828E05322F56A40DA6B/DATAFILE/undo_2.649.1058942823 100 YES 5 32,768

VIHAAN.USERS +DATA/TCDB/B643F68F44676828E05322F56A40DA6B/DATAFILE/users.492.1058942823 5 YES 1 32,768

[ BLOCK TRACKING]DISABLED

[ CONTROL FILE ] +DATA/TCDB/CONTROLFILE/current.591.1058941905 19

[ ONLINE REDO LOG ] +DATA/TCDB/ONLINELOG/group_1.622.1058941909 200 200

[ ONLINE REDO LOG ] +DATA/TCDB/ONLINELOG/group_2.637.1058941909 200 200

[ ONLINE REDO LOG ] +DATA/TCDB/ONLINELOG/group_3.872.1058942559 200 200

[ ONLINE REDO LOG ] +DATA/TCDB/ONLINELOG/group_4.621.1058942559 200 200

[ Spfile or Pfile]SPFILE +DATA/TCDB/PARAMETERFILE/spfile.877.1058942559

23 rows selected.

set line 200
	set pages 1000
	col COMP_ID format a8
	col COMP_NAME format a34
	col SCHEMA format a12
	col STATUS format a10
	col VERSION format a12
	col CON_ID format 99

	select CON_ID, COMP_ID, comp_name, schema, status, version from CDB_REGISTRY order by 1,2;

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Saturday, 26 December 2020

Compile the invalid catalog and catproc in Container and PDB database

Compile the invalid catalog and catproc in Container and PDB database

Upgrading The Time Zone File And Timestamp With Time Zone Data In A 12.2 Oracle Database

Upgrading The Time Zone File And Timestamp With Time Zone Data In A 12.2 Oracle Database

Saturday, 12 December 2020

How to migrate an existing pre-12c database (non-CDB) to 12c CDB database?

How to migrate an existing pre-12c database (non-CDB) to 12c CDB database? ..

1.Prepare the non-CDB environment for conversion.

Oracle DBA

anuj blog Archive