Tuesday, January 22, 2008

Creating a Data Guard Configuration

1) Ensure the Primary database is in ARCHIVELOG mode:

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /export/home/oracle/temp/oracle/arch
Oldest online log sequence 7
Current log sequence 9
SQL> alter database close;

Database altered.

SQL> alter database archivelog;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

Modify the Primary database init.ora so that log_archive_start=true and restart
the instance. Verify that database is in archive log mode and that automatic
archiving is enabled.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /export/home/oracle/temp/oracle/arch
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9

2) Create a backup of the Primary database:

You can use an existing backup of the Primary database as long as you have the
archive logs that have been generated since that backup. You may also take a
hot backup as long as you have all archive logs through the end of the backup
of the last tablespace. To create a cold backup do the following:

SQL>
SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------
/export/home/oracle/temp/oracle/data/sys.dbf

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Create a backup of all datafiles and online redo logs using an OS command or
utility. A backup of the online redo logs is necessary to facilitate switchover.

Once complete startup the instance:

SQL> startup
ORACLE instance started.

Total System Global Area 80512156 bytes
Fixed Size 279708 bytes
Variable Size 71303168 bytes
Database Buffers 8388608 bytes
Redo Buffers 540672 bytes
Database mounted.
Database opened.

3) Connect to the primary database and create the standby control file:

SQL> alter database create standby controlfile as
'/export/home/oracle/temp/oracle/data/backup/standby.ctl';

Database altered.

4) Copy files to the Standby host:

Copy the backup datafiles, standby controlfile, all available archived redo logs,
and online redo logs from the primary site to the standby site. Copying of the
online redo logs is necessary to facilitate switchover.

If the standby is on a separate site with the same directory structure as the
primary database then you can use the same path names for the standby files as
the primary files. In this way, you do not have to rename the primary datafiles
in the standby control file. If the standby is on the same site as the primary
database, or the standby database is on a separate site with a different
directory structure the you must rename the primary datafiles in the standby
control file after copying them to the standby site. This can be done using
the db_file_name_convert and log_file_name_convert parameters or by manually
using the alert database statements.

5) Set the initialization parameters for the primary database:

It is suggested that you maintain two init.ora’s on both the primary and the
standby. This allows you to facilitate role reversal during switchover
operations more easily.

Primary init.ora on Primary host:

log_archive_dest_1='LOCATION=/export/home/oracle/temp/oracle/arch'
log_archive_dest_2='SERVICE=DGD01_hasunclu2 reopen=60'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=%t_%s.dbf
log_archive_start=true
remote_archive_enable=true

Create the standby initialization parameter file and set the initialization
parameters for the standby database. Depending on your configuration, you may
need to set filename conversion parameters.

Standby init.ora on Primary host:

log_archive_dest_1='LOCATION=/export/home/oracle/temp/oracle/arch'
log_archive_dest_state_1=enable
log_archive_format=%t_%s.dbf
log_archive_start=true
standby_archive_dest=/export/home/oracle/temp/oracle/arch
standby_file_management=auto
fal_server=DGD01_hasunclu2
fal_client=DGD01_hasunclu1
remote_arhive_enable=true

NOTE: In the above example db_file_name_convert and log_file_name_convert are
not needed as the directory structure on the two hosts are the same. If the
directory structure is not the same then setting of these parameters is
recommended. Please reference notes 47325.1 and 47343.1 for further
information.

Copy the two init.ora’s from the Primary host to the Standby host. You must
modify the Primary init.ora on the Standby host to have log_archive_dest_2 use
the alias that points to the Primary host (ie DGD01_hasunclu1). You must
modify the Standby init.ora on the standby host to have fal_server and
fal_client use the aliases when standby is running on the Primary host
(ie fal_server=DGD01_hasunclu1 and fal_client=DGD01_hasunclu2).

6) Configure networking components:

On the Primary host create a net service name that the Primary database can
use to connect to the Standby database. On the Primary host create a net
service name that Standby, when running on the Primary host, can use to
connect to the Primary, when it is running on the Standby host. Example from
Primary’s host tnsnames.ora:

DGD01_hasunclu1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hasunclu1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DGD01)
(SERVER = DEDICATED)
)
)
DGD01_hasunclu2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hasunclu2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DGD01)
(SERVER = DEDICATED)
)
)

The above two net service names must exist in the Standby hosts tnsnames.ora
also.

