Weblogic Version

Needed to quickly check the Weblogic version that I was using - quick notes.

From $WL_HOME/server/lib run this :

[oracle@dev01-wls-1 lib]$ pwd

/u01/app/oracle/products/fmw_12214/wlserver/server/lib

[oracle@dev01-wls-1 lib]$ java -cp weblogic.jar weblogic.version -version -verbose

WebLogic Server 12.2.1.4.0 Thu Sep 12 04:04:29 GMT 2019 1974621 ImplVersion: 12.2.1.4.0

Oracle Security Developer Tools Security Engine ImplVersion: 3.1.0

Oracle Security Developer Tools Crypto ImplVersion: 3.1.0

Oracle Universal Connection Pool ImplVersion: 19.3.0.0.0

etc...

There are other ways to check the version but this seems to the easiest and most comprehensive.

Download :

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

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

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
/

Export users - roles & priv (draft)

 

https://stackoverflow.com/questions/57991488/how-to-export-users-in-oracle-with-its-roles-and-system-privileges-using-expdp

 set long 20000

 set lines 200

 set pages 666

 col DBMS_METADATA.GET_DDL('USER',:NAME) format a90

 variable name varchar2(50)

 exec :name:='CON29';

SELECT dbms_metadata.get_ddl('USER', :name)
  FROM dual
UNION ALL
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', grantee)
  FROM dba_role_privs
 WHERE grantee = :name
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('DEFAULT_ROLE', grantee)
  FROM dba_role_privs
 WHERE grantee = :name
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', grantee)
  FROM dba_sys_privs          sp,
       system_privilege_map   spm
 WHERE sp.grantee = :name
   AND sp.privilege = spm.name
   AND spm.property <> 1
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT', grantee)
  FROM dba_tab_privs
 WHERE grantee = :name
   AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', username)
  FROM dba_ts_quotas
 WHERE username = :name
   AND ROWNUM = 1

SQL> /

DBMS_METADATA.GET_DDL('USER',:NAME)

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

   CREATE USER "CON29" IDENTIFIED BY VALUES 'AbcDefGhi'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMPORARY"

.....etc

Run against the new database to create the user.

In AIX to run against multiple DBs e.g.

#!/usr/bin/env bash

set -A array XYZDBA 

## Set the env b4 running this

for i in "${array[@]}"

do

exit | sqlplus -silent / as sysdba << EOF

set head on

set feedback off

set long 30000

set lines 220

set pages 666

## col DBMS_METADATA.GET_DDL('USER',:NAME) format a220

## 

col DBMS_METADATA.GET_DDL('USER','XYZDBA') format a220

SELECT dbms_metadata.get_ddl('USER',  '$i')

  FROM dual......

(   same script as above )

EOF

done

 

This works too...

declare
data CLOB;
BEGIN
SELECT dbms_metadata.get_ddl('USER','SFS') into data from dual;
DBMS_XSLPROCESSOR.CLOB2FILE(data,'DIRECTORY**','create_user.sql');

END;

 

**DIRECTORY - change as approp. e.g. DATA_PUMP_DIR find from dba_directories

For the tablespaces for the above..

SQL> SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN

   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);

SQL> SQL>   2    3     DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);

END;

/

SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)

FROM   dba_tablespaces

WHERE  tablespace_name = DECODE(UPPER('&1'), 'ALL', tablespace_name, UPPER('&1'));

  4    5  SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON

SQL> SQL>   2    3  Enter value for 1: Enter value for 1: SQL>

SQL> /

Enter value for 1: NWLMEAVX

Enter value for 1: NWLMEAVX

  CREATE TABLESPACE "NWLMEAVX" DATAFILE etc......

You will need to alter to suit when going from ASM to Unix datafiles or vice-versa.

see https://oracle-base.com/dba/script?category=script_creation&file=tablespace_ddl.sq

l

PDBS on Oracle OCI and the joy of TDE

Some quick notes regarding PDBs on Oracle OCI

When creating a new pdb in an Oracle OCI database hit an error when trying to create a tablespace during the build.

I opted to continue but had to sort out the issue after which is to do with "Transparent Data Encryption" - TDE on the tablespaces and you are not able to add any tablespaces.

Make sure you make a note of all passwords when building out databases as you are in big trouble with the keystore if you don't have the correct password and no way to reset the password as far as I know (at least not without the help of Oracle).  


