DB Admin: Database size in GBs

Posted on by

 
 SET SERVEROUTPUT ON
declare
total_size_b number;
tfree_size_b number;
tused_size_b number;
begin
dbms_output.enable(100000);
select Sum(bytes) into total_size_b
from dba_data_files;
select Sum(bytes) into tfree_size_b
from dba_free_space;
select Sum(bytes) into tused_size_b
from dba_segments;
dbms_output.put_line('Total:' || TO_CHAR(Round(total_size_b/1024/1024/1024, 2), '999,999.00') || ' GB');
dbms_output.put_line('Free: ' || TO_CHAR(Round(tfree_size_b/1024/1024/1024, 2), '999,999.00') || ' GB');
dbms_output.put_line('Used: ' || TO_CHAR(Round(tused_size_b/1024/1024/1024, 2), '999,999.00') || ' GB');
end;
/
SET SERVEROUTPUT ON

Sample output:

DB Admin: Database size in TBs

Posted on by

SET SERVEROUTPUT ON
declare
total_size_b number;
tfree_size_b number;
tused_size_b number;
begin
dbms_output.enable(100000);
select
Sum(bytes) into total_size_b
from
dba_data_files;
select
Sum(bytes) into tfree_size_b
from
dba_free_space;
select
Sum(bytes) into tused_size_b
from
dba_segments;
dbms_output.put_line('Total:' || TO_CHAR(Round(total_size_b/1024/1024/1024/1024, 2), '999,999.00') || ' TB');
dbms_output.put_line('Free: ' || TO_CHAR(Round(tfree_size_b/1024/1024/1024/1024, 2), '999,999.00') || ' TB');
dbms_output.put_line('Used: ' || TO_CHAR(Round(tused_size_b/1024/1024/1024/1024, 2), '999,999.00') || ' TB');
end;
/
SET SERVEROUTPUT OFF

Database: Oracle: Tablesapces size(MB), free(MB) % used and %free

Posted on by

 set pagesize 150
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

Note: Least free % tablespace will 1st in output. Also it include temp tablespace/s.

AWR Reports in Oracle 12c.

Posted on by

How to generate AWR in 12.1?

AWR Snapshots and Reports can be created only at the CDB level.

How to generate AWR in 12.2 and later?

See the link in Oracle Support.

Fusion Middleware environment start and stop steps

Posted on by

1. Steps, in order, to start Fusion Middleware environment.

1. Start the database-based repository i.e. start the database.
set the correct ORACLE_HOME and ORACLE_SID
Start the Net Listener:
$ORACLE_HOME/bin/lsnrctl start
Start the database instance:
$ORACLE_HOME/bin/sqlplus SQL> connect SYS as SYSDBA SQL> startup
2. Start the Oracle WebLogic Server Administration Server.
You can start/stop WebLogic Server Administration Servers using the WLST command line or a script.
$DOMAIN_HOME/bin/startWebLogic.sh <

Note: While you start/stop also start/stop the processes running in the Administration Server including the WebLogic Server Administration Console and Fusion Middleware Control.

3. Ensure Node Manager is running. Below is the script to stat it.
$WLS_HOME/server/bin/startNodeManager.sh
OR
$DOMAIN_HOME/bin/startNodeManager.sh
Note: stopNodeManager.sh can be used to stop it.

4. Start Oracle Identity Management system components.
Set $ORACLE_HOME and $ORACLE_INSTANCE environment for Identity Management components.
Start/stop OPMN and all system components:
$ORACLE_INSTANCE/bin/opmnctl startall

Steps, in order, to stop Fusion Middleware environment

2. Steps, in order, to stop Fusion Middleware environment

1. Stop system components as Oracle HTTP Server etc.
Note: You can stop them in any order.
Set $ORACLE_HOME and $ORACLE_INSTANCE
$ORACLE_INSTANCE/bin/opmnctl stopall

To stop Oracle Management Agent, use the following command:

opmnctl stopproc ias-component=EMAGENT
2. Stop WebLogic Server Managed Servers.
Note: Any applications deployed to the server are also stopped.
$DOMAIN_HOME/bin/startManagedWebLogic.sh
managed_server_name admin_url

3. Stop Oracle Identity Management components.
set $ORACLE_HOME environment variable to the Oracle home for the Identity Management components.
$ORACLE_INSTANCE/bin/opmnctl stopall

