Hi DBA's,
I'll provide some information about our Oracle 8.0.5 instance, to help in
formulating your opinions.
Alter database backup control file to trace:
Dump file e:\orant\rdbms80\trace\ORA00216.TRC
Wed Jul 25 14:53:48 2001
ORACLE V8.0.5.0.0 - Production vsnsta=0
vsnsql=c vsnxtr=3
Windows NT V4.0, OS V5.101, CPU type 586
Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
Windows NT V4.0, OS V5.101, CPU type 586
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 36
pid: d8
*** SESSION ID:(21.435) 2001.07.25.14.53.48.934
*** 2001.07.25.14.53.48.934
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 1
MAXLOGHISTORY 618
LOGFILE
GROUP 1 'E:\ORANT\DATABASE\LOGPROD1.ORA' SIZE 1M,
GROUP 2 'E:\ORANT\DATABASE\LOGPROD2.ORA' SIZE 1M
DATAFILE
'E:\ORANT\DATABASE\SYS1PROD.ORA',
'E:\ORANT\DATABASE\RBS1PROD.ORA',
'E:\ORANT\DATABASE\USR1PROD.ORA',
'E:\ORANT\DATABASE\TMP1PROD.ORA',
'E:\ORANT\DATABASE\INDX1PROD.ORA',
'E:\ORANT\DATABASE\IRDBASEPROD.ORA',
'E:\ORANT\DATABASE\ROLL1PROD.ORA'
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
Orant\database\initPROD.ora:
db_name = orcl
db_files = 1020
control_files = ("e:\orant\database\ctl1prod.ora",
"e:\orant\database\ctl2prod.ora")
db_file_multiblock_read_count = 16
db_block_buffers = 1000
shared_pool_size = 16000000
log_checkpoint_interval = 8000
processes = 100
dml_locks = 200
log_buffer = 32768
sequence_cache_entries = 30
sequence_cache_hash_buckets = 23
#audit_trail = none
timed_statistics = true
background_dump_dest = e:\orant\rdbms80\trace
user_dump_dest = e:\orant\rdbms80\trace
db_block_size =2048
compatible = 8.0.5.0.0
sort_area_size = 65536
log_checkpoint_timeout = 0
remote_login_passwordfile = shared
max_dump_file_size = 10240
max_enabled_roles=40
open_cursors=400
log_archive_start=TRUE
Initialization Parameters:
NAME VALUE
---------------------------------------- ----------------------------
spin_count 2000
processes 100
sessions 115
timed_statistics TRUE
timed_os_statistics 0
resource_limit FALSE
license_max_sessions 0
license_sessions_warning 0
lm_procs 96
lm_ress 6000
lm_locks 12000
ogms_home
parallel_transaction_resource_timeout 300
cpu_count 2
instance_groups
event
shared_pool_size 16000000
shared_pool_reserved_size 800000
shared_pool_reserved_min_alloc 5K
large_pool_size 0
large_pool_min_alloc 16K
NAME VALUE
---------------------------------------- ----------------------------
pre_page_sga FALSE
shared_memory_address 0
hi_shared_memory_address 0
use_indirect_data_buffers FALSE
use_ism TRUE
lock_sga FALSE
lock_sga_areas 0
instance_nodeset
lock_name_space
enqueue_resources 265
nls_language AMERICAN
nls_territory AMERICA
nls_sort
nls_date_language
nls_date_format
nls_currency
nls_numeric_characters
nls_iso_currency
nls_calendar
disk_asynch_io TRUE
tape_asynch_io TRUE
NAME VALUE
---------------------------------------- ----------------------------
dbwr_io_slaves 0
lgwr_io_slaves 0
arch_io_slaves 0
backup_disk_io_slaves 0
backup_tape_io_slaves FALSE
db_file_direct_io_count 64
control_files e:\orant\database\ctl1prod.o
ra, e:\orant\database\ctl2pr
od.ora
db_file_name_convert
log_file_name_convert
db_block_buffers 1000
buffer_pool_keep
buffer_pool_recycle
db_block_checksum FALSE
db_block_size 2048
db_block_checkpoint_batch 8
db_block_lru_statistics FALSE
db_block_lru_extended_statistics 0
db_writer_processes 1
NAME VALUE
---------------------------------------- ----------------------------
db_block_lru_latches 1
db_block_max_dirty_target 4294967294
max_commit_propagation_delay 90000
compatible 8.0.5.0.0
compatible_no_recovery 0.0.0
log_archive_start TRUE
log_archive_buffers 4
log_archive_buffer_size 127
log_archive_dest %RDBMS80%\
log_archive_duplex_dest
log_archive_min_succeed_dest 1
log_archive_format ARC%s.%t
log_buffer 32768
log_checkpoint_interval 8000
log_checkpoint_timeout 0
log_block_checksum FALSE
log_small_entry_max_size 80
log_simultaneous_copies 2
db_files 1020
db_file_simultaneous_writes 4
db_file_multiblock_read_count 16
NAME VALUE
---------------------------------------- ----------------------------
log_files 255
read_only_open_delayed FALSE
parallel_server FALSE
gc_lck_procs 1
gc_latches 4
gc_releasable_locks 0
gc_rollback_locks
gc_files_to_locks
gc_defer_time 10
thread 0
freeze_DB_for_fast_instance_recovery FALSE
log_checkpoints_to_alert FALSE
recovery_parallelism 0
control_file_record_keep_time 7
temporary_table_locks 115
dml_locks 200
row_locking always
serializable FALSE
replication_dependency_tracking TRUE
delayed_logging_block_cleanouts TRUE
instance_number 0
NAME VALUE
---------------------------------------- ----------------------------
max_rollback_segments 30
transactions 126
transactions_per_rollback_segment 11
rollback_segments
cleanup_rollback_entries 20
transaction_auditing TRUE
discrete_transactions_enabled FALSE
sequence_cache_entries 30
sequence_cache_hash_buckets 23
row_cache_cursors 10
os_roles FALSE
max_enabled_roles 40
remote_os_authent FALSE
remote_os_roles FALSE
O7_DICTIONARY_ACCESSIBILITY TRUE
remote_login_passwordfile SHARED
dblink_encrypt_login FALSE
license_max_users 0
db_domain WORLD
global_names FALSE
distributed_lock_timeout 60
NAME VALUE
---------------------------------------- ----------------------------
distributed_transactions 31
max_transaction_branches 8
distributed_recovery_connection_hold_tim 200
e
commit_point_strength 1
mts_service orcl
mts_rate_log_size
mts_rate_scale
mts_dispatchers
mts_servers 0
mts_max_servers 20
mts_max_dispatchers 5
local_listener
mts_listener_address
mts_multiple_listeners FALSE
open_links 4
open_links_per_instance 4
close_cached_open_cursors FALSE
optimizer_features_enable 8.0.0
fixed_date
NAME VALUE
---------------------------------------- ----------------------------
audit_trail NONE
sort_area_size 65536
sort_area_retained_size 0
sort_direct_writes AUTO
sort_write_buffers 2
sort_write_buffer_size 32768
sort_spacemap_size 512
sort_read_fac 20
db_name orcl
open_cursors 400
ifile
sql_trace FALSE
os_authent_prefix OPS$
optimizer_mode CHOOSE
sql92_security FALSE
blank_trimming FALSE
always_anti_join NESTED_LOOPS
partition_view_enabled FALSE
b_tree_bitmap_plans FALSE
star_transformation_enabled FALSE
complex_view_merging FALSE
NAME VALUE
---------------------------------------- ----------------------------
push_join_predicate FALSE
fast_full_scan_enabled FALSE
parallel_broadcast_enabled FALSE
parallel_adaptive_multi_user FALSE
always_semi_join STANDARD
optimizer_max_permutations 80000
optimizer_index_cost_adj 100
optimizer_index_caching 0
serial_reuse DISABLE
cursor_space_for_time FALSE
session_cached_cursors 0
text_enable FALSE
remote_dependencies_mode TIMESTAMP
utl_file_dir
plsql_v2_compatibility FALSE
job_queue_processes 0
job_queue_interval 60
job_queue_keep_connections FALSE
snapshot_refresh_processes 0
snapshot_refresh_interval 60
snapshot_refresh_keep_connections FALSE
NAME VALUE
---------------------------------------- ----------------------------
optimizer_percent_parallel 0
optimizer_search_limit 5
parallel_min_percent 0
parallel_default_max_instances 0
cache_size_threshold 100
create_bitmap_area_size 8388608
bitmap_merge_area_size 1048576
parallel_min_servers 0
parallel_max_servers 5
parallel_server_idle_time 5
allow_partial_sn_results FALSE
parallel_instance_group
ops_admin_group
parallel_execution_message_size 2148
parallel_min_message_pool 64440
hash_join_enabled TRUE
hash_area_size 0
hash_multiblock_io_count 1
background_dump_dest e:\orant\rdbms80\trace
user_dump_dest e:\orant\rdbms80\trace
max_dump_file_size 10240
NAME VALUE
---------------------------------------- ----------------------------
oracle_trace_enable FALSE
oracle_trace_facility_path %OTRACE80%\ADMIN\FDF\
oracle_trace_collection_path %OTRACE80%\ADMIN\CDF\
oracle_trace_facility_name oracled
oracle_trace_collection_name
oracle_trace_collection_size 5242880
object_cache_optimal_size 102400
object_cache_max_size_percent 10
session_max_open_files 0
aq_tm_processes 0
215 rows selected.
Free Space in Tablespaces:
TABLESPACE_NAME MAX_BLOCKS COUNT_BLOCKS SUM_FREE_BLOCKS PCT_FREE
-------------------- ---------- ------------ --------------- --------
IRD_BASE 728 1 728 71.09
IRD_DATA_LARGE 12235 6 12395 19.30
IRD_TEMP 15649 192 25199 98.43
RBS 19549 122 22574 88.18
ROLLBACKSPACE1 7449 1 7449 29.10
SYSTEM 38896 6 42941 64.51
6 rows selected.
Create Tablespaces schema:
--
--CREATE TABLESPACES
--
CREATE TABLESPACE IRD_BASE
DATAFILE 'E:\ORANT\DATABASE\IRDBASEPROD.ORA' SIZE 2048K AUTOEXTEND OFF
LOGGING
DEFAULT STORAGE(INITIAL 6K
NEXT 10K
MINEXTENTS 1
MAXEXTENTS 121
PCTINCREASE 30)
ONLINE
PERMANENT
/
CREATE TABLESPACE IRD_DATA_LARGE
DATAFILE 'E:\ORANT\DATABASE\USR1PROD.ORA' SIZE 128472K AUTOEXTEND ON
NEXT 2K MAXSIZE UNLIMITED
LOGGING
DEFAULT STORAGE(INITIAL 100K
NEXT 100K
MINEXTENTS 1
MAXEXTENTS 121
PCTINCREASE 1)
ONLINE
PERMANENT
/
CREATE TABLESPACE IRD_INDEXES
DATAFILE 'E:\ORANT\DATABASE\INDX1PROD.ORA' SIZE 87466K AUTOEXTEND ON
NEXT 2K MAXSIZE UNLIMITED
LOGGING
DEFAULT STORAGE(INITIAL 50K
NEXT 50K
MINEXTENTS 1
MAXEXTENTS 121
PCTINCREASE 1)
ONLINE
PERMANENT
/
CREATE TABLESPACE IRD_TEMP
DATAFILE 'E:\ORANT\DATABASE\TMP1PROD.ORA' SIZE 51200K AUTOEXTEND ON NEXT
2K MAXSIZE UNLIMITED
LOGGING
DEFAULT STORAGE(INITIAL 1M
NEXT 100K
MINEXTENTS 1
MAXEXTENTS 999
PCTINCREASE 0)
ONLINE
TEMPORARY
/
CREATE TABLESPACE RBS
DATAFILE 'E:\ORANT\DATABASE\RBS1PROD.ORA' SIZE 51200K AUTOEXTEND ON NEXT
2K MAXSIZE UNLIMITED
LOGGING
DEFAULT STORAGE(INITIAL 1M
NEXT 1M
MINEXTENTS 2
MAXEXTENTS 121
PCTINCREASE 0)
ONLINE
PERMANENT
/
CREATE TABLESPACE ROLLBACKSPACE1
DATAFILE 'E:\ORANT\DATABASE\ROLL1PROD.ORA' SIZE 51200K AUTOEXTEND OFF
LOGGING
DEFAULT STORAGE(INITIAL 100K
NEXT 100K
MINEXTENTS 1
MAXEXTENTS 121
PCTINCREASE 50)
ONLINE
PERMANENT
Total System Global Area 19939328 bytes
Fixed Size 49152 bytes
Variable Size 17768448 bytes
Database Buffers 2048000 bytes
Redo Buffers 73728 bytes
What is the function of the Online/offline REDO Logs?
We are using TCP/IP over ethernet LANs and WAN with 256Kb fiber backbone and
100Mb NICs.
Thanks in advance for your time,
Denmark Weatherburne
====================================================
>From: "Blizzard, Michael" <[EMAIL PROTECTED]>
>To: 'Denmark Weatherburne' <[EMAIL PROTECTED]>
>Subject: RE: Question from Denmark W. in Belize
>Date: Mon, 30 Jul 2001 14:16:50 -0400
>
>Well, I would start by backing up your archive logs. What is the
>structure
>of the database. Do you have mirrored redo logs?, How big is the database
>, When was the last export done.
>
>-----Original Message-----
>From: Denmark Weatherburne [mailto:[EMAIL PROTECTED]]
>Sent: Monday, July 30, 2001 1:59 PM
>To: LazyDBA.com Discussion
>Subject: RE: Question from Denmark W. in Belize
>
>Hi KD etal.,
>
>I'd like to ask you a technical question regarding Oracle database
>recovery.
>I wil take on the DBA role for an Oracle 8.0.5 database running on NT 4.0
>with SP 6. My previous experience with Oracle has been with version 6.x on
>sunOS, since then I worked as a DBA with Informix on Solaris.
>I've started to review the configuration and of course backup and recovery
>is an important issue.
>Currently no tested recovery procedures exist. The current backup strategy
>are as follows:
>All physical files are being backed up to 4mm tape using ArcServeIT 6.61
>for
>
>NT and the Oracle agent for ArcServeIT. The NT registry is also being
>backed
>
>up to tape.
>The database is running in archivelog mode, but the ARC* logs are not being
>up. They are still on the hard disk. The NT Server is running RAID level 5.
>What are my Oracle recovery options in this scenario?
>
>Thanks in advance for your advice,
>
>Denmark Weatherburne
>"Knowledge is power, but it is only useful if it is shared!"
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Denmark Weatherburne
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
