Gather Statistics Commands

Gather Statistics Commands

1. Gather dictionary stats:


EXEC DBMS_STATS.gather_dictionary_stats;
It gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and other internal schemas.


2. Gather fixed object stats:


EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
Fixed object means gv$ or v$views


3. Gather full database stats:


EXEC DBMS_STATS.gather_database_stats;

-- With estimate_percent to 15 percent or any other value , if the db size very huge.

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

-- With auto sample size and parallel degree

EXEC DBMS_STATS.gather_database_stats(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);


4. Gather schema statistics:


EXEC DBMS_STATS.gather_schema_stats('QRUM');

EXEC DBMS_STATS.gather_schema_stats('QRUM', estimate_percent => 25);

EXEC DBMS_STATS.gather_schema_stats('QRUM', estimate_percent => 100, cascade => TRUE);

-- STATS WITH AUTO ESTIMATION and degree 8

exec dbms_stats.gather_schema_stats( ownname => 'QRUM',method_opt => 'FOR ALL COLUMNS SIZE 1',
granularity => 'ALL', degree => 8, cascade => TRUE,
estimate_percent=>dbms_stats.auto_sample_size);


5. Gather table statistics: 


EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP');
EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP', estimate_percent => 15, cascade => TRUE);

exec DBMS_STATS.GATHER_TABLE_STATS  (ownname => 'DBACLASS' , tabname => 'EMP',cascade => true,
method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 8);

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'DBACLASS' , tabname => 'EMP',
cascade => true, method_opt=>'FOR ALL COLUMNS SIZE 1', granularity => 'ALL', degree => 8);


6. Gather stats for single partition of a table:


BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'TEST', --- TABLE NAME
partname => 'TEST_JAN2016' --- PARTITOIN NAME
method_opt=>'for all indexed columns size 1',
GRANULARITY => 'APPROX_GLOBAL AND PARTITION',
degree => 8);
END;
/


7. Lock/unlock statistics:


-- Lock stats of a schema:
EXEC DBMS_STATS.lock_schema_stats('DBACLASS');

-- Lock stats of a table:
EXEC DBMS_STATS.lock_table_stats('DBACLASS', 'EMP');

-- Lock stats of a partition:
EXEC DBMS_STATS.lock_partition_stats('DBACLASS', 'EMP', 'EMP');

-- unlock stats of a schema:

EXEC DBMS_STATS.unlock_schema_stats('DBACLASS');
-- unlock stats of a table:

EXEC DBMS_STATS.unlock_table_stats('DBACLASS', 'DBACLASS');
--unlock stats of a partition:

EXEC DBMS_STATS.unlock_partition_stats('DBACLASS', 'EMP', 'TEST_JAN2016');

--- check stats status:

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';


8 . Delete statistics:


-- Delete complete db statistics:
EXEC DBMS_STATS.delete_database_stats;

-- Delete schema statistics:
EXEC DBMS_STATS.delete_schema_stats('DBACLASS');

-- Delete table statistics:
EXEC DBMS_STATS.delete_table_stats('DBACLASS', 'EMP');

-- Delete column statistics:
EXEC DBMS_STATS.delete_column_stats('DBACLASS', 'EMP', 'EMPNO');

-- Delete index statistics:

EXEC DBMS_STATS.delete_index_stats('DBACLASS', 'EMP_PK');

-- Delete dictionary statistics:
EXEC DBMS_STATS.delete_dictionary_stats;


-- Delete fixed object statistics:

exec dbms_stats.delete_fixed_objects_stats;

-- Delete system statistics:

exec dbms_stats.delete_system_stats('STAT_TAB');


8. Setting statistics preference:


-- View preference details for the database:

SELECT dbms_stats.get_prefs('PUBLISH') EST_PCT FROM dual;

-- View Publish preference for table


-- View Publish preference for schema:

select dbms_stats.get_prefs('PUBLISH', 'SCOTT') from dual

-- View preference details for table

select dbms_stats.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'PUBLISH') FROM DUAL;
select  DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'INCREMENTAL') FROM DUAL;
select  DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'GRANULARITY') FROM DUAL;
select  DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'STALE_PERCENT')  FROM DUAL;
select   DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'ESTIMATE_PERCENT')  FROM DUAL;
select   DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'DEGREE')  FROM DUAL;