You must also configure a listener on the standby database. If you plan to
manage this standby database using the Data Guard broker, you must configure
the listener to use the TCP/IP protocol and statically register the standby
database service using its SID.


7) Start the standby instance and mount the standby database.

oracle@hasunclu2:/export/home/oracle/temp/oracle> sqlplus "/ as sysdba"

SQL*Plus: Release 9.0.1.0.0 - Production on Thu Mar 14 18:00:57 2002

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/initDGD.ora
ORACLE instance started.

Total System Global Area 80512156 bytes
Fixed Size 279708 bytes
Variable Size 71303168 bytes
Database Buffers 8388608 bytes
Redo Buffers 540672 bytes
SQL> alter database mount standby database;

Database altered.

SQL>

8) Create standby redo log files, if necessary:

Standby redo logs are necessary for the higher protection levels such as
Guaranteed, Instant, and Rapid. In these protection modes LGWR from the
Primary host writes transactions directly to the standby redo logs.
This enables no data loss solutions and reduces the amount of data loss
in the event of failure. Standby redo logs are not necessary if you are using
the delayed protection mode.

If you configure standby redo on the standby then you should also configure
standby redo logs on the primary database. Even though the standby redo logs
are not used when the database is running in the primary role, configuring
the standby redo logs on the primary database is recommended in preparation
for an eventual switchover operation.

Standby redo logs must be archived before the data can be applied to the
standby database. The standby archival operation occurs automatically, even if
the standby database is not in ARCHIVELOG mode. However, the archiver process
must be started on the standby database. Note that the use of the archiver
process (ARCn) is a requirement for selection of a standby redo log.

You must have the same number of standby redo logs on the standby as you have
online redo logs on production. They must also be exactly the same size.

The following syntax is used to create standby redo logs:

SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_1.dbf' size 20m;

Database altered.
SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_2.dbf' size 20m;

Database altered.

SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_3.dbf' size 20m;

Database altered.

SQL>

9) Manually change the names of the primary datafiles and redo logs in the
standby control file for all files not automatically renamed using
DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT as noted in step 5. Datafile
names can be changed on the standby at a mounted state with the following
syntax:

SQL> alter database rename file
2 '/export/home/oracle/temp/oracle/data/sys.dbf'
3 to
4 '/export/home/oracle/temp/oracle/data2/sys.dbf';

10) Stop and restart the listeners:

On the primary database, and start the listener on the standby database so that
changes made to the listener.ora can be implemented.

11) Activate parameter changes:

Manually enable initialization parameter changes on the primary database
so that it can initiate archiving to the standby site.

At runtime, the LOG_ARCHIVE_DEST_n initialization parameter can be changed
using ALTER SYSTEM and ALTER SESSION statements. Activate the changes made to
these parameters by either bouncing the instance or activating via alter system.
For example:

SQL> alter system set log_archive_dest_2='SERVICE=DGD01_hasunclu2 reopen=60';

System altered.

12) Verify that automatic archiving is occurring:

On the Primary database switch a log and verfy that it has been shipped
properly using the v$archive_dest view.

SQL> alter system switch logfile;

System altered.

SQL> select status, error from v$archive_dest where dest_id=2;

STATUS ERROR
--------- -------------------------------------------------------
VALID

SQL>

13) Optionally place Standby database in managed recovery:

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> exit

38 comments:

Anonymous said...

Hi Ayyu,

You just copy and pasted the metalink document id Note:180031.1

Please mention the credential when its not your work.

Anonymous said...

Obviously he can't give the reference since it is not supposed to be in all probability - so, I thank both Ayyu and you for the notes and the reference! :) ;)

Anonymous said...

Thanks Ayyu for your post.
it is very useful.

I will try it and let you know if any issues.

Anonymous said...

Thats good..
eventhough , its a metalink note or copy from any where..
It is helpful for all user community..
~cheers

Anonymous said...

Thanks for the post. It is useful.

Anonymous said...

It does not matter if it is pasted from metalink or from other sites as only corporate user have access to metalink. It is a great job from ayyu as u gave the test of metalink to the outside world also. Keep on posting valuable blogs

Anonymous said...

thanks much ayyu. keep doing the great work!!! its just super work you are doing.

Anonymous said...

Can you copy/paste all the metalink Docs like this please?
You are doing an awsome job!
Keep up the good cheating work!

Anonymous said...

Just want to say what a great blog you got here!
I've been around for quite a lot of time, but finally decided to show my appreciation of your work!

