HuckleberryDBA

Tuesday, April 5, 2022

PGA Historical Growth PDB

sql> show pdbs

retrieve pdb con_id

set linesize 200
col begin_time for a30
col end_time for a30
select snap_id, CON_DBID, begin_time, end_time, pga_bytes/1024/1024/1024
from DBA_HIST_RSRC_PDB_METRIC
where con_id =4
order by 1;

Monday, January 24, 2022

AWR/ADDM Snapshot settings: CDB/PDB

get current:

SELECT con_id, extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval,

extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval

FROM dba_hist_wr_control;

19c new feature, enable addm in pdb.

ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=TRUE scope=both sid='*';

EXEC dbms_workload_repository.modify_snapshot_settings(interval=>60);

references:

19c New Feature Support ADDM Analysis for PDBs (Doc ID 2644341.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=124474630246449&id=2644341.1&_afrWindowMode=0&_adf.ctrl-state=8xemq01j2_160

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/AWR_PDB_AUTOFLUSH_ENABLED.html#GUID-08FA21BC-8FB1-4C51-BEEA-139C734D17A7

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/automatic-performance-diagnostics.html#GUID-5A4F4896-6F95-461F-84E3-8E40F6036752

Wednesday, January 5, 2022

TNSPING sqlnet Timeout option for net address not reachable.

sqlnet.ora timeout for tnsping of offline host/vip address, this would normally hang your tnsping for 3 minutes, if you have 1500+ entries in a global tns file, with many of them dead of offline for maintenance, that adds up quickly.

###########################

# tnsping host/vip timeout option

TCP.CONNECT_TIMEOUT=1

###########################

Oracle Tablespace Usage Script

shows allocated and used space and percentages.

# tbs_usage.sql

def aps_prog = 'tf.sql'

def aps_title = 'Database Freespace Summary'

set linesize 200

col tsname format a24 heading 'Tablespace'

col nfrags format 999,990 justify c heading 'Free|Frags'

col mxfrag format 99,990 justify c heading 'Largest|Free Frag'

col totsiz format 99,999,990 justify c heading 'Total|Mbytes'

col filsiz format 99,999,990 justify c heading 'File|Mbytes'

col avasiz format 99,999,990 justify c heading 'Available|Mbytes'

col pctusd format 990.99 justify c heading 'Percent|Used'

set pages 200

select

total.tablespace_name tsname,

count(free.bytes) nfrags,

nvl(max(free.bytes)/1048576,0) mxfrag,

total.bytes/1048576 totsiz,

total.file_bytes/1048576 filsiz,

nvl(tot_free.free_bytes,0)/1048576 avasiz,

--nvl(used.bytes,0)/total.bytes*100 pctusd

(total.file_bytes-nvl(tot_free.free_bytes,0))/total.bytes*100 pctusd

from

(select

tablespace_name,

sum(bytes) file_bytes,

SUM(CASE WHEN maxbytes > bytes THEN maxbytes ELSE bytes END) bytes

from

dba_data_files

where tablespace_name not like 'UNDO%'

group by

tablespace_name)

total,

(select

tablespace_name,

nvl(sum(bytes),0) free_bytes

from

dba_free_space

group by

tablespace_name) tot_free,

dba_free_space free

where

total.tablespace_name = free.tablespace_name(+)

and total.tablespace_name = tot_free.tablespace_name(+)

group by

total.tablespace_name,

total.bytes,

free_bytes,

file_bytes

order by 7

select

total.tablespace_name tsname,

count(free.bytes) nfrags,

nvl(max(free.bytes)/1048576,0) mxfrag,

total.bytes/1048576 totsiz,

total.file_bytes/1048576 filsiz,

nvl(tot_free.free_bytes,0)/1048576 avasiz,

--nvl(used.bytes,0)/total.bytes*100 pctusd

(total.file_bytes-nvl(tot_free.free_bytes,0))/total.bytes*100 pctusd

from

(select

tablespace_name,

sum(bytes) file_bytes,

SUM(CASE WHEN maxbytes > bytes THEN maxbytes ELSE bytes END) bytes

from

dba_data_files

group by

tablespace_name)

total,

(select

tablespace_name,

nvl(sum(bytes),0) free_bytes

from

dba_free_space

group by

tablespace_name) tot_free,

dba_free_space free

where

total.tablespace_name = free.tablespace_name(+)

and total.tablespace_name = tot_free.tablespace_name(+)

group by

total.tablespace_name,

total.bytes,

free_bytes,

file_bytes

order by 1

col tsname heading "Temp Tablespace"

select

total.tablespace_name tsname,

total.bytes/1048576 totsiz,

total.file_bytes/1048576 filsiz

from (

select

tablespace_name,

sum(bytes) file_bytes,

SUM(CASE WHEN maxbytes > bytes THEN maxbytes ELSE bytes END) bytes

from

dba_temp_files

group by

tablespace_name

) total

col name for a30 heading "ASM DiskGroup"

select name, free_mb, total_mb from v$asm_diskgroup order by 1;

RMAN Table PIT Recovery Notes

--recover table and let rman import it in place.

run {

recover table drs.test_me until scn 22044407

auxiliary destination '+SBD818_FRA_01';

}

--pluggable pdb option.

run {

recover table drs.test_me of pluggable database PDB2

until scn 22044407

auxiliary destination '/u02/CDB2/aux';

}

-- recover table and create import dump file.

run {

recover table drs.test_me until scn 22044407

auxiliary destination '+SBD818_FRA_01'

datapump destination '+SBD818_FRA_01/SBD818/tspitr'

dump file 'drs_test_me.dmp'

notableimport;

}

run {

recover table test_dummy.test_table until scn 861867

auxiliary destination '+SBD818_FRA_01'

datapump destination '+SBD818_FRA_01/dpdump_rman'

dump file 'test_my_drop.dmp'

notableimport;

}

create directory FRA_DATAPUMP as '+SBD818_FRA_01/dpdump_rman';

grant read, write on directory FRA_DATAPUMP to public;

create directory DP_LOG as '/srv/users/dba/oracle';

grant read, write on directory DP_LOG to public;

test_restore.par

DUMPFILE=test_my_drop.dmp

TABLES=test_dummy.test_table

LOGFILE=DP_LOG:impdp_test_dummy.log

JOB_NAME=test_import

DIRECTORY=FRA_DATAPUMP

oracle> impdp / parfile=test_dummy.par

Import: Release 19.0.0.0.0 - Production on Tue Nov 2 14:07:52 2021

Version 19.11.0.0.0

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

Master table "OS_ORACLE"."TEST_IMPORT" successfully loaded/unloaded

Starting "OS_ORACLE"."TEST_IMPORT": /******** parfile=test_dummy.par

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "TEST_DUMMY"."TEST_TABLE" 0 KB 0 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "OS_ORACLE"."TEST_IMPORT" successfully completed at Tue Nov 2 14:07:56 2021 elapsed 0 00:00:04

RMAN Backup/Restore Notes

# these list and advice commands do not do much from what i have seen.

RMAN> LIST FAILURE;

RMAN> ADVISE FAILURE;

RMAN> REPAIR FAILURE;

RMAN> list backup summary;

RMAN> VALIDATE backupset 107929604, 107929605, 107929606; (give all ids for CBK_FULL)

RMAN> RESTORE DATABASE VALIDATE;

RMAN> RESTORE ARCHIVELOG ALL VALIDATE;

RMAN> RESTORE DATABASE PREVIEW;

RMAN> run {

allocate channel t1 type SBT_TAPE;

VALIDATE DATABASE;

release channel t1;

}

or VALIDATE PLUGGABLE DATABASE

RMAN> restore controlfile from autobackup;

RMAN> restore spfile from tag='CBK_FULL' to '$ORACLE_HOME/dbs';

SET UNTIL TIME "TO_DATE('2016-01-20 16:00:00','yyyy-mm-dd hh24:mi:ss')";

SET UNTIL SEQUENCE 1234 THREAD 1;

run {

allocate channel t1 type SBT_TAPE;

set until time "TO_DATE('2019-01-20 16:00:00','yyyy-mm-dd hh24:mi:ss')";

report schema device type sbt;

release channel t1;

}

[NOMOUNT MODE]

RMAN> run {

allocate channel t1 type SBT_TAPE;

set until time "TO_DATE('2019-01-20 16:00:00','yyyy-mm-dd hh24:mi:ss')";

RESTORE SPFILE TO PFILE '/opt/oracle/admin/<SID>/init<SID>.ora';

RESTORE CONTROLFILE TO '/opt/oracle/admin/<SID>/ctrl01_temp.ora';

release channel t1;

}

[NOMOUNT MODE]

RMAN> startup nomount;

RMAN> RUN {

SET UNTIL SEQUENCE 12345 THREAD 1;

RESTORE CONTROLFILE ;

ALTER DATABASE MOUNT;

RESTORE DATABASE;

RECOVER DATABASE; # recovers through log 12344

ALTER DATABASE OPEN RESETLOGS;

}

[MOUNT MODE]

RMAN> startup mount;

RMAN> run {

allocate channel t1 type SBT_TAPE;

allocate channel t2 type SBT_TAPE;

allocate channel t3 type SBT_TAPE;

set until time "TO_DATE('2019-01-20 16:00:00','yyyy-mm-dd hh24:mi:ss')";

restore database;

recover database;

release channel t1;

release channel t2;

release channel t3;

}

[PIT RECOVERY] [mount mode]

RMAN> FLASHBACK DATABASE TO SCN 12345678910;

RMAN> RECOVER DATABASE UNTIL TIME 'YYYY-MM-DD:HH24:MI:SS'; #need quotes

RMAN> RECOVER DATABASE UNTIL SCN 12345678910;

RMAN> RECOVER DATABASE UNTIL SEQUENCE 12345 THREAD 1;

[RECOVERY UNTIL LAST AVAILABLE ARCHIVE]

RMAN> recover database until cancel;

[RECOVER UNTIL RESTORE POINT]

STARTUP MOUNT;

RUN

{

SET UNTIL RESTORE POINT 'before_upgrade';

RESTORE DATABASE;

RECOVER DATABASE;

ALTER DATABASE OPEN RESETLOGS;

}

RMAN> alter database open resetlogs;

Thursday, December 16, 2021

Install/Configure Oracle Autonomous Health Framework

at least 10GB needed on local filesystem for data repository.

https://docs.oracle.com/en/engineered-systems/health-diagnostics/autonomous-health-framework/index.html

--as oracle user

export AHF_HOME=<path_to_sw_home>

-create ahf root owned directories

oracle> mkdir -p $AHF_HOME/ahf/data

oracle> sudo chown -R root:root $AHF_HOME/ahf

--as root user

cd /<tfa_software_binary_stage>/TFA/21.3.3_30166242

export AHF_OVERRIDE_INSTALL_CHECKS=1

./ahf_setup -silent -local -ahf_loc $AHF_HOME/ahf -data_dir $AHF_HOME/ahf/data

AHF Installer for Platform Linux Architecture x86_64

AHF Installation Log : /tmp/ahf_install_213300_39838_2021_12_15-16_58_59.log

Starting Autonomous Health Framework (AHF) Installation

AHF Version: 21.3.3 Build Date: 202112130225

AHF Location : $AHF_HOME/ahf/oracle.ahf

Suppressing 'AHF-00014: AHF Location $AHF_HOME/ahf/oracle.ahf is not owned by root in directory hierarchy' as AHF_OVERRIDE_INSTALL_CHECKS=1

AHF Data Directory : $AHF_HOME/ahf/oracle.ahf/data

Suppressing AHF-00014: AHF Data Location $AHF_HOME/ahf/oracle.ahf/data is not owned by root in directory hierarchy as AHF_OVERRIDE_INSTALL_CHECKS=

Extracting AHF to $AHF_HOME/ahf/oracle.ahf

Configuring TFA Services

Discovering Nodes and Oracle Resources

Not generating certificates as GI discovered

Starting TFA Services

Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.

Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.

.--------------------------------------------------------------------------------.

+-------------+---------------+-------+------+------------+----------------------+

| <host_name> | RUNNING | 41142 | 5002 | 21.3.3.0.0 | 21330020211213022521 |

'-------------+---------------+-------+------+------------+----------------------'

Running TFA Inventory...

Adding default users to TFA Access list...

.----------------------------------------------------------------------------.

| Summary of AHF Configuration |

+-----------------+----------------------------------------------------------+

| Parameter | Value |

+-----------------+----------------------------------------------------------+

| AHF Location | $AHF_HOME/ahf/oracle.ahf |

| TFA Location | $AHF_HOME/ahf/oracle.ahf/tfa |

| Orachk Location | $AHF_HOME/ahf/oracle.ahf/orachk |

| Data Directory | $AHF_HOME/ahf/oracle.ahf/data |

| Repository | $AHF_HOME/ahf/oracle.ahf/data/repository |

| Diag Directory | $AHF_HOME/ahf/oracle.ahf/data/<host_name>/diag |

'-----------------+----------------------------------------------------------'

Starting orachk scheduler from AHF ...

AHF binaries are available in $AHF_HOME/ahf/oracle.ahf/bin

AHF is successfully installed

Moving /tmp/ahf_install_213300_39838_2021_12_15-16_58_59.log to $AHF_HOME/ahf/oracle.ahf/data/<hostname>/diag/ahf/

--as oracle

-check install

oracle> cd $AHF_HOME/ahf/oracle.ahf/bin

oracle> systemctl status oracle-tfa.service

● oracle-tfa.service - Oracle Trace File Analyzer

Loaded: loaded (/etc/systemd/system/oracle-tfa.service; enabled; vendor preset: disabled)

Active: active (running) since Wed 2021-12-15 17:41:59 PST; 18min ago

Main PID: 33185 (init.tfa)

CGroup: /system.slice/oracle-tfa.service

├─33185 /bin/sh /etc/init.d/init.tfa run >/dev/null 2>&1 </dev/null

├─33392 $AHF_HOME/ahf/oracle.ahf/jre/bin/java -server -Xms128m -Xmx256m -Djava.awt.headless=true -Ddisable.checkF...

└─63448 /bin/sleep 30

oracle> cd $AHF_HOME/ahf/oracle.ahf/bin

oracle> ./tfactl status

.--------------------------------------------------------------------------------------------------.

+------------+---------------+-------+------+------------+----------------------+------------------+

| <hostname> | RUNNING | 33392 | 5002 | 21.3.3.0.0 | 21330020211213022521 | COMPLETE |

'------------+---------------+-------+------+------------+----------------------+------------------'

OR more verbose from ahfctl

oracle> $AHF_HOME/ahf/oracle.ahf/bin

oracle> ./ahfctl statusahf

.--------------------------------------------------------------------------------------------------.

+------------+---------------+-------+------+------------+----------------------+------------------+

| <hostname> | RUNNING | 33392 | 5002 | 21.3.3.0.0 | 21330020211213022521 | COMPLETE |

'------------+---------------+-------+------+------------+----------------------+------------------'

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

ID: orachk.autostart_client_oratier1

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

AUTORUN_FLAGS = -usediscovery -profile oratier1 -dball -showpass -tag autostart_client_oratier1 -readenvconfig

COLLECTION_RETENTION = 7

AUTORUN_SCHEDULE = 3 2 * * 1,2,3,4,5,6

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

ID: orachk.autostart_client

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

AUTORUN_FLAGS = -usediscovery -tag autostart_client -readenvconfig

COLLECTION_RETENTION = 14

AUTORUN_SCHEDULE = 3 3 * * 0

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

orachk daemon is not running

-increase repo size

oracle> cd $AHF_HOME/ahf/oracle.ahf/bin

oracle> sudo tfactl set reposizeMB=10240

The minimum recommended repository size is 10 GB.

Do you wish to continue with current repository size ? [Y/y/N/n] [N] y

Repository size will consume more than 50% of available space in filesystem.

Do you wish to continue with the new size ? [Y/y/N/n] [N] y

Successfully changed repository size

.----------------------------------------------------------------------------.

| Repository Parameter | Value |

+-----------------------+----------------------------------------------------+

| Location | $AHF_HOME/ahf/oracle.ahf/data/reposi |

| Old Maximum Size (MB) | 2022 |

| New Maximum Size (MB) | 3072 |

| Current Size (MB) | 20 |

| Status | OPEN |

'-----------------------+----------------------------------------------------'

oracle> $AHF_HOME/ahf/oracle.ahf/bin/tfactl toolstatus

.------------------------------------------------------------------.

| TOOLS STATUS - HOST : <HOSTNAME> |

+----------------------+--------------+--------------+-------------+

+----------------------+--------------+--------------+-------------+

| | ls | 21.3.3 | DEPLOYED |

| | ps | 21.3.3 | DEPLOYED |

| | vi | 21.3.3 | DEPLOYED |

+----------------------+--------------+--------------+-------------+

+----------------------+--------------+--------------+-------------+

| | prw | 12.1.13.11.4 | NOT RUNNING |

'----------------------+--------------+--------------+-------------'

oracle> cd $AHF_HOME/ahf/oracle.ahf/bin

oracle> ./ahfctl -h

Usage : ahfctl <command> [options]

| applypatch

| celldiagcollect

| checkpassword

| checkupload

| compliance

| get

| getupgrade

| getupload

| import

| loadpolicy

| querypatch

| redact

| rmap

| rollbackpatch

| set

| setpassword

| setupgrade

| setupload

| showrepo

| startahf

| statusahf

| stopahf

| uninstall

| unloadpolicy

| unset

| unsetpassword

| unsetupgrade

| unsetupload

| upgrade

| upload

| version

For detailed help on each command use: ahfctl <command> -h

- for most detailed analysis, use root admin user.

root#> $AHF_HOME/ahf/oracle.ahf/bin/tfactl menu

Trace File Analyzer Collector Menu System

=========================================

TFA Main Menu

The Oracle Trace File Analyzer Menu provides a simple

interface to the TFA tools.

Select one of the following categories:

1. System Analysis

2. Collections

3. Administration

(B)ack (M)ain (H)elp E(x)it

Please enter your selection :

- tfactl menu details

1. System Analysis

Select one of the following categories:

1. Summary

2. Events

3. Analyze logs

4. TFA Utilities

5. Support Tool Bundle

6. Tools status

2. Collections

Select one of the following options:

1. Default Collection

2. SRDC Collection

3. Engineered System Collection

4. Advanced Collection

3.Administration

Select One of the following options:

1. Version & status

2. Start, stop & auto start

3. Hosts & ports

4. Settings

5. Actions submitted

6. Manage Database logs

7. Tracing Level

8. Users

9. Collect TFA Diagnostic Data