Duplicate Database for Standby

Version imprimable

Bonjour
Pour un ORACLE 11 j'ai executer les commandes suivantes
1) un sqlplus / nolog
2) startup nomount ..
3) un ptit RMAN auxiliary
PUIS
Code:

1 2 3 4 5 6 7 8 9 10 11 DUPLICATE DATABASE FOR STANDBY SPFILE SET LOG_FILE_NAME_CONVERT 'C:\PRIMARY\ONLINELOG\','D:\STANDBY\ONLINELOG\' SET CONTROL_FILES 'D:\STANDBY\CONTROLFILE\CONTROL01.CTL','D:\STANDBY\CONTROLFILE\CONTROL02.CTL' SET DB_FILE_NAME_CONVERT 'C:\PRIMARY\DATAFILE\',D:\STANDBY\DATAFILE\' SET DB_UNIQUE_NAME 'STANDBY' BACKUP LOCATION 'C:\app\flash_recovery_area' NOFILENAMECHECK; EXIT;
Tout se passe bien
J'ai au finale
Fin de Duplicate Db dans 0/02/13
Recovery Manager terminé

Je constate que la redirection des fichiers de C vers D n'a pas été prise en compte.
Question 1 :Ou est le loup ?

Question 2 :La base ne devrait elle pas estre montée et ouverte ?

D'avance merci pour toutes pistes

Hello,

ceci devrai fonctionner il me semble.

Code:

1
2
3
4
5
6
7
8
9
10
 
DUPLICATE TARGET DATABASE
   FOR STANDBY
   BACKUP LOCATION 'C:\app\flash_recovery_area'
      DB_FILE_NAME_CONVERT 'C:\PRIMARY\DATAFILE\','D:\STANDBY\DATAFILE\'
      SPFILE
          PARAMETER_VALUE_CONVERT 'PRIMARY','STANDBY'
          SET LOG_FILE_NAME_CONVERT 'C:\PRIMARY\ONLINELOG\','D:\STANDBY\ONLINELOG\'
          SET DB_UNIQUE_NAME 'STANDBY'
          NOFILENAMECHECK;

A l'issue de la commande ta base sera montée.
Restera plus qu'a créer la config broker.

Bonne chance
jko

retour sur piste

Bonjour
Merci de la piste mais hélas le script ne donne rien de plus.
Rman me donne bien un message lecure fichier C:\... ecriture vers D:\... mais n'en tiens pas compte .
:aie:J'ai cherché a jouer avec les param du init.ora ca na rien donné.

Voici le log rien ne me semble incohérent

Code:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175

RMAN> DUPLICATE TARGET DATABASE
2>    FOR STANDBY
3>    BACKUP LOCATION 'C:\app\flash_recovery_area'
4>    DB_FILE_NAME_CONVERT   'C:\PRIMARY\DATAFILE\','D:\STANDBY\DATAFILE\'
5>       SPFILE
6>           PARAMETER_VALUE_CONVERT 'PRIMARY','STANDBY'
7>           SET  LOG_FILE_NAME_CONVERT  'C:\PRIMARY\ONLINELOG\','D:\STANDBY\ONLINELOG\'
8>           SET DB_UNIQUE_NAME 'STANDBY'
9>           NOFILENAMECHECK;
10>
11> EXIT;
DÚmarrage de Duplicate Db dans 06/02/13
canal affectÚ : ORA_AUX_DISK_1
canal ORA_AUX_DISK_1 : SID=20 type d'unitÚ=DISK

contenu de script mÚmoire:
{
   restore clone spfile to  'C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESTANDBY.ORA';
   sql clone "alter system set spfile= ''C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESTANDBY.ORA''";
}
exÚcution de script mÚmoire

DÚmarrage de restore dans 06/02/13
utilisation du canal ORA_AUX_DISK_1

canal ORA_AUX_DISK_1 : dÚmarrage de la restauration de l'ensemble de sauvegarde des fichiers de donnÚes
canal ORA_AUX_DISK_1 : restauration de SPFILE
nom de fichier de sortie=C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESTANDBY.ORA
canal ORA_AUX_DISK_1 : lecture de l'ÚlÚment de sauvegarde C:\APP\FLASH_RECOVERY_AREA\PRIMARY\AUTOBACKUP\2013_02_06\O1_MF_S_806661984_8K42LLOM_.BKP
canal ORA_AUX_DISK_1 : descripteur d'ÚlÚment=C:\APP\FLASH_RECOVERY_AREA\PRIMARY\AUTOBACKUP\2013_02_06\O1_MF_S_806661984_8K42LLOM_.BKP balise=TAG20130206T084624
canal ORA_AUX_DISK_1 : restauration de l'ÚlÚment de sauvegarde 1
canal ORA_AUX_DISK_1 : restauration terminÚe, temps ÚcoulÚ : 00:00:02
Fin de restore dans 06/02/13

instruction SQL : alter system set spfile= ''C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESTANDBY.ORA''

contenu de script mÚmoire:
{
   sql clone "alter system set  control_files =
 ''C:\STANDBY\CONTROLFILE\O1_MF_8K1MXNMN_.CTL'', ''C:\APP\FLASH_RECOVERY_AREA\STANDBY\CONTROLFILE\O1_MF_8K1MXPCW_.CTL'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=STANDBYXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  LOG_FILE_NAME_CONVERT =
 ''C:\PRIMARY\ONLINELOG\'', ''D:\STANDBY\ONLINELOG\'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''STANDBY'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
exÚcution de script mÚmoire

instruction SQL : alter system set  control_files =  ''C:\STANDBY\CONTROLFILE\O1_MF_8K1MXNMN_.CTL'', ''C:\APP\FLASH_RECOVERY_AREA\STANDBY\CONTROLFILE\O1_MF_8K1MXPCW_.CT
L'' comment= '''' scope=spfile

instruction SQL : alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=STANDBYXDB)'' comment= '''' scope=spfile

instruction SQL : alter system set  LOG_FILE_NAME_CONVERT =  ''C:\PRIMARY\ONLINELOG\'', ''D:\STANDBY\ONLINELOG\'' comment= '''' scope=spfile

instruction SQL : alter system set  db_unique_name =  ''STANDBY'' comment= '''' scope=spfile

instance Oracle arrÛtÚe

connexion Útablie avec la base de donnÚes auxiliaire (non dÚmarrÚe)
instance Oracle dÚmarrÚe

Total System Global Area (SGA)     535662592 octets

Fixed Size                     1375792 octets
Variable Size                301990352 octets
Database Buffers             226492416 octets
Redo Buffers                   5804032 octets

contenu de script mÚmoire:
{
   sql clone "alter system set  control_files =
  ''C:\STANDBY\CONTROLFILE\O1_MF_8K1MXNMN_.CTL'', ''C:\APP\FLASH_RECOVERY_AREA\STANDBY\CONTROLFILE\O1_MF_8K1MXPCW_.CTL'' comment=
 ''Set by RMAN'' scope=spfile";
   restore clone standby controlfile;
}
exÚcution de script mÚmoire

instruction SQL : alter system set  control_files =   ''C:\STANDBY\CONTROLFILE\O1_MF_8K1MXNMN_.CTL'', ''C:\APP\FLASH_RECOVERY_AREA\STANDBY\CONTROLFILE\O1_MF_8K1MXPCW_.C
TL'' comment= ''Set by RMAN'' scope=spfile

DÚmarrage de restore dans 06/02/13
canal affectÚ : ORA_AUX_DISK_1
canal ORA_AUX_DISK_1 : SID=19 type d'unitÚ=DISK

canal ORA_AUX_DISK_1 : dÚmarrage de la restauration de l'ensemble de sauvegarde des fichiers de donnÚes
canal ORA_AUX_DISK_1 : restauration de fichier de contr¶le
canal ORA_AUX_DISK_1 : lecture de l'ÚlÚment de sauvegarde C:\APP\FLASH_RECOVERY_AREA\PRIMARY\AUTOBACKUP\2013_02_06\O1_MF_S_806661984_8K42LLOM_.BKP
canal ORA_AUX_DISK_1 : descripteur d'ÚlÚment=C:\APP\FLASH_RECOVERY_AREA\PRIMARY\AUTOBACKUP\2013_02_06\O1_MF_S_806661984_8K42LLOM_.BKP balise=TAG20130206T084624
canal ORA_AUX_DISK_1 : restauration de l'ÚlÚment de sauvegarde 1
canal ORA_AUX_DISK_1 : restauration terminÚe, temps ÚcoulÚ : 00:00:04
nom de fichier de sortie=C:\STANDBY\CONTROLFILE\O1_MF_8K1MXNMN_.CTL
nom de fichier de sortie=C:\APP\FLASH_RECOVERY_AREA\STANDBY\CONTROLFILE\O1_MF_8K1MXPCW_.CTL
Fin de restore dans 06/02/13

contenu de script mÚmoire:
{
   sql clone 'alter database mount standby database';
}
exÚcution de script mÚmoire

instruction SQL : alter database mount standby database

contenu de script mÚmoire:
{
   set newname for tempfile  1 to
 "D:\STANDBY\DATAFILE\O1_MF_TEMP_8K1N0NP0_.TMP";
   switch clone tempfile all;
   set newname for datafile  1 to
 "D:\STANDBY\DATAFILE\O1_MF_SYSTEM_8K1MJVRL_.DBF";
   set newname for datafile  2 to
 "D:\STANDBY\DATAFILE\O1_MF_SYSAUX_8K1MJWJP_.DBF";
   set newname for datafile  3 to
 "D:\STANDBY\DATAFILE\O1_MF_UNDOTBS1_8K1MJWRQ_.DBF";
   set newname for datafile  4 to
 "D:\STANDBY\DATAFILE\O1_MF_USERS_8K1MJWZ2_.DBF";
   restore
   clone database
   ;
}
exÚcution de script mÚmoire

exÚcution de la commande : SET NEWNAME

fichier temporaire 1 renommÚ D:\STANDBY\DATAFILE\O1_MF_TEMP_8K1N0NP0_.TMP dans le fichier de contr¶le

exÚcution de la commande : SET NEWNAME

exÚcution de la commande : SET NEWNAME

exÚcution de la commande : SET NEWNAME

exÚcution de la commande : SET NEWNAME

DÚmarrage de restore dans 06/02/13
utilisation du canal ORA_AUX_DISK_1

canal ORA_AUX_DISK_1 : dÚmarrage de la restauration de l'ensemble de sauvegarde des fichiers de donnÚes
canal ORA_AUX_DISK_1 : dÚfinition du ou des fichiers de donnÚes Ó restaurer Ó partir de l'ensemble de sauvegarde
canal ORA_AUX_DISK_1 : restauration du fichier de donnÚes 00001 vers D:\STANDBY\DATAFILE\O1_MF_SYSTEM_8K1MJVRL_.DBF
canal ORA_AUX_DISK_1 : restauration du fichier de donnÚes 00002 vers D:\STANDBY\DATAFILE\O1_MF_SYSAUX_8K1MJWJP_.DBF
canal ORA_AUX_DISK_1 : restauration du fichier de donnÚes 00003 vers D:\STANDBY\DATAFILE\O1_MF_UNDOTBS1_8K1MJWRQ_.DBF
canal ORA_AUX_DISK_1 : restauration du fichier de donnÚes 00004 vers D:\STANDBY\DATAFILE\O1_MF_USERS_8K1MJWZ2_.DBF
canal ORA_AUX_DISK_1 : lecture de l'ÚlÚment de sauvegarde C:\APP\FLASH_RECOVERY_AREA\PRIMARY\BACKUPSET\2013_02_06\O1_MF_NNNDF_TAG20130206T083429_8K41W796_.BKP
canal ORA_AUX_DISK_1 : descripteur d'ÚlÚment=C:\APP\FLASH_RECOVERY_AREA\PRIMARY\BACKUPSET\2013_02_06\O1_MF_NNNDF_TAG20130206T083429_8K41W796_.BKP balise=TAG20130206T083429
canal ORA_AUX_DISK_1 : restauration de l'ÚlÚment de sauvegarde 1
canal ORA_AUX_DISK_1 : restauration terminÚe, temps ÚcoulÚ : 00:01:05
Fin de restore dans 06/02/13

contenu de script mÚmoire:
{
   switch clone datafile all;
}
exÚcution de script mÚmoire

fichier de donnÚes 1 changÚ en copie de fichier de donnÚes
copie de fichier de donnÚes d'entrÚe RECID=5 STAMP=806689856 nom de fichier=C:\STANDBY\DATAFILE\O1_MF_SYSTEM_8K4XQGD2_.DBF
fichier de donnÚes 2 changÚ en copie de fichier de donnÚes
copie de fichier de donnÚes d'entrÚe RECID=6 STAMP=806689856 nom de fichier=C:\STANDBY\DATAFILE\O1_MF_SYSAUX_8K4XQGG9_.DBF
fichier de donnÚes 3 changÚ en copie de fichier de donnÚes
copie de fichier de donnÚes d'entrÚe RECID=7 STAMP=806689857 nom de fichier=C:\STANDBY\DATAFILE\O1_MF_UNDOTBS1_8K4XQGH7_.DBF
fichier de donnÚes 4 changÚ en copie de fichier de donnÚes
copie de fichier de donnÚes d'entrÚe RECID=8 STAMP=806689857 nom de fichier=C:\STANDBY\DATAFILE\O1_MF_USERS_8K4XQGJH_.DBF
Fin de Duplicate Db dans 06/02/13

Recovery Manager terminÚ.

Pour ce qui est de la mise en place du BROCKER je cherche un exemple simple et clairement expliqué donc si a défaut de pouvoir répondre à la première question vous aviez cela dans vos tiroirs cela risque d’intéresser plus que moi
:lol:Merci d'avance pour toutes pistes et renseignements

Hello,

Configuration listener:primary

Code:

1
2
3
4
5
6
7
8
9
10
 
SID_LIST_LISTENER = 
(SID_LIST = 
 (SID_DESC = 
  (GLOBAL_DBNAME = PRIM_DGMGRL.entreprise.fr) 
  (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1) 
  (SERVICE_NAME = PRIM.entreprise.fr 
  (SID_NAME = PRIM) 
 ) 
)

Configuration listener:standby

Code:

1
2
3
4
5
6
7
8
9
SID_LIST_LISTENER = 
(SID_LIST = 
 (SID_DESC = 
  (GLOBAL_DBNAME = STB_DGMGRL.entreprise.fr) 
  (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1) 
  (SERVICE_NAME = STB.entreprise.fr 
  (SID_NAME = STB) 
 ) 
)

Configuration tnsnames:primary & standby

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
PRIM = 
   (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.entreprise.fr)(PORT = 1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SID=PRIM) 
    ) 
  ) 
 
STB = 
   (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.entreprise.fr)(PORT = 1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SID=STB) 
    ) 
  )