-- Set table  preferences

exec dbms_stats.set_table_prefs('DBACLASS','EMP','PUBLISH','FALSE');
exec dbms_stats.set_table_prefs('DBACLASS','EMP','ESTIMATE_PERCENT','20');
exec dbms_stats.set_table_prefs('DBACLASS','EMP','DEGREE','8');

-- Set schema preferences:

exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','PUBLISH','FALSE');
exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','ESTIMATE_PERCENT','20');
exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','CASCADE','TRUE');

-- Set database preference:

exec dbms_stats.set_database_prefs('PUBLISH', 'TRUE');
exec dbms_stats.set_database_prefs('DEGREE', '16');

-- Set global preference:

exec dbms_stats.set_global_prefs('PUBLISH', 'TRUE');
exec dbms_stats.set_global_prefs('DEGREE', '16');


9 . Deleting preferences:


-- Deleting schema preference:

exec dbms_stats.delete_schema_prefs('DBACLASS', 'DEGREE');
exec dbms_stats.delete_schema_prefs('DBACLASS', 'CASCADE');


-- Delete database preference:
exec dbms_stats.delete_database_prefs('ESTIMATE_PERCENT', FALSE);
exec dbms_stats.delete_database_prefs('DEGREE', FALSE);


10 . Publish pending statistics:


-- For schema DBACLASS
exec dbms_stats.publish_pending_stats('DBACLASS',null);

-- For table DBACLASS.EMP
EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('DBACLASS','EMP');


11. Delete pending statistics:


-- for table DBACLASS.EMP
exec dbms_stats.delete_pending_stats('DBACLASS', 'EMP');

-- For schema DBACLASS
exec dbms_stats.delete_pending_stats('DBACLASS', null);


12. Upgrade stats table:


----- If we are importing stats table from higher version to lower version,
then before importing in the database, we need to upgrade the stats table.


EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(OWNNAME =>'RAJ',STATTAB =>'STAT_TEST');


13. View/modify statistics retention period:


-- View current stats retention

select dbms_stats.get_stats_history_retention from dual;

-- Modify the stats retention

exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60);


14. create stats table:


--- Create staging table to store the statistics data

exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS');


15. Export stats data:


-- Export full database stats to a table SCOTT.STAT_BACKUP

exec dbms_stats.export_database_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export stats for table DBACLASS.EMP to a stats table SCOTT.STAT_BACKUP

exec dbms_stats.export_table_stats(ownname=>'DBACLASS', tabname=>'EMP', statown =>'SCOTT',stattab=>'STAT_BACKUP', cascade=>true);

-- Export stats for schema DBACLASS to a stats table SCOTT.STAT_BACKUP

exec dbms_stats.export_schema_stats(ownname=>'DBACLASS', statown =>'SCOTT' , stattab=>'STAT_BACKUP');

-- Export fixed object stats to table SCOTT.STAT_BACKUP

exec dbms_stats.export_fixed_objects_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export dictionary stats to table SCOTT.STAT_BACKUP

exec dbms_stats.export_dictionary_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export stats for index DBACLAS.EMP_UK1 to SCOTT.STAT_BACKUP table

exec dbms_stats.export_index_stats(ownname=>'DBACLASS', indname=>'EMP_UK1', statown =>'SCOTT',stattab=>'STAT_BACKUP')


16. Import stats table data:


-- Import full database stats from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_database_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import stats for table DBACLASS.EMP from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_table_stats(ownname=>'DBACLASS', tabname=>'EMP', statown =>'SCOTT',stattab=>'STAT_BACKUP', cascade=>true);

-- Import stats for schema DBACLASS from  stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_schema_stats(ownname=>'DBACLASS', statown =>'SCOTT' , stattab=>'STAT_BACKUP');

-- Import fixed object stats from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_fixed_objects_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import dictionary stats from  table SCOTT.STAT_BACKUP

exec dbms_stats.import_dictionary_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import stats for index DBACLAS.EMP_UK1 from  SCOTT.STAT_BACKUP table

exec dbms_stats.import_index_stats(ownname=>'DBACLASS', indname=>'EMP_UK1', statown =>'SCOTT',stattab=>'STAT_BACKUP');


17 . Few stats related sql queries:


-- Check stale stats for table:

