Solution: Backup of large data in Oracl database

Q: The Oracle database in the company is 20G large (this is the size of a scenario). The original plan has 30G, I have deleted the data can be deleted, how to backup? Feel backup once good slow ah. Does the expert have any guidance? (Another: 20G database is not backup also want 20G this?) I want to be able to back up once a week, preferably once a day.

A: the first choice is to create a pipeline under UNIX (only once):

$mknod./exp_pipe P

$ ls-l Exp_pipe

prw-rw-r--1, 0 Mar 05:20 exp_pipe

Then through the pipeline, the data exported by EXP is compressed directly, note: The following two lines are written in the same shell script.

Compress </exp_pipe >/tmp_now.dmp.z &

Exp user/passwd@ Database connection string file=./exp_pipe direct=y compress=no Log=./exp.log

20G of DMP file, compressed after the size of 4G or so.

　　method Two: Considering the speed and efficiency of the machine, it is generally not recommended to use EXP to back up the database with large data size, because it is slow. Oracle's Rman Backup can support incremental backups, and you can set up a backup strategy for yourself, like a full database backup every Sunday (level 0 backup), then an incremental backup in Monday Tuesday (Level 1 backup), and a full backup of the database in Wednesday (levels 0), the last Thursday, Friday, Saturday to do the incremental backup of the database (Level 1). This ensures that your backup is faster for most of the time, and can be guaranteed faster when you need to recover. The specific backup strategy depends on your requirements, I just give an example.

　　describe what an Rman backup of Oracle is:

Oracle's Rman Backup--

View the current use of that SPFile file:

Sql> Select Name,value from V$parameter where name= ' spfile ';

1. Check how the database is archived. If this is not the specified method, modify the database to be archived.

1.1 Log in with the DBA account,

$ Sqlplus '/as sysdba '; sql> archive log list; Database log mode No Archive mode #非归档方式 Automatic Archival Enabled Archive destination/oracle/bakram/log_archive Oldest online log sequence 161 Current log Sequence 163

1.2 If the first step is No Archive mode, modify the database changes to the archive method

1.2.1 Establish the archive path.

$mkdir/oracle/bakram/log_archive

1.2.2 in/oracle/oraclehome/dbs/to establish a document Firstspfile.ora

The contents of the file are:

Spfile= "/oracle/oraclehome/dbs/spfileorcyehoo.ora" # Instance Name Log_archive_start=true; Log_archive_format=arc%t%s.arc #格式 Log_archive_dest=/oracle/bakram/log_archive #存放的路径

1.3 Stop the database

$/oracle/dbstop.sh

1.4 Modify the filing mode

1.4.1 Create a dbstartmount.sh file

-------- echo "Begin to start Oracle mount ..." Lsnrctl start Connect/as SYSDBA Startup Mount Exit Exit Sleep 10 echo "Oracle have started Oracle mount ..." -------- Execute./dbstartmount.sh Sql>alert database Archivelog; Database altered. Open the Database sql> Alert database open; Database altered. Sql>

1.5. Configure Dbstart.sh. Start the database

　

$vi/oracle/dbstart.sh #编辑启动脚本 --------------------------------------------- echo "Begin to start Oracle ..." Lsnrctl start Connect/as SYSDBA Startup pfile= "/oracle/oraclehome/dbs/firstspfile.ora" #修改这里. A file that loads its own configuration at startup. Exit Exit Sleep 10 echo "Oracle have started ..." --------------------------------------

2. Create an Rman Catalog

$ sqlplus System/data#yes

2.1 Creating a separate table space

Sql> Create tablespace back datafile ' back_css.dmp ' size 50m;

2.2 Creating an Rman user

Sql> create user Rman identified by Rman default tablespace back temporary tablespace temp;

2.3 Granting Rman permission

Sql>grant Connect,resource,recovery_catalog_owner to Rman;

2.4 Open Rman

$RMAN

2.5 Connection Database

Rman>connect catalog Rman/rman;

2.6 Creating a Recovery directory

Rman>create catalog tablespace back;

3. Register the target database (database to be backed up)

3.1 Registration Database

$rman Target Sys/data#yes Catalog Rman/rman@yehoo; #yehoo为实例名. ---- Recovery Manager:release 9.2.0.1.0-production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. Connected to target Database:yehoo (dbid=2840368994) Connected to Recovery Catalog database ---- rman> Register database;

3.2 Query Recovery Directory

How do we know if our oracle9i is an OLTP or a DSS?

4. Backup

Backup table Space:

Backup tag ' tsuser ' format '/oracle/css_20041209_%u_%s_%p ' tablespace css;

5. Maintenance of Rman

5.1 View Existing backups

rman> list backup;

5.2 List Expired Backups

rman> obsolete

6. Choose a backup strategy