How To Generate ASH (Active Session History) Report
To generate ASH report:
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
The report provides below areas:
1. Top User Events
2. Top Service/Module
3. Top SQL Command Types
4. Top Sessions
5. Top Blocking Sessions
6. Top DB Objects
7. Top Phases of Execution
8. Top PL/SQL Procedures
9. Top SQL With Top Row Sources
11. Complete list of SQL text
12. Activity Over Time
How To Generate Explain Plan In Oracle
1.Generating explain plan for a sql query:
We will generate the explain plan for the query "select * from test.qader_t1;"
LOADING THE EXPLAIN PLAN TO PLAN_TABLE
SQL> explain plan for select * from test.qader_t1;
DISPLAYING THE EXPLAIN PLAN
SQL> select * from table(dbms_xplan.display);
2. Explain plan for a sql_id from cursor
set lines 2000
set pagesize 2000
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
3. Explain plan of a sql_id from AWR:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));
Above will display the explain plan for all the plan_hash_value in AWR. If you wish to see the plan for a particular plan_hash_value.
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id',&plan_hash_value));
How to Stop and Start Oracle RAC Services on Single Node
1) Disable the automatic startup of the Oracle Clusterware software:
crsctl disable has
2) Stop the Oracle clusterware stack:
crsctl stop has
3) Once clusterware Services has been stopped we will restart those Services:
crsctl enable has
4) Enable the automatic startup of the Oracle Clusterware software:
crsctl start has
Check the status:
ps -ef|grep d.bin|wc -l
ps -ef|grep pmon
5) Start the Oracle clusterware stack:
crsctl start crs
6)Check the status of Oracle Clusterware Resources:
crsctl stat res -t
Long Running Sessions in Oracle
SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
(or)
set lines 300
col TARGET for a40
col SQL_ID for a20
select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS,SQL_ID from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING;
Above output you can further check the sql_id, sql_text and the wait event for which query is waiting
TO find out sql_id for the above sid:
SQL> select sql_id from v$session where sid='&SID';
To find sql text for the above sql_id:
SQL> select sql_fulltext from V$sql where sql_id='1uksqt2vzxbz5';
To find wait event of the query for which it is waiting for:
SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';
Flashback Query (AS OF) in Oracle Database
Create a test table with no data and check the current SCN and time.
create table flashback_mqm_test (id NUMBER(10));
SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
584777 2020-09-11 23:55:06
Add a row to the test table and commit the change.
INSERT INTO flashback_mqm_test (id) VALUES (1);
COMMIT;
If we check the contents of the table, we can see there is a single row.
SELECT COUNT(*) FROM flashback_mqm_test;
COUNT(*)
----------
1
The following two examples use the AS OF clause to query the table using the timestamp and SCN we captured before the row was inserted.
select count(*) from flashback_mqm_test AS OF TIMESTAMP TO_TIMESTAMP('2020-09-11 23:55:06', 'YYYY-MM-DD HH24:MI:SS');
COUNT(*)
----------
0
select count(*) from flashback_mqm_test AS OF SCN 584777;
COUNT(*)
----------
0
As we can see, the data in the table reflect the point in time specified in the AS OF clause.
There are a few things to consider when using flashback query.
-Flashback query is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the UNDO_RETENTION parameter.
-The flashback operation is performed by winding back the data using the undo information, like a consistent get. The time it takes to wind back the data depends on the number of changes that have happened, so flashback queries of volatile data that go back a long time can be slow.
-Flashback Data Archive (FDA) was introduced in Oracle 11g to allow you to protect UNDO information, allowing guaranteed flashback queries.
-Each table in a query can reference a different point in time.
-The AS OF clause can be included in DML and DDL statements.
Recover dropped tablespace using RMAN tablespace point in time recovery (TSPITR)
Step 1: Create tablespace
SQL> conn / as sysdba
Connected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/QTEST/system01.dbf
/u01/app/oracle/oradata/QTEST/sysaux01.dbf
/u01/app/oracle/oradata/QTEST/undotbs01.dbf
/u01/app/oracle/oradata/QTEST/users01.dbf
SQL> create tablespace testtbs datafile '/u01/app/oracle/oradata/QTEST/testtbs01.dbf' size 100m;
Tablespace created.
SQL> create user testtbs identified by testtbs default tablespace testtbs;
User created.
SQL> grant dba to testtbs;
Grant succeeded.
SQL> conn testtbs/testtbs;
Connected.
SQL> create table test(empname varchar2(20),city varchar2(20));
Table created.
SQL> insert into test values('qader','bahrain');
1 row created.
SQL> insert into test values('kadar','manama');
1 row created.
SQL> commit;
Step 2: Backup database Plus archivelog
[oracle@node2 ~]$ rman target sys/Welcome1
Recovery Manager: Release 12.1.0.2.0 - Production on Sat Aug 29 01:18:39 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: QTEST (DBID=2589001473)
RMAN> backup database plus archivelog;
Step 3: Note Current SCN
SQL> conn / as sysdba
Connected.
SQL> SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1744628
Step 4: Drop tablespace
SQL> drop tablespace testtbs including contents and datafiles;
Tablespace dropped.
Step 5: Create auxiliary & Recover tablespace using tablespace point in time recovery.
[oracle@node2 ~]$ rman target sys/Welcome1
Recovery Manager: Release 12.1.0.2.0 - Production on Sat Aug 29 01:29:27 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: QTEST (DBID=2589001473)
RMAN> run
{
recover tablespace testtbs
until scn 1744628
auxiliary destination '/u01/app/oracle/fast_recovery_area/QTEST'; ------> backup pieces location
}
initialization parameters used for automatic instance:
db_name=QTEST
db_unique_name=hsbr_pitr_QTEST
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1168M
processes=200
db_create_file_dest=/u01/app/oracle/fast_recovery_area/QTEST
log_archive_dest_1='location=/u01/app/oracle/fast_recovery_area/QTEST'
#No auxiliary parameter file used
starting up automatic instance QTEST
Oracle instance started
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 335545040 bytes
Database Buffers 872415232 bytes
Redo Buffers 13852672 bytes
Automatic instance created
List of tablespaces that have been dropped from the target database:
Tablespace TESTTBS
contents of Memory Script:
{
# set requested point in time
set until scn 1744628;
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 29-AUG-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/fast_recovery_area/QTEST/QTEST/controlfile/o1_mf_hnm1rzmd_.ctl
Finished restore at 29-AUG-20
contents of Memory Script:
{
# set requested point in time
"/u01/app/oracle/oradata/QTEST/testtbs01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3, 5;
switch clone datafile all;
}
executing Memory Script
renamed tempfile 1 to /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 29-AUG-20
archived log file name=/u01/app/oracle/fast_recovery_area/QTEST/archivelog/2020_08_29/o1_mf_1_37_hnm1rjny_.arc thread=1 sequence=37
media recovery complete, elapsed time: 00:00:00
Finished recover at 29-AUG-20
database opened
contents of Memory Script:
{
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_hsbr_sAEw":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_hsbr_sAEw" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_hsbr_sAEw is:
EXPDP> /u01/app/oracle/fast_recovery_area/QTEST/tspitr_hsbr_12180.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TESTTBS:
EXPDP> /u01/app/oracle/oradata/QTEST/testtbs01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_hsbr_sAEw" successfully completed at Sat Aug 29 04:09:12 2020 elapsed 0 00:01:11
Export completed
contents of Memory Script:
# shutdown clone before import
shutdown clone abort
executing Memory Script
Oracle instance shut down
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_hsbr_rhej" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_hsbr_rhej":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_hsbr_rhej" successfully completed at Sat Aug 29 04:09:53 2020 elapsed 0 00:00:32
Import completed
contents of Memory Script:
auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/onlinelog/o1_mf_1_hnm1sp84_.log deleted
auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_sysaux_hnm1s60l_.dbf deleted
auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_undotbs1_hnm1s60s_.dbf deleted
auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/datafile/o1_mf_system_hnm1s60g_.dbf deleted
auxiliary instance file /u01/app/oracle/fast_recovery_area/QTEST/QTEST/controlfile/o1_mf_hnm1rzmd_.ctl deleted
auxiliary instance file tspitr_hsbr_12180.dmp deleted
Finished recover at 29-AUG-20