select owner,table_name,STALE_STATS from dba_tab_statistics where owner='&SCHEMA_NAME' and table_name='&TABLE_NAME';

--Check stale stats for index:

select owner,INDEX_NAME,TABLE_NAME from DBA_IND_STATISTICS where owner='&SCHEMA_NAME' and index_name='&INDEX_NAME';

-- For getting history of TABLE statistics
setlines 200
col owner for a12
col table_name for a21
select owner,TABLE_NAME,STATS_UPDATE_TIME from dba_tab_stats_history where table_name='&TABLE_NAME';

-- Space used to store statistic data in SYSAUX tablespace:

SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%';

-- Check whether table stats locked or not:

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

Oracle Enterprise Manager 13c Default Ports

Oracle Enterprise Manager 13c Default Ports

Enable archivelog mode in RAC

Enable archivelog mode in RAC

1. stop the database service.
srvctl stop database -d MAPROD

2. start the database in mount state.
srvctl start database -d MAPROD -o mount

3. enable archive log mode.
alter database archivelog;

4. Restart the database service (using srvctl)
srvctl stop database -d MAPROD
srvctl start database -d MAPROD

Database Corruptions - Physical & Logical Corruptions

Database Corruptions - Physical & Logical Corruptions

--logical corruptions (table level):

SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE;

--physical and logical corruptions through RMAN:

RMAN> validate database;

--logical corruptions inside the database:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

--logical corruption for datafile

RMAN> validate datafile 4; (block level)

--physical corruptions at os level

[oracle@OEL-11g ~]$ dbv

DBVERIFY: Release 11.2.0.3.0 - Production on Thu Oct 8 09:33:10 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
FILE        File to Verify                 (NONE)
START       Start Block                    (First Block of File)
END         End Block                      (Last Block of File)
BLOCKSIZE   Logical Block Size             (8192)
LOGFILE     Output Log                     (NONE)
FEEDBACK    Display Progress               (0)
PARFILE     Parameter File                 (NONE)
USERID      Username/Password              (NONE)
SEGMENT_ID  Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN    Highest Block SCN To Verify    (NONE)
            (scn_wrap.scn_base OR scn)         
[oracle@OEL-11g ~]$ dbv file=/oradata/ORADB/users01.dbf

DBVERIFY: Release 11.2.0.3.0 - Production on Thu Oct 8 09:33:32 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /oradata/ORADB/users01.dbf
DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 14
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 13
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 596
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 17
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1022004 (0.1022004)

RMAN provides a data recovery advisory tool logical and physical corruptions:

RMAN> list failure;
RMAN> advise failure;
RMAN> repair failure;

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
502        HIGH     OPEN      08-OCT-15     One or more non-system datafiles are corrupt

RMAN>

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
502        HIGH     OPEN      08-OCT-15 

One or more non-system datafiles are corrupt analyzing automatic repair options; this may take some time using channel ORA_DISK_1
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 4
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /oracle/base/diag/rdbms/oradb/ORADB/hm/reco_1379785849.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /oracle/base/diag/rdbms/oradb/ORADB/hm/reco_1379785849.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

sql statement: alter database datafile 4 offline

Starting restore at 08-OCT-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /oradata/ORADB/users01.dbf
channel ORA_DISK_1: reading from backup piece /oradata/fra/ORADB/backupset/2015_10_07/o1_mf_nnnd0_TAG20151007T093119_c1968k0o_.bkp
channel ORA_DISK_1: piece handle=/oradata/fra/ORADB/backupset/2015_10_07/o1_mf_nnnd0_TAG20151007T093119_c1968k0o_.bkp tag=TAG20151007T093119
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-OCT-15

Starting recover at 08-OCT-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /oradata/fra/arch/1_4_892373901.dbf
archived log for thread 1 with sequence 5 is already on disk as file /oradata/fra/arch/1_5_892373901.dbf
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_DISK_1: reading from backup piece /oradata/fra/ORADB/backupset/2015_10_07/o1_mf_annnn_TAG20151007T093251_c196ccmm_.bkp
channel ORA_DISK_1: piece handle=/oradata/fra/ORADB/backupset/2015_10_07/o1_mf_annnn_TAG20151007T093251_c196ccmm_.bkp tag=TAG20151007T093251
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/oradata/fra/arch/1_3_892373901.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-OCT-15