sur primary et standby

Code:

1
2
alter system set db_domain ='entreprise.fr' scope = spfile;
-- restart, primary (open) standby (mount)

sur primary et standby

Code:

ALTER SYSTEM SET DG_BROKER_START=true ;

lancer l'outil dgmgrl

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
dgmgrl
connect sys/xxxx@prim
DGMGRL>create configuration 'prim' as primary database is 'prim' connect identifier is prim;
DGMGRL>add database 'stb' as connect identifier is stb;
DGMGRL>enable configuration;
DGMGRL>show configuration 
...
 
DGMGRL>switchover to 'stb';
....
 
DGMGRL>switchover to 'prim';
...

Bonne chance
jko

privilÞges insuffisants

:ccool:Bonjour et merci pour le méthode et les scripts
:?Cependant si tous se passe bien j'ai un petit soucis losque j'ajoute la STB

Code:

1
2
3
 
ADD DATABASE 'STBY' AS CONNECT IDENTIFIER IS STBY;
Erreur : ORA-01031: privilÞges insuffisants

Ma configuration est un peux différente puisque je n'ai pas de domaine
l'erreur ne vient pas de cela !

Mon tnsname

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
 
PRIM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = VM7OracleOrcl)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = PRIM)
    )
  )
STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = VM7CLONE)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = STBY)
    )
  )

mes listeners

Code:

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
 
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = STBY_DGMGRL)
      (ORACLE_HOME = c:\APP\PRODUCT\11.2.0\DBHOME_1)
      (SERVICE_NAME = STBY)
      (SID_NAME = STBY)
    )
   (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = VM7Clone)(PORT = 1521))
    )
  )
 
ADR_BASE_LISTENER = C:\app

Code:

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
 
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
   (SID_DESC =
      (GLOBAL_DBNAME = PRIM_DGMGRL)
      (ORACLE_HOME = c:\APP\PRODUCT\11.2.0\DBHOME_1)
      (SERVICE_NAME = PRIM)
      (SID_NAME = PRIM)
    )
   (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = VM7OracleOrcl)(PORT = 1521))
    )
  )
 
ADR_BASE_LISTENER = C:\app

:cry:meme en ajoutant .localdomain ca ne change rien de toutes facon la commande LSNRCTL RELOAD ne passe pas

Alors ma fois si vous avez une piste encore merci

08/02/2013, 20h06
jkofr

Hello,

Cette erreur vient du fait que tu n'as pas créé de password file sur la standby avec l'outil orapwd.
Tu dois avoir le même password file sur primary et standby.

jko

Bonjour
Merci pour le coup de pouce
J'ai découvert l'option PASSWORD FILE
(malheureusement elle ne fonctionne que pour un FROM ACTIVE DATABASE (oracle 11) )
Code:

1 2 3 4 5 DUPLICATE DATABASE FOR STANDBY FROM ACTIVE DATABASE PASSWORD FILE
:lol:heureux j'ai cherché a l'appliquer et cela a provoqué
Code:

1 2 3 4 5 6 7 8 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: Úchec de l'initialisation du gestionnaire de rÚcupÚration interne RMAN-04005: erreur de la base de donnÚes cible : ORA-12545: Connexion impossible car l'h¶te ou l'objet cible n'existe pas
donc j'ai fais un
orapwd FILE=C:\app\product\11.2.0\dbhome_1\database\PWDSTBY.ORA ENTRIES=5 PASSWORD=DBA FORCE=Y
(Attention sous Win 7 il faut ouvrir la fenetre dos comme administrateur)

Le fichier de password doit il avoir le meme non que la source ?

