Oracle TFA Installation in 2 node cluster

1. Install TFA from node1 this will install in node1 & node2

       

[root@node2 TFA]# ./ahf_setup

AHF Installer for Platform Linux Architecture x86_64

AHF Installation Log : /tmp/ahf_install_382363_2020_04_30-08_32_06.log

Starting Autonomous Health Framework (AHF) Installation

AHF Version: 20.1.2 Build Date: 202004031134

Default AHF Location : /opt/oracle.ahf

Do you want to install AHF at [/opt/oracle.ahf] ? [Y]|N :

AHF Location : /opt/oracle.ahf

AHF Data Directory stores diagnostic collections and metadata.
AHF Data Directory requires at least 5GB (Recommended 10GB) of free space.

Choose Data Directory from below options :

1. /u01/app/grid [Free Space : 9323 MB]
2. Enter a different Location

Choose Option [1 - 2] : 2

Please Enter AHF Data Directory : /depot/TFA_DATA

AHF Data Directory : /depot/TFA_DATA/oracle.ahf/data

Do you want to add AHF Notification Email IDs ? [Y]|N : n

AHF will also be installed/upgraded on these Cluster Nodes :

1. node1

The AHF Location and AHF Data Directory must exist on the above nodes
AHF Location : /opt/oracle.ahf
AHF Data Directory : /depot/TFA_DATA/oracle.ahf/data

Do you want to install/upgrade AHF on Cluster Nodes ? [Y]|N :

Extracting AHF to /opt/oracle.ahf

Configuring TFA Services

Discovering Nodes and Oracle Resources

Do you want us to store the Password for Cells in Oracle Wallet: [Y]|N

Is password same for all Cells: [Y]|N

Please Enter Password for Cell:
Please Confirm Password for Cell:

Both Passwords should be the same...!!!

Please Enter Password for Cell:
Please Confirm Password for Cell:

Verifying Password...

.----------------------------------.
|  | EXADATA CELL | CURRENT STATUS |
+--+--------------+----------------+
'--+--------------+----------------'


Not generating certificates as GI discovered

Starting TFA Services

.-------------------------------------------------------------------------------------.
| Host              | Status of TFA | PID  | Port | Version    | Build ID             |
+-------------------+---------------+------+------+------------+----------------------+
| node2             | RUNNING       | 1077 | 5000 | 20.1.2.0.0 | 20120020200403113404 |
'-------------------+---------------+------+------+------------+----------------------'

Running TFA Inventory...

Adding default users to TFA Access list...

.--------------------------------------------------------------------------.
|                       Summary of AHF Configuration                       |
+-----------------+--------------------------------------------------------+
| Parameter       | Value                                                  |
+-----------------+--------------------------------------------------------+
| AHF Location    | /opt/oracle.ahf                                        |
| TFA Location    | /opt/oracle.ahf/tfa                                    |
| Exachk Location | /opt/oracle.ahf/exachk                                 |
| Data Directory  | /depot/TFA_DATA/oracle.ahf/data                        |
| Repository      | /depot/TFA_DATA/oracle.ahf/data/repository             |
| Diag Directory  | /depot/TFA_DATA/oracle.ahf/data/node2/diag |
'-----------------+--------------------------------------------------------'


Starting exachk daemon from AHF ...

AHF install completed on node2

Installing AHF on Remote Nodes :

AHF will be installed on node1, Please wait.

Installing AHF on node1 :

[node1] Copying AHF Installer

[node1] Running AHF Installer

AHF binaries are available in /opt/oracle.ahf/bin

AHF is successfully installed

Moving /tmp/ahf_install_382363_2020_04_30-08_32_06.log to /depot/TFA_DATA/oracle.ahf/data/node2/diag/ahf/

       
 

2. If all good then you can run below command to check status.

       

[root@node2 TFA]# tfactl status

