Oracle Technology Stack: 2024

Tuesday, October 1, 2024

Shrink datafile after 'ORA-03297'

Problem

When try to resize datafile following error occur

SQL> ALTER DATABASE DATAFILE '/u01/PROD/data/system01.dbf' RESIZE 10G;
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Solution

1. Check the tablespace sizes

SQL> set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_data_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
from V$temp_space_header
group by tablespace_name, bytes_free, bytes_used) fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_temp_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;

2. Find High Watermark of tablespace

SQL>select tablespace_name,
file_id,
file_name DATA_FILE_NAME,Allocated_MBYTES,
High_Water_Mark_MBYTES,
FREE_MBYTES,
trunc((FREE_MBYTES/Allocated_MBYTES)*100,2) "% Free",
trunc(Allocated_MBYTES-High_Water_Mark_MBYTES,2) Resizable
from
(
select ddf.tablespace_name tablespace_name,
ddf.file_id file_id,
ddf.file_name file_name,
ddf.bytes/1024/1024 Allocated_MBYTES,
trunc((ex.hwm*(dt.block_size))/1024/1024,2) High_Water_Mark_MBYTES,
FREE_MBYTES
from dba_data_files ddf
join dba_tablespaces dt on ddf.tablespace_name = dt.tablespace_name
left join (
select file_id, sum(bytes/1024/1024) FREE_MBYTES
from dba_free_space
group by file_id
) free on ddf.file_id = free.file_id
join (
select file_id, max(block_id+blocks) hwm
from dba_extents
group by file_id
) ex on ddf.file_id = ex.file_id
where 1=1
and ddf.tablespace_name='SYSTEM'
order by ddf.tablespace_name, ddf.file_id);

3. List the segments that have extents near the high watermark for given datafile

SQL>select file_name data_file_name,
segment_type,
owner||'.'||segment_name segment_name,
partition_name,
block_id,
blockId_Mbytes,
decode (segment_type,'TABLE','ALTER TABLE '||owner||'.'||segment_name||' MOVE TABLESPACE AA_DATA;','INDEX','ALTER INDEX '||owner||'.'||segment_name||' REBUILD ONLINE;')sc
from
(
select
de.owner owner,
de.segment_name segment_name,
de.segment_type segment_type,
de.block_id block_id,
DE.PARTITION_NAME partition_name,
ddf.file_name file_name,
trunc((de.block_id*(dt.block_size))/1024/1024,2) blockId_Mbytes
from dba_extents de
join dba_data_files ddf on de.file_id=ddf.file_id
join dba_tablespaces dt on ddf.tablespace_name = dt.tablespace_name
where 1=1
and ddf.file_id = 15
order by de.block_id desc
)
where rownum <= 100;

4. Rebuild Segments

i. Tables

SQL>ALTER TABLE AD_MGR.T_THREAD_DUMP MOVE TABLESPACE AA_DATA;

ii. Lobsegments

SQL>select * from dba_lobs where owner='AD_MGR' and segment_name='SYS_LOB0000386007C00005$$';

ALTER TABLE your_schema.your_table MOVE LOB(column_name) STORE AS (TABLESPACE new_tablespace);
ALTER TABLE AD_MGR.T_THREAD_DUMP MOVE LOB(THREAD_DUMP) STORE AS (TABLESPACE AA_DATA);

iii. Index

SQL>ALTER INDEX AA_MGR.IDX$$_199120004 REBUILD ONLINE;

Tuesday, September 10, 2024

Clone PDB on 19c

Check Free space on server

SQL> col name for a10
col percentage 999.99
SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;

NAME FREE_MB TOTAL_MB PERCENTAGE
---------- ---------- ---------- ----------
TSTDB 129716 1351560 9.59750215

Check the size if exisitng PDBS on server

SQL> col name for a10
select con_id, name, open_mode, total_size/1024/1024/1024 "PDB_SIZE_GB" from v$pdbs;

CON_ID NAME OPEN_MODE PDB_SIZE_GB
---------- ---------- ---------- -----------
2 PDB$SEED READ ONLY 2.29394531
3 GISDEV READ WRITE 189.53125
4 GISDB READ WRITE 160.301758
3 rows selected.

Clone on same server

Start Source PDB in readonly mode

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE
---------- ----------
PDB$SEED READ ONLY
GISDEV READ WRITE
GISDB READ WRITE

SQL> alter session set container=GISDEV;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------
/u01/oracle/oradata/cdb1/tstdb/system01.dbf
/u01/oracle/oradata/cdb1/tstdb/sysaux01.dbf

SQL> shutdown immediate;
Pluggable Database closed.

SQL> startup open read only
Pluggable Database opened.

Connect to container database and clone from source PDB

sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 16 12:32:58 2023
Version 19.20.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

SQL> show con_name

CON_NAME
---------------------
CDB$ROOT

SQL> create pluggable database GISTEST from GISDEV FILE_NAME_CONVERT=('/u01/oracle/oradata/cdb1/tstdb','/u01/oracle/oradata/cdb1/tstdb1');

Pluggable database created.

Start the both Source and target PDB's

SQL> sho pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ---------------------- ---------- ----------
2 PDB$SEED READ ONLY NO
3 GISDEV READ ONLY NO
4 GISDB READ WRITE NO
5 GISTEST READ ONLY NO

SQL> ALTER PLUGGABLE DATABASE GISDEV OPEN;
SQL> ALTER PLUGGABLE DATABASE GISTEST OPEN;

SQL> sho pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ---------------------- ---------- ----------
2 PDB$SEED READ ONLY NO
3 GISDEV READ WRITE NO
4 GISDB READ WRITE NO
5 GISTEST READ WRITE NO

Clone from Remote server

Connect to Source PDB

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE
---------- ----------
PDB$SEED READ ONLY
GISPROD READ WRITE
DBPROD READ WRITE

SQL> alter session set container=GISPROD;

Session altered.

Create user and grant

create user clone_pdb identified by clone_pdb;

SQL> grant create session,create pluggable database to clone_pdb;

Create DB link on Target db pointing to source DB, You can use either way to create DB link

Method-1

SQL> CREATE DATABASE LINK prodDB
CONNECT TO clone_pdb IDENTIFIED BY clone_pdb
USING '(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.example.com)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=GISPROD))
)';

Method-2

vi /u01/app/oracle/product/19.0.0.0/db_1/network/admin/tnsnames.ora
ORCLPDB =
(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.example.com)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = GISPROD))
)

SQL> CREATE DATABASE LINK prodDB CONNECT TO clone_pdb IDENTIFIED BY clone_pdb using 'ORCLPDB';

Create pluggable DB using DB link

SQL> create pluggable database orclpdbnew from orclpdb@dblink file_name_convert='/u01/app/oracle/oradata/orcl/orclpdb/','/u01/app/ oracle/oradata/orcl/orclpdbnew/');