Shulou(Shulou.com)06/01 Report--

This article mainly introduces the example analysis of ORACLE backup and recovery, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

Often hear people say that a database backup method is good, a backup method is not good, or now it is popular to use rman to backup, logical backup is outdated, cold backup is not worth mentioning, in fact, database backup has never been good or bad, there is no outdated saying, appropriate is the most important, their own good is really good. All kinds of methods have their own advantages and disadvantages, and no one can replace each other. Only when they are used together according to the actual situation can they play the greatest role, otherwise they can only be copycats.

1. Oracle backup, including noarchivelog mode backup (cold backup), archivelog mode backup (hot backup, rman backup), logical backup (regardless of mode).

1.1. Cold backup:

-1) shut down the database

Shutdown

-- 2) backup data files, control files, and redo log files

-- A. find the location of the backup file

SELECT * FROM data;-- look up data files

Select * from vault control file;-- check the control file

Select * from vault log file;-- check the log file

B, backup data files, control files, redo log files

$copy D:\ oracle\ oradata\ ora9\ * .dbf d:\ bak\ *.

$copy D:\ oracle\ oradata\ ora9\ * .ctl d:\ bak\ *.

$copy D:\ oracle\ oradata\ ora9\ * .log d:\ bak\ *.

1.2. Hot backup:

-1) query which tablespace the backed up data file is related to

SELECT Vantage TABLESPACE.NAME Magna Vendor DATAFILE.name

FROM V$TABLESPACE JOIN V$DATAFILE USING (TS#)

-- 2) backup data files

Alter tablespace tablespace BEGIN BACKUP

$COPY data file storage path

ALTER TABLESPACE tablespace END BACKUP

-3) query whether there are any tablespaces in backup mode

SELECT * FROM backup;-- STATUS is not ACTIVE.

SELECT Vantage TABLESPACE.NAME Magazine Vendor BACKUP.STATUSRecycling Vantage DATAFILE.name

FROM V$TABLESPACE JOIN V$DATAFILE USING (TS#) JOIN V$BACKUP USING (FILE#)

-- 4) backup control files

ALTER DATABASE BACKUP CONTROLFILE TO 'destination path and file name'

1.3. Rman backup:

-- 1) A simple rman full database backup:

> rman nocatalog target "sys/sys"

RMAN > restore controlfile from autobackup

RMAN > configure controlfile autobackup on

RMAN > backup database

-- 2) backup data files, control files, archive logs

RMAN > BACKUP DATAFILE data file name

RMAN > BACKUP CURRENT CONTROLFILE

RMAN > BACKUP ARCHIVELOG ALL

-- 3) backup control file trace

SQL > alter session set tracefile_identifier=system

SQL > alter database backup controlfile to trace

SQL > alter database backup controlfile to'd:\ temp\ controlfile.bak'

-- 4) backup tablespaces

RMAN > backup tablespace tablespace name

-- rman backup is more complex, just give some simple examples.

1.4. Logical backup

-- 1) Database mode (export users need to have exp_full_database permissions)

Exp scott/tiger@ora9 full=y file= "D:\ full.dmp"

-- 2) user mode

Exp userid=scott/tiger@ora9 owner=scott file=d:\ scott.dmp log=d:\ scott.log

-- 3) Table mode

Exp scott/tiger@ora9 tables= (emp,dept) file= "d:\ emp.dmp"

2. Recovery of oracle (simple example):

2.1 recovery of cold backup

1)。 STARTUP MOUNT

2)。 ALTER DATABASE DATAFILE data file OFFLINE DROP

3)。 ALTER DATABASE OPEN

2.2 Hot backup recovery:

1)。 STARTUP MOUNT

2)。 ALTER DATABASE DATAFILE data file OFFLINE;-/ / system01.dbf cannot set non-OFFLINE status

3)。 ALTER DATABASE OPEN

2.3 rman backup recovery:

-- 1) use rman to recover the archive database:

Rman target /

Rman > startup mount

Rman > restore database

Rman > recover database

Rman > alter database open

-- 2) use rman to restore tablespaces:

Rman > restore tablespace

Rman > recover tablespace

Rman >

Run {

Sql "alter tablespace users offline immediate"

Restore tablespace users

Recover tablespace users

Sql "alter tablespace users online"

}

2.4. Logical recovery

-- 1) Database mode (the importing user should have exp_full_database permission)

Imp system/system@ora10 full=y file= "f:\ full.dmp"

-- 2) user mode

Imp userid=scott/tiger@ora10 fromuser=scott touser=scott file=d:\ scott.dmp log=d:\ scott.log

-- 3) Table mode

Imp system/system@ora10 fromuser=scott touser=scott file= "f:\ emp.dmp"

-- Note: you can use the above method to export users in the same way as imported users, otherwise use

Imp scott/tiger@ora10 full=y file= "f:\ emp.dmp"

3. Selection of oracle backup and recovery methods:

1)。 For some small systems, small amount of data, no online requirements of the database, the use of cold backup, simple, convenient, reliable (all files have the same point in time), but if this kind of system personally feel that the use of oracle is a waste, except for learning.

2)。 For the database of 7X24 hour large system, account support system and customer service system, we use archive mode to run, using the combination of full database backup and logical backup.

3)。 For databases such as business analysis system, BI system and query system, we can use the combination of logical backup and tablespace backup to back up the changed data incrementally.

4)。 For some dictionary databases and databases with more static data, logical backup can be used to back up relevant important data on a regular basis.

For the method mentioned above, it is only a superficial thing. In order to ensure the efficient, safe and stable operation of the database, we must have a complete and feasible database operation and management system and strictly implement it. Otherwise, the best method is the moon in the water and flowers in the mirror.

Thank you for reading this article carefully. I hope the article "sample Analysis of ORACLE backup and recovery" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.