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
No comments:
Post a Comment