Скрипт RMAN для создания бекапов. Бекап в каталог


База 12С. Работает в режиме работы ARCHIVELOG


$ mkdir -p /tmp/backups/ORCL12/{DATAFILE,ARCHIVELOG,CONTROLFILE,PARAMETERFILE}


$ mkdir -p $ORACLE_HOME/scripts


$ cd $ORACLE_HOME/scripts


$ vi backup-to-folder-rman-script.rman


RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP AS COMPRESSED BACKUPSET FULL DATABASE TAG "FULL_DATABASE_DATAFILES" FORMAT '/tmp/backups/ORCL12/DATAFILE/bkp_%D_%T_%s_%p_DATA';

SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';

BACKUP ARCHIVELOG ALL FORMAT '/tmp/backups/ORCL12/ARCHIVELOG/bkp_%D_%T_%s_%p_ARCHIVELOG' TAG "FULL_DATABASE_ARCHIVELOGS";

BACKUP SPFILE FORMAT '/tmp/backups/ORCL12/PARAMETERFILE/bkp_%D_%T_%s_%p_PARAM' TAG "FULL_DATABASE_SPFILE";

SQL CREATE PFILE = '/tmp/backups/ORCL12/PARAMETERFILE/pfile.txt' from SPFILE;

BACKUP CURRENT CONTROLFILE FORMAT '/tmp/backups/ORCL12/CONTROLFILE/bkp_%D_%T_%s_%p_CONTROL' TAG "FULL_DATABASE_CONTROLFILE";

SQL ALTER DATABASE BACKUP CONTROLFILE TO TRACE as '/tmp/backups/ORCL12/CONTROLFILE/controlfile.txt';

RELEASE CHANNEL c1;

}

%t = 4 bytes time stamp
%s = backup set number
%p = backup piece number

Проверка синтаксиса созданного файла сценария

$ rman CHECKSYNTAX @$ORACLE_HOME/scripts/backup-to-folder-rman-script.rman

Выполнение скрипта резервного копирования

$ rman target / @$ORACLE_HOME/scripts/backup-to-folder-rman-script.rman


SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
        35


$ cd /tmp/backups/
$ tar -cvzpf ORCL12.tar.gz ./ORCL12


Восстановление из бекапа

Я сначала попытался восстановить базу с другим именем инстанса. У меня ничего не получилось. Я не смог выполнить ALTER DATABASE BACKUP CONTROLFILE TO TRACE as ‘/tmp/controlfile’;

$ ssh oracle12@piter "mkdir -p /tmp/backups/"
$ scp ORCL12.tar.gz [email protected]:/tmp/backups

Восстанавливаю на другом сервере с тем же инстансом:

$ cd /tmp/backups/
$ tar -xvzpf ORCL12.tar.gz ./
./ORCL12/
./ORCL12/DATAFILE/
./ORCL12/DATAFILE/bkp_21_20150821_2_1_DATA
./ORCL12/DATAFILE/bkp_21_20150821_1_1_DATA
./ORCL12/PARAMETERFILE/
./ORCL12/PARAMETERFILE/bkp_21_20150821_5_1_PARAM
./ORCL12/CONTROLFILE/
./ORCL12/CONTROLFILE/bkp_21_20150821_4_1_CONTROL
./ORCL12/ARCHIVELOG/
./ORCL12/ARCHIVELOG/bkp_21_20150821_3_1_ARCHIVELOG


$ export ORACLE_SID=ORCL12
$ rman target / nocatalog


RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/oracle/database/12.1/dbs/initORCL12.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes
Variable Size                281018472 bytes
Database Buffers             784334848 bytes
Redo Buffers                   5455872 bytes


RMAN> restore spfile to pfile '/tmp/initorcl12.ora' from '/tmp/backups/ORCL12/PARAMETERFILE/bkp_21_20150821_5_1_PARAM';


