Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Recovery Question from Denmark W. in Belize

RE: Recovery Question from Denmark W. in Belize

From: Denmark Weatherburne <denmark_weatherburne_at_hotmail.com>
Date: Wed, 01 Aug 2001 13:46:31 -0700
Message-ID: <F001.0035CC43.20010801135615@fatcity.com>

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

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" <Michael.Blizzard_at_aig.com>
>To: 'Denmark Weatherburne' <denmark_weatherburne_at_hotmail.com>
>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:denmark_weatherburne_at_hotmail.com]
>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: denmark_weatherburne_at_hotmail.com

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: ListGuru_at_fatcity.com (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).
Received on Wed Aug 01 2001 - 15:46:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US