提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

oracle-rman-1

Oracle RMAN
2023-09-27 14:23:14 时间

 

 备份脚本

#############################

[oracle@rac02 backup_scripts]$ cat rman_bus_level_0_bk.sh
###########################
##  rman_bus_level_0_bk.sh      ##
##        2014-04-02              ##
###########################

#!/bin/sh

# ---------------------------------------------------------------------------
# Oracle home path.
# ---------------------------------------------------------------------------
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_HOME
# ---------------------------------------------------------------------------
# the Oracle SID of the target database.
# ---------------------------------------------------------------------------
ORACLE_SID=bus
export ORACLE_SID
# ---------------------------------------------------------------------------
# the RMAN_LOG_FILE.
# ---------------------------------------------------------------------------
dt=`date +%Y%m%d`;
backDest=/mnt/bak
RMAN_LOG_FILE=$backDest/rman_bus_dest/bus_level_0_bk_$dt.log
# ---------------------------------------------------------------------------
# Set the Oracle Recovery Manager name.
# ---------------------------------------------------------------------------
RMAN=$ORACLE_HOME/bin/rman

$RMAN nocatalog target /  log $RMAN_LOG_FILE append <<-EOF
RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;

backup incremental level 0 DATABASE FORMAT '$backDest/rman_bus_dest/db_level_0_%U_%T' skip inaccessible    TAG='inc_level_0';

sql 'alter system archive log current';

backup FORMAT '$backDest/rman_bus_dest/arch_%U_%T' skip inaccessible  ARCHIVELOG ALL tag='bus_arch' DELETE ALL INPUT;
backup current controlfile tag='bus_ctl' format='$backDest/rman_bus_dest/ctl_%U_%T';
#backup spfile tag='bus_spfile' format='$backDest/rman_bus_dest/spfile_%U_%T';

release channel c2;
release channel c1;
}
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backup;
list backup summary;
EOF
#
# Let's delete the redundancy backup logs
#

find $backDest/rman_bus_dest  -type f -mtime +0  -name "*.log"  -exec rm {} \;

#############################

# ---------------------------------------------------------------------------
# Oracle home path.
# ---------------------------------------------------------------------------
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_HOME
# ---------------------------------------------------------------------------
# the Oracle SID of the target database.
# ---------------------------------------------------------------------------
export ORACLE_SID=bus
# ---------------------------------------------------------------------------
# the RMAN_LOG_FILE.
# ---------------------------------------------------------------------------
date=`date +%Y%m%d`
backDest=/mnt/bak
RMAN_LOG_FILE=$backDest/rman_bus_dest/bus_level_2_bk_$date.log
# ---------------------------------------------------------------------------
# Set the Oracle Recovery Manager name.
# ---------------------------------------------------------------------------
RMAN=$ORACLE_HOME/bin/rman

$RMAN nocatalog target /  log $RMAN_LOG_FILE append <<-EOF
RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;

backup incremental level 2 DATABASE FORMAT '$backDest/rman_bus_dest/db_level_2_%U_%T' skip inaccessible    TAG='inc_level_2';

sql 'alter system archive log current';

backup FORMAT '$backDest/rman_bus_dest/arch_%U_%T' skip inaccessible  ARCHIVELOG ALL tag='bus_arch' DELETE ALL INPUT;
backup current controlfile tag='bus_ctl' format='$backDest/rman_bus_dest/ctl_%U_%T';
#backup spfile tag='bus_spfile' format='$backDest/rman_bus_dest/spfile_%U_%T';
release channel c2;
release channel c1;
}
#report obsolete;
#delete noprompt obsolete;
crosscheck backup;
delete noprompt expired backup;
list backup summary;
EOF

#############################

Rman的format格式中的%
%c 备份片的拷贝数
%d 数据库名称
%D 位于该月中的第几天 (DD)
%M 位于该年中的第几月 (MM)
%F 一个基于DBID唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII为该数据库的DBID，YYYYMMDD为
日期，QQ是一个1-256的序列
%n 数据库名称，向右填补到最大八个字符
%u 一个八个字符的名称代表备份集与创建时间
%p 该备份集中的备份片号，从1开始到创建的文件数
%U 一个唯一的文件名，代表%u_%p_%c
%s 备份集的号
%t 备份集时间戳
%T 年月日格式(YYYYMMDD)

 