Thumbs up, and keep it going!

Cheers
Christian, iwspo.net

Anonymous said...

Just want to say what a great blog you got here!
I've been around for quite a lot of time, but finally decided to show my appreciation of your work!

Thumbs up, and keep it going!

Cheers
Christian, iwspo.net

Anonymous said...

why two init.ora files are needed?

Anonymous said...

thank u.
Though it is metalink notes, its useful those who dont hav metalink access.
Thanks..

Ravi kumar said...

Hi Ayyu,
My self Ravi Kumar

can i configure Physical standby in a stand alone system(single).
If yes How ?
Thank u

shana said...

It is seriously helpful for those who dont have access to metalink ... anyway it's a great help big thanks :)

Anonymous said...

I have been exploring for a bit for any high quality articles or weblog posts on this kind of area .
Exploring in Yahoo I ultimately stumbled upon this website.
Reading this information So i am satisfied to exhibit that I have an incredibly good uncanny feeling I found out exactly what I needed.
I most no doubt will make certain to don?t omit this website and give it a glance on a continuing basis.
Feel free to visit my blog post ; Hardcore Sex

Anonymous said...

Hi there would you mind sharing which blog platform you're using? I'm
planning to start my own blog soon but I'm having a tough time selecting between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design seems different then most blogs and I'm looking
for something completely unique. P.S Apologies for being off-topic but I had to ask!
Also see my site > Horny Pussy Eating Lesbians

Anonymous said...

hello there and thank you for your info – I have certainly picked up something new from right
here. I did however expertise some technical issues using this
website, since I experienced to reload the web site many times previous to
I could get it to load properly. I had been wondering if your hosting
is OK? Not that I am complaining, but sluggish loading instances times will often affect
your placement in google and could damage your quality score if ads and marketing with Adwords.

Anyway I'm adding this RSS to my email and can look out for much more of your respective exciting content. Ensure that you update this again very soon.
my site: click through the following web site

Anonymous said...

I do not even know how I ended up here, but I thought this post was great.
I do not know who you are but certainly you are going to a famous blogger if you are not already ;) Cheers!
my webpage :: http://amazingteenbabes.com

Anonymous said...

I drop a leave a response whenever I like a post on a site or I
have something to add to the conversation. Usually
it is a result of the passion displayed in the article I browsed.

And after this article "Creating a Data Guard Configuration".
I was moved enough to post a leave a responsea response :-P I do have 2 questions for
you if you don't mind. Could it be only me or does it look like like a few of these remarks appear like they are left by brain dead folks? :-P And, if you are writing at additional online sites, I'd like to
keep up with anything new you have to post. Would you list every one of all your shared sites like your twitter feed, Facebook page or linkedin profile?
My homepage :: biggest dick gay in porn

Anonymous said...

Hello Dear, are you in fact visiting this website on a regular basis,
if so then you will without doubt get fastidious know-how.
Also see my web site: www.xxxvideofix.com

Anonymous said...

Thanks for finally writing about > "Creating a Data Guard Configuration" < Loved it!
Stop by my homepage ... porn

Anonymous said...

Hello there, You've done a great job. I will definitely digg it and personally suggest to my friends. I am sure they will be benefited from this web site.
Also visit my homepage ... Hear Me Laugh As I CUM!!!

Anonymous said...

Heya i'm for the first time here. I found this board and I find It truly useful & it helped me out a lot. I hope to give something back and help others like you aided me.
My web page :: Orlando hookah bar

Anonymous said...

I’m not that much of a internet reader to be honest
but your blogs really nice, keep it up! I'll go ahead and bookmark your site to come back down the road. Many thanks
Here is my blog :: Genital Warts Treatment

Anonymous said...

Heya! I just wanted to ask if you ever have any problems with hackers?
My last blog (wordpress) was hacked and I ended up losing several weeks of hard work due to no data backup.

Do you have any methods to protect against hackers?
My website :: the truth teller show

Anonymous said...

Hi there, just wanted to tell you, I liked this blog post.
It was helpful. Keep on posting!

Also visit my web page: http://www.leb4Host.com

Anonymous said...

