backup

[tokra]

I am not a dba, but its what I'm stuck doing right now so I have some questions.
I have a DB with a couple hundred million records that i need to come up with a backup strategy for.

The target is to be at that same point time that the failure occured at within 1 day.

Taking the db down for any length of time regularly is not an option.

Ideally I would like two servers that have reasonably identicle copies of the database.
1.If the primary one fails having the second one automatically take over is not a requirement
2.I need to be able to use the second server as the primary one while the primary server rebuilds.

Materialized views seem promising, but I have a couple of questions about them
1:How do I populate a materialized view with hundreds of millions of records over a network?

2:What happens to snapshot views when the master site goes down?

3:Can I insert and update snapshot views while the master site is down?

4:How do I rebuild that master site from the materialized views.

I realize this is a really broad topic. I don't mean to ask for a blow by blow strategy, but rather key points to research or common gotcha's and tips. Thanks in advance for any help.

WR

 

[SantaMufasa]

Tokra,

The best protection against even one lost (committed) transaction is having your database in ARCHIVELOG MODE. This is Oracle's only method of offering database RECOVERY (that is, resurrecting the database to the most recent committed transaction). Any other stategy, at best, offers only database RESTORE capability (putting the database back to it's state at last backup).

The COST of putting a database in ARCHIVELOG MODE is disk space: each time the on-line redo log file fills, it generates an archive copy of the just-filled on-line redo log file.

Even if you want to implement the secondary, backup-server strategy you mentioned, it hinges upon having your database in ARCHIVELOG MODE.

Materialized views can provide, at best, database recovery; only a restoral to the point of the last successful refresh. If you are talking "hundreds of millions of rows" then using materialized views to reduce risk of loss from database failure is really like "using a screwdriver to hammer a nail," in my opinion.

So, if you are trying to reduce exposure to losses due to database-failure risk, I believe conventional wisdom points to ARCHIVELOG MODE.

Other ideas or points of view?

Dave

 

[Scunningham99]

You can create a stanby database on the other server and apply the archivelogs to it through your second archive destination.

The standby database applys the archive logs from production keeping it in sync.

If down time is not an option for prod, then you can create a standby database using rman. If you would like the syntax etc for creating a standby with rman reply to thread.

We have just been working on this and it works a treat!



Sy UK

 

[sgwisby]

I would be interested in the syntax etc for creating a standby with rman.
Thanks

 

[Scunningham99]

Scenario: Standby database needs to be created on a different host to the
primary

Primary Server – euratlsunx02, Database - AEULIVE
Requesting Client – auratlsunx04, Database – ALIVE_SB


Solution:

1. Logon to the primary database (server – euratlsunx02) and create a
standby controlfile:

Create the standby controlfile to tape:

RMAN> run
2> {
3> allocate channel ch1 type 'SBT_TAPE';
4> backup current controlfile for standby;
5> }


2. Carry out a full backup of all datafiles and archivelogs to tape.

3. Check that the init.ora and listener.ora files have all the necessary parameters relating to the standby database – see metalink note: 120855.1

4. Logon to the requesting server – euratlsunx04

Check the following:

i. The Oracle_SID is pointing to the correct database.
ii. The database that is to be refreshed is not mounted.
iii. The following NLS settings are set:
NLS_DATE_FORMAT=YYYY/MM/DD:HH24:MI:SS
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
iv. The password file for the database has been created.
v. The oracle user for the primary database needs to be the same as that of the requesting database.





Check the tnsnames.ora

i. Ensure that the target database (primary) and the RMAN database is set up in the tnsnames.ora file


Check the init.ora file:

i. Ensure that the database name is the same as the primary database.
ii. Ensure that the db_file_name_convert and log_file_name_convert are set up.

Multiple destinations can be set up with 9i.
However you must use SET NEWNAME if your database is 8i.




























5. Connect to RMAN, auxiliary and target database, and create the standby database:

RMAN> connect auxiliary /

connected to auxiliary database: AEULIVE (not mounted)

RMAN> connect target sys/aeulive@aeulive

connected to target database: AEULIVE (DBID=2826997474)

RMAN> connect catalog rman/rman@rman

connected to recovery catalog database

RMAN> run
{
allocate auxiliary channel ch1 type 'SBT_TAPE';
allocate channel ch2 type 'SBT_TAPE';
send "NB_ORA_POLICY=ORAhot_AEULIVE";
send "NB_ORA_SERV=euratlsunx02";
send "NB_ORA_CLIENT=euratlsunx02";
send "NB_ORA_SCHED=full";
DUPLICATE TARGET DATABASE FOR STANDBY
DORECOVER;
}



6. Once the restore is complete – copy across all the archives that have been generated by the primary database.

7. On the standby database, do the following:

i. startup nomount
ii. alter database mount standby database;
iii. recover standby database; ?
auto|cancel or type in filename >






8. Once all archivelogs have been applied – re-enable log_archive_dest_2

9. To put the standby into automatic recovery, type the following:

RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;




Sy UK

 

[sgwisby]

Thanks for the detailed instructions. Being new to Oracle(and it being the monster that it is) I really appreciate you taking the time to explain this so thoroughly.

David