SQL> select name||'|'||open_mode||'|'||database_role from v$database;
NAME||'|'||OPEN_MODE||'|'||DATABASE_ROLE
-----------------------------------------------
ORCLCDB|READ WRITE|PRIMARY
SQL> set heading off
SQL> /
ORCLCDB|READ WRITE|PRIMARY
SQL> set feedback off
SQL> /
ORCLCDB|READ WRITE|PRIMARY
SQL> set echo off
SQL> /
ORCLCDB|READ WRITE|PRIMARY
SQL>
SQL> select to_char(sysdate,('DD-MON-YYYY HH24:MI:SS'))||','||NAME FROM JTEST1;
07-AUG-2020 05:16:30,ORCLCDB
SQL>
SELECT DISTINCT al.thread#, al.sequence#, al.resetlogs_change#, al.resetlogs_time
FROM v$archived_log al, (select grsp.rspfscn from_scn, grsp.rspscn to_scn, dbinc.resetlogs_change# resetlogs_change#, dbinc.resetlogs_time resetlogs_time from x$kccrsp grsp, v$database_incarnation dbinc where grsp.rspincarn = dbinc.incarnation# and bitand(grsp.rspflags, 2) != 0 and bitand(grsp.rspflags, 1) = 1 -- guaranteed and grsp.rspfscn <= grsp.rspscn -- filter clean grp and grsp.rspfscn != 0 ) grsp WHERE al.next_change# >= grsp.from_scn AND al.first_change# <= (grsp.to_scn + 1) AND al.resetlogs_change# = grsp.resetlogs_change# AND al.resetlogs_time = grsp.resetlogs_time AND al.archived = 'YES'; Sample Output: SQL> !date Sun Jun 28 13:31:20 UTC 2020 THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS ---------- ---------- ----------------- --------- 1 32 1920977 24-JUN-20 1 30 1920977 24-JUN-20 1 31 1920977 24-JUN-20 1 29 1920977 24-JUN-20 1 34 1920977 24-JUN-20 1 33 1920977 24-JUN-20 6 rows selected. |
V$ACTIVE_SESSION_HISTORY view. The data present in ASH can be rolled up in various dimensions that it captures over a specified duration and gathered into an ASH report.ashrpt.sql SQL script generates an HTML or text report that displays ASH information for a specified duration on the local database instance.@$ORACLE_HOME/rdbms/admin/ashrpt.sql
Enter value for report_type: text
Enter value for begin_time: -10
Enter value for duration:
Enter value for report_name: Using the report name ashrpt_1_0310_0131.txt
ashrpt_1_0310_0131 is generated. The report will gather ASH information beginning from 10 minutes before the current time and ending at the current time.ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration on a specified database and instance. This script enables you to specify a database and instance for which the ASH report will be generated.@$ORACLE_HOME/rdbms/admin/ashrpti.sql
Enter value for report_type: html
Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ----------- -------- ------------ ------------ ------------ 3309173529 1 MAIN main examp1690 3309173529 1 TINT251 tint251 samp251
dbid) and instance number (inst_num):Enter value for dbid: 3309173529 Using 3309173529 for database id Enter value for inst_num: 1
You are running ASH report on a Standby database. To generate the report over data sampled on the Primary database, enter 'P'. Defaults to 'S' - data sampled in the Standby database. Enter value for stdbyflag: Using Primary (P) or Standby (S): S
Enter value for begin_time: -10
Enter value for duration:
Enter value for slot_width:
target_session_idtarget_sql_idtarget_wait_classtarget_service_hashtarget_module_nametarget_action_nametarget_client_idtarget_plsql_entryEnter value for report_name: Using the report name ashrpt_1_0310_0131.txt
ashrpt_1_0310_0131 is generated. The report will gather ASH information on the database instance with a database ID value of 3309173529 beginning from 10 minutes before the current time and ending at the current time.ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration for specified databases and instances in an Oracle Real Application Clusters (Oracle RAC) environment. Only ASH data that is written to disk will be used to generate the report. This report will only use ASH samples from the last 10 minutes that are found in the DBA_HIST_ACTIVE_SESS_HISTORY table.@$ORACLE_HOME/rdbms/admin/ashrpti.sql
Enter value for report_type: html
Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ----------- -------- ------------ ------------ ------------ 3309173529 1 MAIN main examp1690 3309173529 1 TINT251 tint251 samp251 3309173529 2 TINT251 tint252 samp252 3309173529 3 TINT251 tint253 samp253 3309173529 4 TINT251 tint254 samp254
dbid) and instance number (inst_num):Enter value for dbid: 3309173529 Using database id: 3309173529 Enter instance numbers. Enter 'ALL' for all instances in an Oracle RAC cluster or explicitly specify list of instances (e.g., 1,2,3). Defaults to current instance. Enter value for inst_num: ALL Using instance number(s): ALL
Enter value for begin_time: -1:10
Enter value for duration: 10
Enter value for slot_width:
target_session_idtarget_sql_idtarget_wait_classtarget_service_hashtarget_module_nametarget_action_nametarget_client_idtarget_plsql_entryEnter value for report_name: Using the report name ashrpt_rac_0310_0131.txt
ashrpt_rac_0310_0131 is generated. The report will gather ASH information on all instances belonging to the database with a database ID value of 3309173529 beginning from 1 hour and 10 minutes before the current time and ending at 1 hour before the current time.SELECT or UPDATE commands—that accounted for the highest percentages of sampled session activity.SQL, then the % Current column shows the percentage of time spent executing SQL for this subprogram.| Column | Description |
|---|---|
Slot Time (Duration)
|
Duration of the slot
|
Slot Count
|
Number of sampled sessions in the slot
|
Event
|
Top three wait events in the slot
|
Event Count
|
Number of ASH samples waiting for the wait event
|
% Event
|
Percentage of ASH samples waiting for wait events in the entire analysis period
|
ashrpti.sql script, as described in "Generating an ASH Report on a Specific Database Instance".