Perte de données / Oracle 10GR1

**davy.g** · 11/07/2008, 08h12

Bonjour,

Suite à une perte de disques, plusieurs bases ont crashé.
Après la résolution de cet incident, les bases ont redémarré.
Comment est-il possible de vérifier que nous n'avons pas perdu de données ?
Peut on utilliser le controlfile?

Merci.

**pifor** · 11/07/2008, 08h42

Si la base a été ouverte sans erreur au redémarrage de l'instance, alors il n'y a pas de problème car Oracle vérifie automatiquement au redémarrage de chaque instance que la base peut être ouverte sans perte de transaction validée.

S'il y a eu crash de l'instance, Oracle effectue une étape de crash recovery automatique et il y a des messages à ce sujet dans le fichier alert.log. En cas d'erreur dans cette étape (fichier endommagé), la base ne pourrait pas être ouverte sans intervention du DBA qui doit alors restaurer un ou des fichier(s) ou récupérer la base avec RECOVER DATABASE.

**davy.g** · 11/07/2008, 09h02

Dans le fichier Alert.log, je trouve la ligne suivante :

Setting recovery target incarnation to 1

Pouvez-vous m'en dire plus?

**pifor** · 11/07/2008, 09h06

Il faudrait avoir le contenu complet du fichier alert.log entre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Starting ORACLE instance (normal)

et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Completed: ALTER DATABASE OPEN

**davy.g** · 11/07/2008, 09h22

