Dupliquer une base Oracle 10g pour Dataguard ... - Une question aux meilleurs DBA du forum!

visiteur_33 · Novembre 21, 2005, 7:53

Bonjour à tous,

Je suis actuellement en train de créer une configuration Oracle Dataguard entre 2 oracle 10g. Pour se faire, je dois dupliquer ma base primaire vers ma base secondaire. Et c’est la que le bas blesse, je ne sais pas faire… Certes je connais l’aspect “theorique”, mais pas la pratique. Je serais incapable de faire étape par étape, les différentes actions pour dupliquer ma base primaire vers ma base de secours (standby).

Pourriez-vous m’aider en me donnant le processus complet ? Existe-t-il une doc complete sur Internet, je vous avoue ne pas avoir trouvé.

Je sais qu’il existe 2 methodes : RMAN et copie à froid. Est-il possible qu’une personne m’explique comment faire le plus simplement possible ?

Merci de votre aide

quazardous · Novembre 22, 2005, 5:28

https://metalink.oracle.com/

c la ou il faut chercher mais faut une license je crois je ne me souviens plus trop ça fait longtemps que je m y suis inscrit

quazardous · Novembre 22, 2005, 5:36

c est une doc pour la 8i

Subject: How To Create a Standby Database

* goal: How To Create a Standby Database
* fact: Backup / Recovery (BR)
* fact: Oracle Server - Enterprise Edition

fix:

On the Primary database:

Backup datafiles on the primary system.
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ctlfilename
–Note: where ctlfilename is name of the controlfile
ALTER SYSTEM ARCHIVE LOG CURRENT
(This insures consistency in backup, standby controlfile, and logfiles)
Transfer all the duplicate files to the standby site.
Use the same naming convention, or in the initSID.ora for the standby, use
the following parameters:

DB_FILE_STANDBY_NAME_CONVERT (V7)
LOG_FILE_STANDBY_NAME_CONVERT (V7)
or
DB_FILE_NAME_CONVERT (V8)
LOG_FILE_NAME_CONVERT (V8)

–Note: You must use the above parameters if the standby system is on the same
machine as the primary.

–Note: If the above initSID.ora parameters are used, you must use:
ALTER DATABASE RENAME FILE oldname TO newname
When the standby is activated, use:
ALTER DATABASE RENAME FILE oldname TO newname

If a data or log file is renamed at the standby site, or the AS clause is used
in an ALTER DATABASE CREATE DATAFILE, the conversion parameters will not apply
to that file.

On the Standby database:

Put the standby in recovery mode, using the Oracle Server Manager utility.

STARTUP NOMOUNT PFILE=/full/path/to/initSID.ora
ALTER DATABASE MOUNT STANDBY DATABASE
RECOVER STANDBY DATABASE

Then apply archive logs as appropriate.

SOME NOTES:

The standby database must be kept in recovery mode until it is needed (if
ever).
To keep the standby current, manually copy archived redo logs as they are
generated on the primary, then apply them to the standby. (This can be
accomplished best by a batch job).
Any physical changes that occur on the primary must be accomplished on the
standby as well. Added datafiles are added to the standby controlfile
automatically (via redo logs). The file must be manually copied to the standby
system. If the standby can’t find a datafile, recovery will stop. Use ALTER
DATABASE CREATE oldname AS newname or physically copy the datafile to the
standby system.
To maintain (only) a subset of the primary database, ALTER OFFLINE unneeded
datafiles on the standby system; Oracle will just maintain the online
files. Offlined files are not recovered.
You can add or drop log files on the primary without affecting the standby.
If an additional thread is enabled on the primary (using MTS or OPS) you must
recreate the standby controlfile (see (2) above).
If ALTER DATABASE CLEAR UNARCHIVED LOGFILE is performed on the primary, the
standby is rendered invalid. This also occurs if ALTER DATABASE OPEN RESETLOGS
is done. This is because the standby doesn’t then have the archivelogs it needs
to continue.
If you do an ALTER DATABASE CREATE CONTROLFILE on the primary, you must also
create a new controlfile for the standby. First, shut down the standby. Then
do an ALTER DATABASE ARCHIVE LOG CURRENT so the standby will be current on redo.
(Then copy the files over to the standby site). Then, at the standby site,
STARTUP NOMOUNT, then ALTER DATABASE MOUNT STANDBY DATABASE. Finally, RECOVER
STANDBY DATABASE.

TO ACTIVATE THE STANDBY DATABASE:

To activate the standby, first try to copy the most recent archive logs over to
the standby, and apply them. Then ALTER DATABASE ACTIVATE STANDBY DATABASE

Then shutdown the standby to reset file headers and clear all buffers. You
can’t copy online redo logs from the primary to the standby.

The standby is dismounted when activated.

The standby bit in the controlfile is now set, so you can never go back - the
standby is now your primary database. Since the redo log sequence was reset
when the standby was opened, it is a good idea to take a full backup at this
point.

.
Copyright © 2005, Oracle. All rights reserved. Legal Notices and Terms of Use.

BZValoche · Novembre 23, 2005, 9:38

Voici une doc pour la 10g sous Linux.

Doc ID: Note:248382.1
Subject: Creating a 10g Data Guard Physical Standby on Linux
Type: REFERENCE
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 11-SEP-2003
Last Revision Date: 04-MAY-2004

PURPOSE

This document has been created with step-by-step instructions on how
to create a Data Guard Physical Standby environment, derived from the
10G documentation.

Creating a Physical Data Guard scenario in 10G on Linux.

Primary host : stdg2
Standby host : stdg1

Platform : Linux Redhat AS 2.1
Kernel : 2.4.9-e.24