sql statement: alter database datafile 4 online
repair failure complete

Query to check corruptions:


SQL> SELECT DISTINCT owner, segment_name FROM  v$database_block_corruption dbc JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1 ORDER BY 1,2;

Monitoring a session activity

Monitoring a session activity

Scenario:

There is an application, in which there are 100 users and for this 100 users, consider there is 1 tab/page. After clicking a tab in the application all 99 users are able get the results out of the tab as quick as possible, but only 1% whenever he clicks on the tab it will take lot of time to load the page, and if all the tabs are working fine thenonly 1 tab when he actually clicks if it is taking long time defintely that user raised a issue to the support team and it might reach to database team to look into it.

Now you have to analyze the 1% user, whenever he clicking the tab what is actual happening in the database.

SQL> select sid,serial#, from v$session where username='SCOTT';

SID    SERIAL#
---------- ----------
24    15

SQL> select s.sid,s.sql_id,p.spid from v$session s, v$process p where s.sid=24 and s.paddr=p.addr; 

SIDSQL_ID SPID
----------------------
24 2472

SQL> !ps -ef | grep 2472
oracle    2472     1  0 09:20 ?        00:00:00 oracleORADB (LOCAL=NO)
oracle    2490  2257  0 09:27 pts/0    00:00:00 /bin/bash -c ps -ef | grep 2472
oracle    2492  2490  0 09:27 pts/0    00:00:00 grep 2472

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>24, serial#=>15, sql_trace=>TRUE); --- To enable trace

SQL> select * from emp;
SQL> select * from dept;
SQL> select * from salgrade;

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>24, serial#=>15, sql_trace=>FALSE); --- To disable trace

SQL> show parameter user

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_users      integer 0
parallel_adaptive_multi_user    boolean TRUE
redo_transport_user      string
user_dump_dest      string /oracle/base/diag/rdbms/oradb/QADB/trace

[oracle@OEL-11g trace]$ ls -ltr *2472*
-rw-r----- 1 oracle oinstall   315 Oct  2 09:33 QADB_ora_2472.trm
-rw-r----- 1 oracle oinstall 23582 Oct  2 09:33 QADB_ora_2472.trc

[oracle@OEL-11g trace]$ tkprof ORADB_ora_2472.trc /home/oracle/sql_trace.oitut sys =no

Note: If you choose sys=no it will not capture sys operations.

-rw-r--r-- 1 oracle oinstall 5902 Oct  1 09:38 ctl.bkp
-rw-r--r-- 1 oracle oinstall 5943 Oct  2 09:37 sql_trace.out
[oracle@OEL-11g ~]$
[oracle@OEL-11g ~]$ more sql_trace.out

TKPROF: Release 11.2.0.3.0 - Development on Fri Oct 2 09:37:59 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: ORADB_ora_2472.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 3154rqzb8xudy Plan Hash: 3383998547

select * from emp;


--More--(13%)
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.05          4         58          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01          2          3          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.07          6         61          0           4

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         4          4          4  TABLE ACCESS FULL DEPT (cr=3 pr=2 pw=0 time=13922 us cost=3 size=80 c
ard=4)

********************************************************************************

SQL ID: a2dk8bdn0ujx7 Plan Hash: 3956160932

select * from dept;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0         73          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          6          3          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          6         76          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        14         14         14  TABLE ACCESS FULL EMP (cr=3 pr=6 pw=0 time=2150 us cost=3 size=532 ca
rd=14)

********************************************************************************

SQL ID: 30p00zk91247c Plan Hash: 2489195056

select * from salgrade;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          6          3          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          6          3          0           5

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         5          5          5  TABLE ACCESS FULL SALGRADE (cr=3 pr=6 pw=0 time=2214 us cost=3 size=5
0 card=5)

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.01       0.06          4        131          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.01         14          9          0          23
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.01       0.08         18        140          0          23

Misses in library cache during parse: 3


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute     51      0.00       0.00          0          0          0           0
Fetch       73      0.00       0.02          4        165          0          51
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      124      0.00       0.02          4        165          0          51


Misses in library cache during parse: 0

    3  user  SQL statements in session.
   13  internal SQL statements in session.
   16  SQL statements in session.