.---------------------------------------------------------------------------------------------------------.
| Host              | Status of TFA | PID   | Port | Version    | Build ID             | Inventory Status |
+-------------------+---------------+-------+------+------------+----------------------+------------------+
| node2             | RUNNING       |  1077 | 5000 | 20.1.2.0.0 | 20120020200403113404 | RUNNING          |
| node1             | RUNNING       | 28675 | 5000 | 20.1.2.0.0 | 20120020200403113404 | RUNNING          |
'-------------------+---------------+-------+------+------------+----------------------+------------------'

       
 

Issue:

       

 [root@node1 ssh]# tfactl status

.----------------------------------------------------------------------------------------------------------.
| Host              | Status of TFA | PID    | Port | Version    | Build ID             | Inventory Status |
+-------------------+---------------+--------+------+------------+----------------------+------------------+
| node1 | RUNNING       | 101416 | 5000 | 20.1.2.0.0 | 20120020200403113404 | STOPPED          |
| node2 | RUNNING       |  89841 | 5000 | 20.1.2.0.0 | 20120020200403113404 | RUNNING          |
'-------------------+---------------+--------+------+------------+----------------------+------------------'
[root@node1 ssh]#

       
 

After many round of troubleshooting we notice PermitRootLogin was not allowed.

We change below in /etc/ssh/sshd_config file in both node (node1 and node2):

PasswordAuthentication yes
PermitRootLogin yes

2. restart sshd service

service sshd restart

3. Uninstall TFA and Install it back
3. Uninstall TFA and Install it back

       

[root@node2 ssh]# tfactl uninstall
Starting AHF Uninstall
NOTE : Uninstalling does not return all the space used by the AHF repository
TFA-00104 Cannot establish connection with TFA Server. Please check TFA Certificates
TFA-00104 Cannot establish connection with TFA Server. Please check TFA Certificates
AHF will be uninstalled on:
node2
node1 node2

Do you want to continue with AHF uninstall ? [Y]|N :

Stopping AHF service on local node node2...
Stopping TFA Support Tools...


TFA-00002 Oracle Trace File Analyzer (TFA) is not running
Stopping exachk scheduler ...
Removing exachk cache discovery....
No exachk cache discovery found.





Removed exachk from inittab



Stopping and removing AHF in node1...
TFA-00002 Oracle Trace File Analyzer (TFA) is not running
Removing exachk cache discovery....
No exachk cache discovery found.





Removed exachk from inittab


Successfully uninstalled AHF on node node1
Removing AHF setup on node2:
Removing /etc/rc.d/rc0.d/K17init.tfa
Removing /etc/rc.d/rc1.d/K17init.tfa
Removing /etc/rc.d/rc2.d/K17init.tfa
Removing /etc/rc.d/rc4.d/K17init.tfa
Removing /etc/rc.d/rc6.d/K17init.tfa
Removing /etc/init.d/init.tfa...
Removing /opt/oracle.ahf/jre
Removing /opt/oracle.ahf/common
Removing /opt/oracle.ahf/bin
Removing /opt/oracle.ahf/python
Removing /opt/oracle.ahf/analyzer
Removing /opt/oracle.ahf/tfa
Removing /opt/oracle.ahf/ahf
Removing /opt/oracle.ahf/exachk
Removing /opt/oracle.ahf/data/node2
Removing /opt/oracle.ahf/install.properties

[root@node2 ssh]#

       
 

Oracle DB Connection with Sybase

Oracle DB Connect to Sybase database steps:

1. Install Oracle Gateway software in Oracle database

2. Configure Sybase server details

$ORACLE_HOME/dg4sybs/admin/initdg4sybs.ora

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Sybase
#
# HS init parameters
#

HS_FDS_CONNECT_INFO=<sybase_server_host_name>:<port>/<schema>
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_KEEP_REMOTE_COLUMN_SIZE = ALL
HS_NLS_LENGTH_SEMANTICS = CHAR
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15


3. add below entry into your listener file (if rac then grid home).

