a simple oracle rman backup script
[
2010/03/05 16:17 | by askwan ]
2010/03/05 16:17 | by askwan ]
Note:this backup script is suitable for db size < 200G,
and for backup file safety ,may be you should use an independent disk to store backup files
1 configuration target database to archive log mode
2 configuration or check log_archive_dest and db_recovery_file_dest or log_archive_dest_n
3 write a rman script
oracle@dbtest:~$ vim backup.rman
4 write backup shell script
oracle@dbtest:~$ vim backup.sh
5 set cronjob for user oracle
and for backup file safety ,may be you should use an independent disk to store backup files
1 configuration target database to archive log mode
2 configuration or check log_archive_dest and db_recovery_file_dest or log_archive_dest_n
3 write a rman script
oracle@dbtest:~$ vim backup.rman
run{
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/oracle/orabak/ctfile_%F';
ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/opt/oracle/orabak/DBFULL_%U';
BACKUP DATABASE SKIP INACCESSIBLE PLUS ARCHIVELOG FILESPERSET 20 DELETE ALL INPUT;
DELETE OBSOLETE;
RELEASE CHANNEL C1;
}
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/oracle/orabak/ctfile_%F';
ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/opt/oracle/orabak/DBFULL_%U';
BACKUP DATABASE SKIP INACCESSIBLE PLUS ARCHIVELOG FILESPERSET 20 DELETE ALL INPUT;
DELETE OBSOLETE;
RELEASE CHANNEL C1;
}
4 write backup shell script
oracle@dbtest:~$ vim backup.sh
#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_SID=capitalv
export ORACLE_HOME=$ORACLE_BASE/oracle/product/10.2.0/db_1
PATH=/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/bin:/sbin:$PATH;
export PATH=$ORACLE_HOME/bin:$PATH
rman TARGET / log='/opt/oracle/orabak/rman.log' cmdfile='/opt/oracle/backup.rman'
export ORACLE_BASE=/opt/oracle
export ORACLE_SID=capitalv
export ORACLE_HOME=$ORACLE_BASE/oracle/product/10.2.0/db_1
PATH=/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/bin:/sbin:$PATH;
export PATH=$ORACLE_HOME/bin:$PATH
rman TARGET / log='/opt/oracle/orabak/rman.log' cmdfile='/opt/oracle/backup.rman'
5 set cronjob for user oracle
30 02 * * 0,3 bash /opt/oracle/backup.sh
how to create a rman catalog
[ 2010/03/02 12:07 | by askwan ]
2010/03/02 12:07 | by askwan ]
SQL> create tablespace rmantest datafile '/u01/oracle/oradata/askwan/rmantest01.dbf' size 100M autoextend on ;
Tablespace created.
SQL> create user rman identified by rman;
User created.
SQL> alter user rman default tablespace rmantest temporary tablespace temp;
User altered.
SQL> alter user rman quota unlimited on rmantest;
User altered.
SQL> grant recovery_catalog_owner,connect,resource to rman;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
login with user rman to create catalog
[oracle@oracle ~]$ rman catalog rman/rman
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 2 12:10:44 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN>
RMAN> create catalog tablespace rmantest;
recovery catalog created
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 2 12:10:44 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN>
RMAN> create catalog tablespace rmantest;
recovery catalog created
create rman catalog done!
log_archive_dest and db_recovery_file_dest
[ 2010/03/02 11:45 | by askwan ]
2010/03/02 11:45 | by askwan ]
默认安装情况下，oracle归档路径可能只有一个，即$ORACLE_HOME/flash_recovery_area这个位置：
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /opt/oracle/oracle/product/10.
2.0/db_1/flash_recovery_area
db_recovery_file_dest_size big integer 2G
而其他归档日志参数默认值都为空：
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
使用参数 log_archive_dest 修改归档日志路径
SQL> alter system set log_archive_dest='/opt/oracle/archive' scope=both;
alter system set log_archive_dest='/opt/oracle/archive' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST
报db_recovery_file_dest参数不能和LOG_ARCHIVE_DEST还有 LOG_ARCHIVE_DEST_N同时使用，先将
db_recovery_file_dest参数置空：
SQL> alter system set db_recovery_file_dest='' scope=both;
System altered.
SQL> alter system set log_archive_dest='/opt/oracle/archive' scope=both;
System altered.
归档日志参数的格式：
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> alter system switch logfile;
System altered.
查看归档日志：
SQL> host ls -al /opt/oracle/archive/
total 1156
drwxr-xr-x 2 oracle oinstall 4096 2010-03-02 11:28 .
drwxrwxr-x 25 oracle oinstall 4096 2010-03-02 11:14 ..
-rw-r----- 1 oracle oinstall 1170432 2010-03-02 11:28 1_34_708277817.dbf
oracle建议归档日志使用log_archive_dest_n代替log_archive_dest,故一般将log_archive_dest和db_recovery_file_dest置空，然后设置log_archive_dest_n .
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /opt/oracle/oracle/product/10.
2.0/db_1/flash_recovery_area
db_recovery_file_dest_size big integer 2G
而其他归档日志参数默认值都为空：
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
使用参数 log_archive_dest 修改归档日志路径
SQL> alter system set log_archive_dest='/opt/oracle/archive' scope=both;
alter system set log_archive_dest='/opt/oracle/archive' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST
报db_recovery_file_dest参数不能和LOG_ARCHIVE_DEST还有 LOG_ARCHIVE_DEST_N同时使用，先将
db_recovery_file_dest参数置空：
SQL> alter system set db_recovery_file_dest='' scope=both;
System altered.
SQL> alter system set log_archive_dest='/opt/oracle/archive' scope=both;
System altered.
归档日志参数的格式：
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> alter system switch logfile;
System altered.
查看归档日志：
SQL> host ls -al /opt/oracle/archive/
total 1156
drwxr-xr-x 2 oracle oinstall 4096 2010-03-02 11:28 .
drwxrwxr-x 25 oracle oinstall 4096 2010-03-02 11:14 ..
-rw-r----- 1 oracle oinstall 1170432 2010-03-02 11:28 1_34_708277817.dbf
oracle建议归档日志使用log_archive_dest_n代替log_archive_dest,故一般将log_archive_dest和db_recovery_file_dest置空，然后设置log_archive_dest_n .
configuration oracle 10g archive log mode
[ 2010/01/15 15:18 | by askwan ]
2010/01/15 15:18 | by askwan ]
VERSION:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Check current database archive log mode
shutdown database clear
startup database to mount status:
open database archive log mode :
stop archive log mode :
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Check current database archive log mode
SQL> select dbid,name,log_mode from v$database;
DBID NAME LOG_MODE
---------- --------- ------------
2406511032 CAPITALV NOARCHIVELOG
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 32
Current log sequence 34
DBID NAME LOG_MODE
---------- --------- ------------
2406511032 CAPITALV NOARCHIVELOG
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 32
Current log sequence 34
shutdown database clear
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Database closed.
Database dismounted.
ORACLE instance shut down.
startup database to mount status:
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 142607832 bytes
Database Buffers 134217728 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 142607832 bytes
Database Buffers 134217728 bytes
Redo Buffers 6303744 bytes
Database mounted.
open database archive log mode :
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 32
Next log sequence to archive 34
Current log sequence 34
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 32
Next log sequence to archive 34
Current log sequence 34
stop archive log mode :
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 142607832 bytes
Database Buffers 134217728 bytes
Redo Buffers 6303744 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 32
Current log sequence 34
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 142607832 bytes
Database Buffers 134217728 bytes
Redo Buffers 6303744 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 32
Current log sequence 34