uhgqmur [url=http://www.guccisprings.com/]グッチ アウトレット[/url] tfazykt wznqrnz [url=http://www.guccisprings.com/]gucci 財布[/url] dncqayh zvxaoen [url=http://www.gucciiget.com/]グッチ バッグ[/url] jgqortf nwjkxpv http://www.lovelovegucci.com/ グッチ 財布 メンズ bfbhjvq xpsniwc http://www.gucciiget.com/ グッチ バッグ yumsdua psbiidl [url=http://www.gucciiget.com/]グッチ メンズ[/url] zcvuxbu jqxcxmr [url=http://www.lovelovegucci.com/]グッチ 財布[/url] qaqdgdw vbyxuyv [url=http://www.gucciiget.com/]グッチ アウトレット[/url] lpvponw hwvsxgh [url=http://www.guccisprings.com/]グッチ 財布[/url] mrxxkym evnnvbs http://www.guccisprings.com/ グッチ バッグ fuvkloc ohomvlu [url=http://www.lovelovegucci.com/]グッチ アウトレット[/url] lbcornu qvpflkk [url=http://www.lovelovegucci.com/]グッチ 財布 メンズ[/url] ypyxeta xisehmm [url=http://www.gucciiget.com/]グッチ 財布[/url] tmotqce jnneopf [url=http://www.lovelovegucci.com/]グッチ バッグ[/url] raojbzp tmyfybz [url=http://www.guccisprings.com/]グッチ バッグ[/url] thijxku aggwnvk [url=http://www.chloefind.com/]クロエ アウトレット[/url] hvssyn fhtjtm [url=http://www.chloefind.com/]クロエ バッグ[/url] nbfbqk miyxzf [url=http://www.chloefind.com/]クロエ 財布[/url] ygyahy flkjsm http://www.chloefind.com/ yyanuq deiohkj [url=http://www.chloe2013ss.com/]クロエ 財布[/url] nkoypl jrslak [url=http://www.chloe2013ss.com/]クロエ バッグ[/url] fjloha tmsgmy [url=http://www.chloe2013ss.com/]クロエ 財布新作[/url] ouovaw otqikq http://www.chloe2013ss.com/ opdupu
gwvdyzs http://www.gucciiget.com/ グッチ アウトレット hxurokg
jekewzq http://www.lovelovegucci.com/ グッチ 財布 oglardv
atuyxul http://www.guccisprings.com/ グッチ バッグ xgqgfsa

Anonymous said...

I really like reading a post that will make people think.
Also, thank you for allowing me to comment!

My web page ... torrent movies download DownloadShield

Anonymous said...

Terrific article! That is the kind of information that should be shared across the net.
Disgrace on Google for no longer positioning this submit upper!
Come on over and talk over with my web site
. Thanks =)

my web site - bmi calculator for adults

Anonymous said...

Complication resolve with Samsung ML-1520 on WIN 8 Pro 64 bit.

Thanks to you !!!!!!
Wonderful !!!!!!

Here is my web-site :: xerox phaser 8560 ink sticks

Anonymous said...

qqvuaz http://eServ.biz/_private/nikefreerun.html tnintv [url=http://eServ.biz/_private/nikefreerun.html]eServ.biz/_private/nikefreerun.html[/url] npfltv [url=http://eServ.biz/_private/nikefreerun.html]nike free run[/url] xqkpar [url=http://eServ.biz/_private/nikefreerun.html]nike free run 2[/url] cedtmh [url=http://eServ.biz/_private/nikefreerun.html]Nike Free 2[/url] kjmyiw http://eServ.biz/Classes/_vti_cnf/jordansale.html cbtgvw [url=http://eServ.biz/Classes/_vti_cnf/jordansale.html]eServ.biz/Classes/_vti_cnf/jordansale.html[/url] cxvkwb [url=http://eServ.biz/Classes/_vti_cnf/jordansale.html]cheap jordans[/url] xrdgub [url=http://eServ.biz/Classes/_vti_cnf/jordansale.html]cheap jordan shoes[/url] czbunc [url=http://eServ.biz/Classes/_vti_cnf/jordansale.html]air jordans[/url] wggevc http://korfbal.be/beta/Stats/guccishoessu.html wpmbfq [url=http://korfbal.be/beta/Stats/guccishoessu.html]korfbal.be/beta/Stats/guccishoessu.html[/url] drbonr [url=http://korfbal.be/beta/Stats/guccishoessu.html]gucci shoes[/url] smszzr [url=http://korfbal.be/beta/Stats/guccishoessu.html]gucci outlet[/url] wwldth [url=http://korfbal.be/beta/Stats/guccishoessu.html]gucci store[/url] ccvljt

Anonymous said...