(SID_DESC =
        (SID_NAME = dg4sybs)
        (ORACLE_HOME = /u02/app/oracle/product/12.2.0/dbhome_2)
        (PROGRAM = dg4sybs)
)
 )

4. create tnsentry into ORACLE_HOME Which will connect to database

ORACLE_TO_SYBASE=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=<oracle db host name>)
         (PORT=1522)
      )
      (CONNECT_DATA=
         (SID=dg4sybs))
      (HS=OK))


5. create database link into oracle database

CREATE PUBLIC DATABASE LINK SYBASE_TO_ORACLE CONNECT TO "sybase_db_username" IDENTIFIED BY "sybase_db_password" USING 'ORACLE_TO_SYBASE';     

6. Test database connection

select * from dual@SYBASE_TO_ORACLE;

Oracle database dummy process (100 / 50 / 25 / 10 )

Hello Friends,

Here is a quick command to create multiple process in oracle database.


1) SQL Script - the script below will create session and hold for 10 minute

sqlplus -s / as sysdba <<EOF
select resource_name, current_utilization, max_utilization from v\$resource_limit where resource_name in ('processes','sessions');
exec DBMS_LOCK.Sleep( 600 );
EOF


2) Call SQL Script multiple time. (50 times)


nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh & nohup /tmp/jm1.sh &

RC-50013: Fatal: Instantiate driver did not complete successfully.

Oracle EBS 12.2.6 - DB Tire configuration error

