New Things

 Archive Generation:

set line 2000

SELECT to_date(first_time) DAY,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

from

v$log_history

where to_date(first_time) > sysdate-5

GROUP by

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

order by to_date(first_time);

Tracefile deletion:

find /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/*.aud -type f -mtime +60 -exec rm {} \;

If we got error like "-bash: /usr/bin/find: Argument list too long" use below command to delete

find /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/ -type f -name '*.aud' -mtime +10 -exec ls -ltr {} \;

find /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/ -type f -name '*.aud' -mtime +90 -exec rm {} \;

FRA:

Sometimes the Flash Recovery Area (FRA) is full and the Oracle DBA wants to know what is it’s usage, size and the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

Check and confirm FRA location with below parameter 

SQL>Select name,open_mode,database_role from v$database;

NAME          OPEN_MODE    DATABASE_ROLE

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

ORADB1        READ WRITE   PRIMARY

SQL> Show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      +FRA

db_recovery_file_dest_size           big integer 120G

Check FRA usage like Total, used and free space  with below select statement

SQL> Select space_limit/1024/1024/1024 TOTAL_GB,space_used/1024/1024/1024 USED_GB from v$recovery_file_dest;

  TOTAL_GB    USED_GB

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

  120         88.8515625

Check the fra usage in percentage with below select statement

SQL>  col NAME for a15

Select x.*,round(100 * (x.SPACE_USED/x.SPACE_LIMIT), 3) fra_pct_used from v$recovery_file_dest x;

NAME     SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES   CON_ID     FRA_PCT_USED

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

+FRA     1.2885E+11  9.5404E+10 9.5404E+10        285               0           74.043

Check  the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

Select * from v$recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID

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

CONTROL FILE                             0                         0               0          0

REDO LOG                                 0                         0               0          0

ARCHIVED LOG                         73.53                     73.53             282          0

BACKUP PIECE                           .51                       .51               3          0

IMAGE COPY                               0                         0               0          0

FLASHBACK LOG                            0                         0               0          0

FOREIGN ARCHIVED LOG                     0                         0               0          0

AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

   or 

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID

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

CONTROL FILE                             0                         0               0          0

REDO LOG                                 0                         0               0          0

ARCHIVED LOG                         73.53                     73.53             282          0

BACKUP PIECE                           .51                       .51               3          0

IMAGE COPY                               0                         0               0          0

FLASHBACK LOG                            0                         0               0          0

FOREIGN ARCHIVED LOG                     0                         0               0          0

AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

Check Size, usage, Reclaimable space used 

SQL>SELECT ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,ROUND((A.SPACE_RECLAIMABLE/1024/1024/1024), 2) AS FLASH_RECLAIMABLE_GB,SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED

FROM  V$RECOVERY_FILE_DEST A,  V$FLASH_RECOVERY_AREA_USAGE B GROUP BY  SPACE_LIMIT,   SPACE_USED ,   SPACE_RECLAIMABLE ;

FLASH_IN_GB FLASH_USED_IN_GB FLASH_RECLAIMABLE_GB PERCENT_OF_SPACE_USED

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

        120            88.85                88.85                 74.04

As per above output FRA usage 74.04%

We have thre scenarios to fix FRA issue.

1. We can increase FRA space if we have sufficient free disk space :

Check FRA disk free space with below select statement

SQL> select NAME,TOTAL_MB/1024,FREE_MB/1024 from v$asm_diskgroup;

NAME                           TOTAL_MB/1024 FREE_MB/1024

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

DATA                                    1000   303.550781

FRA                                      200   109.339844

REDO                                      60   47.4726563

    or

[grid@Ora11gOra11g ~]$ asmcmd

ASMCMD> lsdg

State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  EXTERN  N         512             512   4096  4194304   1024000   310836                0          310836              0             N  DATA/

MOUNTED  EXTERN  N         512             512   4096  4194304    204800   111964                0          111964              0             N  FRA/

MOUNTED  EXTERN  N         512             512   4096  4194304     61440    48612                0           48612              0             N  REDO/

As per above output we have free space approx 109GB, So we are good to increase FRA with below syntax

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=140G scope=both;

System altered.

Now check FRA Usage after increasing.

SQL> Select space_limit/1024/1024/1024 TOTAL_GB,space_used/1024/1024/1024 USED_GB from v$recovery_file_dest;

  TOTAL_GB    USED_GB

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

       140 88.8515625

SQL>  Select x.*,round(100 * (x.SPACE_USED/x.SPACE_LIMIT), 3) fra_pct_used from v$recovery_file_dest x;

NAME     SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES   CON_ID     FRA_PCT_USED

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

+FRA     1.2885E+11  9.5404E+10 9.5404E+10        285               0           62.043

2. Change the FRA to a new location, new archives will be created to this new location:

SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/backup/archive' scope =both;

System Altered.

Now check FRA location after changing

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /backup/archive

db_recovery_file_dest_size           big integer 120G

3. Deleting Old archive logfiles to reclaim FRA space.

Check backup of database+archivelogs backup atleast for one week with below select statement, If all backups are successfull and valid backups then we can remove old archive logfiles to reclaim space.

SQL>

col output_bytes_display form a15

col input_bytes_display form a15

col STATUS form a25

col time_taken_display form a10

col INPUT_BYTES_PER_SEC_DISPLAY form a15

col OUTPUT_BYTES_PER_SEC_DISPLAY form a17

col input_rate_per_sec form a20

col output_rate_per_sec form a20

select session_key,

input_type,

status,

to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,

to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,

input_bytes_display,

output_bytes_display,

--INPUT_BYTES_PER_SEC_DISPLAY as input_rate_per_sec,

OUTPUT_BYTES_PER_SEC_DISPLAY as output_rate_per_sec,

time_taken_display

from v$rman_backup_job_details

order by session_key asc;

SESSION_KEY INPUT_TYPE    STATUS                    START_TIME       END_TIME         INPUT_BYTES_DIS OUTPUT_BYTES_DI OUTPUT_RATE_PER_SEC  TIME_TAKEN

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

          9 ARCHIVELOG    COMPLETED                 2020-02-16 05:03 2020-02-16 05:05   322.98M         324.00M           2.63M            00:02:03

         12 DB INCR       COMPLETED      2020-02-16 05:44 2020-02-16 11:35   157.76G          88.56G           4.31M            05:50:37

         14 ARCHIVELOG    COMPLETED                 2020-02-16 06:04 2020-02-16 06:05     1.27G           1.27G          14.18M            00:01:32

         17 ARCHIVELOG    COMPLETED                 2020-02-16 07:03 2020-02-16 07:05     1.94G           1.94G          19.33M            00:01:43

  84 ARCHIVELOG    COMPLETED         2020-02-16 17:02 2020-02-16 17:04     2.41G           2.42G          24.25M            00:01:42

         87 DB INCR       COMPLETED                 2020-02-16 17:12 2020-02-16 17:14     0.00K           0.00K           0.00K            00:02:05

        144 ARCHIVELOG    COMPLETED                 2020-02-17 11:04 2020-02-17 11:06     4.81G           4.81G          34.43M            00:02:23

        186 ARCHIVELOG    COMPLETED                 2020-02-17 12:03 2020-02-17 12:05     5.36G           5.37G          48.62M            00:01:53

        244 ARCHIVELOG    COMPLETED                 2020-02-18 06:06 2020-02-18 06:40    11.90G          11.90G           5.97M            00:34:01

        246 DB INCR       COMPLETED                 2020-02-18 06:15                      0.00K           0.00K

        249 ARCHIVELOG    COMPLETED                 2020-02-18 07:04 2020-02-18 07:15    11.91G          11.91G          17.40M            00:11:41

        252 ARCHIVELOG    COMPLETED                 2020-02-18 08:03 2020-02-18 08:09    12.00G          12.00G          38.28M            00:05:21

        343 ARCHIVELOG    COMPLETED                 2020-02-19 02:03 2020-02-19 02:54    21.56G          21.56G           7.20M            00:51:08

        345 DB INCR       COMPLETED                 2020-02-19 02:40 2020-02-19 02:42     0.00K           0.00K           0.00K            00:02:06

        432 ARCHIVELOG    COMPLETED                 2020-02-19 22:04 2020-02-19 22:09     8.58G           8.58G          26.88M            00:05:27

        435 ARCHIVELOG    COMPLETED                 2020-02-19 23:05 2020-02-19 23:06     8.67G           8.68G         116.89M            00:01:16

        441 ARCHIVELOG    COMPLETED                 2020-02-20 01:05 2020-02-20 01:09     9.16G           9.16G          39.92M            00:03:55

        444 DB INCR       COMPLETED                 2020-02-20 02:02 2020-02-20 02:17   704.54G           6.25G           6.97M            00:15:18

        446 ARCHIVELOG    COMPLETED                 2020-02-20 02:04 2020-02-20 02:12     9.26G           9.26G          20.57M            00:07:41

        450 ARCHIVELOG    COMPLETED                 2020-02-20 02:20 2020-02-20 02:21     9.26G           9.26G         135.51M            00:01:10

        531 ARCHIVELOG    COMPLETED                 2020-02-21 00:04 2020-02-21 00:05    19.00G          19.01G         173.78M            00:01:52

        537 DB INCR       COMPLETED                 2020-02-21 02:01 2020-02-21 02:12   704.54G           5.64G           8.51M            00:11:19

        539 ARCHIVELOG    COMPLETED                 2020-02-21 02:05 2020-02-21 02:07    19.31G          19.31G         125.16M            00:02:38

        543 ARCHIVELOG    COMPLETED                 2020-02-21 02:16 2020-02-21 02:17    19.31G          19.31G         184.83M            00:01:47

       3376 ARCHIVELOG    COMPLETED                 2020-02-22 01:04 2020-02-22 01:08    56.15G          56.16G         246.80M            00:03:53

       3379 DB INCR       COMPLETED                 2020-02-22 02:01 2020-02-22 02:22   574.54G         352.57G         274.55M            00:21:55

       3381 ARCHIVELOG    COMPLETED                 2020-02-22 02:03 2020-02-22 02:11    56.26G          56.26G         118.54M            00:08:06

       3385 ARCHIVELOG    COMPLETED                 2020-02-22 02:24 2020-02-22 02:28    56.26G          56.26G         249.42M            00:03:51

       4123 ARCHIVELOG    COMPLETED                 2020-02-23 23:04 2020-02-22 23:12    59.87G          59.88G         126.94M            00:08:03

       4156 DB INCR       COMPLETED                 2020-02-23 00:04 2020-02-23 00:33    59.89G          59.89G          34.73M            00:29:26

       4159 ARCHIVELOG    COMPLETED                 2020-02-23 01:04 2020-02-23 01:13    59.93G          59.94G         121.53M            00:08:25

       4163 ARCHIVELOG    COMPLETED                 2020-02-23 23:04 2020-02-22 23:12    59.87G          59.88G         126.94M            00:08:03

       4193 ARCHIVELOG    COMPLETED                 2020-02-24 02:58 2020-02-24 03:03    65.98G          65.98G         226.74M            00:04:58

       4195 DB INCR       COMPLETED                 2020-02-24 03:02 2020-02-24 03:34   574.54G           4.95G           2.67M            00:31:41

       4199 ARCHIVELOG    COMPLETED                 2020-02-24 03:36 2020-02-24 03:42    66.07G          66.07G         214.78M            00:05:15

       4202 ARCHIVELOG    COMPLETED                 2020-02-24 04:08 2020-02-24 04:11    66.07G          66.08G         393.38M            00:02:52

       4299 ARCHIVELOG    COMPLETED                 2020-02-25 01:04 2020-02-25 01:08    76.69G          76.69G         409.02M            00:03:12

       4302 DB INCR       COMPLETED                 2020-02-25 02:02 2020-02-25 02:10   574.54G           5.95G          12.36M            00:08:13

       4304 ARCHIVELOG    COMPLETED                 2020-02-25 02:04 2020-02-25 02:09    76.74G          76.74G         242.54M            00:05:24

       4308 ARCHIVELOG    COMPLETED                 2020-02-25 02:11 2020-02-25 02:15    76.74G          76.74G         370.68M            00:03:32

       4404 ARCHIVELOG    COMPLETED                 2020-02-26 01:06 2020-02-26 01:17    87.23G          87.24G         131.18M            00:11:21

       4407 DB INCR       COMPLETED                 2020-02-26 02:01 2020-02-26 02:12   574.54G           5.93G           9.49M            00:10:40

       4409 ARCHIVELOG    COMPLETED                 2020-02-26 02:05 2020-02-26 02:14    87.24G          87.24G         160.39M            00:09:17

       4413 ARCHIVELOG    COMPLETED                 2020-02-26 02:15 2020-02-26 02:21    87.24G          87.25G         230.26M            00:06:28

      

As per above output backup is successfull, Connect rman prompt and remove old archive log files

RMAN>delete noprompt archivelog until time 'sysdate-7' backed up 1 times to device type disk;

or 

Manually delete from ASM command prompt

Note:Not recomended approach since by deleting manually it won't release space we need to run (CROSSCHECK ARCHIVELOG ALL command from RMAN to release space)

ASMCMD> pwd

+FRA/INSISCLN/ARCHIVELOG

ASMCMD> ls

2020_02_19/

2020_02_20/

2020_02_21/

2020_02_22/

2020_02_23/

2020_02_24/

2020_02_25/

2020_02_26/

ASMCMD>cd 2020_02_19/

ASMCMD>rm *.arc

ASMCMD> ..

ASMCMD> ls

2020_02_20/

2020_02_21/

2020_02_22/

2020_02_23/

2020_02_24/

2020_02_25/

2020_02_26/

After removing old archive logfile with retention of 7 days check current FRA usage.

SQL> Select space_limit/1024/1024/1024 TOTAL_GB,space_used/1024/1024/1024 USED_GB from v$recovery_file_dest;

  TOTAL_GB    USED_GB

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

       120 73.8515625

Check the fra usage in percentage with below select statement

SQL>  Select x.*,round(100 * (x.SPACE_USED/x.SPACE_LIMIT), 3) fra_pct_used from v$recovery_file_dest x;

NAME     SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES   CON_ID     FRA_PCT_USED

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

+FRA     1.2885E+11  9.5404E+10 9.5404E+10        285               0           62.043

Drop Disk :

SQL> select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;

NAME                           TOTAL_MB/1024 FREE_MB/1024

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

DATA                                    1300       58.125

FRA                                      400   393.566406

REDO                                      210   204.66796875

SQL> set linesize 400

col path for a35

select name,group_number,disk_number,header_status,mode_status,state,os_Mb/1024,total_mb/1024,free_mb/1024,path from v$asm_disk;

select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;SQL> SQL>

NAME                           GROUP_NUMBER DISK_NUMBER HEADER_STATU MODE_ST STATE    OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024 PATH

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

REDO_0001                                 3           1 MEMBER       ONLINE  NORMAL          200           200   194.945313 /dev/oracleasm/asm11

DATA_0006                                 1           6 MEMBER       ONLINE  NORMAL          200           200   8.94921875 /dev/oracleasm/asm10

REDO_0000                                 3           0 MEMBER       ONLINE  NORMAL           10            10    9.7109375 /dev/oracleasm/asm09

DATA_0000                                 1           0 MEMBER       ONLINE  NORMAL          100           100   4.48046875 /dev/oracleasm/asm01

FRA_0001                                  2           1 MEMBER       ONLINE  NORMAL          200           200   196.773438 /dev/oracleasm/asm05

FRA_0000                                  2           0 MEMBER       ONLINE  NORMAL          200           200   196.792969 /dev/oracleasm/asm04

DATA_0004                                 1           4 MEMBER       ONLINE  NORMAL          200           200    8.9296875 /dev/oracleasm/asm07

DATA_0002                                 1           2 MEMBER       ONLINE  NORMAL          200           200   8.96484375 /dev/oracleasm/asm03

DATA_0001                                 1           1 MEMBER       ONLINE  NORMAL          200           200    8.9296875 /dev/oracleasm/asm02

DATA_0003                                 1           3 MEMBER       ONLINE  NORMAL          200           200   8.93359375 /dev/oracleasm/asm06

DATA_0005                                 1           5 MEMBER       ONLINE  NORMAL          200           200       8.9375 /dev/oracleasm/asm08

11 rows selected.

SQL> SQL>

NAME                           TOTAL_MB/1024 FREE_MB/1024

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

DATA                                    1300       58.125

FRA                                      400   393.566406

REDO                                     210    204.65625

SQL> alter diskgroup REDO drop disk REDO_0001;

Diskgroup altered.

set linesize 400

col path for a35

select name,group_number,disk_number,header_status,mode_status,state,os_Mb/1024,total_mb/1024,free_mb/1024,path from v$asm_disk;

SQL> SQL>

NAME                           GROUP_NUMBER DISK_NUMBER HEADER_STATU MODE_ST STATE    OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024 PATH

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

                                          0           0 FORMER       ONLINE  NORMAL          200             0            0 /dev/oracleasm/asm11

DATA_0006                                 1           6 MEMBER       ONLINE  NORMAL          200           200   8.94921875 /dev/oracleasm/asm10

REDO_0000                                 3           0 MEMBER       ONLINE  NORMAL           10            10   4.66796875 /dev/oracleasm/asm09

DATA_0000                                 1           0 MEMBER       ONLINE  NORMAL          100           100   4.48046875 /dev/oracleasm/asm01

FRA_0001                                  2           1 MEMBER       ONLINE  NORMAL          200           200   196.773438 /dev/oracleasm/asm05

FRA_0000                                  2           0 MEMBER       ONLINE  NORMAL          200           200   196.792969 /dev/oracleasm/asm04

DATA_0004                                 1           4 MEMBER       ONLINE  NORMAL          200           200    8.9296875 /dev/oracleasm/asm07

DATA_0002                                 1           2 MEMBER       ONLINE  NORMAL          200           200   8.96484375 /dev/oracleasm/asm03

DATA_0001                                 1           1 MEMBER       ONLINE  NORMAL          200           200    8.9296875 /dev/oracleasm/asm02

DATA_0003                                 1           3 MEMBER       ONLINE  NORMAL          200           200   8.93359375 /dev/oracleasm/asm06

DATA_0005                                 1           5 MEMBER       ONLINE  NORMAL          200           200       8.9375 /dev/oracleasm/asm08

SQL> select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;

NAME                           TOTAL_MB/1024 FREE_MB/1024

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

DATA                                    1300       58.125

FRA                                      400   393.566406

REDO                                      10   4.66796875

ASM Candidate Disk Status:

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

set linesize 400

col path for a35

select name,group_number,disk_number,header_status,mode_status,state,os_Mb/1024,total_mb/1024,free_mb/1024,path from v$asm_disk;

select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;

Find max changes during high archive:

 SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time, 

        dhsso.object_name, 

        sum(db_block_changes_delta) as maxchages 

  FROM dba_hist_seg_stat dhss, 

         dba_hist_seg_stat_obj dhsso, 

         dba_hist_snapshot dhs 

  WHERE dhs.snap_id = dhss.snap_id 

    AND dhs.instance_number = dhss.instance_number 

    AND dhss.obj# = dhsso.obj# 

     AND dhss.dataobj# = dhsso.dataobj# 

    AND begin_interval_time BETWEEN to_date('2020_03_20 09','YYYY_MM_DD HH24') 

                                           AND to_date('2020_03_20 12','YYYY_MM_DD HH24') 

  GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'), 

           dhsso.object_name order by maxchages asc; 

   

UNDO History:

select   sql_text from dba_hist_sqltext where  sql_id in  (select distinct maxquerysqlid from  (select * from (select  snap_id, maxquerylen,  undoblks, maxquerysqlid,

to_char(begin_time,?yyyy/mm/dd hh24:mi?) begin,   to_char(end_time,?yyyy/mm/dd hh24:mi?) end from  dba_hist_undostat  order by  undoblks desc,   maxquerylen desc)

   where rownum<11

  )

);

CHECK THE DB NAME, INSTANCE, OPEN MODE AND LOG MODE

colu name format a20

colu StartTime format a30

colu SysDateTime format a30

set linesize 150

select to_char(startup_time,'DD-MM-YYYY HH24:MI:SS') StartTime, to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') SysDateTime, name,instance_name,log_mode, open_mode from dual, v$database, v$instance;

---DATABASE SIZE INFORMATION

SELECT 'Physical Size (GB):' ||TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99) FROM DBA_DATA_FILES union

SELECT 'Actual Size (GB) :' || TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99)  FROM DBA_SEGMENTS union

SELECT 'SGA Size (GB):' || TO_CHAR(SUM(VALUE)/1024/1024/1024,9999999.99)  FROM V$SGA;

SELECT name,a.size_IN_GB,SYSDATE from V$DATABASE vd, (Select sum(bytes)/1024/1024/1024 size_IN_GB from dba_segments WHERE tablespace_name not like '%UNDO%') a;

RMAN FULL, INCRMENTAL AND ARCHIVELOG BACKUP

set linesize 400

col START_TIME for a20

col END_TIME for a20

col output_bytes_display form a15

col input_bytes_display form a15

col STATUS form a25

col time_taken_display form a10

col INPUT_BYTES_PER_SEC_DISPLAY form a15

col OUTPUT_BYTES_PER_SEC_DISPLAY form a17

col input_rate_per_sec form a20

col output_rate_per_sec form a20

select session_key,

input_type,

status,

to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,

to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,

input_bytes_display,

output_bytes_display,

--INPUT_BYTES_PER_SEC_DISPLAY as input_rate_per_sec,

OUTPUT_BYTES_PER_SEC_DISPLAY as output_rate_per_sec,

time_taken_display

from v$rman_backup_job_details

order by session_key asc;

SQL taking more PGA

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

select SQL_ID,round(PGA_MB,1) PGA_MB,percent,rpad('*',percent*10/100,'*') star

from

(

select SQL_ID,sum(DELTA_PGA_MB) PGA_MB ,(ratio_to_report(sum(DELTA_PGA_MB)) over ())*100 percent,rank() over(order by sum(DELTA_PGA_MB) desc) rank

from

(

select SESSION_ID,SESSION_SERIAL#,sample_id,SQL_ID,SAMPLE_TIME,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,PGA_ALLOCATED,

greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_PGA_MB"

from

dba_hist_active_sess_history

where

IS_SQLID_CURRENT='Y'

and sample_time between to_date('11-jul-2017 16:15','dd-mon-yyyy hh24:mi') and to_date('11-jul-2017 16:30','dd-mon-yyyy hh24:mi')

order by 1,2,3,4

)

group by sql_id

having sum(DELTA_PGA_MB) > 0

)

where rank < (11)

order by rank;

Sizing UNDO

***********

  SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *

       g.undo_block_per_sec) / (1024*1024) 

      "NEEDED UNDO SIZE [MByte]"

  FROM (

       SELECT SUM(a.bytes) undo_size

         FROM v$datafile a,

              v$tablespace b,

              dba_tablespaces c

        WHERE c.contents = 'UNDO'

          AND c.status = 'ONLINE'

          AND b.name = c.tablespace_name

          AND a.ts# = b.ts#

       ) d,

      v$parameter e,

       v$parameter f,

       (

       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

         undo_block_per_sec

         FROM dba_hist_undostat

       ) g

WHERE e.name = 'undo_retention'

  AND f.name = 'db_block_size';

USEFUL Queries : proc to find number of record in each table

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

set serveroutput on

spool a.txt

declare

lv_sql varchar2(500);

lv_count number;

lv_owner varchar(30);

lv_tname varchar(30);

cursor  c1 is select owner,table_name from dba_tables where owner like 'INSIS%';

begin

open c1;

loop 

fetch c1 into lv_owner,lv_tname;

exit when c1%notfound;

-- this query to get the number of record from each table

lv_sql:='select count(*) from ' || lv_owner || '.' || lv_tname;

execute immediate lv_sql INTO lv_count;

dbms_output.put_line ( lv_owner || ',' || lv_tname || ',' || lv_count);

end loop;

close c1;

end;

/

spool off

golden gate commands

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

ps -ef|grep mgr.prm

ggsci

ggsci>info all

STOP EXTRACT group_name

START EXTRACT group_name

start and stop the golden gate process in cluster

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

D:\app\xag\bin>agctl start goldengate GGATE 

D:\app\xag\bin>agctl stop goldengate GGATE 

this is the command

and you have to check crsctl stat res -t

where 

xag.GGATE-vip.vip

      1        ONLINE  ONLINE       e75l0265                 STABLE 

this is running

gg will start only in the node this is running

to relocate the vip to node2 for gg

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

agctl relocate goldengate GGATE --node e75l0067

run this from D:\app\xag\bin  folder

rman

****

run

{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

sql 'alter system archive log current';

sql 'alter system archive log current';

sql 'alter system archive log current';

backup as compressed backupset full database format 'E:\RMANBACKUP\INSISDB\%d_%U_%T_%s_%p_Full';

sql 'alter system archive log current';

sql 'alter system archive log current';

sql 'alter system archive log current';

backup format 'E:\RMANBACKUP\INSISDB\%d_%U_%T_ARCH' archivelog all;

backup format 'E:\RMANBACKUP\INSISDB\%d_%U_%T_CTL' current controlfile;

release channel c1;

release channel c2;

}

run

{

allocate channel device type sbt

backup plus archivelog

}

RMAN> crosscheck archivelog all;

RMAN> delete noprompt archivelog like '%' backed up 1 times to device type sbt_tape ;

Recovery catalog

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

on recovery catalog database:

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

CREATE USER E75LRL2047_INSISMAT IDENTIFIED BY rcatpass TEMPORARY TABLESPACE temp DEFAULT TABLESPACE RMAN_LINUX account unlock;

  

 GRANT RECOVERY_CATALOG_OWNER TO E75LRL2047_INSISMAT;

 

 GRANT UNLIMITED TABLESPACE to E75LRL2047_INSISMAT;

grant connect,resource to  E75LRL2047_INSISMAT;

    exit

 

Rman>CONNECT CATALOG E75LRL2047_INSISMAT/rcatpass@rmandb

 

 CREATE CATALOG;

on source database:

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

RMAN> CONNECT catalog E75LRL2047_INSISMAT/rcatpass@rmandb

 

recovery catalog database Password: password

connected to recovery catalog database

RMAN> REGISTER DATABASE;

 

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete 

alter system set db_recovery_file_dest_size=170G scope=both;

 checking blocking sessions

select blocking_session,sid,serial#,wait_class,seconds_in_wait from gv$session where blocking_session is not NULL order by blocking_session;

with w as (

select

chain_id,rownum n,level l

,lpad(' ',level,' ')||(select instance_name from gv$instance where inst_id=w.instance)||' '''||w.sid||','||w.sess_serial#||'@'||w.instance||'''' "session"

,lpad(' ',level,' ')||w.wait_event_text ||

   case

   when w.wait_event_text like 'enq: TM%' then

    ' mode '||decode(w.p1 ,1414332418,'Row-S' ,1414332419,'Row-X' ,1414332420,'Share' ,1414332421,'Share RX' ,1414332422,'eXclusive')

     ||( select ' on '||object_type||' "'||owner||'"."'||object_name||'" ' from all_objects where object_id=w.p2 )

   when w.wait_event_text like 'enq: TX%' then

    (

     select ' on '||object_type||' "'||owner||'"."'||object_name||'" on rowid '

     ||dbms_rowid.rowid_create(1,data_object_id,relative_fno,w.row_wait_block#,w.row_wait_row#)

     from all_objects ,dba_data_files where object_id=w.row_wait_obj# and w.row_wait_file#=file_id

    )

   end "wait event"

, w.in_wait_secs/60 "minutes"

, s.username , s.program,s.client_identifier

from v$wait_chains w join gv$session s on (s.sid=w.sid and s.serial#=w.sess_serial# and s.inst_id=w.instance)

connect by prior w.sid=w.blocker_sid and prior w.sess_serial#=w.blocker_sess_serial# and prior w.instance = w.blocker_instance

start with w.blocker_sid is null

)

select * from w where chain_id in (select chain_id from w group by chain_id having max("minutes") >= 1 and max(l)>1 )

order by n

Oracle Performance Tunning Dump-1

Q1) Define Performance Tuning

Answer: Enabling ideal usage of the system with the prevailing resources is known as Performance Tuning.

Q2) List out the type of tunings

Answer: Different types of tunings are IO Tuning, Database Tuning,  CPU Tuning, Application Tuning, and Memory Tuning.

Q3) Explain IO Tuning

Answer: Database files must be sized correctly and located on the right place to afford supreme disk subsystem quantity. Also, look out for missing indexes, regular disk sorts, row chaining, data fragmentation, complete table scans, and so on.

Q4) Explain Memory Tuning

Answer: Properly sizing the database buffers such as buffer cache, log buffer, shared pool, buffer cache by analyzing the ratios of buffer hit. Large objects are pinned into memory to avoid recurrent refills.

Q5) Explain Application Tuning

Answer: As per various experiences, it is well displaying that about 80 percent of Oracle system performance issues are fixed using optimal SQL code. Batch tasks must be properly scheduled on time.

Q6) Explain proactive tuning

Answer: Application engineers will be able to determine which grouping of oracle features and system resources will best fulfill the requirements at the time of development and planning. This process is called proactive tuning.

Q7) Explain reactive tuning

Answer: Bottom-up method is employed to discover and resolve the blockages. This process is called reactive tuning. The goal of reactive tuning is to run Oracle quicker.

Q8) Define optimizer

Answer: Optimizer, a mechanism that makes the SQL statement execution plan

Q9) What are the types of Optimizer?

Answer: Cost Based Optimizer (CBO) and Rule-Based Optimizer (RBO)

Q10) What is Rule-Based Optimizer (RBO)?

Answer: When a server does not have internal statistics with respect to the objects influenced by the statement, here RBO plays its role.

Q11) What is Cost Based Optimizer (CBO)?

Answer: If internal statistics are available, CBO method is employed over there. CBO performs various checks on all the possible execution plans and picks one that has the bottommost charge depending on the system resources.

Q12) To use Optimizer, what are the pre-requisites?

Answer: Initially set the optimizer mode and collect the object statistics

Q13) How to collect table statistics?

Answer: Examine the table emp estimate statistics (or) the table emp compute statistics

Q14) What is the abbreviation of FTS?

Answer: Abbreviation of FTS is “Full Table Scan”

Q15) Provide the difference between the estimate and compute?

Answer: FTS happens if using compute. Only 10% of the table gets read if using the estimate.

Q16) Name the best optimizer

Answer: Best optimizer is Cost Based Optimizer (CBO)

Q17) Name the optimizer which is followed by Data Dictionary

Answer: Rule-Based Optimizer (RBO) is the optimizer followed by Data Dictionary

Q18) Define Chain Rows

Answer: Chain rows are the rows that span in various blocks

Q19) Define Execution Plan

Answer: Execution plan is a road map giving details on how oracle DB executes SQL

Q20) Mention the count of Hits

Answer: There are two (2) hits

Q21) Provide the hits of Oracle Performance Tuning

Answer: Two hits of Oracle Performance Tuning are Library hit and Buffer hit

Q22) Provide the Wait Events types

Answer: Direct path read, and CPU time is the Wait Event types

Q23) Provide the quickest query method for a table?

Answer: The quickest query method for a table is to fetch by rowid

Q24) How will you fix if there is a contention for Library Caches?

Answer: If there is a contention for library caches, just increase the shared pool size

Q25) Is it a problem to have various extents in the temporary tablespace?

Answer: If all the extents are of the same size, there is no problem at all. It can even expand performance as Oracle does not require to generate a fresh extent when there is a need.

Q26) How to gather schema/user statistics?

Answer: Use ‘exec dbms_stats.gather_schema_stats(Scott)’ to gather schema/user statistics

Q27) What is the process to set up tablespaces at the time of Oracle installation?

Answer: Try to use Oracle Flexible Architecture standard or any other segregating scheme to confirm on the appropriate separation of Data, System, Temporary, Redo Log, Rollback, and Index segments.

Q28) How to find whether a tablespace has extreme or unnecessary fragmentation?

Answer: When a select against the table ‘dba_free_space’ displays that the tablespaces extents count is larger than the data files count, you can surely mention that as fragmented.

Q29) Mention the cause of a high value for recursive calls?

Answer: Extreme dynamic space administration activities, unnecessary statement re-parses, and inappropriate cursor practice can lead to a high value for recursive calls.

Q30) How to fix when seeing a high value for recursive calls?

Answer: Initially it is important to determine the causes and correct it with any of the following activities:

Make use of appropriate space management procedures

Relinking applications to grip the cursors

Confirm the repeated questions are in packages for proper reuse.

Q31) If a tablespace contains a table with 30 extents, do you think it is bad?

Answer: Multiple extents within the table is not bad. Though, if there are chained rows, it can easily wound Performance Tuning.

Q32) If there is a statistics dealing with “undo”, what do you mean by that?

Answer: It means they are talking about rollback segments and connected structures.

Q33) What will happen when a tablespace has a standard pct increase of zero with respect to the smon process?

Answer: The SMON process will not mechanically combine the available free space fragments.

Q34) Define Oracle correlated sub-queries

Answer: Oracle correlated sub-queries are the ones that make use of the values from the external query. These subqueries are executed only once and use the results of the entire assessment in the external query.

Q35) Give an example for Oracle correlated sub-queries

Answer: select student_id, fees_id, fees_amount From student

where fees_amount < (select max(fees_amount)

from student y

where student_id = y. student_id);

Q36) What is Intersect?

Answer: ‘Intersect’ permits to combine the outcomes of two or more select queries. If you can see a record available in a query and not visible on the other, those records get removed from Intersect results.

Q37) What is Union?

Answer: ‘Union’ operator is for combining the outcomes which are a set of two or more Select statements. Both the select statement of that table should have a similar count of columns along with the same data types as the duplicates are eliminated.

Q38) Give an example for Union concept

Answer: SELECT school_address FROM school_chennai

UNION

SELECT school_address FROM school_bangalore

Q39) List out the tools provided by Oracle to help in performance tuning

Answer: Below is the tools provided by Oracle to help in performance tuning:

Statspack

TKProf

Oracle Enterprise Manager

SQL and UTLESTAT.SQL

Q40) What is decoding in Oracle?

Answer: The Decode function in Oracle performs a comparison with one expression to one or more other expressions. If the search expression is equal to base expression, the function returns the equivalent result. If there is no match found, the default expression is returned. If the default expression is not specified, then it returns a value as ‘NA’.

Q41) In the SYSTEM tablespace if you find multiple fragments, what should be checked first?

Answer: Make sure that users are not having the System tablespace as their default or temporary tablespace assignment by inspecting the view of DBA_USERS.

Q42) Provide the difference between latches and locks?

Answer: Maintaining read steadiness on instance memory structures is called latches. Maintaining read steadiness on tables is called locks.

Q43) When will you say a database object is invalid?

Answer: A database object is said to be invalid when the fundamental base objects are modified.

Q44) Mention a query to make use of the entire full table scan though the table has an index

Answer: Use the query “hint” to use the entire full table scan

Q45) Provide the regularity of performing database re-organization?

Answer: Database re-organization is not required if ASSM is used. If ASSM is not used, good to perform the re-organization once in every 6 months.

Q46) Why indexes are becoming unusable while moving a table?

Answer: When a table is moved, the row ids are changed, and the index is dependent on that row id. Hence, indexes become unusable.

Q47) What is the purpose of partitioning?

Answer: Partitioning helps in hunting data simply as the data are stockpiled in numerous partitions.

Q48) What is the reason to opt composite partitioning?

Answer: If there is a large table and faster access is required, composite portioning helps much.

Q49) Is it possible to move a table to the same tablespace?

Answer: Yes, it is possible to move a table to the same tablespace if we have adequate free space

Q50) Define ADDM in 10g

Answer: ADMM (Automatic Database Diagnostic Monitor) is a utility helpful in affording few endorsements depending on the statistics gathered for every 1 hour.

-bash: /usr/bin/find: Argument list too long

Linux - Usr bin find argument list too long, Unable to remove files list too long

Usr bin find argument list too long

When I was trying to find and remove particular files from a directory, I was unable to remove those files from directory and I received the error as /usr/bin/find argument list too long. I executed the below command.

find ./ -name '*.log' -mtime +10 -exec rm {} \;

This error is because the proper arguments are not passed correctly to the remove command and also the argument list is too long and hence the arguments are not passed.

In order to overcome such scenario you can use the below command's

find /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/ -type f -name '*.log' -mtime +10 -exec ls -ltr {} \;

This command will ensure that all the arguments are passed properly and those files are listed with last access time

Command to remove the files accessed before 10 days

find /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/ -type f -name '*.log' -mtime +10 -exec rm {} \;

When you receive the error /usr/bin/find argument list too long. You can try this command to overcome such issue.