Discussion :

[patgabjoe]

Bonsoir,

Avant de passer à un autre OS (XP-> Vista), j'avais fait une sauvegarde des mes datafiles (correspondant à mes tablespaces). J'ai sauvegardé aussi d'autres données comme les archives (ARCHIVELOG)

J'ai insallé Oracle 10g sur Vista. J'ai recréé l'instance ORCL dans un premier temps avec par défaut les tablespaces qu'Oracle nous installe (system, sysaux, temp, ...)
J'avais réussi, en arrêtant la base, modifiant mon fichier d'init, copier mes datafiles, à revenir à une situation presque normale après avoir effectué un recover database mais je n'arrivais toujours pas à ouvrir ma base (alter database open)

J'ai donc détruit la base ORCL, et ai relancé l'assistant de config d'une base de données pour en "recréer une" sans lancer l'execution.

Ma question est :

Est-ce que je peux utiliser tous mes anciens datafiles pour que ma base soit de nouveau cohérente, y compris les DBF SYSTEM01, SYSAUX01, TEMP01, UNDOTBS01 ... que logiquement il me construit pour une base "vierge".

Merci.

[orafrance]

il faut juste créer le service avec ORADIM et copier ton instance. C'était quoi ton message d'erreur empêchant le OPEN ? T'as recréé les controlfiles ? T'as lu ce tuto : http://orafrance.developpez.com/dbahelp/#L2 ?

[patgabjoe]

En ce qui concerne les tablespaces temporaires que j'avais (3).

Me faut-il les reconstruire ?
Est-ce que Oracle les utilise tels qu'ils étaient ?

Mon erreur lors de l'ouverture de la base était :

ORA-01113: le fichier 1 nécessite une récupération après défaillance matérielle
ORA-01110: fichier de données 1 : 'D:\oracle\product\10.2.0\oradata\ORCL\SYSTEM01.DBF'

Merci.

[patgabjoe]

Selon la procédure que tu m'as donné, les controlfiles ont bien été recréés :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'D:\oracle\product\10.2.0\oradata\ORCL\DESIGNER10GR1_DATA',
'D:\oracle\product\10.2.0\oradata\ORCL\DESIGNER10GR1_IDX',
'D:\oracle\product\10.2.0\oradata\ORCL\DESIGNER_RBS',
'D:\oracle\product\10.2.0\oradata\ORCL\EJOB_DWH',
'D:\oracle\product\10.2.0\oradata\ORCL\EJOB_DWH_IDX',
'D:\oracle\product\10.2.0\oradata\ORCL\EJOB_TRANSAC',
'D:\oracle\product\10.2.0\oradata\ORCL\EJOB_TRANSAC_IDX',
'D:\oracle\product\10.2.0\oradata\ORCL\EXAMPLE01.DBF',
'D:\oracle\product\10.2.0\oradata\ORCL\OWB10GR1_REF',
'D:\oracle\product\10.2.0\oradata\ORCL\OWB10GR1_REF_IDX',
'D:\oracle\product\10.2.0\oradata\ORCL\OWB10GR1_RUN',
'D:\oracle\product\10.2.0\oradata\ORCL\OWB10GR1_RUN_IDX',
'D:\oracle\product\10.2.0\oradata\ORCL\OWB10GR2_REF',
'D:\oracle\product\10.2.0\oradata\ORCL\OWB10GR2_REF_IDX',
'D:\oracle\product\10.2.0\oradata\ORCL\OWB10GR2_RUN',
'D:\oracle\product\10.2.0\oradata\ORCL\OWB10GR2_RUN_IDX',
'D:\oracle\product\10.2.0\oradata\ORCL\SYN_ERECRUTFR_DATA',
'D:\oracle\product\10.2.0\oradata\ORCL\SYN_ERECRUTFR_IDX',
'D:\oracle\product\10.2.0\oradata\ORCL\SYN_USERBOFR_DATA',
'D:\oracle\product\10.2.0\oradata\ORCL\SYN_USERBOFR_IDX',
'D:\oracle\product\10.2.0\oradata\ORCL\SYSAUX01.DBF',
'D:\oracle\product\10.2.0\oradata\ORCL\SYSTEM01.DBF',
'D:\oracle\product\10.2.0\oradata\ORCL\UNDOTBS01.DBF',
'D:\oracle\product\10.2.0\oradata\ORCL\USERS01.DBF'
LOGFILE 
GROUP 1 ('D:\oracle\product\10.2.0\oradata\ORCL\redo01.log') SIZE 51200K,
GROUP 2 ('D:\oracle\product\10.2.0\oradata\ORCL\redo02.log') SIZE 51200K,
GROUP 3 ('D:\oracle\product\10.2.0\oradata\ORCL\redo03.log') SIZE 51200K ;

Ensuite j'ai pu exécuter toutes ces commandes :

# 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;

Et là j'ai eu le message suivant :

Code :

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

1
2
3
4
5
SQL> alter database open;
alter database open
*
ERREUR à la ligne 1 :
ORA-01092: instance ORACLE interrompue. Déconnexion imposée

Je n'ai oublié aucun TABLESPACE

Que faire ?
Merci.

[pifor]

Vérifiez le contenu du fichier alert.log de l'instance: il contient très probablement l'erreur qui provoque la déconnexion.

[patgabjoe]

L'alert ne nous dit pas à grand chose. Le plus détaillé se trouve dans le répertoire udump.

Pour résumer il me dit que la base doit-être ouverte avec l'option UPGRADE.

Voici le contenu du fichier de trace :