Primary Set up on host stdg2
ORACLE_SID=v10g
Initialization parameters (PRIMARY)
==========================
*.control_files='/u02/admin/v10g/datafiles/control01.ctl'  
*.log_archive_config='DG_CONFIG=(v10g_stdg2,v10g_stdg1)'
*.log_archive_dest_1='LOCATION=/u02/admin/v10g/v10garch/ 
          VALID_FOR=(ALL_LOGFILES,ALL_ROLES) *.db_unique_name=v10g_stdg2'
*.log_archive_dest_2='SERVICE=v10g_stdg1 
                          VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
                          DB_UNIQUE_NAME=v10g_stdg1 LGWR ASYNC REOPEN=10'

*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.db_unique_name='v10g_stdg2'

*.FAL_CLIENT='v10g_stdg2'
*.FAL_SERVER='v10g_stdg1'

*.standby_archive_dest='/u02/admin/v10g/v10garch'
*.standby_file_management='auto'
*.remote_login_passwordfile='EXCLUSIVE'
Place the primary datbase in ARCHIVELOG mode.
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL>ALTER DATABASE ARCHIVELOG;
SQL>ALTER DATABASE OPEN;
SQL>ARCHIVE LOG LIST;

Creating the physical standby.

Take a backup of the primary. (Cold backup was used)

SQL>STARTUP MOUNT (Primary)

SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE
AS <PATH>/STDBY.CTL;

SQL>ALTER DATABASE OPEN;

Create a pfile for the standby database

SQL>CREATE PFILE=<PATH>/INITSTDBY.ORA FROM SPFILE;

Initialization parameter(STANDBY)

*.control_files=’/u02/admin/v10g/datafiles/standby.ctl’
*.log_archive_config=‘DG_CONFIG=(v10g_stdg2,v10g_stdg1)’
*.log_archive_dest_1=‘LOCATION=/u02/admin/v10g/v10garch/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
*.db_unique_name=v10g_stdg1’
*.log_archive_dest_2=‘SERVICE=v10g_stdg2
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=v10g_stdg2 LGWR ASYNC REOPEN=10’

*.log_archive_dest_state_1=‘enable’
*.log_archive_dest_state_2=‘enable’
*.db_unique_name=‘v10g_stdg1’

*.FAL_CLIENT=‘v10g_stdg1’
*.FAL_SERVER=‘v10g_stdg2’

*.standby_archive_dest=’/u02/admin/v10g/v10garch’
*.standby_file_management=‘auto’
*.remote_login_passwordfile=‘EXCLUSIVE’

Set up the listeners (Net Services)

Primary:

LISTENER.ORA
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 138.1.137.235)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/oracle/product/10g)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = v10g)
(ORACLE_HOME = /u01/oracle/product/10g)
(SID_NAME = v10g)
)
)

TNSNAMES.ORA:

v10g_stdg1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 138.1.137.234)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = v10g)
)
)
v10g_stdg2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 138.1.137.235)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = v10g)
)

NOTE: For the standby make sure you change the ip (HOST) from .234 to .235 appropriately
Create the spfile on the standby from the pfile moved from the primary.

SQL>CREATE SPFILE=?/DBS/spfile<sid>.ora
FROM PFILE=INITSTDBY.ORA;

SQL>STARTUP MOUNT;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Check to see if the redo from the primary is being shipped to the standby

On the primary do the following:

SQL>ALTER SYSTEM SWITCH LOGFILE;

SQl>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME

71 15-AUG-03 15-AUG-03
72 15-AUG-03 15-AUG-03
73 15-AUG-03 15-AUG-03
74 15-AUG-03 15-AUG-03
Physical Standby:

SQL&gt;SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V&#036;ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME

71 15-AUG-03 15-AUG-03
72 15-AUG-03 15-AUG-03
73 15-AUG-03 15-AUG-03
74 15-AUG-03 15-AUG-03
Verify if the archived logs are applied successfully on the standby

SQL>SELECT SEQUENCE#,APPLIED FROM
V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP

68 YES
69 YES
70 YES
71 YES
72 YES
73 YES
74 YES
Standby Redo Logs (SRLs)

This is similar to redo logs except that a standby log is used only when the database is in standby mode.

NOTE: The ARCHIVER (ARCn) or the (LGWR) process on the primary can transmit redo data directly to standby logfile.
(The details of SRL’s is not discussed in this document)

To create a standby logfile:

Stop the redo apply on the standby

Alter database recover managed standby database cancel;
&nbsp; Create the SRLs 3 of them on the standby.
 &nbsp; SQL&gt;alter database add standby logfile
  '/u02/admin/v10g/srldest/srl_1.log' size 10m;
Restart the redo apply;

visiteur_33 · Novembre 23, 2005, 7:42

Vraiment merci !

Donc en fait pour dupliquer la base primaire, je fais un "cold backup" (en fait je copie les fichiers .dbf et .ctl), je "copie colle" sur ma base standby, et ca marchera directement lorsque je ferais un startup sans meme faire de restore ou de recover sur la standby ?

b_gregory · Novembre 24, 2005, 6:11

si tu veux dupliquer simplement, tu prends ta sauvegarde à froid.

Par contre si tu veux mettre en oeuvre dataguard et continuer à maintenir la réplication, il faut faire comme écrit :


startup nomount
alter database mount stand by database;
recover standby database;

Avec RMAN, c’est encore plus simple mais il faut avoir mis RMAN en oeuvre…

Je pense pas que tu sois sur le bon forum pour poser ce genre de questions, clubic n’étant pas le forum de rencontres des DBAs. si tu veux plus d’infos, tu peux m’envoyer un MP…

ntouadri · Janvier 22, 2010, 10:23

Bonjour,

c’est quoi la méthode pour optimiser une instance oracle??

et pour optimiser du code pl/sql?

Merci d’avance