Starting up ORACLE RDBMS Version: 10.1.0.5.0.
System parameters with non-default values:
processes = 550
sessions = 1000
resource_limit = TRUE
sga_max_size = 1795162112
__shared_pool_size = 587202560
shared_pool_size = 587202560
__large_pool_size = 16777216
__java_pool_size = 16777216
spfile = +RACDATA/dbtl/spfiledbtl.ora
sga_target = 1795162112
control_files = +RACDATA/dbtl/control01.ctl, +RACDATA/dbtl/control02.ctl, +RACDATA/dbtl/control03.ctl
db_block_size = 8192
__db_cache_size = 1157627904
db_cache_size = 1157627904
compatible = 10.1.0.4.0
log_archive_dest = /oralog/dbtl1/archive
log_archive_format = DBTL_%r_%t_%s.ARC
db_file_multiblock_read_count= 16
cluster_database = TRUE
cluster_database_instances= 2
db_create_file_dest = +RACDATA
thread = 1
instance_number = 1
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 0
_recyclebin = FALSE
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=dbtlXDB)
remote_listener = LISTENERS_DBTL
session_cached_cursors = 100
plsql_debug = TRUE
job_queue_processes = 10
parallel_max_servers = 0
parallel_execution_message_size= 4096
background_core_dump = FULL
background_dump_dest = /oracle/admin/dbtl/bdump
user_dump_dest = /oracle/admin/dbtl/udump
max_dump_file_size = UNLIMITED
core_dump_dest = /oracle/admin/dbtl/cdump
audit_trail = DB
db_name = dbtl
open_cursors = 900
parallel_adaptive_multi_user= TRUE
parallel_threads_per_cpu = 3
parallel_automatic_tuning= TRUE
pga_aggregate_target = 710934528
aq_tm_processes = 2
Deprecated system parameters with specified values:
parallel_automatic_tuning
End of deprecated system parameter listing
Cluster communication is configured to use the following interface(s) for this instance
10.176.1.200
Thu Jul 10 21:56:41 2008
cluster interconnect IPC version:Oracle UDP/IP
IPC Vendor 1 proto 2 Version 1.0
PMON started with pid=2, OS id=7559
DIAG started with pid=3, OS id=7561
LMON started with pid=4, OS id=7563
* allocate domain 0, invalid = TRUE
LMD0 started with pid=5, OS id=7565
LMS0 started with pid=6, OS id=7567
LMS1 started with pid=7, OS id=7577
MMAN started with pid=8, OS id=7584
DBW0 started with pid=9, OS id=7586
LGWR started with pid=10, OS id=7588
CKPT started with pid=11, OS id=7590
SMON started with pid=12, OS id=7592
RECO started with pid=13, OS id=7594
Thu Jul 10 21:56:42 2008
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
CJQ0 started with pid=14, OS id=7596
Thu Jul 10 21:56:42 2008
starting up 1 shared server(s) ...
Thu Jul 10 21:56:42 2008
lmon registered with NM - instance id 1 (internal mem no 0)
Thu Jul 10 21:56:42 2008
Reconfiguration started (old inc 0, new inc 1)
List of nodes:
0
Global Resource Directory frozen
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Thu Jul 10 21:56:43 2008
LMS 0: 0 GCS shadows cancelled, 0 closed
Thu Jul 10 21:56:43 2008
LMS 1: 0 GCS shadows cancelled, 0 closed
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Post SMON to start 1st pass IR
Thu Jul 10 21:56:43 2008
LMS 0: 0 GCS shadows traversed, 0 replayed
Thu Jul 10 21:56:43 2008
LMS 1: 0 GCS shadows traversed, 0 replayed
Thu Jul 10 21:56:43 2008
Submitted all GCS remote-cache requests
Post SMON to start 1st pass IR
Fix write in gcs resources
Reconfiguration complete
LCK0 started with pid=17, OS id=7619
Thu Jul 10 21:56:43 2008
Oracle Data Guard is not available in this edition of Oracle.
Thu Jul 10 21:56:44 2008
ALTER DATABASE MOUNT
Thu Jul 10 21:56:44 2008
This instance was first to mount
Thu Jul 10 21:56:44 2008
Starting background process ASMB
ASMB started with pid=19, OS id=7638
Starting background process RBAL
RBAL started with pid=20, OS id=7642
Thu Jul 10 21:56:52 2008
SUCCESS: diskgroup RACDATA was mounted
Controlfile identified with block size 16384
Thu Jul 10 21:56:56 2008
Setting recovery target incarnation to 1
Thu Jul 10 21:56:56 2008
Successful mount of redo thread 1, with mount id 3297792540
Thu Jul 10 21:56:56 2008
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE).
Completed: ALTER DATABASE MOUNT
Thu Jul 10 21:56:56 2008
ALTER DATABASE OPEN
This instance was first to open
Thu Jul 10 21:56:58 2008
Beginning crash recovery of 2 threads
Thu Jul 10 21:56:58 2008
Started redo scan
Thu Jul 10 21:57:06 2008
Completed redo scan
476341 redo blocks read, 5557 data blocks need recovery
Thu Jul 10 21:57:20 2008
Started redo application at
Thread 1: logseq 62697, block 213179, scn 0.0
Thread 2: logseq 67549, block 223469, scn 0.0
Thu Jul 10 21:57:20 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 62697 Reading mem 0
Mem# 0 errs 0: +RACDATA/dbtl/onlinelog/group_1.262.638963425
Thu Jul 10 21:57:20 2008
Recovery of Online Redo Log: Thread 2 Group 8 Seq 67549 Reading mem 0
Mem# 0 errs 0: +RACDATA/dbtl/onlinelog/group_8.356.638968623
Thu Jul 10 21:57:26 2008
Recovery of Online Redo Log: Thread 2 Group 4 Seq 67550 Reading mem 0
Mem# 0 errs 0: +RACDATA/dbtl/onlinelog/group_4.271.638968695
Thu Jul 10 21:57:28 2008
Completed redo application
Thu Jul 10 21:57:28 2008
Completed crash recovery at
Thread 1: logseq 62697, block 385147, scn 8.1495375722
Thread 2: logseq 67550, block 118247, scn 8.1495375725
5557 data blocks read, 5539 data blocks written, 476341 redo blocks read
Picked Lamport scheme to generate SCNs
Thu Jul 10 21:57:29 2008
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=22, OS id=8655
ARC0: Archival started
Thu Jul 10 21:57:29 2008
LGWR: STARTING ARCH PROCESSES COMPLETE
LGWR: Primary database is in CLUSTER CONSISTENT mode
ARC1 started with pid=23, OS id=8662
ARC1: Archival started
Thu Jul 10 21:57:29 2008
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Thu Jul 10 21:57:29 2008
ARC1: Becoming the heartbeat ARCH
Thu Jul 10 21:57:29 2008
Thread 1 advanced to log sequence 62698
Maximum redo generation record size = 120832 bytes
Maximum redo generation change vector size = 116476 bytes
Private_strands 0 at log switch
Thread 1 opened at log sequence 62698
Current log# 3 seq# 62698 mem# 0: +RACDATA/dbtl/onlinelog/group_3.263.638963455
Successful open of redo thread 1
Thu Jul 10 21:57:29 2008
SMON: enabling cache recovery
Thu Jul 10 21:57:29 2008
ARC0: Evaluating archive thread 1 sequence 62697
Thu Jul 10 21:57:29 2008
ARC1: Evaluating archive thread 1 sequence 62697
Thu Jul 10 21:57:29 2008
ARC0: Beginning to archive thread 1 sequence 62697 (35853386768-35855114094) (dbtl1)
Thu Jul 10 21:57:29 2008
ARCH: Connecting to console port...
Thu Jul 10 21:57:29 2008
ARC0: Creating local archive destination LOG_ARCHIVE_DEST_1: '/oralog/dbtl1/archive/DBTL_564231291_1_62697.ARC' (thread 1 sequence 62697)
(dbtl1)
ARCH: Connecting to console port...
Thu Jul 10 21:57:30 2008
Successfully onlined Undo Tablespace 1.
Thu Jul 10 21:57:30 2008
SMON: enabling tx recovery
Thu Jul 10 21:57:32 2008
ARC1: Unable to archive thread 1 sequence 62697
Log actively being archived by another process
ARC1: Evaluating archive thread 1 sequence 62697
ARC1: Unable to archive thread 1 sequence 62697
Log actively being archived by another process
Thu Jul 10 21:57:32 2008
Database Characterset is WE8ISO8859P9
Thu Jul 10 21:57:34 2008
Starting background process QMNC
QMNC started with pid=25, OS id=8739
Thu Jul 10 21:57:36 2008
ARC0: Closing local archive destination LOG_ARCHIVE_DEST_1: '/oralog/dbtl1/archive/DBTL_564231291_1_62697.ARC'
(dbtl1)
Thu Jul 10 21:57:36 2008
ARCH: Connecting to console port...
Committing creation of archivelog '/oralog/dbtl1/archive/DBTL_564231291_1_62697.ARC'
Thu Jul 10 21:57:37 2008
ARC0: Completed archiving thread 1 sequence 62697 (35853386768-35855114094) (dbtl1)
ARCH: Connecting to console port...
Thu Jul 10 21:57:38 2008
replication_dependency_tracking turned off (no async multimaster replication found)
Thu Jul 10 21:57:39 2008
Starting background process MMON
Starting background process MMNL
MMON started with pid=26, OS id=8808
MMNL started with pid=27, OS id=8810
Thu Jul 10 21:57:39 2008
Completed: ALTER DATABASE OPEN
Thu Jul 10 21:57:40 2008
Gather Stale Start...
Thu Jul 10 22:01:44 2008
Reconfiguration started (old inc 1, new inc 2)
List of nodes:
0 1
Global Resource Directory frozen
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Thu Jul 10 22:01:45 2008
LMS 1: 0 GCS shadows cancelled, 0 closed
Thu Jul 10 22:01:45 2008
LMS 0: 0 GCS shadows cancelled, 0 closed
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Thu Jul 10 22:01:45 2008
LMS 0: 12837 GCS shadows traversed, 4004 replayed
Thu Jul 10 22:01:45 2008
LMS 1: 13008 GCS shadows traversed, 4002 replayed
Thu Jul 10 22:01:45 2008
LMS 0: 12806 GCS shadows traversed, 4003 replayed
Thu Jul 10 22:01:45 2008
LMS 1: 12790 GCS shadows traversed, 4005 replayed
Thu Jul 10 22:01:45 2008
LMS 0: 12191 GCS shadows traversed, 4002 replayed
Thu Jul 10 22:01:45 2008
LMS 1: 12333 GCS shadows traversed, 4003 replayed
Thu Jul 10 22:01:45 2008
LMS 0: 11693 GCS shadows traversed, 4002 replayed
Thu Jul 10 22:01:45 2008
LMS 1: 12347 GCS shadows traversed, 4001 replayed
Thu Jul 10 22:01:45 2008
LMS 0: 12096 GCS shadows traversed, 4001 replayed
Thu Jul 10 22:01:45 2008
LMS 1: 12477 GCS shadows traversed, 4001 replayed
LMS 1: 4270 GCS shadows traversed, 1310 replayed
Thu Jul 10 22:01:45 2008
LMS 0: 4892 GCS shadows traversed, 1428 replayed
Thu Jul 10 22:01:45 2008
Submitted all GCS remote-cache requests
Post SMON to start 1st pass IR
Fix write in gcs resources
Reconfiguration complete
Thu Jul 10 22:05:23 2008
ALTER SYSTEM SET service_names='dbtl','taf' SCOPE=MEMORY SID='dbtl1';
Thu Jul 10 22:05:27 2008
Private_strands 0 at log switch
Thread 1 advanced to log sequence 62699
Current log# 7 seq# 62699 mem# 0: +RACDATA/dbtl/onlinelog/group_7.355.638968643
Thu Jul 10 22:05:27 2008
ARC1: Evaluating archive thread 1 sequence 62698
Thu Jul 10 22:05:27 2008
ARC1: Beginning to archive thread 1 sequence 62698 (35855114094-35855148539) (dbtl1)
ARCH: Connecting to console port...
Thu Jul 10 22:05:27 2008
ARC1: Creating local archive destination LOG_ARCHIVE_DEST_1: '/oralog/dbtl1/archive/DBTL_564231291_1_62698.ARC' (thread 1 sequence 62698)
(dbtl1)
ARCH: Connecting to console port...
Thu Jul 10 22:05:36 2008
ARC1: Closing local archive destination LOG_ARCHIVE_DEST_1: '/oralog/dbtl1/archive/DBTL_564231291_1_62698.ARC'
(dbtl1)
ARCH: Connecting to console port...
Committing creation of archivelog '/oralog/dbtl1/archive/DBTL_564231291_1_62698.ARC'
Thu Jul 10 22:05:37 2008
ARC1: Completed archiving thread 1 sequence 62698 (35855114094-35855148539) (dbtl1)
ARCH: Connecting to console port...
Thu Jul 10 22:10:45 2008
Gather Stale End
Gather Empty Start...
Thu Jul 10 22:10:58 2008
Gather Empty End
Gather Auto Start...
Thu Jul 10 22:11:28 2008
Gather Auto End
Gather Schema Start...

**pifor** · 11/07/2008, 09h39

Entre Thu Jul 10 21:56:42 2008 et Thu Jul 10 21:57:39 2008; il y a bien eu une étape de crash recovery qui s'est bien passée:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Completed crash recovery at
Thread 1: logseq 62697, block 385147, scn 8.1495375722
Thread 2: logseq 67550, block 118247, scn 8.1495375725
5557 data blocks read, 5539 data blocks written, 476341 redo blocks read

Il n'y a donc pas eu de perte de données à ce moment là.