4.Stop the Administration Server.
You can start Server Administration Servers using the WLST command line or a script.

$DOMAIN_HOME/bin/bin/stopWebLogic.sh

Note: While you start/stop also start/stop the processes running in the Administration Server including the WebLogic Server Administration Console and Fusion Middleware Control.

5. Stop the database.
Set $ORACLE_HOME and $ORACLE_INSTANCE
connect SYS as SYSDBA SQL> shutdown immediate ;

ASM Disks performance metric

Posted on by

ASM Disks performance metric

SET ECHO off
SET FEEDBACK 8
SET HEADING ON
SET LINESIZE 230
SET PAGESIZE 300
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a20 HEAD ‘Disk Group Name’
COLUMN disk_path FORMAT a20 HEAD ‘Disk Path’
COLUMN reads FORMAT 999,999,999,999 HEAD ‘Reads’
COLUMN writes FORMAT 999,999,999,999 HEAD ‘Writes’
COLUMN read_errs FORMAT 999,999,999 HEAD ‘Read|Errors’
COLUMN write_errs FORMAT 999,999,999 HEAD ‘Write|Errors’
COLUMN read_time FORMAT 999,999,999,999 HEAD ‘Read|Time’
COLUMN write_time FORMAT 999,999,999,999 HEAD ‘Write|Time’
COLUMN bytes_read FORMAT 999,999,999,999,999 HEAD ‘Bytes|Read’
COLUMN bytes_written FORMAT 999,999,999,999,999 HEAD ‘Bytes|Written’
BREAK ON report ON disk_group_name SKIP 2
COMPUTE sum LABEL “” OF reads writes read_errs write_errs read_time write_time bytes_read bytes_written ON disk_group_name
COMPUTE sum LABEL “Grand Total: ” OF reads writes read_errs write_errs read_time write_time bytes_read bytes_written ON report
SELECT
a.name disk_group_name
, b.path disk_path
, b.reads reads
, b.writes writes
, b.read_errs read_errs
, b.write_errs write_errs
, b.read_time read_time
, b.write_time write_time
, b.bytes_read bytes_read
, b.bytes_written bytes_written
FROM v$asm_diskgroup a JOIN v$asm_disk b USING (group_number)
ORDER BY a.name ;

Fusion Middleware environment start and stop steps

Posted on by

Steps to start and stop Fusion Middleware environment

1. Steps, in order, to start Fusion Middleware environment
2. Steps, in order, to stop Fusion Middleware environment

Starting an Oracle Fusion Middleware Environment

1. Steps, in order, to start Fusion Middleware environment.

Note: While you start/stop also start/stop the processes running in the Administration Server including the WebLogic Server Administration Console and Fusion Middleware Control.

Steps, in order, to stop Fusion Middleware environment

2. Steps, in order, to stop Fusion Middleware environment

1. Stop system components as Oracle HTTP Server etc.
Note: You can stop them in any order.
Set $ORACLE_HOME and $ORACLE_INSTANCE
$ORACLE_INSTANCE/bin/opmnctl stopall

To stop Oracle Management Agent, use the following command:

3. Stop Oracle Identity Management components.
set $ORACLE_HOME environment variable to the Oracle home for the Identity Management components.
$ORACLE_INSTANCE/bin/opmnctl stopall

4.Stop the Administration Server.
You can start Server Administration Servers using the WLST command line or a script.

$DOMAIN_HOME/bin/bin/stopWebLogic.sh

Note: While you start/stop also start/stop the processes running in the Administration Server including the WebLogic Server Administration Console and Fusion Middleware Control.

5. Stop the database.
Set $ORACLE_HOME and $ORACLE_INSTANCE
connect SYS as SYSDBA SQL> shutdown immediate ;

Oracle: RAC Database stop and start steps

Posted on by

Welcome to WordPress. This is your first post. Edit or delete it, then start writing!

Steps to Shutdown/Start RAC Database

1. Shutdown Oracle Home process accessing database.
2. Shutdown RAC Database Instances on all nodes.
3. Shutdown All ASM instances from all nodes.
4. Shutdown Node applications running on nodes.
5. Shut down the Oracle Cluster ware or CRS.

Note:
Starting steps are from 5 to 1(means which stopped last should be started 1st).