********************************************************************************
Trace file: ORADB_ora_2472.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
      13  internal SQL statements in trace file.
      16  SQL statements in trace file.
      16  unique SQL statements in trace file.
     283  lines in trace file.
       8  elapsed seconds in trace file.

Database triggers for different notifications

Database triggers for different notifications

--shutdown trigger
--startup trigger
--logon trigger
--logoff trigger
--server error trigger


Create a table to store the trigger information:
create table trigger_table (database_name varchar2(30), event_name varchar2(20), event_time date, triggered_by_user varchar2(30));

Set the date format a session level:
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'

--shutdown

create or replace trigger log_shutdown
before shutdown on database
begin
insert into trigger_table (database_name, event_name, event_time, triggered_by_user)
values ('QADER', 'SHUTDOWN INITIATED', sysdate, user);
commit;
end;
/

--startup

create or replace trigger log_startup
after startup on database
begin
insert into trigger_table (database_name, event_name, event_time, triggered_by_user)
values ('QADER','STARTUP INITIATED',sysdate,user);
commit;
end;
/

--logon

create table LOGON_table (login_date date, user_name varchar2(10), status varchar2(10));

create or replace trigger logon_trigger
after logon on database
begin
insert into logon_table
values
(SYSDATE, USER, 'logged_in');
commit;
end logon_trigger;
/

--logout

create or replace trigger log_out
before logoff on database
begin
insert into logon_table
values
(SYSDATE, USER, 'logged_out');
commit;
end logon_trigger;
/

--logon_failure

create or replace trigger logon_failures
after servererror on database
begin
if (IS_SERVERERROR(1017)) THEN
INSERT INTO logon_table
(login_date, user_name, status)
values
(sysdate, sys_context('USERENV','AUTHENTICATED_IDENTITY'),'ORA-01017');
END IF;
COMMIT;
END logon_failures;
/

Datapump Activities

Datapump Activities

-Take refresh in the same database and from the same schema
-Take refresh in the same database but different schema
-Take refresh in the same database into different schema with different name
-Take backup activity and restoration back the same table.
-Take refresh to the remote database, to the same schema with different name.
-Multiple table backups from multiple schemas.
-Restore specific set of tables from the multiple tables backup

select segment_name, bytes/1024, owner from dba_segments where segment_name='DEPT' and owner='SCOTT';
select * from dba_directories;
mkdir -p /u01/oradata/dpbkps
create or replace directory DPBKPS as '/u01/oradata/dpbkps';

--Take the table dept backup from scott schema and refresh to the same schema when requested later.

expdp dumpfile=exp_scott.dept.dmp logfile=scott.dept.log directory=DPBKPS tables=scott.dept compression=all

Note: compression will include additional load on cpu, and it will take more time compare to normal.

You can drop the existing table or used table_exists_action command.

impdp directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log table_exists_action=replace/truncate/append

--Table refresh in the same database but different schema

impdp directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log remap_schema=SCOTT:SYSTEM

--Take refresh in the same database into different schema with different name

select table_name from dba_tables where table_name='DEPT_TMP';

impdp system/manager directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log remap_schema=SCOTT:SYSTEM remap_table=DEPT.DEPT_TEMP

--Take refresh to the remote database, to the same schema with different name.

scp the export dumpfile to target location:

impdp system/manager directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log table_exists_action=replace

--Multiple table backups from multiple schemas.

check the size of all tables.

select sum(bytes/1024) from dba_segments where segment_name in ('DEPT','DEPT_TEMP','EMP') and owner in ('SCOTT','SYSTEM');

expdp directory=DPBKPS dumpfile=expdp_multi_tables.dmp logfile=expdp_multi_tables.log tables=scott.dept,scott.emp,system.dept,system.dept_temp

impdp directory=DPBKPS dumpfile=
expdp_multi_tables.dmp logfile=expdp_multi_tables.log tables=system.dept table_exists_action=replace

-A schema backup when required
-Restore the same schema on the same database when requested
-A schema refresh from PROD to NON-PROD environment.
-A schema backup when requested and restore only set of tables from the backup dump.
-Taking multiple schemas backup backup and only restore one schema to the database or only one table of one schema to restore.

--A schema backup when required
Take the backup of SCOTT schema on MYDB database.

check the schema size:
select sum(byes/1024/1024) from dba_segments where owner='SCOTT';