$ cat /tmp/initorcl12.ora
ORCL12.__data_transfer_cache_size=0
ORCL12.__db_cache_size=822083584
ORCL12.__java_pool_size=16777216
ORCL12.__large_pool_size=33554432
ORCL12.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment
ORCL12.__pga_aggregate_target=402653184
ORCL12.__sga_target=1207959552
ORCL12.__shared_io_pool_size=50331648
ORCL12.__shared_pool_size=268435456
ORCL12.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/ORCL12/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='+DATA/ORCL12/CONTROLFILE/current.261.888345847','+ARCH/ORCL12/CONTROLFILE/current.256.888345849'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='ORCL12'
*.db_recovery_file_dest='+ARCH'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL12XDB)'
*.open_cursors=300
*.pga_aggregate_target=382m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1148m
*.undo_tablespace='UNDOTBS1'


В общем обязательно нужно создать каталог для audit_file_dest

$ mkdir -p /u01/oracle/admin/ORCL12/adump

Если ничего не меняется в конфигах (мой вариант), то можно воспользоваться командой:

RMAN> restore spfile from '/tmp/backups/ORCL12/PARAMETERFILE/bkp_21_20150821_5_1_PARAM';

Если меяется то:

RMAN> startup nomount pfile='/tmp/initorcl12.ora'

Восстановили spfile.

RMAN> shutdown immediate;
RMAN> startup nomount;


RMAN> restore controlfile from '/tmp/backups/ORCL12/CONTROLFILE/bkp_21_20150821_4_1_CONTROL';

RMAN> shutdown immediate;
RMAN> startup mount;

Если расположение файлов, которые требуются для восстановления базы нужно явно указать. Это можно сделать следующими командами:

RMAN> catalog start with '/tmp/wtf/ORCL12/DATAFILE';
RMAN> catalog start with '/tmp/wtf/ORCL12/ARCHIVELOG';

Чтобы удалить EXPIRED данные

RMAN> DELETE EXPIRED BACKUP;


RMAN> LIST BACKUP;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    336.77M    DISK        00:01:31     21-AUG-15
        BP Key: 1   Status: AVAILABLE  Compressed: YES  Tag: FULL_DATABASE_DATAFILES
        Piece Name: /tmp/backups/ORCL12/DATAFILE/bkp_21_20150821_1_1_DATA
    List of Datafiles in backup set 1
    File LV Type Ckp SCN    Ckp Time  Name
    ---- -- ---- ---------- --------- ----
    1       Full 1679187    21-AUG-15 +DATA/ORCL12/DATAFILE/system.258.888345709
    3       Full 1679187    21-AUG-15 +DATA/ORCL12/DATAFILE/sysaux.257.888345623
    4       Full 1679187    21-AUG-15 +DATA/ORCL12/DATAFILE/undotbs1.260.888345795
    6       Full 1679187    21-AUG-15 +DATA/ORCL12/DATAFILE/users.259.888345795

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.03M      DISK        00:00:03     21-AUG-15
        BP Key: 2   Status: AVAILABLE  Compressed: YES  Tag: FULL_DATABASE_DATAFILES
        Piece Name: /tmp/backups/ORCL12/DATAFILE/bkp_21_20150821_2_1_DATA
    SPFILE Included: Modification time: 21-AUG-15
    SPFILE db_unique_name: ORCL12
    Control File Included: Ckp SCN: 1679322      Ckp time: 21-AUG-15

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3       1.20M      DISK        00:00:00     21-AUG-15
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: FULL_DATABASE_ARCHIVELOGS
        Piece Name: /tmp/backups/ORCL12/ARCHIVELOG/bkp_21_20150821_3_1_ARCHIVELOG

    List of Archived Logs in backup set 3
    Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
    ---- ------- ---------- --------- ---------- ---------
    1    22      1677757    21-AUG-15 1678819    21-AUG-15
    1    23      1678819    21-AUG-15 1678838    21-AUG-15
    1    24      1678838    21-AUG-15 1678908    21-AUG-15
    1    25      1678908    21-AUG-15 1679140    21-AUG-15
    1    26      1679140    21-AUG-15 1679143    21-AUG-15
    1    27      1679143    21-AUG-15 1679146    21-AUG-15
    1    28      1679146    21-AUG-15 1679149    21-AUG-15
    1    29      1679149    21-AUG-15 1679152    21-AUG-15
    1    30      1679152    21-AUG-15 1679155    21-AUG-15
    1    31      1679155    21-AUG-15 1679158    21-AUG-15
    1    32      1679158    21-AUG-15 1679161    21-AUG-15
    1    33      1679161    21-AUG-15 1679164    21-AUG-15
    1    34      1679164    21-AUG-15 1679341    21-AUG-15
    1    35      1679341    21-AUG-15 1679349    21-AUG-15


