Posts

Rename PDB in 12.2

Create test pdb called s24pdb:


SQL> create pluggable database s24pdb admin user s24test identified by ****;

Pluggable database created.

SQL> alter pluggable database S24PDB open;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST1                          READ WRITE NO
         5 S24PDB                         READ WRITE NO

SQL> alter pluggable database S24PDB close;

Pluggable database altered.

SQL> alter pluggable database S24PDB open restricted;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST1                          READ WRITE NO
         5 S24PDB                         READ WRITE YE…

RELOCATE PDB IN ORACLE 12.2

RELOCATE PDB IN ORACLE 12.2
Prerequisite:
Local Undo should be enabled on both Source and Target CDB's. Both Databases should be in archivelog mode.


Source CDB: ========== COLUMN property_name FORMAT A30 COLUMN property_value FORMAT A30
SELECT property_name, property_value FROM   database_properties WHERE  property_name = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME                  PROPERTY_VALUE ------------------------------ ------------------------------ LOCAL_UNDO_ENABLED             TRUE
SQL> SELECT log_mode FROM   v$database;
LOG_MODE ------------ ARCHIVELOG
CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL; GRANT CREATE SESSION, SYSOPER, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;

Target CDB: ==========
COLUMN property_name FORMAT A30 COLUMN property_value FORMAT A30
SELECT property_name, property_value FROM   database_properties WHERE  property_name = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME                  PROPERTY_VALUE ------------------…

How to create user without C## in oracle 12c

How to create user without C## in oracle 12c
Even after using hidden parameter in init file, i am unable to create user without c##.
But when we  setup session level then  i am able to create user without c##.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> sho parameter _oracle
NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ _oracle_script                       boolean     TRUE
SQL> create user anil identified by anil; create user anil identified by anil             * ERROR at line 1: ORA-65096: invalid common user or role name
After setting hidden parameter at session level ..
SQL>alter session set "_oracle_script"=true;
Session altered.
SQL> sho pdbs
    CON_ID CON_NAME      OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO          3 TESTPDB1  …

PDB is in READ WRITE, but it stays in RESTRICTED mode.

Issue: PDB is in Restricted mode.

Today we have upgraded 11g datbase to 12c and plugged in container.

After running noncdb_to_pdb.sql we tried to open PDB,

PDB status Restricted is  YES..


CDB$ROOT@AGIIPRDC> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 RQBIPRDPDB1                            READ WRITE YES



Reason:
Rows with type ERROR and status PENDING from pdb_plug_in_violations
select name, cause, type, message, status from pdb_plug_in_violations where type = 'ERROR' and status != 'RESOLVED'  order by name, time;


CDB$ROOT@AGIIPRDC> set linesize 150 col name for a15 col cause for a15 col message for a50
select name, cause, type, message, status from pdb_plug_in_violations where type = 'ERROR' and status != 'RESOLVED'  order by name, time;CDB$ROOT@AGIIPRDC> CDB$ROOT@AGIIPRDC> CDB$ROOT@AGIIPRDC> CDB$ROOT@AGIIPRDC> CDB$ROOT@AGIIPRDC>
NAME    …

ORA-00600: internal error code, arguments: [qksvcGetGuardCol:2], [100313], [0] After upgraded to 12C

Error: ORA-00600: internal error code, arguments: [qksvcGetGuardCol:2], [100313], [0], [], [], [], [], [], [], [], [], []

We have upgraded Database from 11.2.0.2 to 12.2.0.1 from Exadata ASM to Normal File system.

and plugged into container.

Everything went fine .. but we encountered ORA-600 error with Few ETL Tables .

select  count(*) from table  is working fine..but select * from table is failing.

CDB$ROOT@EBTUPRDC> select count(*) from "EBAOREP"."F_POLICY_CT_TRANS";

  COUNT(*)
----------
   9208566

select * from is failing with ORA-600 Error.

CDB$ROOT@EBTUPRDC> select * from EBTUS_GS.T_AS_MAILING_ADDRESS;
select * from EBTUS_GS.T_AS_MAILING_ADDRESS
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qksvcGetGuardCol:2], [228717], [0],
[], [], [], [], [], [], [], [], []




Solution:  We Exported Failed Tables from source database and imported into Upgraded 12c database.

Solution Provided by Oracle support :  ORA-600 [qkebCreateColById:1] After upgraded to …

ORA-65139: Mismatch between XML metadata file and data file

Error:

CDB$ROOT@EBTUPRDC> CREATE PLUGGABLE DATABASE EBTUSPRDPDB1 using '/home/oracle/EBTUSPRDC.xml' nocopy tempfile reuse;
CREATE PLUGGABLE DATABASE EBTUSPRDPDB1 using '/home/oracle/EBTUSPRDC.xml' nocopy tempfile reuse
*
ERROR at line 1:
ORA-65139: Mismatch between XML metadata file and data file
/u05/odb/ORADATA_1/EBTUSPRDC/datafile/system.1531.807800993 for value of fcpsb
(2230502002 in the plug XML file, 2230505492 in the data file)




Fix: Don't open Non-CDB database until we complete pdb creation other wise we will get above error.

Shutdown immediate;
startup mount;
alter database open read only;

create xml file.

shutdown immediate;


Connect to CDB and Create Pluggable database .


12c Non-CDB to Container PDB with NOCOPY Option

12c NON-CDB to Container PDBSteps for Non-CDB:
EBTUSPRD@EBTUSPRD> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. EBTUSPRD@EBTUSPRD> startup mount ORACLE instance started. Total System Global Area 3355443200 bytes Fixed Size                  8626240 bytes Variable Size            1040191424 bytes Database Buffers         2164260864 bytes Redo Buffers              142364672 bytes Database mounted. EBTUSPRD@EBTUSPRD> alter database open read only; Database altered.
Create XML file:
BEGIN   DBMS_PDB.DESCRIBE(     pdb_descr_file => '/home/oracle/EBTUSPRDC.xml'); END; / PL/SQL procedure successfully completed.

Shutdown NON-CDB:
EBTUSPRD@EBTUSPRD> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
Steps for Container Database:
SET SERVEROUTPUT ON DECLARE   compatible CONSTANT VARCHAR2(3) :=       CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(             pdb_descr_file => '/home/oracle/EBTUSPRDC.xml&#…