Le fichier password qui n'existait pas est maintenant créer
:(J'avance
:oops:mais le probleme change
Code:

1 2 3 4 5 6 7 8 DGMGRL> CONNECT SYS/DBA@PRIM Connexion Útablie DGMGRL> ADD DATABASE 'STBY' AS CONNECT IDENTIFIER IS STBY; Erreur : ORA-01031: privilÞges insuffisants Echec
Il ne devrait pas y avoir de cause a effect entre les 2 plobemes
Quel est le privillége a accorder a SYS ?
Donc merci de la toutes pistes et remarques

10/02/2013, 20h13
jkofr

Hello
Les mots de passe sys doivent être identiques.
Tu dois spécifier ignorecase pour éviter toute
ambiguïté
Jko

Mot de passe ok

Bonjour
Alors la je ne l'aurais pas trouvé
:ccool:C'était juste ce qu'il fallais

Voici ce qui a été fais et ou j'en suis

Code:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
 
 
 
------------------------------------------------------------------------------------------------------------
CONNECT TARGET SYS/DBA@PRIM
CONNECT AUXILIARY SYS/CHANGE_ON_INSTALL@STBY
STARTUP CLONE NOMOUNT;
DUPLICATE TARGET DATABASE
FOR STANDBY
BACKUP LOCATION 'C:\app\flash_recovery_area'
DB_FILE_NAME_CONVERT 'PRIM','STBY'
SPFILE
PARAMETER_VALUE_CONVERT  'PRM','STBY'
SET LOG_FILE_NAME_CONVERT  'PRIM','STBY'
SET DB_UNIQUE_NAME='STBY'
NOFILENAMECHECK;
EXIT;
 
------------------------------------------------------------------------------------------------------------
orapwd FILE=C:\app\product\11.2.0\dbhome_1\database\PWDSTBY.ORA ENTRIES=5 PASSWORD=DBA FORCE=Y ignorecase=Y
 
------------------------------------------------------------------------------------------------------------
Sur les 2 base 
ALTER SYSTEM SET DG_BROKER_START=true ;
 
------------------------------------------------------------------------------------------------------------
DGMGRL
DGMGRL> CONNECT SYS/DBA@PRIM
Connexion Útablie
 
DGMGRL> CREATE configuration 'PRIM' AS PRIMARY DATABASE IS 'PRIM' CONNECT IDENTIFIER IS PRIM;
Configuration "PRIM" crÚÚe avec la base de donnÚes principale "PRIM"
 
DGMGRL> ADD DATABASE 'STBY' AS CONNECT IDENTIFIER IS STBY;
Base de donnÚes "STBY" ajoutÚe.
 
DGMGRL> show configuration
Configuration - PRIM
  Mode de protection : MaxPerformance
  Bases de donnÚes :
    PRIM - Base de donnÚes principale
    STBY - Base de donnÚes de secours physique
Fonction Fast-Start Failover : DISABLED
Statut de configuration :
DISABLED
 
DGMGRL> enable configuration;
ActivÚ
-------------------------------------------------------------------------------
DGMGRL> show configuration;
 
Configuration - PRIM
 
  Mode de protection : MaxPerformance
  Bases de donnÚes :
    PRIM - Base de donnÚes principale
      Erreur : ORA-16810: plusieurs erreurs ou avertissements ont ÚtÚ dÚtectÚs pour la base de donnÚes
 
    STBY - Base de donnÚes de secours physique
      Erreur : ORA-16664: impossible de recevoir le rÚsultat depuis une base de donnÚes
 
Fonction Fast-Start Failover : DISABLED
 
Statut de configuration :
ERROR
 
DGMGRL> SHOW DATABASE DB_NAME StatusReport;
L'objet db_name est introuvable.
DGMGRL> SHOW DATABASE PRIM StatusReport;
L'objet prim est introuvable.
DGMGRL> SHOW DATABASE 'PRIM' StatusReport;
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
                   *      ERROR ORA-16783: impossible de rÚsoudre le vide pour la base de donnÚes STBY
                   *    WARNING ORA-16789: les fichiers de journalisation de secours ne sont pas configurÚs
 
DGMGRL> SHOW DATABASE 'STBY' StatusReport;
Erreur : ORA-16664: impossible de recevoir le rÚsultat depuis une base de donnÚes
 
DGMGRL> show database 'PRIM';
 
Base de donnÚes - PRIM
 
  R¶le :           PRIMARY
  Etat prÚvu :     TRANSPORT-ON
  Instances :
    prim
 
  Avertissements relatifs Ó la base de donnÚes :
    ORA-16789: les fichiers de journalisation de secours ne sont pas configurÚs
 
Statut de base de donnÚes :
WARNING
 
 
 SQL> select * from v$log;
 
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TI NEXT_CHANGE# NEXT_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------- ------------ --------
         1          1         25   52428800        512          2 YES INACTIVE               1108538 11/02/13      1115603 11/02/13
         2          1         26   52428800        512          2 NO  CURRENT                1115603 11/02/13   2,8147E+14
         3          1         24   52428800        512          2 YES INACTIVE               1108500 11/02/13      1108538 11/02/13
 
 
SQL> select group#, member from v$logfile;
 
    GROUP# MEMBER
         3 C:\APP\ORADATA\PRIM\ONLINELOG\O1_MF_3_8KBTFM63_.LOG
         3 C:\APP\FLASH_RECOVERY_AREA\PRIM\ONLINELOG\O1_MF_3_8KBTFV89_.LOG
         2 C:\APP\ORADATA\PRIM\ONLINELOG\O1_MF_2_8KBTDX54_.LOG
         2 C:\APP\FLASH_RECOVERY_AREA\PRIM\ONLINELOG\O1_MF_2_8KBTFFBF_.LOG
         1 C:\APP\ORADATA\PRIM\ONLINELOG\O1_MF_1_8KBTD940_.LOG
         1 C:\APP\FLASH_RECOVERY_AREA\PRIM\ONLINELOG\O1_MF_1_8KBTDO8K_.LOG
 
6 ligne(s) sÚlectionnÚe(s).
 
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 1 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 1 SIZE 50M
*
ERREUR Ó la ligne 1 :
ORA-01184: le groupe de fichiers journaux 1 existe dÚjÓ
 
SQL>  ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
 
Base de donnÚes modifiÚe.
 
SQL>
SQL>  ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
 
Base de donnÚes modifiÚe.
 
SQL>  ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
 
Base de donnÚes modifiÚe.
SQL> select * from v$standby_log;
 
GROUP DBID        BYTES  BLOCKSIZE       USED ARC STATUS     
4     UNASSIGNED 52428800        512      512 YES UNASSIGNED           
5     UNASSIGNED 52428800        512      512 YES UNASSIGNED           
6     UNASSIGNED 52428800        512      512 YES UNASSIGNED 
 
 
 
DGMGRL> show configuration;
 
Configuration - PRIM
 
  Mode de protection : MaxPerformance
  Bases de donnÚes :
    PRIM - Base de donnÚes principale
    STBY - Base de donnÚes de secours physique
 
Fonction Fast-Start Failover : DISABLED
 
Statut de configuration :
ORA-16610: commande "Broker automatic health check" en cours d'exÚcution
DGM-17017: impossible de dÚterminer le statut de la configuration
 
SQL> SHUTDOWN IMMEDIATE
Base de donnÚes fermÚe.
Base de donnÚes dÚmontÚe.
Instance ORACLE arrÛtÚe.
SQL> STARTUP
Instance ORACLE lancÚe.
 
Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             373293520 bytes
Database Buffers          155189248 bytes
Redo Buffers                5804032 bytes
Base de donnÚes montÚe.
Base de donnÚes ouverte.
 
DGMGRL> CONNECT SYS/DBA@PRIM
Connexion Útablie
DGMGRL> show configuration;
 
Configuration - PRIM
 
  Mode de protection : MaxPerformance
  Bases de donnÚes :
    PRIM - Base de donnÚes principale
      Erreur : ORA-16724: impossible de rÚsoudre le vide pour au moins une base de donnÚes de secours
 
    STBY - Base de donnÚes de secours physique
      Erreur : ORA-16664: impossible de recevoir le rÚsultat depuis une base de donnÚes
 
Fonction Fast-Start Failover : DISABLED
 
Statut de configuration :
ERROR
 
DGMGRL> SHOW DATABASE 'PRIM' StatusReport;
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
                   *      ERROR ORA-16783: impossible de rÚsoudre le vide pour la base de donnÚes STBY
 
DGMGRL> SHOW DATABASE 'STBY' StatusReport;
Erreur : ORA-16664: impossible de recevoir le rÚsultat depuis une base de donnÚes
 
DGMGRL> SHOW DATABASE 'PRIM'
 
Base de donnÚes - PRIM
 
  R¶le :           PRIMARY
  Etat prÚvu :     TRANSPORT-ON
  Instances :
    prim
 
  Erreurs de base de donnÚes :
    ORA-16783: impossible de rÚsoudre le vide pour la base de donnÚes STBY
 
Statut de base de donnÚes :
ERROR
 
 
DGMGRL> SHOW DATABASE 'STBY' StatusReport;
Erreur : ORA-16664: impossible de recevoir le rÚsultat depuis une base de donnÚes
 
 
DGMGRL> SHOW DATABASE 'STBY'
 
Base de donnÚes - STBY
 
  R¶le :           PHYSICAL STANDBY
  Etat prÚvu :     APPLY-ON
  DÚcalage de transport : (inconnu)
  DÚcalage d'application des transactions : (inconnu)
  Real-Time Query : OFF
  Instances :
    stby
 
Statut de base de donnÚes :
DGM-17016: Úchec de l'extraction du statut pour la base de donnÚes "STBY
ORA-16664: impossible de recevoir le rÚsultat depuis une base de donnÚes
 
 
 
SQL> SELECT *  from V$ARCHIVE_GAP;
 
   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1             7              9
 
SQL> SELECT STATUS,DELETED,SEQUENCE#,REGISTRAR,APPLIED,NAME from V$ARCHIVED_LOG;
 
S DEL  SEQUENCE# REGISTR APPLIED   NAME
A NO          10 RFS     NO        C:\APP\FLASH_RECOVERY_AREA\STBY\ARCHIVELOG\2013_02_12\O1_MF_1_10_8KNO365D_.ARC
A NO          11 RFS     NO        C:\APP\FLASH_RECOVERY_AREA\STBY\ARCHIVELOG\2013_02_12\O1_MF_1_11_8KNO3C91_.ARC
A NO          13 RFS     NO        C:\APP\FLASH_RECOVERY_AREA\STBY\ARCHIVELOG\2013_02_12\O1_MF_1_13_8KNQO8P5_.ARC
A NO          12 RFS     NO        C:\APP\FLASH_RECOVERY_AREA\STBY\ARCHIVELOG\2013_02_12\O1_MF_1_12_8KNQODCD_.ARC
A NO          15 RFS     NO        C:\APP\FLASH_RECOVERY_AREA\STBY\ARCHIVELOG\2013_02_12\O1_MF_1_15_8KNRFMPH_.ARC
A NO          14 RFS     NO        C:\APP\FLASH_RECOVERY_AREA\STBY\ARCHIVELOG\2013_02_12\O1_MF_1_14_8KNRFWS2_.ARC

Histoire d'avoir une vue plus claire sur les parametres

Code:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
 
DGMGRL> SHOW DATABASE VERBOSE 'STBY';
 
Base de donnÚes - STBY
 
  R¶le :           PHYSICAL STANDBY
  Etat prÚvu :     APPLY-ON
  DÚcalage de transport : (inconnu)
  DÚcalage d'application des transactions : (inconnu)
  Real-Time Query : OFF
  Instances :
    stby
 
  PropriÚtÚs :
    DGConnectIdentifier             = 'stby'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = 'PRIM, STBY'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'VM7CLONE'
    SidName                         = 'stby'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM7CLONE)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBY_DGMGRL)(INSTANCE_NAME=stby)(SERVER=DEDICATED)
))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ARC%S_%R.%T'
    TopWaitEvents                   = '(monitor)'
 