check the directory location and its space:
select * from dba_directories;

expdp directory=dpbkps dumpfile=expdp_scott.dmp logfile=expdp_scott.log compression=all schema=SCOTT

Note: compression option will take longer time to export the backup.

expdp directory=dpbkps dumpfile=expdp_scott_%U.dmp logfile=expdp_scott.log compression=all schema=SCOTT parallel=2

--Restore the same schema on the same database when requested

drop the user if it is already available using cascade option.

impdp directory=dpbkp dumpfile=exp_scott_%U.dmp logfile=exp_scott.log parallel=2

--How to take DDL's backup only ?

expdp directory=dpbkps dumpfile=expdp_scott.dmp logfile=expdp_scott.log compression=all schema=SCOTT content=metadata_only

--A schema backup when requested and restore only set of tables from the backup dump.

impdp directory=dpbkp dumpfile=exp_scott_%U.dmp logfile=impdp_scott.dept.log parallel=2 tables=scott.dept table_exist_action=replace

--Taking multiple schemas backup backup and only restore one schema to the database or only one table of one schema to restore.

check the schema size:
select sum(byes/1024/1024) from dba_segments where owner in('SCOTT','OUTLN');

expdp directory=dpbkps dumpfile=expd_multi_%U.dmp logfile=exp_multi.log compression=all parallel=2 schemas=scott,outln

impdp directory=dpbkps dumpfile=exp_multi_%U.dmp logfile=impdp_multi.log parallel=2 tables=outln.ol$ table_exists_action=replace

Flashback Table In Oracle

Flashback Table In Oracle

-we can recover a table to a specific timestamp without downtime.
-we can flashback to the original table.
-we can flashback to a new table
-we can flashback query to identify if undo segments have the old data.

flashback table is enabled by-default.

example:

insert into scott.dept values(&dept,'&ename','&loc');
commit;
alter system checkpoint;

query to check:

select count(*) from scott.dept as of timestamp TO_TIMESTAMP('27-11-2019 09:18:00 'DD-MM-YYYY HH24:MI:SS');

If above query will shows any data, then we can recover the table till that time.

1.flashback to a different table.

create table scott.dept_temp as select * from scott.dept as of timestamp TO_TIMESTAMP('27-11-2019 09:18:00 'DD-MM-YYYY HH24:MI:SS');
select * from scott.dept_temp;

2.flashback to a original table.

alter table scott.dept enable row movement;
flashback table scott.dept to timestamp TO_TIMESTAMP('27-11-2019 09:18:00 'DD-MM-YYYY HH24:MI:SS');
alter table scott.dept disable row movement;

If user drops a table.
drop table scott.dept_temp;

flashback table scott.dept_temp to before drop;

Note: if a user truncte the table, then you cannot use flashback.

Flashback database:

flashback database is not enabled by-default.

To check:
select flashback_on from v$database;

Important parameters of flashback database:

show parameter db_recovery
db_recovery_file_dest
db_recovery_file_dest_size
show parameter flash
db_flashback_retention_target ---by-default 1440 mins

How to enable flashback:

Till 10g version, flashback will be enabled in mount state, but in 11g version we can enabled it open state.
alter database flashback on;

Note: when you enable flashback, one directory is automatically created with flashback name in fra location, and it is pre-requisite to set fra location before we enabled the flashback.

insert into scott.dept values(&dept,'&ename','&loc');
commit;

Note: To performing the flashback database technique, your database should be open in mount state.

shutdown immediate;
startup mount;
rman target /
flashback database to time "TO_TIMESTAMP('27-11-2019 10:01:00','DD-MM-YYYY HH24:MI:SS')";

alter database open resetlogs;
alter database open;

Restore point:

-application release
-upgradation of database
-application upgradation
-ETL

create restore point A;
shutdown immediate;
startup mount;

rman target /
flashback database to restore point A;
alter database open resetlogs;

Note: restore point will be stored based on your retention period.

create restore point B guarantee flashback database;

Note: It will not dependant on retention period, and stored the restore points until you drop them(useful for longer applications)

shutdown immediate;
startup mount;
rman target /
flashback database to restore point B
alter database open resetlogs;
select name, guarantee_flashback_database from v$restore_point;
drop restore point B;

Note: even after you purging the recycebin it will hold the dropped values based on your retention period.