RMAN> list backup of archivelog all;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3       1.20M      DISK        00:00:00     21-AUG-15
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: FULL_DATABASE_ARCHIVELOGS
        Piece Name: /tmp/backups/ORCL12/ARCHIVELOG/bkp_21_20150821_3_1_ARCHIVELOG

    List of Archived Logs in backup set 3
    Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
    ---- ------- ---------- --------- ---------- ---------
    1    22      1677757    21-AUG-15 1678819    21-AUG-15
    1    23      1678819    21-AUG-15 1678838    21-AUG-15
    1    24      1678838    21-AUG-15 1678908    21-AUG-15
    1    25      1678908    21-AUG-15 1679140    21-AUG-15
    1    26      1679140    21-AUG-15 1679143    21-AUG-15
    1    27      1679143    21-AUG-15 1679146    21-AUG-15
    1    28      1679146    21-AUG-15 1679149    21-AUG-15
    1    29      1679149    21-AUG-15 1679152    21-AUG-15
    1    30      1679152    21-AUG-15 1679155    21-AUG-15
    1    31      1679155    21-AUG-15 1679158    21-AUG-15
    1    32      1679158    21-AUG-15 1679161    21-AUG-15
    1    33      1679161    21-AUG-15 1679164    21-AUG-15
    1    34      1679164    21-AUG-15 1679341    21-AUG-15
    1    35      1679341    21-AUG-15 1679349    21-AUG-15


Нужно, чтобы статус был AVAILABLE

RMAN> RESTORE DATABASE VALIDATE;
RMAN> RESTORE ARCHIVELOG ALL VALIDATE;


RMAN> restore database;

Starting restore at 21-AUG-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/ORCL12/DATAFILE/system.258.888345709
channel ORA_DISK_1: restoring datafile 00003 to +DATA/ORCL12/DATAFILE/sysaux.257.888345623
channel ORA_DISK_1: restoring datafile 00004 to +DATA/ORCL12/DATAFILE/undotbs1.260.888345795
channel ORA_DISK_1: restoring datafile 00006 to +DATA/ORCL12/DATAFILE/users.259.888345795
channel ORA_DISK_1: reading from backup piece /tmp/backups/ORCL12/DATAFILE/bkp_21_20150821_1_1_DATA
channel ORA_DISK_1: piece handle=/tmp/backups/ORCL12/DATAFILE/bkp_21_20150821_1_1_DATA tag=FULL_DATABASE_DATAFILES
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:45
Finished restore at 21-AUG-15

sequence = max secuence + 1


RMAN> run {
set until sequence 36;
recover database;
}


RMAN> alter database open resetlogs;


SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    35

После рекомендуется сделать полный бекап.


PS. Если нужно переименовывать

run {
    SET NEWNAME FOR DATABASE   TO  '/u01/app/oracle/oradata/orcl/%b';
    SET NEWNAME FOR tempfile  1 TO  '/u01/app/oracle/oradata/orcl/%b';
    restore database;
    switch datafile all;
    switch tempfile all;
}

см.

http://gavinsoorma.com/2013/02/restoring-a-asm-backup-to-non-asm-and-restoring-from-rac-to-single-instance/

http://docs.oracle.com/cd/B12037_01/server.101/b10735/recov.htm