Statut de base de donnÚes :
DGM-17016: Úchec de l'extraction du statut pour la base de donnÚes "STBY"
ORA-16664: impossible de recevoir le rÚsultat depuis une base de donnÚes

Quelques soucis ici et la j'y ai remédié d'une facon certainement pas trés accadémique mais j'ai avancé.

:aie:Je me trouve actuellement confronté a
ORA-16783: impossible de rÚsoudre le vide pour la base de donnÚes STBY
et au
ORA-16664: impossible de recevoir le rÚsultat depuis une base de donnÚes
qui doit en découlé!

:ccool:Alors ma fois un petit coups de pouce pour avancé.....
D'avance merci

12/02/2013, 22h28
jkofr
Hello,

Il te manques les standby redo logs...

Si tu as 3 redo logs de 50Mo

Alors au niveau primary tu dois créer 4 standby redo logs
Code:

1 2 alter database add standby logfile thread 1 group 6 size 52428800, group 7 size 52428800, group 8 size 52428800, group 9 size 52428800;
Normalement il faut créer ces standby redo logs avant de faire le duplicate for standby :-)

Bonne chance
jko
16/02/2013, 23h02
Pomalaix

Concernant le mot de passe SYS, il y a des nouveautés en V11.

1) Il faut certes que le mot de passe soit identique des deux côtés, mais on ne crée plus le fichier de mots de passe côté secours, il faut le recopier depuis la base primaire.
2) Lors d'une duplication active de la base, RMAN s'occupe implicitement de le copier, il n'y a pas d'option pour ça.

D'après la doc :

Citation:

"If you are using active database duplication, then RMAN always copies the password file to the standby host because the password file on the standby database must be an exact copy of the password file on the target database. In this case, the PASSWORD FILE clause is not necessary. RMAN overwrites any existing password file for the auxiliary instance. With backup-based duplication you must copy the password file used on the primary to the standby, for Data Guard to ship logs"

Quant à utiliser l'option IGNORECASE, je trouve que c'est un mauvais conseil...
17/02/2013, 09h51
jkofr

Citation:

Envoyé par Pomalaix

Quant à utiliser l'option IGNORECASE, je trouve que c'est un mauvais conseil...

Hello Pomalax,

Oui pour la 11g c'est implicite avec le from active database.

Pour le IGNORECASE, c'est une habitude, sans doute pas terrible, suite à de mauvaises expériences lors de migrations 9 et 10 vers 11g;)

jko

Redo ok

Bonjour

:ccool:Effectivement il m'a suffit d'ajouter ce petit script juste apres le DUPLICATE DATABASE

Code:

1
2
3
4
5
6
7
8
9
10
11
 
 
connect SYS/DBA@PRIM AS SYSDBA
 
ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;
 
ALTER DATABASE ADD standby logfile thread 1 GROUP 4 size 52428800, GROUP 5 size 52428800, GROUP 6 size 52428800, GROUP 7 size 51200K;
DISCONNECT;

Une grosse phase de paramétrage du Brocker avec les commandes
pas forcement accadémique mais cela permet d'avancer sur une base solide

Code:

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
36
37
38
39
40
41
42
43
44
45
 
C:\>DGMGRL
DGMGRL FOR 32-bit Windows: Version 11.2.0.1.0 - Production
 
Copyright (c) 2000, 2009, Oracle. ALL rights reserved.
 
Bienvenue dans DGMGRL, tapez "help" pour obtenir des informations.
DGMGRL> CONNECT SYS/DBA@PRIM
 
EDIT DATABASE 'PRIM' SET PROPERTY    DGConnectIdentifier             = 'PRIM';
EDIT DATABASE 'PRIM' SET PROPERTY    ObserverConnectIdentifier       = '';
EDIT DATABASE 'PRIM' SET PROPERTY    LogXptMode                      = 'ASYNC';
EDIT DATABASE 'PRIM' SET PROPERTY    DelayMins                       = '0';
EDIT DATABASE 'PRIM' SET PROPERTY    Binding                         = 'optional';
EDIT DATABASE 'PRIM' SET PROPERTY    MaxFailure                      = '0';
EDIT DATABASE 'PRIM' SET PROPERTY    MaxConnections                  = '1';
EDIT DATABASE 'PRIM' SET PROPERTY    ReopenSecs                      = '300';
EDIT DATABASE 'PRIM' SET PROPERTY    NetTimeout                      = '30';
EDIT DATABASE 'PRIM' SET PROPERTY    RedoCompression                 = 'DISABLE';
EDIT DATABASE 'PRIM' SET PROPERTY    LogShipping                     = 'ON';
EDIT DATABASE 'PRIM' SET PROPERTY    PreferredApplyInstance          = '';
EDIT DATABASE 'PRIM' SET PROPERTY    ApplyInstanceTimeout            = '0';
EDIT DATABASE 'PRIM' SET PROPERTY    ApplyParallel                   = 'AUTO';
EDIT DATABASE 'PRIM' SET PROPERTY    StandbyFileManagement           = 'AUTO';
EDIT DATABASE 'PRIM' SET PROPERTY    ArchiveLagTarget                = '0';
EDIT DATABASE 'PRIM' SET PROPERTY    LogArchiveMaxProcesses          = '30';
EDIT DATABASE 'PRIM' SET PROPERTY    LogArchiveMinSucceedDest        = '1';
EDIT DATABASE 'PRIM' SET PROPERTY    DbFileNameConvert               = 'STBY,PRIM';
EDIT DATABASE 'PRIM' SET PROPERTY    LogFileNameConvert              = 'STBY,PRIM';
EDIT DATABASE 'PRIM' SET PROPERTY    FastStartFailoverTarget         = '';
EDIT DATABASE 'PRIM' SET PROPERTY    StatusReport                    = '(monitor)';
EDIT DATABASE 'PRIM' SET PROPERTY    InconsistentProperties          = '(monitor)';
EDIT DATABASE 'PRIM' SET PROPERTY    InconsistentLogXptProps         = '(monitor)';
EDIT DATABASE 'PRIM' SET PROPERTY    SendQEntries                    = '(monitor)';
EDIT DATABASE 'PRIM' SET PROPERTY    LogXptStatus                    = '(monitor)';
EDIT DATABASE 'PRIM' SET PROPERTY    RecvQEntries                    = '(monitor)'
EDIT DATABASE 'PRIM' SET PROPERTY    HostName                        = 'VM7ORACLEORCL';
EDIT DATABASE 'PRIM' SET PROPERTY    SidName                         = 'PRIM';
EDIT DATABASE 'PRIM' SET PROPERTY    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM7ORACLEORCL)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRIM_DGMGRL)(INSTANCE_NAME=prim)(SERVER=DEDIC
ATED)))';
EDIT DATABASE 'PRIM' SET PROPERTY    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST';
EDIT DATABASE 'PRIM' SET PROPERTY    AlternateLocation               = '';
EDIT DATABASE 'PRIM' SET PROPERTY    LogArchiveTrace                 = '0';
EDIT DATABASE 'PRIM' SET PROPERTY    LogArchiveFormat                = 'ARC%S_%R.%T';
EDIT DATABASE 'PRIM' SET PROPERTY    TopWaitEvents                   = '(monitor)';

le réultat géniale ou presque :lol::calim2:

Code:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
 
Microsoft Windows [version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. Tous droits réservés.
 
C:\>DGMGRL
DGMGRL for 32-bit Windows: Version 11.2.0.1.0 - Production
 
Copyright (c) 2000, 2009, Oracle. All rights reserved.
 
Bienvenue dans DGMGRL, tapez "help" pour obtenir des informations.
DGMGRL> CONNECT SYS/DBA@PRIM
Connexion Útablie
DGMGRL> CREATE configuration 'PRIM' AS PRIMARY DATABASE IS 'PRIM' CONNECT IDENTIFIER IS PRIM;
Configuration "PRIM" crÚÚe avec la base de donnÚes principale "PRIM"
DGMGRL> ADD DATABASE 'STBY' AS CONNECT IDENTIFIER IS STBY MAINTAINED AS PHYSICAL;
Base de donnÚes "STBY" ajoutÚe.
DGMGRL> show configuration;
 
Configuration - PRIM
 
  Mode de protection : MaxPerformance
  Bases de donnÚes :
    PRIM - Base de donnÚes principale
    STBY - Base de donnÚes de secours physique
 
Fonction Fast-Start Failover : DISABLED
 
Statut de configuration :
DISABLED
 
DGMGRL> enable configuration;
ActivÚ
DGMGRL> show configuration;
 
Configuration - PRIM
 
  Mode de protection : MaxPerformance
  Bases de donnÚes :
    PRIM - Base de donnÚes principale
    STBY - Base de donnÚes de secours physique
 
Fonction Fast-Start Failover : DISABLED
 
Statut de configuration :
ORA-16610: commande "Broker automatic health check" en cours d'exÚcution
DGM-17017: impossible de dÚterminer le statut de la configuration
 
DGMGRL> show database verbose 'PRIM';
 
Base de donnÚes - PRIM
 
  R¶le :           PRIMARY
  Etat prÚvu :     TRANSPORT-ON
  Instances :
    prim
 
  PropriÚtÚs :
    DGConnectIdentifier             = 'prim'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'STBY, PRIM'
    LogFileNameConvert              = 'STBY, PRIM'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'VM7ORACLEORCL'
    SidName                         = 'prim'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM7ORACLEORCL)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRIM_DGMGRL)(INSTANCE_NAME=prim)(SERVER=DEDIC
ATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ARC%S_%R.%T'
    TopWaitEvents                   = '(monitor)'
 
Statut de base de donnÚes :
SUCCESS
 
DGMGRL> show database verbose 'STBY';
 
Base de donnÚes - STBY
 
  R¶le :           PHYSICAL STANDBY
  Etat prÚvu :     APPLY-ON
  DÚcalage de transport : (inconnu)
  DÚcalage d'application des transactions : (inconnu)
  Real-Time Query : OFF
  Instances :
    stby
 
  PropriÚtÚs :
    DGConnectIdentifier             = 'stby'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'PRIM,STBY'
    LogFileNameConvert              = 'PRIM,STBY'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'VM7CLONE'
    SidName                         = 'stby'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM7CLONE)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBY_DGMGRL)(INSTANCE_NAME=stby)(SERVER=DEDICATED)
))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ARC%S_%R.%T'
    TopWaitEvents                   = '(monitor)'
 