Dump file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4400.trc
Mon Feb 11 23:07:12 2008
ORACLE V10.2.0.3.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows NT Version V6.0
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:889M/2046M, Ph+PgF:1902M/4307M, VA:1298M/2047M
Instance name: orcl

Redo thread mounted by this instance: 0 <none>

Oracle process number: 14

Windows thread id: 4400, image: ORACLE.EXE (SHAD)


*** 2008-02-11 23:07:12.569
*** SERVICE NAME) 2008-02-11 23:07:12.554
*** SESSION ID160.3) 2008-02-11 23:07:12.554
kccsga_update_ckpt: num_1 = 8, num_2 = 0, num_3 = 0, lbn_2 = 0, lbn_3 = 0
Control file created with size 430 blocks
Recovery target incarnation = 1, activation ID = 0
Influx buffer limit = 25449 (50% x 50898)
Successfully allocated 2 recovery slaves
Using 545 overflow buffers per recovery slave
Start recovery at thread 1 ckpt scn 3782375 logseq 220 block 3
*** 2008-02-11 23:07:20.423
Media Recovery add redo thread 1
*** 2008-02-11 23:07:20.442
Recovery of Online Redo Log: Thread 1 Group 3 Seq 220 Reading mem 0
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 0Kb in 0.18s => 0.00 Mb/sec
Total physical reads: 4096Kb
----------------------------------------------
*** 2008-02-11 23:07:20.609
Media Recovery drop redo thread 1
File 1 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 2 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 3 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 4 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 5 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 6 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 7 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 8 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 9 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 10 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 11 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 12 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 13 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 14 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 30 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 31 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 32 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 33 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 34 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 35 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 36 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 37 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 38 (stop scn 3782376) completed recovery at checkpoint scn 3782376
File 39 (stop scn 3782376) completed recovery at checkpoint scn 3782376
*** 2008-02-11 23:08:45.390
Successfully allocated 2 recovery slaves
Using 545 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 220, block 2, scn 3782372
start recovery at logseq 220, block 2, scn 3782372
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 0Kb in 0.17s => 0.00 Mb/sec
Total physical reads: 4096Kb
Longest record: 0Kb, moves: 0/1 (0%)
Longest LWN: 0Kb, moves: 0/1 (0%), moved: 0Mb
Last redo scn: 0x0000.0039b6e6 (3782374)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
*** 2008-02-11 23:08:45.665
KCRA: start recovery claims for 0 data blocks
*** 2008-02-11 23:08:45.666
KCRA: blocks processed = 0/0, claimed = 0, eliminated = 0
*** 2008-02-11 23:08:45.669
Recovery of Online Redo Log: Thread 1 Group 3 Seq 220 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
ORA-00704: echec de processus d'amorce
ORA-39700: la base de donnees doit etre ouverte avec l'option UPGRADE

[patgabjoe]

Le voici le fichier alert à partir de la commande : alter database open;

Mon Feb 11 23:08:45 2008
alter database open
Mon Feb 11 23:08:45 2008
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Mon Feb 11 23:08:45 2008
Started redo scan
Mon Feb 11 23:08:45 2008
Completed redo scan
1 redo blocks read, 0 data blocks need recovery
Mon Feb 11 23:08:45 2008
Started redo application at
Thread 1: logseq 220, block 2, scn 3782372
Mon Feb 11 23:08:45 2008
Recovery of Online Redo Log: Thread 1 Group 3 Seq 220 Reading mem 0
Mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Mon Feb 11 23:08:45 2008
Completed redo application
Mon Feb 11 23:08:45 2008
Completed crash recovery at
Thread 1: logseq 220, block 3, scn 3802375
0 data blocks read, 0 data blocks written, 1 redo blocks read
Mon Feb 11 23:08:46 2008
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=17, OS id=4756
ARC1 started with pid=18, OS id=2060
Mon Feb 11 23:08:46 2008
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Thread 1 advanced to log sequence 221
Thread 1 opened at log sequence 221
Current log# 1 seq# 221 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Mon Feb 11 23:08:46 2008
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Feb 11 23:08:46 2008
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Mon Feb 11 23:08:46 2008
ARC1: Becoming the heartbeat ARCH
Mon Feb 11 23:08:46 2008
SMON: enabling cache recovery
Mon Feb 11 23:08:47 2008
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4400.trc:
ORA-00704: echec de processus d'amorce
ORA-39700: la base de donnees doit etre ouverte avec l'option UPGRADE

Mon Feb 11 23:08:47 2008
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Mon Feb 11 23:08:47 2008
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_4104.trc:
ORA-00704: echec de processus d'amorce

Mon Feb 11 23:08:48 2008
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_reco_4344.trc:
ORA-00704: echec de processus d'amorce

Mon Feb 11 23:08:48 2008
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_3740.trc:
ORA-00704: echec de processus d'amorce

Mon Feb 11 23:08:49 2008
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_ckpt_5308.trc:
ORA-00704: echec de processus d'amorce

Mon Feb 11 23:08:49 2008
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_5424.trc:
ORA-00704: echec de processus d'amorce

Mon Feb 11 23:08:49 2008
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw0_4184.trc:
ORA-00704: echec de processus d'amorce

Mon Feb 11 23:08:50 2008
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_psp0_4272.trc:
ORA-00704: echec de processus d'amorce

Mon Feb 11 23:08:50 2008
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mman_2112.trc:
ORA-00704: echec de processus d'amorce

Instance terminated by USER, pid = 4400
ORA-1092 signalled during: alter database open...