Archive log

init.ora parameters
set log_checkpoints_to_alert=TRUE
remove log_checkpoint_timeout, log_checkpoint_interval

general
set numformat 999999999999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

turn on archive log mode
steps

A. configure
1) alter system set db_recovery_file_dest='+ORAFLASH001' scope=both sid='*';
2) alter system set db_recovery_file_dest_size = 20G scope=both sid='*';

B. archivelog mode
1) shutdown database
2) startup database in mount mode
3) alter database archivelog; # as sysdba
4) archive log list # as sysdba
sys > ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 416
Next log sequence to archive 418
Current log sequence 418
5) open database


FIX archive log destination to ORAFLASH001 in 10g using ASM, if set incorrectly
steps
# of course db_recovery_file_dest should be set to +ORAFLASH001
1) alter system set log_archive_dest_10='LOCATION=use_db_recovery_file_dest' scope=both sid='*';
2) alter system set log_archive_dest_1='' scope=both sid='*'; # to null out this parameter
3) alter system switch logfile; # to test, check alert log


others tests
alter system archive log current;
alter system archive log all;
alter system switch logfile;

increase archive log destination size. but 1st find out, How much archived
steps
1) select count(*),sum(blocks*block_size) from v$archived_log where backup_count=0 and deleted='NO';
2) increase db_recovery_file_dest_size
ALTER SYSTEM SET db_recovery_file_dest_size = 30G SCOPE=BOTH SID='*';
3) SHOW PARAMETER RECOVERY

Check space utilization
steps
1. col name format a20
select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- ----------- ---------- ----------------- ---------------
+ORAFLASH001 2147483648 1271777792 596992 33

2. select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .71 0 1
ONLINELOG 0 0 0
ARCHIVELOG 16.81 .03 24
BACKUPPIECE 41.63 0 8
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

6 rows selected.

list archived files not backed-up
select NAME, ARCHIVED , deleted, status
from v$archived_log
where status='A';

others queries
SELECT SEQUENCE#, FIRST_TIME, FIRST_CHANGE#, NEXT_TIME, NEXT_CHANGE#
FROM v$archived_log;

SELECT sequence#, thread#, first_change#, next_change#
FROM v$archived_log;

SELECT MAX(sequence#)
FROM v$archived_log;

find archive logs to be restored

select completion_time,name,sequence#,first_change#,first_time,next_change#,next_time, archived
from v$archived_log;

restore archive log using seqeunce
run {
allocate channel demo_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier1)';
restore archivelog from sequence 35347 until sequence 35359;
}

run {
allocate channel demo_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2)';
set archivelog destination to '/oraarc01/oracle/arch/demo/' ;
restore archivelog from logseq=70241 until logseq=70250 ;
}

run {
allocate channel demo_t3 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2)';
set archivelog destination to '/opt/splexmv/tmparclog/' ;
restore archivelog sequence between 70276 and 70376 ;
}
restore archive log using time
run {
allocate channel icmt_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2,NB_ORA_CLIENT=rs204-bkup)';
restore archivelog
from time = "to_date('Apr 5 2005 19:20:00','Mon DD YYYY HH24:MI:SS')"
until time = "to_date('Apr 5 2005 20:00:00','Mon DD YYYY HH24:MI:SS')";
}

backup archive log
run {
allocate channel ch1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';
set command id to 'rman backup';
backup
maxsetsize = 1G
diskratio = 0
skip inaccessible
tag = 'Archive Logs'
(archivelog all delete input format'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p');
}

backup database including archivelog
backup setsize = 62914560
(database)
plus archivelog
skip inaccessible;


backup
full
filesperset = 1024
diskratio = 0
tag = 'Full'
(database format 'SBT_TAPE/RMAN_%d.%t.full.%c.%s.%p')
plus archivelog
skip inaccessible
delete input
format 'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p';

Commands

Alter system archive log [start|stop|all|...]

alter system switch logfile;

alter system archive log all;
alter system archive log next;
alter system archive log sequence 104;
alter system archive log current;
alter system archive log stop;
alter system archive log current noswitch;

alter system checkpoint;

alter system flush buffer_cache;
alter system flush buffer_pool;

alter system quiesce restricted;
alter system suspend|resume;
alter system switch logfile;

More Info

export and import using pipe

# export and compress using pipe

export EXPDATE=`date "+%m%d_%H%M"`
export DUMPFILE=demo_user_${EXPDATE}.dmp
export EXPLOGFILE=demo_user_${EXPDATE}.log


/usr/sbin/mknod ${DUMPFILE} p
/usr/bin/gzip < ${DUMPFILE} > ${DUMPFILE}.gz &
sleep 30

exp scott/tiger file=${DUMPFILE} log=${EXPLOGFILE} \
owner=SYSADM consistent=y buffer=10000000 direct=Y statistics=none

tail -5 demo_user_${EXPDATE}.log | mailx -s "demo export" me@google.com

# import from compresed file using pipe

export DUMPFILE=myexp.dmp
export IMPLOGFILE=imp_user_${EXPDATE}.log

/usr/sbin/mknod ${DUMPFILE} p