Statut de base de donnÚes :
DGM-17016: Úchec de l'extraction du statut pour la base de donnÚes "STBY"
ORA-16665: temporisation d'attente du rÚsultat depuis une base de donnÚes

me voila donc je pense a la derniere étape ou j'avoue rammer
Alors encore une dernniere fois
:calim2:Une piste pour DGM-17016 ?
D'avance merci

19/02/2013, 22h10
jkofr
Hello,
Code:

1 2 sqlplus sys@STBY as sysdba password:
Cela fonctionne?

jko

Err 16664 Toujours

Bonjour

De la machine PRIM

Code:

1
2
 
SQLPLUS SYS/DBA@STBY AS SYSDBA

la connexion est ok la base ouverte

De la machine STBY

Code:

1
2
 
SQLPLUS SYS/DBA@STBY AS SYSDBA

la connexion est ok la base ouverte

En intérogeant le LSNRCTL STATUS j'ai ce qui suit

Code:

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
 
 
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Date de dÚpart                       21-F+VR.-2013 19:35:45
DurÚe d'activitÚ                    0 jours 0 heures 2 min. 26 sec
Niveau de trace           off
SÚcuritÚ                  ON: Local OS Authentication
SNMP                      OFF
Fichier de paramÞtres du processus d'Úcoute     C:\app\product\11.2.0\dbhome_1\network\admin\listener.ora
Fichier journal du processus d'Úcoute             c:\app\product\11.2.0\dbhome_1\log\diag\tnslsnr\VM7Clone\listener\alert\log.xml
RÚcapitulatif d'Úcoute des points d'extrÚmitÚ...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM7Clone)(PORT=1521)))
RÚcapitulatif services...
Le service "PRIM_DGB" comporte 1 instance(s).
  L'instance "stby", statut READY, comporte 1 gestionnaire(s) pour ce service...
Le service "STBY" comporte 2 instance(s).
  L'instance "STBY", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce service...
  L'instance "stby", statut READY, comporte 1 gestionnaire(s) pour ce service...
Le service "STBYXDB" comporte 1 instance(s).
  L'instance "stby", statut READY, comporte 1 gestionnaire(s) pour ce service...
Le service "STBY_DGB" comporte 1 instance(s).
  L'instance "stby", statut READY, comporte 1 gestionnaire(s) pour ce service...
Le service "STBY_DGMGRL" comporte 1 instance(s).
  L'instance "STBY", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce service...
La commande a rÚussi

Dans le BROKER

Code:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
 
DGMGRL> SHOW DATABASE VERBOSE 'PRIM'
 
Base de donnÚes - PRIM
 
  R¶le :           PRIMARY
  Etat prÚvu :     TRANSPORT-ON
  Instances :
    PRIM
 
  PropriÚtÚs :
    DGConnectIdentifier             = 'PRIM'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'STBY,PRIM'
    LogFileNameConvert              = 'STBY,PRIM'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'VM7ORACLEORCL'
    SidName                         = 'PRIM'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM7ORACLEORCL)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRIM_DGMGRL)(INSTANCE_NAME=prim)(SERVER=DEDIC
ATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ARC%S_%R.%T'
    TopWaitEvents                   = '(monitor)'
 
Statut de base de donnÚes :
SUCCESS

Jusque la tous vas bien :ccool: c'est :lol:

Code:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
DGMGRL> SHOW DATABASE VERBOSE 'STBY'
 
Base de donnÚes - STBY
 
  R¶le :           PHYSICAL STANDBY
  Etat prÚvu :     APPLY-ON
  DÚcalage de transport : (inconnu)
  DÚcalage d'application des transactions : (inconnu)
  Real-Time Query : OFF
  Instances :
    stby
 
  PropriÚtÚs :
    DGConnectIdentifier             = 'stby'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'PRIM, STBY'
    LogFileNameConvert              = 'PRIM, STBY'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'VM7CLONE'
    SidName                         = 'stby'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM7CLONE)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBY_DGMGRL)(INSTANCE_NAME=stby)(SERVER=DEDICATED)
))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ARC%S_%R.%T'
    TopWaitEvents                   = '(monitor)'
 
Statut de base de donnÚes :
DGM-17016: Úchec de l'extraction du statut pour la base de donnÚes "STBY"
ORA-16664: impossible de recevoir le rÚsultat depuis une base de donnÚes

:aie:mais la je tourne sur place j'ai désactivé le fire-wall mais rien n'y fais
:arf:

21/02/2013, 21h37
jkofr

Hello,

Je pense que depuis la Standby tu ne peux pas te connecter à la primaire.

Vérifie stpl.

jko

BROKER STEP BY STEP

Voici la solution que j’ai retenue
Elle n’est certainement pas très académiques, les puristes et professionnel auront certainement des commentaires à faire
Ca tombe bien car dans l’intérêt commun et l’esprit du forum j’y compte.
Sachant que mon discourt n’est simplement que de dire :
ca tel quel ca donne le résultat attendu

Environnement
Windows
Une base Principale Nommé PRIM sur l’ordinateur VM7OracleOrcl avec password DBA pour SYS
Une base Standby Nommé STBY sur l’ordinateur VM7OracleOrcl
=========================================================
Création du service

Code:

1
2
 
oradim.exe -new -sid STBY -startmode manual -spfile

Remaque :Suivant la version de windows il vous faudrat ouvrir la fenetre dos en tant qu'administrateur
==========================================================
Créer un fichier listener.ora
Contenant

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=STBY       )(ORACLE_HOME=C:\APP\PRODUCT\11.2.0\DBHOME_1)(SID_NAME=STBY))
                            (SID_DESC=(GLOBAL_DBNAME=PRIM_DGMGRL)(ORACLE_HOME=C:\APP\PRODUCT\11.2.0\DBHOME_1)(SID_NAME=PRIM))
                            (SID_DESC=(GLOBAL_DBNAME=PRIM       )(ORACLE_HOME=C:\APP\PRODUCT\11.2.0\DBHOME_1)(SID_NAME=PRIM))
                            (SID_DESC=(GLOBAL_DBNAME=STBY_DGMGRL)(ORACLE_HOME=C:\APP\PRODUCT\11.2.0\DBHOME_1)(SID_NAME=STBY)))
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = VM7OracleOrcl)(PORT = 1521))
  )
ADR_BASE_LISTENER = C:\app\product\11.2.0\dbhome_1\log
SAVE_CONFIG_ON_STOP_LISTENER = TRUE
LOGGING_LISTENER = ON

==========================================================
Créer un fichier TNSNAMES.ora
Contenant

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
PRIM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = VM7OracleOrcl)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = PRIM)
      (SERVER = DEDICATED)
    )
  )
STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = VM7OracleOrcl)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = STBY)
      (SERVER = DEDICATED)
    )
  )

=========================================================
Créer un fichier sqlnet.ora
Contenant

Code:

1
2
3
 
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)

==========================================================
arreter et redemarer les listenrer (Sur STBY et PRIM) pour la prise en compte des modifiations

Code:

1
2
3
 
C:>LSNRCTL STOP
C:>LSNRCTL START

Remaque :Suivant la version de windows il vous faudrat ouvrir la fenetre dos en tant qu'administrateur
==========================================================
La création du ficher de password posant quelques soucis j’ai opté pour ce qui fonctionne a tous les coups
Copie de C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\PWDPRIM.ORA en C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\PWDSTBY.ORA
==========================================================
Créer un fichier GoCreateBrocker_Script.SQL
Contenant

Code:

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
 
HOST RMAN TARGET SYS/DBA@PRIM NOCATALOG cmdfile=BS_BACKUP_FOR_STANDBY.SQL
 CONNECT SYS/DBA@PRIM AS SYSDBA
 -- Uniquement afin de pouvoir redémarrer la base en cas de dérapage 
 CREATE PFILE='D:\init-SOS.ora' FROM MEMORY;
 --------------------------------------------------------------------
 @2_SCRIPT_PRIM_SPFILE_AVANT.sql
 @3_SCRIPT_PRIM_SPFILE_APRES.SQL
 @4_SCRIPT_STBY-PRIM_SPFILE_APRES.SQL
 @5_SCRIPT_PRIM_REDO_APRES.SQL
 @ShutDownAndStartup.SQL
 HOST RMAN TARGET SYS/DBA@PRIM auxiliary SYS/CHANGE_ON_INSTALL@STBY NOCATALOG cmdfile=FichierCas71Action3V2.SQL
 DISCONNECT
 CONNECT SYS/DBA@STBY AS SYSDBA
 @SCRIPT_STBY_SPFILE_APRES.SQL
 @4_SCRIPT_STBY-PRIM_SPFILE_APRES.SQL
 ALTER SYSTEM SET dg_broker_START=TRUE SCOPE=SPFILE;
 ALTER SYSTEM SET dg_broker_START=TRUE SCOPE=MEMORY;
 @ShutDownAndStartupMount.SQL;
 --Je n’ai pas trouve plus élégant
 host DGMGRL SYS/DBA@PRIM "REMOVE DATABASE 'STBY';"
 host DGMGRL SYS/DBA@PRIM "REMOVE CONFIGURATION;"
 host DGMGRL SYS/DBA@PRIM "CREATE configuration 'PRIM' AS PRIMARY DATABASE IS 'PRIM' CONNECT IDENTIFIER IS PRIM;"
 host DGMGRL SYS/DBA@PRIM "ADD DATABASE 'STBY' AS CONNECT IDENTIFIER IS STBY;
 host DGMGRL SYS/DBA@PRIM "enable configuration;"
 host DGMGRL SYS/DBA@PRIM "show configuration"
 host DGMGRL SYS/DBA@PRIM "show DATABASE VERBOSE 'PRIM'"
 host DGMGRL SYS/DBA@PRIM "show DATABASE VERBOSE 'STBY'"

=========================================================
Créer un fichier BS_BACKUP_FOR_STANDBY.SQL
Contenant

Code:

