Backup Script Help. Please 1

[Jamfool]

Hi All

Can someone confirm the following syntax/proceedure for me.
I need to backup the entire contents of an oracle database,
and then copy the files to a another directory in a linux environment.

Assumptions:

I need control file and all tablespaces backedup.

1. Check files I need.

select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;



2. How do you find tablespaces you have?...they conrespond with data/log files?

ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyfFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;

3. ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';

Has any one got a scipt to do this?...any help appreciated.


Thanks


9iDB on Red Hat Enterprise

 

[KenCunningham]

Will

select name from v$tablespace;

do?

 

[Jamfool]

ok great. so i back up all the tablespaces and the control file and thats all i need to do a complete restore?

Thanks

 

[KenCunningham]

Sorry, I have no experience of hot backups, so don't feel qualified to advise you. Your best bet would be to do a search for 'hot backup' or similar in this forum and/or through Google. Good luck.

 

[pete91z]

why not have a look at using rman? you can backup your database and control files and there is functionality for cloning databases.

 

[Scunningham99]

This document will outline the procedures in order to take a copy of database online (hot)

Put the tablespaces into Hot Backup Mode via running the code returned by this query:-

select 'alter tablespace '||tablespace_name|| ' begin backup;'
from dba_tablespaces;

Putting the tablespaces into hot backup mode means the SCN is frozen until we take them out of this mode.

Once in backup mode we can now copy the datafiles across to new location. Once the copy has finished, we need to put the datafiles out of backup mode:-


select 'alter tablespace '||tablespace_name|| ' end backup;'
from dba_tablespaces;


As soon as we take the tablespaces out of backup mode we need to backup the controlfile immediately:-

SQL> alter database backup controlfile to ‘/u01/oracle/ctrlControl01.ctl’;

OR…..If for a standby then issue :-

SQL> alter database create standby controlfile as ‘/u01/oracle/stbyControl01.ctl’;

Then issue a switch logfile as the end marker will be in the current logfile which needs to be archived and copied across the refresh location.

SQL>alter system switch logfile;

IT IS IMPORTANT THAT WE SWITCH THE LOGFILE ELSE THE END MARKER WILL NOT BE COPIED ACROSS IN THE ARCHIVES TO THE REFRESH LOCATION. THIS WILL MEAN THAT THE RESTORE WILL NOT WORK AS THE SYSTEM FILE WILL BE INCONSISTENT.



Sy UK

 

[Jamfool]

so after the table space files are copied across, the control file is backedup a marker is put in the log which will then also need to be copied across.

so we have

table space files backed up to new location
control file copied to new location

and logfile with a system switch

select member from sys.v_$logfile; to new location.

...is that about right?

Thanks again.

 

[Scunningham99]

Nearly .. copy your datafiles in hot backup mode (begin backup) once the coppy has finished issue the script to end backup mode..

once you have done this make a mackup of your controlfile (this will be in the udump directory) then issue alter system switch logfile which will archive the current logfile to log_archive_dest_1.

You then need to copy your archives across from the point in time when you put the datafiles in hot backup mode till the very last archivelog which you switched. (if theres not that many archivelogs it might be easier to copy the lot!)

ONE MORE POINT YOU DATABASE NEEDS TO BE IN ARCHIVELOG MODE!

Your point of seleting from v$logfile ... this is only the online logfiles, when a log switch occurs the system will archive each one off to log_archive_dest.

HTH

let us know if ok or if further explanition is needed...



Sy UK

 

[Jamfool]

ARCHIVELOG: how do i set this?..or would it already be set? is this part of the backup process...or is it a one off thing?

ok so i need to copy all logs up to the the switch point which have a date greater than the back up date.. sounds good.

 

[Scunningham99]

Hi


Choosing Between NOARCHIVELOG and ARCHIVELOG Mode
The issues you must consider when choosing to run your database in NOARCHIVELOG or ARCHIVELOG mode:-

Running a Database in NOARCHIVELOG Mode
Running a Database in ARCHIVELOG Mode
Running a Database in NOARCHIVELOG Mode
When you run your database in NOARCHIVELOG mode, you disable the archiving of the online redo log. The database's control file indicates that filled groups are not required to be archived. Therefore, when a filled group becomes inactive after a log switch, the group is available for reuse by LGWR.

The choice of whether to enable the archiving of filled groups of online redo log files depends on the availability and reliability requirements of the application running on the database. If you cannot afford to lose any data in your database in the event of a disk failure, use ARCHIVELOG mode. The archiving of filled online redo log files can require you to perform extra administrative operations.

NOARCHIVELOG mode protects a database only from instance failure, but not from media failure. Only the most recent changes made to the database, which are stored in the groups of the online redo log, are available for instance recovery. In other words, if a media failure occurs while in NOARCHIVELOG mode, you can only restore (not recover) the database to the point of the most recent full database backup. You cannot recover subsequent transactions.

Also, in NOARCHIVELOG mode you cannot perform online tablespace backups. Furthermore, you cannot use online tablespace backups previously taken while the database operated in ARCHIVELOG mode. You can only use whole database backups taken while the database is closed to restore a database operating in NOARCHIVELOG mode. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals.

Running a Database in ARCHIVELOG Mode
When you run a database in ARCHIVELOG mode, you specify the archiving of the online redo log. The database control file indicates that a group of filled online redo log files cannot be used by LGWR until the group is archived. A filled group is immediately available for archiving after a redo log switch occurs.

The archiving of filled groups has these advantages:

A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.
You can use a backup taken while the database is open and in normal system use if you keep an archived log.
You can keep a standby database current with its original database by continually applying the original's archived redo logs to the standby.
Decide how you plan to archive filled groups of the online redo log. You can configure an instance to archive filled online redo log files automatically, or you can archive manually. For convenience and efficiency, automatic archiving is usually best. Figure 8-1 illustrates how the archiver process (ARC0 in this illustration) writes filled online redo log files to the database's archived redo log.

If all databases in a distributed database operate in ARCHIVELOG mode, you can perform coordinated distributed database recovery. If any database in a distributed database uses NOARCHIVELOG mode, however, recovery of a global distributed database (to make all databases consistent) is limited by the last full backup of any database operating in NOARCHIVELOG mode.

you can check what state your database is in by querying the following:-


select name, log_mode from v$database

If you need help in putting your database in archivelog let us know..






Sy UK

 

[Jamfool]

wow, thanks, most appreciated.