WARNING: [AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
      <filename>  <phase>  <return code where appropriate>

  [APPLY PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /u01/app/oracle/tech_st/12.1.0/perl/bin/perl -I /u01/app/oracle/tech_st/12.1.0/perl/lib/5.14.1 -I /u01/app/oracle/tech_st/12.1.0/perl/lib/site_perl/5.14.1 -I /u01/app/oracle/tech_st/12.1.0/appsutil/perl /u01/app/oracle/tech_st/12.1.0/appsutil/clone
      ouicli.pl               INSTE8_APPLY       1



AutoConfig is exiting with status 1

WARNING: RC-50013: Fatal: Instantiate driver did not complete successfully.


Issue 1: Check the inventory location

Please delete existing files under oraInventory folder and retry the adcfgclone.

[root@rac1 E122V_rac1]# cat /etc/oraInst.loc
#inventory_loc=/u01/app/oraInventory
inventory_loc=/u01/app/oracle/oraInventory
inst_group=dba
[root@rac1 E122V_rac1]#


Issue 2: check the error in appsutil/log



[root@rac1 logs]# cat installActions2019-07-26_07-50-28AM.log
The file oraparam.ini could not be found at /u01/app/oracle/tech_st/12.1.0/oui/bin/oraparam.ini
Using paramFile: /u01/app/oracle/tech_st/12.1.0/oui/oraparam.ini


Checking swap space: 0 MB available, 500 MB required.    Failed <<<<

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation,
Exiting Oracle Universal Installer, log for this session can be found at /u01/app/oracle/oraInventory/logs/installActions2019-07-26_07-50-28AM.log
[root@rac1 logs]#

Oracle ASM extend Diskgroup

Hello,

here are the steps to drop and re-create the disk group

check the disk size (it's 50 gb)


[root@rac1 ~]# lsblk
NAME    MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
xvda    202:0    0   15G  0 disk
└─xvda1 202:1    0   15G  0 part /
xvdf    202:80   0   50G  0 disk
└─xvdf1 202:81   0   50G  0 part /u01
xvdg    202:96   0  200G  0 disk
└─xvdg1 202:97   0   50G  0 part
xvdh    202:112  0  200G  0 disk
└─xvdh1 202:113  0   50G  0 part
xvdi    202:128  0   50G  0 disk
└─xvdi1 202:129  0   50G  0 part
xvdj    202:144  0   50G  0 disk
└─xvdj1 202:145  0   50G  0 part
xvdk    202:160  0   16G  0 disk [SWAP]
xvdl    202:176  0  200G  0 disk
└─xvdl1 202:177  0  200G  0 part
xvdm    202:192  0  200G  0 disk
└─xvdm1 202:193  0  200G  0 part
xvdn    202:208  0  150G  0 disk
└─xvdn1 202:209  0  150G  0 part /u04
[root@rac1 ~]# parted /dev/xvdg

Disk size is 200gb but allocated size/partition size is 50gb only.

Step:
1. Drop the current disk group
2. re-create partition
3. update disk UUID into dev rule
4. create a disk group with new size.

1. Drop the current disk group

SQL> alter diskgroup data mount;

Diskgroup altered.

SQL> drop diskgroup data;

Diskgroup dropped.

## Check disk is empty now.

set linesize 180

col path for a50
select path,header_status from v$asm_disk;

PATH                                               HEADER_STATU
-------------------------------------------------- ------------
/dev/oracleasm/disk01                              FORMER
/dev/oracleasm/disk02                              FORMER
/dev/oracleasm/disk06                              MEMBER
/dev/oracleasm/disk04                              MEMBER
/dev/oracleasm/disk05                              MEMBER
/dev/oracleasm/disk03                              MEMBER

Note: ASM does not allow to drop disk group if it's not mounted.

2. re-create partition
#create partation
parted /dev/xvdm
mktable gpt
mkpart asmpart1 0% 100%
quit

After paration:

[root@rac1 ~]# lsblk

NAME    MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT

xvda    202:0    0   15G  0 disk

└─xvda1 202:1    0   15G  0 part /

xvdf    202:80   0   50G  0 disk

└─xvdf1 202:81   0   50G  0 part /u01

xvdg    202:96   0  200G  0 disk

└─xvdg1 202:97   0  200G  0 part

xvdh    202:112  0  200G  0 disk

└─xvdh1 202:113  0  200G  0 part

xvdi    202:128  0   50G  0 disk

└─xvdi1 202:129  0   50G  0 part

xvdj    202:144  0   50G  0 disk

└─xvdj1 202:145  0   50G  0 part

xvdk    202:160  0   16G  0 disk [SWAP]

xvdl    202:176  0  200G  0 disk

└─xvdl1 202:177  0  200G  0 part

xvdm    202:192  0  200G  0 disk

└─xvdm1 202:193  0  200G  0 part

xvdn    202:208  0  150G  0 disk

└─xvdn1 202:209  0  150G  0 part /u04

3. update disk UUID into dev rule

# get UUID one by one and create file

[root@rac1 ~]# udevadm info --query=property /dev/xvdg1 | grep ID_PART_ENTRY_UUID

ID_PART_ENTRY_UUID=ad4b9fef-a590-480f-baef-627b1ef773a4

[root@rac1 ~]#  udevadm info --query=property /dev/xvdh1 | grep ID_PART_ENTRY_UUID

ID_PART_ENTRY_UUID=a12f74db-fdda-491c-b65d-680da0581251

cat > /etc/udev/rules.d/99-oracle-asmdevices.rules <<EOF

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="ad4b9fef-a590-480f-baef-627b1ef773a4", SYMLINK+="oracleasm/disk01", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="a12f74db-fdda-491c-b65d-680da0581251", SYMLINK+="oracleasm/disk02", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="8fa30be3-244e-4f53-af2c-8d9db1492b58", SYMLINK+="oracleasm/disk03", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="4885524d-4b7a-48ee-aca3-2c90d804bf79", SYMLINK+="oracleasm/disk04", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="560d278a-22b6-4afe-9a3e-63d568b27f26", SYMLINK+="oracleasm/disk05", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="627edeac-5dbc-41b4-b36e-475277b43722", SYMLINK+="oracleasm/disk06", OWNER="oracle", GROUP="dba", MODE="0660"

EOF

## Validate that disks are visible

[root@rac1 ~]# ls -al /dev/oracleasm/*

lrwxrwxrwx. 1 root root 8 Jul 30 15:56 /dev/oracleasm/disk01 -> ../xvdg1

lrwxrwxrwx. 1 root root 8 Jul 30 15:56 /dev/oracleasm/disk02 -> ../xvdh1

lrwxrwxrwx. 1 root root 8 Jul 30 15:56 /dev/oracleasm/disk03 -> ../xvdi1

lrwxrwxrwx. 1 root root 8 Jul 30 15:56 /dev/oracleasm/disk04 -> ../xvdj1

lrwxrwxrwx. 1 root root 8 Jul 30 15:56 /dev/oracleasm/disk05 -> ../xvdl1

lrwxrwxrwx. 1 root root 8 Jul 30 15:56 /dev/oracleasm/disk06 -> ../xvdm1

4. create a disk group with new size.

 CREATE DISKGROUP RECO1 EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disk01','/dev/oracleasm/disk02';

# Check  diskgroup

[grid@rac1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    409596   219377                0          219377              0             N  DATA1/
MOUNTED  EXTERN  N         512   4096  1048576    409596   409542                0          409542              0             N  RECO1/
MOUNTED  NORMAL  N         512   4096  1048576    102396   100412                0           50206              0             N  REDO/

Note: I am droppping the diskgroup here so be c

Oracle Database Managing the Redo Log

List the current redo file and size:

set linesize 160
col MEMBER for a50
select a.GROUP#, a.THREAD#, a.BYTES/1024/1024 BYTES_MB, b.MEMBER from  V$LOG a, v$logfile b where a.GROUP# = b.GROUP#;


Add Redo log group:

ALTER DATABASE ADD LOGFILE GROUP 3 ('/diso3i/oradata/data/log03a.dbf','/diso3i/oradata/data/log03b.dbf') size 1000M;

Oracle DB Parameter remove

Hi DBA,

Below is the simple method to remove the parameter from spfile

-- Check parameter
orasmk@ebssitap:~$ strings $ORACLE_HOME/dbs/spfileEBSSMK.ora | grep optimizer_dynamic_sampling
*.optimizer_dynamic_sampling=2
orasmk@ebssitap:~$

-- remove parameter
alter system reset "optimizer_dynamic_sampling" scope=spfile sid='*';
alter system reset "__db_cache_size" scope=spfile sid='EBSSMK';


-- check in spfile
orasmk@ebssitap:~$ strings $ORACLE_HOME/dbs/spfileEBSSMK.ora | grep optimizer_dynamic_sampling
orasmk@ebssitap:~$


Below is the grep command to check the parameters in spfile

strings $ORACLE_HOME/dbs/spfileEBSSMK.ora | egrep '_always_anti_join|_always_semi_join|_complex_view_merging|_index_join_enabled|_kks_use_mutex_pin|_new_initial_join_orders|_optimizer_cost_based_transformation|_optimizer_cost_model|_optimizer_mode_force|_optimizer_undo_changes|_or_expand_nvl_predicate|_ordered_nested_loop|_push_join_predicate|_push_join_union_view|_shared_pool_reserved_min_alloc|_sortmerge_inequality_join_off|_sqlexec_progression_cost|_table_scan_cost_plus_one|_unnest_subquery|_use_column_stats_for_function|always_anti_join|always_semi_join|background_dump_dest|core_dump_dest|db_block_buffers|db_cache_size|db_file_multiblock_read_count|DRS_START|enqueue_resources|event|hash_area_size|java_pool_size|job_queue_interval|large_pool_size|max_enabled_roles|nls_language|optimizer_dynamic_sampling|optimizer_features_enable|optimizer_index_caching|optimizer_index_cost_adj|optimizer_max_permutations|optimizer_mode|optimizer_percent_parallel|parallel_instance_group|instance_groups|plsql_compiler_flags|plsql_native_library_dir|plsql_native_library_subdir_count|plsql_optimize_level|query_rewrite_enabled|rollback_segments|row_locking|sort_area_size|sql_trace|SQL_VERSION|timed_statistics|undo_retention|undo_suppress_errors|user_dump_dest'

Oracle RMAN Backup and Restore

Convert Gigabits Per Second to Gigabytes Per Hour
http://www.kylesconverter.com/data-bandwidth/gigabits-per-second-to-gigabytes-per-hour

copy backup from ASM to FS and FS to ASM
http://select-star-from.blogspot.com/2013/08/how-to-copy-files-from-asm-to.html
https://dbaclass.com/article/copy-asm-file-from-one-server-to-another-server/


run {
BACKUP AS BACKUPSET SECTION SIZE 100G INCREMENTAL LEVEL 0 DATABASE FILESPERSET 1 TAG 'bkup_weekly_L0' plus ARCHIVELOG;
backup spfile format '/zfssa/proddb/backup1/spfile_%d_%s_%T_dbid%I.rman';
backup current controlfile format '/zfssa/proddb/backup1/Controlfile_%d_%T_dbid%I_s%s_p%p';
}

Oracle Database archive Alert Log

Hello DBAs,

Here is the simple script which can be used for clear archive log on every Sunday. I know we should make it smart but this is for easy setup :)

Steps 1: create a shell script to do this operation


#!/usr/bin/sh
# File name : alert_log_archive.sh
LOGFILE=/u01/oracle/12.1.1/diag/rdbms/dev/DEV/trace/alert_DEV.log
DDATE=`date +"%Y%m%d"`
cp $LOGFILE $LOGFILE.$DDATE
cat /dev/null > $LOGFILE
touch $LOGFILE


Steps 2: schedule it in cronjob

30 23 * * 0 sh /home/oracle/alert_log_archive.sh

Please put your suggestion in a comment.

Oracle Database Archive Log Tables

This script will help you to query the oracle database and list how archive logs are generating.

Script name: archtab.sql

set line 120

set linesize 180

set pagesize 180

SELECT to_date(first_time) DAY,

to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",

to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",

to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",

to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",

to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",

to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",

to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",

to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",

to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",

to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",

to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",

to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",

to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",

to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",

to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",

to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",

to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",

to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",

to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",

to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",

to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",

to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",

to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",

to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"

from

v$log_history

where to_date(first_time) > sysdate - 8

GROUP by

to_char(first_time,'YYYY-MON-DD'), to_date(first_time)

order by to_date(first_time)

/

Oracle Database tablespace Query

Check Tablespace size

scriptname : tbsn.sql

set heading on

set linesize 180

set pagesize 100

col TABLESPACE_NAME for a25

col bytes_gb for 99,999,999.999

col FREE_bytes_gb for 99,999,999.999

col ACTUAL_USED_MB for 99,999,999.999

col FILE_USED_PTC for a8

col MAXbytes_gb for 99,999,999.999

col BAL_MAX_EXT_MB for 99,999,999.999

col max_ext_used_pt for a8

col AUTO_EXTEND for a3

set feedback off

select name from v$database;

SELECT a.tablespace_name

     , round(a.bytes_gb,3) bytes_gb

     , round(b.free_bytes_gb,3) free_bytes_gb

     , round((a.bytes_gb - b.free_bytes_gb),3) actual_used_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) || '%' file_used_ptc

     , round(a.maxbytes_gb,3) maxbytes_gb

     , round((a.maxbytes_gb - (a.bytes_gb - b.free_bytes_gb)),3) bal_max_ext_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.maxbytes_gb),3) || '%' max_ext_used_pt

     , decode(a.autoext,0,'No','Yes') auto_extend

  FROM (SELECT   dbf.tablespace_name

               , SUM (dbf.bytes)/1024/1024/1024 bytes_gb

               , SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024/1024 maxbytes_gb

               , sum (dbf.maxbytes) autoext

            FROM dba_data_files dbf

        GROUP BY tablespace_name) a

     , (SELECT   tablespace_name

               , SUM (BYTES)/1024/1024/1024 free_bytes_gb

            FROM dba_free_space

        GROUP BY tablespace_name) b

 WHERE a.tablespace_name = b.tablespace_name