1
2
3
4
5
6
7
8
 
  run {
	ALLOCATE CHANNEL T1 TYPE DISK;
 	#Ensuite on sauvegarde l'ensemble de la base de données dans des librairies (backupsets)	BACKUP FULL DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;
        sql 'ALTER SYSTEM SWITCH LOGFILE';
	BACKUP DATABASE INCLUDE CURRENT CONTROLFILE FOR STANDBY;
  }
  EXIT;

=========================================================
Créer un fichier 2_SCRIPT_PRIM_SPFILE_AVANT.sql
Contenant

Code:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
 
spool d:\2_script_prim_spfile_avant.log append
----------------------------------------------------------------------------
--                réinitialisation du spfile                              --
----------------------------------------------------------------------------
ALTER SYSTEM  SET "_aggregation_optimization_settings"	=0	SCOPE=SPFILE;
ALTER SYSTEM  SET "_always_anti_join"			='CHOOSE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_always_semi_join"			='CHOOSE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_and_pruning_enabled"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET audit_file_dest			='C:\APP\ADMIN\PRIM\ADUMP' SCOPE=SPFILE;
ALTER SYSTEM  SET audit_trail				='DB'	SCOPE=SPFILE;
ALTER SYSTEM  SET "_bloom_filter_enabled"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_bloom_folding_enabled"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_bloom_pruning_enabled"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_b_tree_bitmap_plans"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET compatible				='11.2.0.0.0' SCOPE=SPFILE;
ALTER SYSTEM  SET "_complex_view_merging"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_compression_compatibility"		='11.2.0.0.0' SCOPE=SPFILE;
ALTER SYSTEM  SET "_connect_by_use_union_all"		='TRUE'	SCOPE=SPFILE;
ALTER SYSTEM  SET "_convert_set_to_join"		=FALSE	SCOPE=SPFILE;
ALTER SYSTEM  SET core_dump_dest			='c:\app\diag\rdbms\prim\prim\cdump' SCOPE=SPFILE;
ALTER SYSTEM  SET "_cost_equality_semi_join"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_cpu_to_io"				=0	SCOPE=SPFILE;
ALTER SYSTEM  SET "__db_cache_size"			=208M	SCOPE=SPFILE;
ALTER SYSTEM  SET db_create_file_dest			='C:\app\oradata' SCOPE=SPFILE;
ALTER SYSTEM  SET db_domain				=''	SCOPE=SPFILE;
ALTER SYSTEM  SET db_recovery_file_dest			='C:\app\flash_recovery_area' SCOPE=SPFILE;
ALTER SYSTEM  SET db_recovery_file_dest_size		=3852M	SCOPE=SPFILE;
ALTER SYSTEM  SET diagnostic_dest			='C:\APP' SCOPE=SPFILE;
ALTER SYSTEM  SET "_dimension_skip_null"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_eliminate_common_subexpr"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_enable_type_dep_selectivity"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_fast_full_scan_enabled"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_first_k_rows_dynamic_proration"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_gby_hash_aggregation_enabled"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_generalized_pruning_enabled"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_globalindex_pnum_filter_enabled"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_gs_anti_semi_join_allowed"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_improved_outerjoin_card"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_improved_row_length_enabled"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_index_join_enabled"			=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "__java_pool_size"			=4M	SCOPE=SPFILE;
ALTER SYSTEM  SET "_ksb_restart_policy_times"		='0'	SCOPE=SPFILE;
ALTER SYSTEM  SET "_ksb_restart_policy_times"		='60'	SCOPE=SPFILE;
ALTER SYSTEM  SET "_ksb_restart_policy_times"		='120'	SCOPE=SPFILE;
ALTER SYSTEM  SET "_ksb_restart_policy_times"		='240'	SCOPE=SPFILE;
ALTER SYSTEM  SET "__large_pool_size"			=4M	SCOPE=SPFILE;
ALTER SYSTEM  SET "_left_nested_loops_random"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_local_communication_costing_enabled"=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET log_archive_format			='ARC%S_%R.%T'	SCOPE=SPFILE;
ALTER SYSTEM  SET log_buffer				=5611520 SCOPE=SPFILE;
ALTER SYSTEM  SET memory_target				=592M	SCOPE=SPFILE;
ALTER SYSTEM  SET "_minimal_stats_aggregation"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_mmv_query_rewrite_enabled"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_new_initial_join_orders"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_new_sort_cost_estimate"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_nlj_batching_enabled"		=1	SCOPE=SPFILE;
ALTER SYSTEM  SET open_cursors				=300    SCOPE=SPFILE;
ALTER SYSTEM  SET "_optim_adjust_for_part_skews"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optim_enhance_nnull_detection"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_adaptive_cursor_sharing"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_better_inlist_costing"	='ALL'	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_cbqt_no_size_restriction"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_coalesce_subqueries"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_complex_pred_selectivity"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_compute_index_stats"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_connect_by_combine_sw"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_connect_by_cost_based"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_connect_by_elim_dups"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_correct_sq_selectivity"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_cost_based_transformation"='LINEAR' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_cost_hjsmj_multimatch"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_cost_model"		='CHOOSE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_dim_subq_join_sel"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_distinct_agg_transform"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_distinct_elimination"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_distinct_placement"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET optimizer_dynamic_sampling		=2	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_eliminate_filtering_join"=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_enable_density_improvements"=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_enable_extended_stats"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_enhanced_filter_push"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_extended_cursor_sharing"	='UDO'	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_extended_cursor_sharing_rel"='SIMPLE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_extended_stats_usage_control"=224	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_extend_jppd_view_types"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_fast_access_pred_analysis"=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_fast_pred_transitivity"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_filter_pred_pullup"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_fkr_index_cost_bias"	=10	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_group_by_placement"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_improve_selectivity"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_join_elimination_enabled"=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_join_factorization"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_join_order_control"	=3	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_join_sel_sanity_check"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_max_permutations"		=2000	SCOPE=SPFILE;
ALTER SYSTEM  SET optimizer_mode			='ALL_ROWS' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_mode_force"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_multi_level_push_pred"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_native_full_outer_join"	='FORCE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_new_join_card_computation"=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_null_aware_antijoin"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_order_by_elimination_enabled"=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_or_expansion"		='DEPTH' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_outer_to_anti_enabled"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_push_down_distinct"	=0	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_push_pred_cost_based"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_rownum_bind_default"	=10	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_rownum_pred_based_fkr"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_skip_scan_enabled"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_sortmerge_join_inequality"=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_squ_bottomup"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_star_tran_in_with_clause"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_system_stats_usage"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_table_expansion"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_transitivity_retain"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_try_st_before_jppd"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_undo_cost_change"		='11.2.0.1' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_unnest_corr_set_subq"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_unnest_disjunctive_subq"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_use_cbqt_star_transformation"=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_use_feedback"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optim_new_default_join_sel"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_optim_peek_user_binds"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "__oracle_base"			='C:\app' SCOPE=SPFILE;
ALTER SYSTEM  SET "_ordered_nested_loop"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_or_expand_nvl_predicate"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_parallel_broadcast_enabled"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_partition_view_enabled"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "__pga_aggregate_target"		=240M	SCOPE=SPFILE;
ALTER SYSTEM  SET "_pivot_implementation_method"	='CHOOSE' SCOPE=SPFILE;
ALTER SYSTEM  SET plsql_warnings			='DISABLE:ALL' SCOPE=SPFILE;
ALTER SYSTEM  SET "_pred_move_around"			=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_pre_rewrite_push_pred"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET processes				=150	SCOPE=SPFILE;
ALTER SYSTEM  SET "_push_join_predicate"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_push_join_union_view"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_push_join_union_view2"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_px_minus_intersect"			=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_px_pwg_enabled"			=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_px_ual_serial_input"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET query_rewrite_enabled			='TRUE'	SCOPE=SPFILE;
ALTER SYSTEM  SET "_query_rewrite_setopgrw_enable"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET remote_login_passwordfile		='EXCLUSIVE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_remove_aggr_subquery"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_replace_virtual_columns"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET result_cache_max_size			=1536K	SCOPE=SPFILE;
ALTER SYSTEM  SET "_right_outer_hash_enable"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "__sga_target"			=352M	SCOPE=SPFILE;
ALTER SYSTEM  SET "__shared_io_pool_size"		=0	SCOPE=SPFILE;
ALTER SYSTEM  SET "__shared_pool_size"			=128M	SCOPE=SPFILE;
ALTER SYSTEM  SET skip_unusable_indexes			=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_sql_model_unfold_forloops"		='RUN_TIME' SCOPE=SPFILE;
ALTER SYSTEM  SET "_sqltune_category_parsed"		='DEFAULT' SCOPE=SPFILE;
ALTER SYSTEM  SET "__streams_pool_size"			=0	SCOPE=SPFILE;
ALTER SYSTEM  SET "_subquery_pruning_enabled"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_subquery_pruning_mv_enabled"	=FALSE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_table_scan_cost_plus_one"		=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET undo_tablespace			='UNDOTBS1' SCOPE=SPFILE;
ALTER SYSTEM  SET "_union_rewrite_for_gs"		='YES_GSET_MVS'	SCOPE=SPFILE;
ALTER SYSTEM  SET "_unnest_subquery"			=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "_use_column_stats_for_function"	=TRUE	SCOPE=SPFILE;
ALTER SYSTEM  SET "__dg_broker_service_names"		=''	SCOPE=SPFILE;
ALTER SYSTEM  SET dg_broker_config_file1		=''	SCOPE=SPFILE;	
ALTER SYSTEM  SET dg_broker_config_file2		=''	SCOPE=SPFILE;	
ALTER SYSTEM  SET fal_client				=''	SCOPE=SPFILE;	
ALTER SYSTEM  SET fal_server				=''	SCOPE=SPFILE;	
ALTER SYSTEM  SET log_archive_dest_1			=''	SCOPE=SPFILE;	
ALTER SYSTEM  SET log_archive_dest_2			=''	SCOPE=SPFILE;	
ALTER SYSTEM  SET Log_archive_config                    =''     SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
spool off;

