Shulou(Shulou.com)05/31 Report--

This article is about how to back up and restore oracle databases. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Question:

1. Whether the logical backup contains table structure

The answer contains the table structure

Is 2.Rman a logical backup or a physical backup? is it also a backup?

Answer physical backup, which belongs to hot backup

3. What is the way of hot backup?

Answer: command backup or Rman backup under SQL

4. Which files can be backed up by hot backup

5. What is the use of online redo files

Answer: in the Oracle database, after performing the data modification operation, the data file is not written immediately, but the redo information is first generated and written to a fixed area called LOG_BUFFER in the SGA. LOG_BUFFER has certain trigger conditions. When the trigger condition is met, the corresponding process will write the contents of the LOG_BUFFER into the data block. Oracle online log function: 1, mainly used to record changes to the database to prevent data loss. 2. Quick response for database operation.

Type of backup

Depending on the backup method, backups can be divided into two categories:

Logical backup: refers to the backup of data through logical export. Logical backup uses import and export tools: EXPDP/IMPDP or EXP/IMP. Logical backup is a supplement to physical backup. Because logical backup is platform independent, logical backup is used as the main means of data migration and movement.

Physical backup: refers to the backup of the database by means of physical file copies, which can be divided into cold backup and hot backup.

Cold backup: refers to the copy backup after the database is closed, such a backup has consistent and complete point-in-time data, and you only need to restore all files to start the database.

Hot backup: the most common backup method in the production system is hot backup. The database for hot backup needs to be run in archive mode, and the database does not need to be closed during hot backup, so as to ensure the continuous operation of the system. During recovery, the database can be fully restored through the backed-up data files and archived log files. Hot backup includes Rman backup, which is also called online backup.

Logical backup:

Export

Shell > exp username / password file=/home/backup/ymq.dmp

Import

Shell > imp username / password full=y file=/home/backup/ymq.dmp ignore=y

Physical backup

Hot backup

Command backup under SQL

1. Check to see if archiving is enabled

ARCHIVE LOG LIST

two。 Set the tablespace that needs to be backed up (such as user) to the backup mode

Shell > Alter tablespace user begin backup

3. Copy parameter file

Shell >! cp / u01/oradata/prod/user01.ora / backup/prod/user01.ora

Shell > cp / u01/oradata/prod/user01.ora / backup/prod/user01.ora

1. Create a separate tablespace for the catalog

SQL > Create tablespace tools datafile 'fielname' size 50m

two。 Create a RMAN user

SQL > Create user RMAN identified by RMAN default tablespace tools temporary tablespace temp

3. Grant permissions to RMAN

SQL > Grant connect, resource, recovery_catalog_owner to rman

4. Open RMAN

$> RMAN

5. Connect to the database

RMAN > connect catalog rman/rman

6. Create a recovery directory

RMAN > Create catalog tablespace tools

Register the target database, and after the recovery directory is created successfully, you can register the target database. The target database is the database that needs to be backed up. A recovery directory can register multiple target databases. The command to register the target database is as follows:

$> RMAN target internal/password catalog rman/rman@rcdb

RMAN > Register database

Once the database is registered, you can use RMAN for backup. For more commands, please refer to the ORACLE online manual or the ORACLE8i backup and recovery manual.

Logging out of the database is not simply a matter of unregistering at the prompt of RMAN. You need to run a package as follows:

1. Connect to the target database and get the target database ID

$> RMAN target internal/password catalog rman/rman@rcdb

RMAN-06005: connected to target database: RMAN (DBID=1231209694)

two。 Query the recovery catalog for more detailed information

SQL > SELECT db_key, db_id FROM db WHERE db_id = 1231209694

DB_KEY DB_ID

--

1 1237603294

1 row selected.

3. Run the process dbms_rcvcat.unregisterdatabase to log out of the database, such as

SQL > EXECUTE dbms_rcvcat.unregisterdatabase (1, 1237603294)

4.3.1.3 backup with RMAN

RMAN uses scripts to back up databases. Here are some examples of RMAN backing up.

1. Back up the entire database

Backup full tag 'basicdb' format' / bak/oradata/full_%u_%s_%p' database

two。 Back up a tablespace

Backup tag 'tsuser' format' / bak/oradata/tsuser_%u_%s_%p' tablespace users

3. Backup archive log

Backup tag 'alog' format' / bak/archivebak/arcbak_%u_%s_%p' archivelog all delete input

4.3.2 maintain RMAN4.3.2.1 to view RMAN information

Check existing backup

RMAN > list backup

List expired backups

RMAN > report obsolete

Delete expired backups

RMAN > allocate channel for maintenance type disk

RMAN > change backupset id delete

RMAN > release channel

Synchronize or reset Rman

If the physical object of the target database changes, such as adding a data file, you need to synchronize with the following command:

RMAN > resync catalog

If the target database reset the database, you need to synchronize with the following command

RMAN > reset database

When you manually delete the archive file for the database, perform the following script synchronization

RMAN > allocate channel for maintenance type disk

RMAN > change archivelog all crosscheck

RMAN > release channel

When you manually delete the RMAN backup of the database, perform the following script synchronization

RMAN > allocate channel for maintenance type disk

RMAN > crosscheck backup

RMAN > delete expire backup

RMAN > release channel

Customize the appropriate backup strategy customize the correct policy

Make a full backup of the database every six months (including all data and read-only tablespaces)

Make a level zero backup once a month (excluding read-only tablespaces)

Make a first-level backup once a week

Make a secondary backup once a day

Any changes to the database require resynchronization of the CATALOG directory and re-backup (such as adding data files) or re-backup (such as modifying the tablespace to read-only)

You can back up the archive log or back up the archive log regularly after each backup.

If possible, you can back up directly to the tape.

Script for full backup of database

Run {

Allocate channel c1 type disk

Allocate channel c2 type disk

Allocate channel c3 type disk

Backup full tag 'dbfull' format' / u01Accord oradata database backupfull% uplink% slots% p' database

Include current controlfile

Sql 'alter system archive log current'

Backup fileaperset 3 format'/ u01Accord oradata Universe backupchar% uplink% slots% p'

Archivelog all delete input; # backup archiving is optional and can be backed up separately and regularly

Release channel c1

Release channel c2

Release channel c3

}

Script for level zero backup

Run {

Allocate channel c1 type disk

Allocate channel c2 type disk

Allocate channel c3 type disk

Backup incremental level 0 tag 'db0' format' / u01Universe oradataAccord backupDb0% upright% slots% p'

Database skip readonly

Sql 'alter system archive log current'

Backup fileaperset 3 format'/ u01Accord oradata Universe backupchar% uplink% slots% p'

Archivelog all delete input; # backup archiving is optional and can be backed up separately and regularly

Release channel c1

Release channel c2

Release channel c3

}

Similarly, we can get first-level backup, second-level backup scripts, such as first-level backup scripts.

Run {

Allocate channel c1 type disk

Allocate channel c2 type disk

Allocate channel c3 type disk

Backup incremental level 1 tag 'db1' format' / u01Universe oradataPlacement backupDb1% uplink% slots% p'

Database skip readonly

Sql 'alter system archive log current'

Backup fileaperset 3 format'/ u01Accord oradata Universe backupchar% uplink% slots% p'

Archivelog all delete input; # backup archiving is optional and can be backed up separately and regularly

Release channel c1

Release channel c2

Release channel c3

}

Thank you for reading! This is the end of the article on "how to backup and restore oracle database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.