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



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

or 
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

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

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 Copyright (c) 1998, 2003, Oracle Corporation.  All rights reserved.
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 Copyright (c) 2003, 2015, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
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
-------------------
                  1

SQL>
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
---------------------------
                          0

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 Copyright (c) 2014, 2015, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
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;
 18
 19  BEGIN
 20     -- Check for existence of the table marking disabled functional indices
 21
 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;
 25
 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;
 35
 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;
 47
 48        -- Output any indexes in the table that could not be re-enabled
 49        -- due to ORA-30552 during ALTER INDEX...ENBLE command
 50
 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:');
 55
 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';
 60
 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;
 68
 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;
 74
 75        END IF;
 76
 77        EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';
 78
 79     EXCEPTION
 80        WHEN NO_DATA_FOUND THEN NULL;
 81
 82  END;
 83  /

PL/SQL procedure successfully completed.

SQL>
SQL> DROP function local_enquote_name;

Function dropped.

SQL> SET serveroutput off
SQL>
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>
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 = CDB$ROOT Id = 1 ====
/tmp/tmp10.log:==== Current Container = PDB$SEED Id = 2 ====
/tmp/tmp10.log:==== Current Container = PDB$SEED Id = 2 ====
/tmp/tmp10.log:==== Current Container = VIHAAN Id = 5 ====
/tmp/tmp10.log:==== Current Container = VIHAAN Id = 5 ====
/tmp/tmp11.log:==== Current Container = ANUJ Id = 3 ====
/tmp/tmp11.log:==== Current Container = ANUJ Id = 3 ====
/tmp/tmp11.log:==== Current Container = PDB2 Id = 4 ====
/tmp/tmp11.log:==== Current Container = PDB2 Id = 4 ====
/tmp/tmp11.log:==== Current Container = VIHAAN2 Id = 6 ====
/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
1

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

 

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

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

 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(*)
----------
         0

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

  COUNT(*)
----------
         0


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;


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

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
                               7

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

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
                               7

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;

Oracle DBA

anuj blog Archive