Remarque : l’intérêt n’est que de pouvoir refaire l’opération plusieurs fois
==========================================================
Créer un fichier 3_SCRIPT_PRIM_SPFILE_APRES.SQL
Contenant

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
spool d:\3_SCRIPT_PRIM_SPFILE_APRES.log append
----------------------------------------------------------------------------
--                Parametrage  du spfile pour la base PRIM                --
----------------------------------------------------------------------------
ALTER SYSTEM SET "__db_cache_size"	=128M	SCOPE=SPFILE;
ALTER SYSTEM SET "__dg_broker_service_names"='PRIM_DGB' SCOPE=SPFILE;
ALTER SYSTEM SET "__pga_aggregate_target"=204M	SCOPE=SPFILE;
ALTER SYSTEM SET "__sga_target"	=388M	SCOPE=SPFILE;
ALTER SYSTEM SET "__shared_pool_size"	=236M	SCOPE=SPFILE;
ALTER SYSTEM SET "__streams_pool_size"	=4M	SCOPE=SPFILE;
ALTER SYSTEM SET audit_file_dest	='C:\APP\ADMIN\PRIM\ADUMP'	SCOPE=SPFILE;
ALTER SYSTEM SET audit_trail		='DB'	SCOPE=SPFILE;
ALTER SYSTEM SET core_dump_dest		='C:\APP\DIAG\RDBMS\PRIM\PRIM\CDUMP'	SCOPE=SPFILE;
ALTER SYSTEM SET db_file_name_convert	='STBY','PRIM'	SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert	='STBY','PRIM'	SCOPE=SPFILE;
ALTER SYSTEM SET dg_broker_config_file1	='C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\DR1PRIM.DAT'	SCOPE=SPFILE;
ALTER SYSTEM SET dg_broker_config_file2	='C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\DR2PRIM.DAT'	SCOPE=SPFILE;
ALTER SYSTEM SET fal_client		='PRIM'	SCOPE=SPFILE;
ALTER SYSTEM SET fal_server		='STBY'	SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_1	='LOCATION=USE_DB_RECOVERY_FILE_DEST','VALID_FOR=(ALL_LOGFILES,ALL_ROLES)','DB_UNIQUE_NAME="PRIM"' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_2	='service="STBY"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="STBY" net_timeout=30','valid_for=(all_logfiles,primary_role)'	SCOPE=SPFILE;
spool off;

==========================================================
Créer un fichier 4_SCRIPT_STBY-PRIM_SPFILE_APRES.SQL
Contenant

Code:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
 
 
spool d:\4_SCRIPT_STBY-PRIM_SPFILE_APRES.log append
----------------------------------------------------------------------------
--  Parametrage  du spfile pour la base PRIM et STBY  (Tronc commun)      --
----------------------------------------------------------------------------
ALTER SYSTEM  SET "__java_pool_size"			=4M SCOPE=SPFILE;
ALTER SYSTEM  SET "__large_pool_size"			=4M SCOPE=SPFILE;
ALTER SYSTEM  SET "__shared_io_pool_size"		=0 SCOPE=SPFILE;
ALTER SYSTEM  SET "_aggregation_optimization_settings"	=0 SCOPE=SPFILE;
ALTER SYSTEM  SET "_always_anti_join"			='CHOOSE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_always_semi_join"			='CHOOSE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_and_pruning_enabled"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_b_tree_bitmap_plans"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_bloom_filter_enabled"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_bloom_folding_enabled"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_bloom_pruning_enabled"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_complex_view_merging"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_compression_compatibility"		='11.2.0.0.0' SCOPE=SPFILE;
ALTER SYSTEM  SET "_connect_by_use_union_all"		='TRUE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_convert_set_to_join"		=FALSE SCOPE=SPFILE;
ALTER SYSTEM  SET "_cost_equality_semi_join"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_cpu_to_io"				=0 SCOPE=SPFILE;
ALTER SYSTEM  SET "_dimension_skip_null"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_eliminate_common_subexpr"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_enable_type_dep_selectivity"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_fast_full_scan_enabled"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_first_k_rows_dynamic_proration"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_gby_hash_aggregation_enabled"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_generalized_pruning_enabled"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_globalindex_pnum_filter_enabled"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_gs_anti_semi_join_allowed"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_improved_outerjoin_card"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_improved_row_length_enabled"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_index_join_enabled"			=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_ksb_restart_policy_times"		='0' SCOPE=SPFILE;
ALTER SYSTEM  SET "_ksb_restart_policy_times"		='120' SCOPE=SPFILE;
ALTER SYSTEM  SET "_ksb_restart_policy_times"		='240' SCOPE=SPFILE;
ALTER SYSTEM  SET "_ksb_restart_policy_times"		='60' SCOPE=SPFILE;
ALTER SYSTEM  SET "_left_nested_loops_random"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_local_communication_costing_enabled"=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_minimal_stats_aggregation"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_mmv_query_rewrite_enabled"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_new_initial_join_orders"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_new_sort_cost_estimate"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_nlj_batching_enabled"		=1 SCOPE=SPFILE;
ALTER SYSTEM  SET "_optim_adjust_for_part_skews"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optim_enhance_nnull_detection"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optim_new_default_join_sel"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optim_peek_user_binds"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_adaptive_cursor_sharing"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_better_inlist_costing"	='ALL' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_cbqt_no_size_restriction"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_coalesce_subqueries"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_complex_pred_selectivity"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_compute_index_stats"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_connect_by_combine_sw"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_connect_by_cost_based"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_connect_by_elim_dups"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_correct_sq_selectivity"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_cost_based_transformation"='LINEAR' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_cost_hjsmj_multimatch"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_cost_model"		='CHOOSE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_dim_subq_join_sel"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_distinct_agg_transform"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_distinct_elimination"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_distinct_placement"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_eliminate_filtering_join"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_enable_density_improvements"=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_enable_extended_stats"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_enhanced_filter_push"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_extend_jppd_view_types"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_extended_cursor_sharing"	='UDO' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_extended_cursor_sharing_rel"='SIMPLE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_extended_stats_usage_control"=224 SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_fast_access_pred_analysis"=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_fast_pred_transitivity"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_filter_pred_pullup"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_fkr_index_cost_bias"	=10 SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_group_by_placement"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_improve_selectivity"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_join_elimination_enabled"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_join_factorization"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_join_order_control"	=3 SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_join_sel_sanity_check"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_max_permutations"		=2000 SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_mode_force"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_multi_level_push_pred"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_native_full_outer_join"	='FORCE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_new_join_card_computation"=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_null_aware_antijoin"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_or_expansion"		='DEPTH' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_order_by_elimination_enabled"=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_outer_to_anti_enabled"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_push_down_distinct"	=0 SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_push_pred_cost_based"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_rownum_bind_default"	=10 SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_rownum_pred_based_fkr"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_skip_scan_enabled"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_sortmerge_join_inequality"=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_squ_bottomup"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_star_tran_in_with_clause"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_system_stats_usage"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_table_expansion"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_transitivity_retain"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_try_st_before_jppd"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_undo_cost_change"		='11.2.0.1' SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_unnest_corr_set_subq"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_unnest_disjunctive_subq"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_use_cbqt_star_transformation"=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_optimizer_use_feedback"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_or_expand_nvl_predicate"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_ordered_nested_loop"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_parallel_broadcast_enabled"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_partition_view_enabled"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_pivot_implementation_method"	='CHOOSE' SCOPE=SPFILE;
ALTER SYSTEM  SET "_pre_rewrite_push_pred"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_pred_move_around"			=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_push_join_predicate"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_push_join_union_view"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_push_join_union_view2"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_px_minus_intersect"			=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_px_pwg_enabled"			=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_px_ual_serial_input"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_query_rewrite_setopgrw_enable"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_remove_aggr_subquery"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_replace_virtual_columns"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_right_outer_hash_enable"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_selfjoin_mv_duplicates"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_sql_model_unfold_forloops"		='RUN_TIME' SCOPE=SPFILE;
ALTER SYSTEM  SET "_sqltune_category_parsed"		='DEFAULT'  SCOPE=SPFILE;
ALTER SYSTEM  SET "_subquery_pruning_enabled"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_subquery_pruning_mv_enabled"	=FALSE SCOPE=SPFILE;
ALTER SYSTEM  SET "_table_scan_cost_plus_one"		=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_union_rewrite_for_gs"		='YES_GSET_MVS' SCOPE=SPFILE;
ALTER SYSTEM  SET "_unnest_subquery"			=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET "_use_column_stats_for_function"	=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET archive_lag_target			=0 SCOPE=SPFILE;
ALTER SYSTEM  SET compatible				='11.2.0.0.0' SCOPE=SPFILE;
ALTER SYSTEM  SET db_create_file_dest			='C:\app\oradata' SCOPE=SPFILE;
ALTER SYSTEM  SET db_domain				='' SCOPE=SPFILE;
ALTER SYSTEM  SET db_recovery_file_dest			='C:\app\flash_recovery_area' SCOPE=SPFILE;
ALTER SYSTEM  SET db_recovery_file_dest_size		=3852M SCOPE=SPFILE;
ALTER SYSTEM  SET db_ultra_safe				='DATA_AND_INDEX' SCOPE=SPFILE;
ALTER SYSTEM  SET dg_broker_start			=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET diagnostic_dest			='C:\APP' SCOPE=SPFILE;
ALTER SYSTEM  SET Log_archive_config                    ='DG_CONFIG=(PRIM,STBY)' SCOPE=SPFILE;
ALTER SYSTEM  SET log_archive_dest_state_1		='ENABLE' SCOPE=SPFILE;
ALTER SYSTEM  SET log_archive_dest_state_2		='ENABLE' SCOPE=SPFILE;
ALTER SYSTEM  SET log_archive_format			='ARC%S_%R.%T' SCOPE=SPFILE;
ALTER SYSTEM  SET log_archive_max_processes		=30 SCOPE=SPFILE;
ALTER SYSTEM  SET log_archive_min_succeed_dest		=1 SCOPE=SPFILE;
ALTER SYSTEM  SET log_archive_trace			=0 SCOPE=SPFILE;
ALTER SYSTEM  SET log_buffer				=5611520 SCOPE=SPFILE;
ALTER SYSTEM  SET memory_target				=592M SCOPE=SPFILE;
ALTER SYSTEM  SET open_cursors				=300 SCOPE=SPFILE;
ALTER SYSTEM  SET optimizer_dynamic_sampling		=2 SCOPE=SPFILE;
ALTER SYSTEM  SET optimizer_mode			='ALL_ROWS' SCOPE=SPFILE;
ALTER SYSTEM  SET plsql_warnings			='DISABLE:ALL'  SCOPE=SPFILE;
ALTER SYSTEM  SET processes				=150 SCOPE=SPFILE;
ALTER SYSTEM  SET query_rewrite_enabled			='TRUE' SCOPE=SPFILE;
ALTER SYSTEM  SET remote_login_passwordfile		='EXCLUSIVE' SCOPE=SPFILE;
ALTER SYSTEM  SET resource_manager_plan			='SCHEDULER[0x3009]:DEFAULT_MAINTENANCE_PLAN' SCOPE=SPFILE;
ALTER SYSTEM  SET result_cache_max_size			=1536K SCOPE=SPFILE;
ALTER SYSTEM  SET skip_unusable_indexes			=TRUE SCOPE=SPFILE;
ALTER SYSTEM  SET standby_file_management		='AUTO' SCOPE=SPFILE;
ALTER SYSTEM  SET undo_tablespace			='UNDOTBS1' SCOPE=SPFILE;
spool off;