#############################

 

 

下面是两种连接方式
[oracle@oracle3A ~]$ rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Feb 22 16:59:59 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: FLT (DBID=4164685130)

RMAN>

[oracle@oracle3A ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Feb 22 17:00:57 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target

connected to target database: FLT (DBID=4164685130)

SQL> select dbid from v$database;

      DBID
----------
4158553139

 

RMAN> show all;

RMAN configuration parameters for database with db_unique_name FLT are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle3A/product/11.2.0/dbhome_1/dbs/snapcf_flt.f'; # default

 

 

http://blog.chinaunix.net/uid-17240229-id-306718.html

把归档日志的物理文件删除后，我们就可以正常登入ORACLE了，但是还没完全把归档日志删除干净，ORACLE的controlfile中仍然记录着这些archivelog的信息，在oracle的OEM管理器中有可视化的日志展现出，当我们手工清除archive目录下的文件后，这些记录并没有被我们从controlfile中清除掉，接下去我们要做的就是这个工作。
我们利用RMAN进行删除操作，


两种方式：

1、先查看使用率，再删除物理文件，再用rman删除。
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
[oracle@stdg archivelog]$ pwd
/u01/app/oracle/fast_recovery_area/STDGGT/archivelog
[oracle@stdg archivelog]$ rm -rf 2016_11_22
[oracle@stdg archivelog]$ rman target /
RMAN> list archivelog all;
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-6';
上面直接不能删除，需要先用下面的命令，然后才能删除。
RMAN>　crosscheck archivelog all;
RMAN> delete expired archivelog all;
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
用上面的sql查看文件数量与容量大小相应的减少了。

2、先查看使用率，直接用rman删除。
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
[oracle@stdg archivelog]$ pwd
/u01/app/oracle/fast_recovery_area/STDGGT/archivelog
[oracle@stdg archivelog]$ rman target /
RMAN> list archivelog all;
RMAN> delete archivelog until time 'sysdate-5';
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-6';
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;


SYSDATE-7，表明当前的系统时间7天前，before关键字表示在7天前的归档日志，如果使用了闪回功能，也会删除闪回的数据。
同样道理，也可以删除从7天前到现在的全部日志，不过这个命令要考虑清楚，做完这个删除，最好马上进行全备份数据库
DELETE ARCHIVELOG from TIME 'SYSDATE-7'; 删除从7天前到现在的全部日志,慎用
UNIX/LINUX下也可以通过FIND找到7天前的归档数据，使用EXEC子操作删除
find /oraarchive -xdev -mtime +7 -name "*.dbf" -exec rm -f {} ;
这样做仍然会在RMAN里留下未管理的归档文件
仍需要在RMAN里执行下面2条命令
crosscheck archivelog all;
delete expired archivelog all;
所以还不如上面的方法好用，不过用FIND的好处就是，可以在条件上，和EXEC子项上做很多操作，实现更复杂的功能

 

 

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    3106619392 bytes

Fixed Size                     2257272 bytes
Variable Size               2768244360 bytes
Database Buffers             318767104 bytes
Redo Buffers                  17350656 bytes

RMAN> backup database format '/u01/app/oracle/full%U.bak';

Starting backup at 12-JAN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1885 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/DFLCP
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-JAN-17

 

RMAN> backup as copy database;

Starting backup at 01-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA1/orcl/datafile/system.256.934287737
output file name=+FRA/orcl/datafile/system.261.934824851 tag=TAG20170201T173411 RECID=2 STAMP=934824856
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA1/orcl/datafile/sysaux.257.934287737
output file name=+FRA/orcl/datafile/sysaux.262.934824859 tag=TAG20170201T173411 RECID=3 STAMP=934824862
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA1/orcl/datafile/example.269.934287837
output file name=+FRA/orcl/datafile/example.263.934824865 tag=TAG20170201T173411 RECID=4 STAMP=934824866
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA1/orcl/datafile/undotbs1.258.934287737
output file name=+FRA/orcl/datafile/undotbs1.256.934824867 tag=TAG20170201T173411 RECID=5 STAMP=934824868
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+FRA/orcl/controlfile/backup.257.934824871 tag=TAG20170201T173411 RECID=6 STAMP=934824870
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA1/orcl/datafile/users.259.934287737
output file name=+FRA/orcl/datafile/users.258.934824871 tag=TAG20170201T173411 RECID=7 STAMP=934824871
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 01-FEB-17
channel ORA_DISK_1: finished piece 1 at 01-FEB-17
piece handle=+FRA/orcl/backupset/2017_02_01/nnsnf0_tag20170201t173411_0.264.934824873 tag=TAG20170201T173411 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-FEB-17



RMAN> delete obsolete;
using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           2      01-FEB-17
  Backup Piece       2      01-FEB-17          +FRA/orcl/backupset/2017_02_01/ncsnf0_tag20170201t122244_0.260.934806193

Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=+FRA/orcl/backupset/2017_02_01/ncsnf0_tag20170201t122244_0.260.934806193 RECID=2 STAMP=934806192
Deleted 1 objects


############
report命令

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     +DATA1/orcl/datafile/system.256.934287737
2    550      SYSAUX               ***     +DATA1/orcl/datafile/sysaux.257.934287737
3    100      UNDOTBS1             ***     +DATA1/orcl/datafile/undotbs1.258.934287737
4    5        USERS                ***     +DATA1/orcl/datafile/users.259.934287737
5    313      EXAMPLE              ***     +DATA1/orcl/datafile/example.269.934287837

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       +DATA1/orcl/tempfile/temp.268.934287837


RMAN> report need backup database;

using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1    0     /u01/app/oracle/oradata/testdb/system01.dbf
2    0     /u01/app/oracle/oradata/testdb/sysaux01.dbf
3    0     /u01/app/oracle/oradata/testdb/undotbs01.dbf
4    0     /u01/app/oracle/oradata/testdb/users01.dbf
5    0     /u01/app/oracle/oradata/testdb/lcpsys.dbf
6    0     /u01/app/oracle/oradata/testdb/monitor.dbf

 

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found

RMAN> report need backup database;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

 

############

list命令

 

 

RMAN> list copy;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
2       1    A 01-FEB-17       1084830    01-FEB-17
        Name: +FRA/orcl/datafile/system.261.934824851
        Tag: TAG20170201T173411

3       2    A 01-FEB-17       1084836    01-FEB-17
        Name: +FRA/orcl/datafile/sysaux.262.934824859
        Tag: TAG20170201T173411

5       3    A 01-FEB-17       1084840    01-FEB-17
        Name: +FRA/orcl/datafile/undotbs1.256.934824867
        Tag: TAG20170201T173411

7       4    A 01-FEB-17       1084843    01-FEB-17
        Name: +FRA/orcl/datafile/users.258.934824871
        Tag: TAG20170201T173411

4       5    A 01-FEB-17       1084839    01-FEB-17
        Name: +FRA/orcl/datafile/example.263.934824865
        Tag: TAG20170201T173411

List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time
------- - --------------- ---------- ---------------
6       A 01-FEB-17       1084841    01-FEB-17
        Name: +FRA/orcl/controlfile/backup.257.934824871
        Tag: TAG20170201T173411

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
4       1    11      A 01-FEB-17
        Name: +FRA/orcl/archivelog/2017_02_01/thread_1_seq_11.259.934826715


RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    80.00K     DISK        00:00:00     01-FEB-17
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20170201T173411
        Piece Name: +FRA/orcl/backupset/2017_02_01/nnsnf0_tag20170201t173411_0.264.934824873
  SPFILE Included: Modification time: 01-FEB-17
  SPFILE db_unique_name: ORCL

 

相关文章