-- and   round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) > 85

 order by round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) desc ;

set heading off

 SELECT a.tablespace_name

     , round(a.bytes_gb,3) bytes_gb

     , round(b.free_bytes_gb,3) free_bytes_gb

     , round((a.bytes_gb - b.free_bytes_gb),3) actual_used_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) || '%' file_used_ptc

     , round(a.maxbytes_gb,3) maxbytes_gb

     , round((a.maxbytes_gb - (a.bytes_gb - b.free_bytes_gb)),3) bal_max_ext_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.maxbytes_gb),3) || '%' max_ext_used_pt

     , decode(a.autoext,0,'No','Yes') auto_extend

  FROM (SELECT   dbf.tablespace_name

               , SUM (dbf.bytes)/1024/1024/1024 bytes_gb

               , SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024/1024 maxbytes_gb

               , sum (dbf.maxbytes) autoext

            FROM dba_temp_files dbf

        GROUP BY tablespace_name) a

     , (SELECT   tablespace_name

               , SUM (BYTES)/1024/1024/1024 free_bytes_gb

            FROM dba_temp_files

        GROUP BY tablespace_name) b

 WHERE a.tablespace_name = b.tablespace_name

-- and   round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) > 85

 order by round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) desc ;

==> OLD SQL

set linesize 120
set pagesize 100
col TABLESPACE_NAME for a25
col BYTES_MB for 99,999.999
col FREE_BYTES_MB for 99,999.999
col ACTUAL_USED_MB for 99,999.999
col FILE_USED_PTC for a8
col MAXBYTES_MB for 999,999.999
col BAL_MAX_EXT_MB for 999,999.999
col max_ext_used_pt for a8
col AUTO_EXTEND for a3
SELECT a.tablespace_name
     , round(a.bytes_mb,3) bytes_mb
     , round(b.free_bytes_mb,3) free_bytes_mb
     , round((a.bytes_mb - b.free_bytes_mb),3) actual_used_mb
     , round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) || '%' file_used_ptc
     , round(a.maxbytes_mb,3) maxbytes_mb
     , round((a.maxbytes_mb - (a.bytes_mb - b.free_bytes_mb)),3) bal_max_ext_mb
     , round(((a.bytes_mb - b.free_bytes_mb)*100/a.maxbytes_mb),3) || '%' max_ext_used_pt
     , decode(a.autoext,0,'No','Yes') Auto_extend
  FROM (SELECT   dbf.tablespace_name
               , SUM (dbf.bytes)/1024/1024 bytes_mb
               , SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024 maxbytes_mb
               , sum (dbf.maxbytes) autoext
            FROM dba_data_files dbf
        GROUP BY tablespace_name) a
     , (SELECT   tablespace_name
               , SUM (BYTES) / 1024 / 1024 free_bytes_mb
            FROM dba_free_space
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name
-- and   round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) > 85
 order by round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) desc ;


Get Datafile in Table Space


set linesize 120
col file_name for a60
select file_name,bytes/1024/1024 bytes,maxbytes/1024/1024 maxbytes,AUTOEXTENSIBLE from dba_data_files where tablespace_name = '&TB';

Temporary Tablespace

set linesize 120

col tablespace_name for a10

col file_name for a60

select tablespace_name,file_name,bytes/1024/1024 bytes,maxbytes/1024/1024 maxbytes,AUTOEXTENSIBLE from dba_temp_files order by 1;

Add datafile

alter tablespace APPS_UNDOTS1 add datafile '/db02/PROD/proddata/APPS_UNDOTS2.dbf' SIZE 1024M autoextend on NEXT 16M MAXSIZE 4096M;

Resize tablespace

ALTER DATABASE DATAFILE '/u02/oracle/PROD/db/apps_st/data/sysaux05.dbf' AUTOEXTEND ON NEXT 16M MAXSIZE 4096M;