==========================================================
Créer un fichier 5_SCRIPT_PRIM_REDO_APRES.SQL
Contenant

Code:

1
2
3
4
5
6
7
8
 
spool d:\5_SCRIPT_PRIM_REDO_APRES.log append
ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;
ALTER DATABASE ADD standby logfile thread 1 GROUP 4 size 52428800, GROUP 5 size 52428800, GROUP 6 size 52428800, GROUP 7 size 51200K;
spool off;

==========================================================
Créer un fichier ShutDownAndStartup.SQL
Contenant

Code:

1
2
3
 
SHUTDOWN IMMEDIATE;
STARTUP;

==========================================================
Créer un fichier FichierCas71Action3V2.SQL
Contenant

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
  STARTUP CLONE NOMOUNT;
   duplicate target database
   For standby
   BACKUP LOCATION 'C:\app\flash_recovery_area'
   spfile
   PARAMETER_VALUE_CONVERT  'PRIM','STBY'
   SET DB_FILE_NAME_CONVERT 'PRIM','STBY'
   SET LOG_FILE_NAME_CONVERT  'PRIM','STBY'
   SET DB_UNIQUE_NAME='STBY'
   SET fal_client = 'STBY'
   SET fal_server ='PRIM'
   SET standby_file_management = 'AUTO'
   SET log_archive_config = 'dg_config=(PRIM,STBY)'
SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST','VALID_FOR=(ALL_LOGFILES,ALL_ROLES)','DB_UNIQUE_NAME="STBY"'	
#SET log_archive_dest_2='service="PRIM"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="PRIM" net_timeout=30','valid_for=(all_logfiles,primary_role)'
SET log_archive_dest_2='service="PRIM"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="PRIM" net_timeout=30','valid_for=(all_logfiles,primary_role)'
   SET LOG_ARCHIVE_DEST_STATE_1='ENABLE'
   SET LOG_ARCHIVE_DEST_STATE_2='ENABLE'
   SET db_ultra_safe = 'DATA_AND_INDEX'
 nofilenamecheck;

Remarque : Attention si vous utilisez une version 10 d’oracle il faudra démarrer la base avant et donc modifier ce script.
STARTUP CLONE NOMOUNT;
Ne fonctionnant qu’avec Oracle 11
==========================================================
Créer un fichier SCRIPT_STBY_SPFILE_APRES.SQL
Contenant

Code:

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
36
37
38
 
spool d:\SCRIPT_STBY_SPFILE_APRES.log append
ALTER SYSTEM  SET log_archive_dest_1	=LOCATION=USE_DB_RECOVERY_FILE_DEST','VALID_FOR=(ALL_LOGFILES,ALL_ROLES)','DB_UNIQUE_NAME="STBY"'	SCOPE=SPFILE;	
--  Archivage Valeur Modifiée
ALTER SYSTEM  SET db_file_name_convert	='PRIM','STBY'	SCOPE=SPFILE;	
--  Base de données de secours Valeur Modifiée
ALTER SYSTEM  SET log_file_name_convert	='PRIM','STBY'	SCOPE=SPFILE;
ALTER SYSTEM  SET fal_client		='STBY'	SCOPE=SPFILE;	
--  Base de données de secours Valeur Modifiée
ALTER SYSTEM  SET fal_server		='PRIM'	SCOPE=SPFILE;	
--  Base de données de secours Valeur Modifiée
ALTER SYSTEM  SET core_dump_dest	='c:\app\diag\rdbms\stby\stby\cdump'	SCOPE=SPFILE;	
--  Diagnostics et statistiques Valeur Modifiée
ALTER SYSTEM  SET log_archive_config	=’DG_CONFIG=(STBY,PRIM)’	SCOPE=SPFILE;	
--  Sauvegarde et récupération Valeur Modifiée
ALTER SYSTEM  SET "__db_cache_size"	=224M	SCOPE=SPFILE;	
-- Non Modifiée
ALTER SYSTEM  SET "__dg_broker_service_names"	='STBY_DGB'	SCOPE=SPFILE;	
-- Non Modifiée
ALTER SYSTEM  SET "__pga_aggregate_target"	=244M	SCOPE=SPFILE;	
-- Non Modifiée
ALTER SYSTEM  SET "__sga_target"	=348M	SCOPE=SPFILE;	
-- Non Modifiée
ALTER SYSTEM  SET "__shared_pool_size"	=108M	SCOPE=SPFILE;	
-- Non Modifiée
ALTER SYSTEM  SET "__streams_pool_size"	=0	SCOPE=SPFILE;	
-- Non Modifiée
ALTER SYSTEM  SET audit_file_dest	='C:\APP\ADMIN\STBY\ADUMP'	SCOPE=SPFILE;	
-- Non Modifiée
ALTER SYSTEM  SET audit_trail		='OS' 	SCOPE=SPFILE;	
-- Non Modifiée
ALTER SYSTEM  SET db_unique_name	='STBY'	SCOPE=SPFILE;	
-- Non Modifiée
ALTER SYSTEM  SET dg_broker_config_file1='C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\DR1STBY.DAT'	SCOPE=SPFILE;	
-- Non Modifiée
ALTER SYSTEM  SET dg_broker_config_file2='C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\DR2STBY.DAT'	SCOPE=SPFILE;	
-- Non Modifiée
spool off;

==========================================================
Créer un fichier ShutDownAndStartupMount.SQL;
Contenant

Code:

1
2
3
4
 
 
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

========================================================
Vous devez avoir dans le meme repertoire les fichiers suivants
GoCreateBrocker_Script.SQL
BS_BACKUP_FOR_STANDBY.SQL
2_SCRIPT_PRIM_SPFILE_AVANT.sql
3_SCRIPT_PRIM_SPFILE_APRES.SQL
4_SCRIPT_STBY-PRIM_SPFILE_APRES.SQL
5_SCRIPT_PRIM_REDO_APRES.SQL
ShutDownAndStartup.SQL
FichierCas71Action3V2.SQL
SCRIPT_STBY_SPFILE_APRES.SQL
4_SCRIPT_STBY-PRIM_SPFILE_APRES.SQL
ShutDownAndStartupMount.SQL;

Il ne vous reste plus sous dos a vous rendre dans le dis repertoire et lancer la commande

Code:

1
2
 
c:>SQLPLUS SYS/DBA@PRIM AS SYSDBA @GoCreateBrocker_Script.SQL

Sous linux il vous faudrat transformer
les chemins windows exemple APP\PRODUCT\11.2.0\DBHOME_1\DATABASE
en chemin linux exemple APP/PRODUCT/11.2.0/DBHOME_1/DATABASE
Tous les scripts etant executé via sqlplus cela ne pose aucun probleme
=========================================================
Si vous désirez avoir une configuration du type
Une base Principale Nommé PRIM sur l’ordinateur VM7OracleOrcl avec password DBA pour SYS
Une base Standby Nommé STBY sur l’ordinateur VM7Clone

Créer un fichier listener.ora
Contenant

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=STBY       )(ORACLE_HOME=C:\APP\PRODUCT\11.2.0\DBHOME_1)(SID_NAME=STBY))
                            (SID_DESC=(GLOBAL_DBNAME=PRIM_DGMGRL)(ORACLE_HOME=C:\APP\PRODUCT\11.2.0\DBHOME_1)(SID_NAME=PRIM))
                            (SID_DESC=(GLOBAL_DBNAME=PRIM       )(ORACLE_HOME=C:\APP\PRODUCT\11.2.0\DBHOME_1)(SID_NAME=PRIM))
                            (SID_DESC=(GLOBAL_DBNAME=STBY_DGMGRL)(ORACLE_HOME=C:\APP\PRODUCT\11.2.0\DBHOME_1)(SID_NAME=STBY)))
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = VM7Clone)(PORT = 1521))
  )
ADR_BASE_LISTENER = C:\app\product\11.2.0\dbhome_1\log
SAVE_CONFIG_ON_STOP_LISTENER = TRUE
LOGGING_LISTENER = ON

==========================================================
Modifier le fichier TNSNAMES.ora
de la facon suivant

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
PRIM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = VM7OracleOrcl)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = PRIM)
      (SERVER = DEDICATED)
    )
  )
 
STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = VM7Clone)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = STBY)
      (SERVER = DEDICATED)
    )
  )

remarque :a faire sur les deux machines
==========================================================
Supprimez de GoCreateBrocker_Script.SQL

Code:

RMAN TARGET SYS/DBA@PRIM auxiliary SYS/CHANGE_ON_INSTALL@STBY NOCATALOG cmdfile=FichierCas71Action3V2.SQL

et executer cette commande a la main puis copiez le resultat sur la machine VM7Clone
==========================================================
Pour résoudre le probleme de connexion ci dessus abordé j'ai opté pour la solution d'arreter les par-feux windows via le gestionnaire de service sur les deux machines
=========================================================
Merci a pomalaix et surtout a jkofr pour votre collaboration et votre patience.

Je l'aisse cette discussion ouverte de facon a permettre aux prof d'apporter leurs remarques contructives sur les scripts.