Oracle Inside Eygle Itpub

深入浅出ORACLE CHAPTER 1 — 4
http://potato212.itpub.net/post/39229/491220

---------- 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;
RMAN> restore spfile to '/tmp/spfileharry.ora' from '/u01/oradata/rman/c-123567-20000101-00';

---------- 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 ---------——