Durham Oracle

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[@]}"

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

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

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.