---------------------------------------------- Chapter 1 DB start and shutdown -------------------------------------------------
-- Check heartbeat value
select cphbt from x$kcccp;

--- check wait event of heartbeat
select event#,name from v$event_name where name like '%heart%';

-- Generate orapw file( filename: orapwSID)
orapwd file=orapwharry password=123 entries=5

-- Get system change number ( SCN )
select dbms_flashback.get_system_change_number from dual;

-- Get checkpoint SCN
select a.file#, a.checkpoint_change#, b.file_name, to_char(a.checkpoint_time,'yyyy-mm-dd hh24:mi:ss') CPT
from v$datafile a, dba_data_files b
where a.file#=b.file_id;

select dbid, checkpoint_change# from v$database;

--- Get header info of control and data file
alter system set events 'immediate trace name controlf level 12';
alter system set events 'immediate trace name file_hdrs level 10';

-- Check option feture installed
select * from v$option;

--- Check MTTR advice info
select mttr_target_for_estimate,
advice_status ,
dirty_limit ,
estd_cache_writes,
estd_cache_write_factor ,
estd_total_writes,
estd_total_write_factor,
estd_total_ios
from v$mttr_target_advice;

--- Check statistics setting
select * from v$statistics_level
where statistics_name='MTTR Advice';

--- Check instace recovery status
select RECOVERY_ESTIMATED_IOS REIO,
ACTUAL_REDO_BLKS ARB,
TARGET_REDO_BLKS TRB,
LOG_FILE_SIZE_REDO_BLKS LFSRB,
LOG_CHKPT_TIMEOUT_REDO_BLKS LCTRB,
LOG_CHKPT_INTERVAL_REDO_BLKS LCIRB,
FAST_START_IO_TARGET_REDO_BLKS FSIOTRB,
TARGET_MTTR TMTTR,
ESTIMATED_MTTR EMTTR,
CKPT_BLOCK_WRITES CBW
from v$instance_recovery;

--- Oracle initial
select * from bootstrap$;

---------------------------------------------- Chapter 1 DB start and shutdown -------------------------------------------------

---------------------------------------------- Chapter 2 -------------------------------------------------
select * from v$spparameter;

archive log list

oerr ora 30012

alter system reset open_cursors scope=spfile;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON
RMAN> restore spfile to '/tmp/spfileharry.ora' from autobackup;
RMAN> restore controlfile to '/tmp/control01.ctl' from autobackup;

---------------------------------------------- Chapter 2 -------------------------------------------------

---------------------------------------------- Chapter 3 Data Dictionary-------------------------------------------------

------ Data dicionary
X$ -- it is core table of oracle DB, trace DB internal information and maintain DB status
end by "$" -- Stores table, Index and constrains information in DB
gv$, v$ -- Dynamic performance view. V$ based on gv$ ( where inst_id=USERENV('Instance') )

--- import file
sql.bsq

select * from v$fixed_view_definition
select * from gv$fixed_table
select * from v$fixed_table

-- Check core table
select kvittag,kvitval,kvitdsc from x$kvit;

$ORACLE_HOME/rdbms/admin/catalog.sql

--- Priority
The priority of view than synonym

--- Using 10046 events
alter session set events '10046 trace name context forever, level12';

-- Parse path
table / view -> synonym -> public synonym -> Error ORA-00942

---------------------------------------------- Chapter 3 Data Dictionary- -------------------------------------------------

---------------------------------------------- Chapter 4 Memory management -------------------------------------------------
1. Data buffer pool

Default, keep and recycle pool
Default: db_cache_size
keep: db_keep_cache_size
recycle: db_recycle_cache_size

alter system set db_keep_cache_size=4m;
alter system set db_recycle_cache_size=4m;

select id,name,block_size,current_size,target_size from v$buffer_pool;

2. Shared pool
shared_pool_size

3. Redo log buffer
log_buffer

-- SGA
select * from v$sga;
select * from v$sgastat;
select * from v$sgainfo;

------- advice

select id, name, block_size, size_for_estimate, size_factor,
estd_physical_read_factor, estd_physical_reads from v$DB_CACHE_ADVICE; -- Affact by the parameter db_cache_advice

select shared_pool_size_for_estimate, shared_pool_size_factor, estd_lc_size,
estd_lc_memory_objects, estd_lc_time_saved, estd_lc_time_saved_factor,
estd_lc_memory_object_hits from v$SHARED_POOL_ADVICE; -- Affact by the parameter statistics_level

-- Note: timed statistics deponds on the parameter timed_statistics

select statistics_name,session_status,system_status,activation_level, session_settable
from v$statistics_level;

--- Chcek SGA component's status
select COMPONENT,CURRENT_SIZE,MIN_SIZE,LAST_OPER_TYPE,
last_oper_mode, to_char(last_oper_time,'yyyy-mm-dd hh24:mi:ss')
from v$sga_dynamic_components;

---------------------------------------------- Chapter 4 Memory management -------------------------------------------------