Durham Oracle: July 2021

Clone / Refresh of a PDB from remote database

Using the note from here -

https://oracle-base.com/articles/12c/multitenant-clone-remote-pdb-or-non-cdb-12cr1

"Oracle-Base" always excellent but please check this note on Metalink and the note below if using TDE.

Cloning PDB From PDB Fails With ORA-28357: Password Required To Open The Wallet (Doc ID 2415131.1)

Example database ORAPDB and example password used.

++++++++++++++++++++++++++++++

[oracle@dev01tier2-dbs admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 30 12:25:22 2021

Version 19.9.0.0.0

Connected to:

Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production

Version 19.9.0.0.0

SQL> DROP DATABASE LINK clone_link;

DROP DATABASE LINK clone_link

ERROR at line 1:

ORA-02024: database link not found

SQL> CREATE DATABASE LINK clone_link CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user USING 'ORAPDB';

Database link created.

SQL> DESC user_tables@clone_link

Name Null? Type

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

TABLE_NAME NOT NULL VARCHAR2(128)

TABLESPACE_NAME VARCHAR2(30)

…etc

HAS_SENSITIVE_COLUMN VARCHAR2(3)

ADMIT_NULL VARCHAR2(3)

DATA_LINK_DML_ENABLED VARCHAR2(3)

LOGICAL_REPLICATION VARCHAR2(8)

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 RADND READ WRITE NO

4 RADARPDB READ WRITE NO

5 ORAPDB READ ONLY NO

SQL> drop pluggable database ORAPDB including datafiles;

drop pluggable database ORAPDB including datafiles

ERROR at line 1:

ORA-65025: Pluggable database ORAPDB is not closed on all instances.

// Will try anyway to create to show error

SQL> CREATE PLUGGABLE DATABASE ORAPDB FROM ORAPDB@clone_link;

CREATE PLUGGABLE DATABASE SFSD FROM ORAPDB@clone_link

ERROR at line 1:

ORA-65012: Pluggable database ORAPDB already exists.

SQL> alter pluggable database ORAPDB close IMMEDIATE;

Pluggable database altered.

SQL> drop pluggable database ORAPDB including datafiles;

Pluggable database dropped.

// Now we can create but...

SQL> CREATE PLUGGABLE DATABASE ORAPDB FROM ORAPDB@clone_link;

CREATE PLUGGABLE DATABASE ORAPDB FROM ORAPDB@clone_link

ERROR at line 1:

ORA-46697: Keystore password required.

// See Doc ID 2415131.1

SQL> CREATE PLUGGABLE DATABASE ORAPDB FROM ORAPDB@clone_link keystore IDENTIFIED BY "Password#2021#";

Pluggable database created.

SQL> l

1* SELECT name, open_mode FROM v$pdbs

SQL> /

NAME OPEN_MODE

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

PDB$SEED READ ONLY

RADND READ WRITE

RADARPDB READ WRITE

ORAPDB MOUNTED

SQL> ALTER PLUGGABLE DATABASE ORAPDB OPEN;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 RADND READ WRITE NO

4 RADARPDB READ WRITE NO

5 ORAPDB READ WRITE NO

SQL> alter session set container = ORAPDB;

Session altered.

//Remember to perform sanity check against pdb - check for invalid objects etc..

1* select owner, object_type, count(object_type) from dba_objects where status != 'VALID' group by owner, object_type order by 1,2

SQL> /

OWNER OBJECT_TYPE COUNT(OBJECT_TYPE)

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

ABC FUNCTION 4

ABC PACKAGE BODY 1

2 rows selected.

Impdp issues...

If you ever end up with lots of invalid synonyms after impdp - quick sql to sort it out.

Just a note as no doubt this will happen again and I won't remember :(

SQL> select owner, object_type, count(object_type) from dba_objects where status != 'VALID' group by owner, object_type order by 1,2

2 /

OWNER OBJECT_TYPE COUNT(OBJECT_TYPE)

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

CON29GIS2 SYNONYM 129

SQL> select 'alter synonym '||owner||'.'||object_name||' compile; ' from dba_objects where status='INVALID' and object_type='SYNONYM' and owner='CON29GIS2' order by 1

2 /

'ALTERSYNONYM'||OWNER||'.'||OBJECT_NAME||'COMPILE;'

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

alter synonym CON29GIS2.ANSWERS compile;

alter synonym CON29GIS2.ANSWER_COMBINATIONS compile;

alter synonym CON29GIS2.API_CALLED compile;

....etc

JAVA CLASS and JAVA SOURCE Objects Become Invalid After Impdp. (Doc ID 1462622.1)

SQL> select owner, object_type, count(object_type) from dba_objects where status != 'VALID' group by owner, object_type order by 1,2
2 /

OWNER OBJECT_TYPE COUNT(OBJECT_TYPE)
----- ----------- ----------
CON29R2 JAVA CLASS 76

SQL> select 'ALTER JAVA CLASS CON29R2."'||OBJECT_NAME||'" resolve;' from dba_objects where status !='VALID' and object_type = 'JAVA CLASS'
2 /

'ALTERJAVACLASSCON29R2."'||OBJECT_NAME||'"RESOLVE;'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER JAVA CLASS CON29R2."SFSexportBlob" resolve;
ALTER JAVA CLASS CON29R2."SFSLiveLinkAPI" resolve;
ALTER JAVA CLASS CON29R2."com/opentext/api/LAPI_ATTRIBUTES" resolve;
ALTER JAVA CLASS CON29R2."com/opentext/api/LAPI_ATTRSERVER" resolve;
ALTER JAVA CLASS CON29R2."com/opentext/api/LAPI_DOCUMENTS" resolve;

Alter user as approp.

Script to resize Oracle database

I have created a link and credit to this in the useful links section but as web pages can often disappear without notice - making a note here as this has proved to very useful :

set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/