It's very effortless to find out any matter on web as compared to books, as I found this article at this web site.

my site TeenPorn

Anonymous said...

Hi there! Someone in my Facebook group shared this site with us so I came to look
it over. I'm definitely enjoying the information. I'm book-marking and will be tweeting
this to my followers! Wonderful blog and fantastic design.


Look at my page: http://sexygirlchat.net/

Anonymous said...

Incredible points. Sound arguments. Keep up the amazing spirit.


My homepage; adult movies online

Anonymous said...

I leave a response whenever I especially enjoy a post on a
website or I have something to contribute to the conversation.
Usually it is caused by the sincerness displayed in the article I read.

And on this article "Creating a Data Guard Configuration".
I was excited enough to post a comment :
-) I actually do have 2 questions for you if it's okay. Could it be just me or do a few of the responses come across as if they are coming from brain dead people? :-P And, if you are writing on other online sites, I'd like to follow you.

Could you make a list all of all your social sites like your Facebook page, twitter
feed, or linkedin profile?

Also visit my web site :: http://www.tamporno.net/videos/young-latina-got-anal-sex-22361.html

Anonymous said...

Switched out the cartridge, very same problem with grinding.
Don't know just what to do since its 6 years aged, and no actual repair spots. I might tear it apart and consider it. Thanks.

Here is my site :: Xerox Phaser 8560 Review

Anonymous said...

Thanks ... teamed up with my Lexmark E210 on Windows 8 Pro.



Feel free to visit my webpage :: xerox phaser 8560 ram error ()

Xia Zhang said...

http://www.shopbestgoods.com/
http://www.bestcustomsonline.com/
http://www.nike-jordanshoes.com/
http://www.polo-tshirts.com/
http://www.burberry-factory.com/
http://www.kate-spades.com/
http://www.barbour-factory.com/
http://www.coachlosangeles.com/
http://www.official-coachoutlet.com/
http://www.louisvuittonas.com/
http://www.burberryoutlet2014.com/
http://www.official-mkoutlet.com/
http://www.official-pradaoutlet.com/
http://www.beatsbydreoutlet.net/
http://www.michaelkorsonlineusa.com/
http://www.northsclearance.com/
http://www.ralph-laurensale.com/
http://www.gucci-shoesuk2014.com/
http://www.michael-korsusa.com/
http://www.polo-outlets.com/
http://www.hermes-outletonline.com/
http://www.ralphslauren.co.uk/
http://www.marcjacobsonsale.com/
http://www.mcmworldwides.com/
http://www.warmbootssale.com/
http://www.salongchamppairs.com/
http://www.canada-gooser.com/
http://www.michaelkors.so/
http://www.oakley-sunglassoutlet.com/
http://www.north-faceoutlets.net/
http://www.moncler-clearance.com/
http://www.woolrich-clearance.com/
http://www.barbour-jacketsoutlet.com/
http://www.moncler-jacketsoutletonline.com/
http://www.monsterbeatsbydres.net/
http://www.lv-guccishoesfactory.com/
http://www.cheapdiscountoutlet.com/
http://www.coachsfactoryoutlet.com/
http://www.coach-blackfriday2014.com/
http://www.coach-storeoutletonline.com/
http://www.coach-factorysoutletonline.com/
http://www.coachccoachoutlet.com/
http://www.coach-factories.net/
http://www.coach-pursesoutletonline.com/
http://www.coach-outletsusa.com/
http://www.zxcoachoutlet.com/
http://www.mischristmas.com/
http://www.misblackfriday.com/
http://www.newoutletonlinemall.com/
http://www.ralphlaurenepolo.com/
http://michaelkorsoutlet.mischristmas.com/
http://mcmbackpack.mischristmas.com/
http://monsterbeats.mischristmas.com/
http://northfaceoutlet.mischristmas.com/
http://mk.misblackfriday.com/
http://coachoutlet.misblackfriday.com/
http://coachfactory.misblackfriday.com/
http://uggaustralia.misblackfriday.com/
http://coachpurses.misblackfriday.com/
http://coachusa.misblackfriday.com/
http://coach.misblackfriday.com/
http://michaelkorss.misblackfriday.com/
http://michaelkors.misblackfriday.com/
http://airmax.misblackfriday.com/
http://michael-kors.misblackfriday.com/
https://twitter.com/CoachOutlet2014
https://www.facebook.com/coachoutletstoreonline
https://www.facebook.com/ralphlaurenoutletonline