imp scott/tiger file=${DUMPFILE} log=${IMPLOGFILE} \
fromuser=SYSADM touser=SYSADM indexes=y ignore=y buffer=10000000 commit=y &

/usr/bin/gzip -d < ${DUMPFILE}.gz > ${DUMPFILE}
sleep 300

tail -5 demo_imp_user_${EXPDATE}.log | mailx -s "demo export" me@google.com

export full, zip it and remove files other than 5 days - all in one

#!/bin/ksh
export ORACLE_SID=demo
export ORACLE_HOME=/ora01/oracle/product/db_1020
export PATH=$ORACLE_HOME/bin:$PATH
export EXPDATE=`date "+%m%d_%H%M"`

$ORACLE_HOME/bin/exp \'/ as sysdba\' file=demo_full_${EXPDATE}.dmp full=y statistics=none log=demo_full_${EXPDATE}.log

/bin/gzip demo_full_${EXPDATE}.dmp

find -name exp_demo\* -mtime +5 -exec rm {} \;

tail -5 demo_full_${EXPDATE}.log | mailx -s "demo export" me@google.com

SYSTEM related statistics in 10gr2

3 More SYSTEM related statistics in 10gr2 system, dictionary and fixed_objects

# Gather system statistics
execute dbms_stats.gather_system_stats('Start');

-- some time delay while the database is under a typical workload

execute dbms_stats.gather_system_stats('Stop');

-- OR

exec dbms_stats.gather_system_stats('interval', interval=>N);
where N is the number of minutes stats will be gathered

# collect OLTP and BATCH related system stats and import it when desired
exec dbms_stats.create_stat_table('SYS','CSX_STAT');

BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab => 'CSX_STAT',
statid => 'OLTP');
END;
/

BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab => 'CSX_STAT',
statid => 'OLAP');
END;
/

During the day, the following jobs import the OLTP statistics for the daytime run:
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.IMPORT_SYSTEM_STATS(''CSX_STAT'',''OLTP'');'
SYSDATE, 'SYSDATE + 1');
COMMIT;
END;
/

During the night, the following jobs import the OLAP statistics for the nighttime run:

BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.IMPORT_SYSTEM_STATS(''CSX_STAT'',''OLAP'');'
SYSDATE + 0.5, 'SYSDATE + 1');
COMMIT;
END;
/


# Gather statistics on dictionay
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
-- or
begin
dbms_stats.gather_dictionary_stats(
comp_id => null,
estimate_percent => 15,
granularity => 'DEFAULT',
cascade => TRUE);

end;
/

# gather fixed object(synamic performance tables eg. x$) stats
exec dbms_stats.gather_fixed_objects_stats;
-- or
SQL> conn / as sysdba
SQL> exec DBMS_STATS.CREATE_STAT_TABLE('sys','csxfixedtablestat');
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ('csxfixedtablestat');



# Flush statistics from memory
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

# view
USER_TAB_MODIFICATIONS

# system statistics views
select pname, pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN';

gather statistics, dbms_stats

Manage statistics using dbms_stats 
 
create stat table
begin
dbms_stats.create_stat_table(
ownname => 'SYSADM',
stattab => 'mystattable',
tblspace=> 'tools');
end;

drop stat table
begin
dbms_stats.drop_stat_table(
ownname => 'SYSADM',
stattab => 'mystattable');
end;

gather database Stat
begin
dbms_stats.gather_database_stats(
method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade=>TRUE);
end;

delete database stat
EXEC DBMS_STATS.delete_database_stats;

gather schema Stat
begin
dbms_stats.gather_schema_stats(
ownname => 'SYSADM',
method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade=>TRUE);
end;

delete schema stat
EXEC DBMS_STATS.delete_schema_stats('SCOTT');

export table stat
dbms_stats.export_table_stats(
ownname => 'SYSADM',
tabname =>'my_table',
stattab =>'mystattable',
statid =>'bkup',
cascade =>true);
end;

import table stat
begin
dbms_stats.import_table_stats(
ownname => 'SYSADM',
tabname =>'my_table',
stattab =>'mystattable',
statid =>'bkup',
cascade =>true);
end;

delete table stat
begin
dbms_stats.delete_table_stats(
ownname => 'SYSADM',
tabname =>'my_table');
end;

gather table stat
begin
dbms_stats.gather_table_stats(
ownname => 'SYSADM',
tabname => 'my_table',
method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade => TRUE );
end;

gather index stat
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

delete index stat
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

delete column stat
exec dbms_stats.delete_column_stats(USER, 'PERSON', 'PERSON_ID');

check stats
select table_name, num_rows from dba_tables
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name ;

select table_name,index_name, num_rows from dba_indexes
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name ;

select TABLE_NAME, COLUMN_NAME, NUM_BUCKETS from dba_tab_columns
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name, column_name;

select count(*) from PS_SCRTY_TBL_DEPT ;
select count(*) from PS_JOB ;
select count(*) from PS_NAMES ;
select count(*) from PS_PERSON ;


Steps

1. Export current stats
2. Gather or Import or Set Stats
3. trace
4. tkprof

More info



Hope this helps. Regards Rupam