See this document :

Creating and Activating a Master Encryption Key for a PDB (in OCI and OCI-Classic) 

(Doc ID 2469398.1)

For the new pluggable database : 

SQL> select * from v$encryption_wallet

WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC ------------------------------ -------------------- --------- -------- --------- CON_ID ---------- FILE

OPEN_NO_MASTER_KEY PASSWORD SINGLE UNITED UNDEFINED 4

​
SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'tag' FORCE KEYSTORE IDENTIFIED BY Password# WITH BACKUP USING 'backup_10062021' 2 /

keystore altered.
​
So no single or double quotes surrounding the password.

That was after closing the keystore and then reopening.
​
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE close identified by Password99#

keystore altered.
​
As root :

dbcli update-tdekey -i da29189a-b5eb-46f7-bde2-8c334dc21e19 -n DB123 -hp Password99#

dbcli describe-job -i da29189a-b5eb-46f7-bde2-8c334dc21e19

Job details
----------------------------------------------------------------
ID: da29189a-b5eb-46f7-bde2-8c334dc21e19
Description: TDE update DB123 - PDBs: [PDB123]
Status: Success
Created: June 10, 2021 4:24:39 PM BST
Progress: 100%
Message:

Everything working as expected.

See also : 

https://database-heartbeat.com/2020/12/31/create-pdbs-in-a-data-guard-environment-with-tde-enabled/

expdp / impdp as sysdba

Example of importing with sysdba when you don't have any schema passwords - you could create yourself a user and give yourself dba privs but this is easier. 

The expdp was just the reverse - putting this in a post as I have used this in the past but took an age to find it in my notes and the syntax is a bit odd to remember.


[oracle@db301] $ impdp \'/ as sysdba\' SCHEMAS=ENGARDE_NORTH1REP DIRECTORY=DATA_PUMP_DIR DUMPFILE=ENGARDE_NORTH1REP.dmp LOGFILE=ENGARDE_NORTH1REP_import.log

Import: Release 12.1.0.2.0 - Production on Fri May 28 14:04:55 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" SCHEMAS=ENGARDE_NORTH1REP DIRECTORY=DATA_PUMP_DIR DUMPFILE=ENGARDE_NORTH1REP.dmp LOGFILE=ENGARDE_NORTH1REP_import.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ENGARDE_NORTH1REP"."FSM_NW_ACTIVITY" 988.7 MB 1952353 rows
. . imported "ENGARDE_NORTH1REP"."ALLOCATIONS" 623.4 MB 12463222 rows
. . imported "ENGARDE_NORTH1REP"."EVENTS" 539.0 MB 3761100 rows
. . imported "ENGARDE_NORTH1REP"."JOBDESC" 499.4 MB 8609289 rows
. . imported "ENGARDE_NORTH1REP"."ACTIVITIES" 252.1 MB 5394085 rows
. . imported "ENGARDE_NORTH1REP"."ASSETS" 23.13 MB 313412 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri May 28 14:29:22 2021 elapsed 0 00:24:26


Quick check :

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count(*) from ENGARDE_NORTH1REP.ALLOCATIONS;

COUNT(*)
----------
12463222

Example of exporting tables with a parameter file

expdp system/secret_password parfile=expdp_TL_BACK_par

$ cat ./expdp_TL_BACK_par

DIRECTORY=dpump
DUMPFILE=expdp_TL_BACK_%U.dmp
LOGFILE=expdp_TL_BACK.log
FILESIZE=1000M
TABLES=nwgis.TL_BUILD
nwgis.TL_HANT
nwgis.TL_STRUC
nwgis.TL_WATER

Need to know export size - 

$ expdp  \'/ as sysdba\' FULL=y ESTIMATE_ONLY=y NOLOGFILE=y

Export: Release 11.2.0.4.0 - Production on Mon May 22 15:16:42 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Real Application Testing option

Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" FULL=y ESTIMATE_ONLY=y NOLOGFILE=y

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

.  estimated "LYODBA"."TARCCNF"                          26.12 GB

.  estimated "LYODBA"."TREPOS_TRANSC_ITEM"               22.46 GB

etc....

Total estimation using BLOCKS method: 365.6 GB

Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Mon May 22 15:17:40 2023 elapsed 0 00:00:57