Using RMAN Stored Scripts

Backup & Recovery / 13/07/2022

Uma alternativa para os comandos executados manualmente no RMAN, seriam os Stored Scripts, que nada mais são do que estes mesmos comandos encapsulados/armazenados dentro do próprio RMAN. Este recurso está disponível para qualquer cliente RMAN que possa se conectar em algum target database ou catálogo. Além disso, temos 2 categorias de SS: Locais (que é associado com o target database usado quando o script foi criado) ou Globais (que pode ser executado em qualquer database registrado no catálogo). Neste artigo vamos explorar um pouco esse recurso, usando exemplos de SS locais.

Criando um Stored Script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[oracle@oel7 ~]$ rman target / catalog CORTEX_CATALOG/CORTEX_CATALOG@RMANDB
  
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jun 9 05:15:39 2021
Version 19.3.0.0.0
  
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
  
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
  
RMAN> CREATE SCRIPT FULL_DB_SCRIPT
{ BACKUP DATABASE PLUS ARCHIVELOG TAG 'FULL_DB'; }2>
  
created script FULL_DB_SCRIPT

Podemos validar a sua criação com o comando LIST:

1
2
3
4
5
6
7
8
9
10
11
RMAN> LIST SCRIPT NAMES;
  
List of Stored Scripts in Recovery Catalog
  
  
    Scripts of Target Database CORTEX
  
       Script Name
       Description
       -----------------------------------------------------------------------
       FULL_DB_SCRIPT

Conseguimos visualizar o conteúdo do SS com o comando print:

1
2
3
4
RMAN> PRINT SCRIPT FULL_DB_SCRIPT;
  
printing stored script: FULL_DB_SCRIPT
{ BACKUP DATABASE PLUS ARCHIVELOG TAG 'FULL_DB'; }

Para executá-lo, usamos o comando abaixo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
RMAN> run { execute script FULL_DB_SCRIPT;}
  
executing script: FULL_DB_SCRIPT
  
  
Starting backup at 09-JUN-21
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=88 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1039127675
input archived log thread=1 sequence=6 RECID=2 STAMP=1039295758
input archived log thread=1 sequence=7 RECID=3 STAMP=1039296485
input archived log thread=1 sequence=8 RECID=4 STAMP=1039301471
input archived log thread=1 sequence=9 RECID=5 STAMP=1039303921
input archived log thread=1 sequence=10 RECID=6 STAMP=1039304261
input archived log thread=1 sequence=11 RECID=7 STAMP=1039369019
input archived log thread=1 sequence=12 RECID=8 STAMP=1039371217
input archived log thread=1 sequence=13 RECID=9 STAMP=1039371935
input archived log thread=1 sequence=14 RECID=10 STAMP=1039725409
input archived log thread=1 sequence=15 RECID=11 STAMP=1039810779
input archived log thread=1 sequence=16 RECID=12 STAMP=1041718798
input archived log thread=1 sequence=17 RECID=13 STAMP=1060626438
input archived log thread=1 sequence=18 RECID=14 STAMP=1060987482
input archived log thread=1 sequence=19 RECID=15 STAMP=1061073000
input archived log thread=1 sequence=20 RECID=16 STAMP=1065501952
input archived log thread=1 sequence=21 RECID=17 STAMP=1065506515
input archived log thread=1 sequence=22 RECID=18 STAMP=1065506554
input archived log thread=1 sequence=23 RECID=19 STAMP=1071811097
input archived log thread=1 sequence=24 RECID=20 STAMP=1071811858
input archived log thread=1 sequence=25 RECID=21 STAMP=1071812134
input archived log thread=1 sequence=26 RECID=22 STAMP=1073535805
input archived log thread=1 sequence=27 RECID=23 STAMP=1073625862
input archived log thread=1 sequence=28 RECID=24 STAMP=1074747851
input archived log thread=1 sequence=29 RECID=25 STAMP=1074748747
channel ORA_DISK_1: starting piece 1 at 09-JUN-21
channel ORA_DISK_1: finished piece 1 at 09-JUN-21
piece handle=+DG_FRA/CORTEX/BACKUPSET/2021_06_09/annnf0_full_db_0.260.1074748749 tag=FULL_DB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 09-JUN-21
  
Starting backup at 09-JUN-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DG_DATA/CORTEX/DATAFILE/system.256.1039033445
input datafile file number=00005 name=+DG_DATA/CORTEX/DATAFILE/soe.266.1065506205
input datafile file number=00003 name=+DG_DATA/CORTEX/DATAFILE/sysaux.257.1039033519
input datafile file number=00004 name=+DG_DATA/CORTEX/DATAFILE/undotbs1.258.1039033555
input datafile file number=00007 name=+DG_DATA/CORTEX/DATAFILE/users.259.1039033555
channel ORA_DISK_1: starting piece 1 at 09-JUN-21
channel ORA_DISK_1: finished piece 1 at 09-JUN-21
piece handle=+DG_FRA/CORTEX/BACKUPSET/2021_06_09/nnndf0_tag20210609t051955_0.261.1074748797 tag=TAG20210609T051955 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
Finished backup at 09-JUN-21
  
Starting backup at 09-JUN-21
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=26 STAMP=1074748921
channel ORA_DISK_1: starting piece 1 at 09-JUN-21
channel ORA_DISK_1: finished piece 1 at 09-JUN-21
piece handle=+DG_FRA/CORTEX/BACKUPSET/2021_06_09/annnf0_full_db_0.262.1074748923 tag=FULL_DB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-JUN-21
  
Starting Control File and SPFILE Autobackup at 09-JUN-21
piece handle=+DG_FRA/CORTEX/AUTOBACKUP/2021_06_09/s_1074748924.263.1074748927 comment=NONE
Finished Control File and SPFILE Autobackup at 09-JUN-21

Validando os elementos de backup criados:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
RMAN> LIST BACKUPSET TAG 'FULL_DB';
  
  
List of Backup Sets
===================
  
  
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
262     948.10M    DISK        00:00:41     09-JUN-21
        BP Key: 263   Status: AVAILABLE  Compressed: NO  Tag: FULL_DB
        Piece Name: +DG_FRA/CORTEX/BACKUPSET/2021_06_09/annnf0_full_db_0.260.1074748749
  
  List of Archived Logs in backup set 262
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    5       2017317    29-APR-20 2143151    30-APR-20
  1    6       2143151    30-APR-20 2291543    01-MAY-20
  1    7       2291543    01-MAY-20 2392920    01-MAY-20
  1    8       2392920    01-MAY-20 2505442    01-MAY-20
  1    9       2505442    01-MAY-20 2608402    01-MAY-20
  1    10      2608402    01-MAY-20 2709343    01-MAY-20
  1    11      2709343    01-MAY-20 2810234    02-MAY-20
  1    12      2810234    02-MAY-20 2926273    02-MAY-20
  1    13      2926273    02-MAY-20 3027319    02-MAY-20
  1    14      3027319    02-MAY-20 3138045    06-MAY-20
  1    15      3138045    06-MAY-20 3258948    07-MAY-20
  1    16      3258948    07-MAY-20 3369054    29-MAY-20
  1    17      3369054    29-MAY-20 3474226    31-DEC-20
  1    18      3474226    31-DEC-20 3575887    04-JAN-21
  1    19      3575887    04-JAN-21 3693929    05-JAN-21
  1    20      3693929    05-JAN-21 3804163    26-FEB-21
  1    21      3804163    26-FEB-21 3812081    26-FEB-21
  1    22      3812081    26-FEB-21 3815548    26-FEB-21
  1    23      3815548    26-FEB-21 3928583    06-MAY-21
  1    24      3928583    06-MAY-21 3931358    06-MAY-21
  1    25      3931358    06-MAY-21 3932493    06-MAY-21
  1    26      3932493    06-MAY-21 4032810    26-MAY-21
  1    27      4032810    26-MAY-21 4138313    27-MAY-21
  1    28      4138313    27-MAY-21 4239416    09-JUN-21
  1    29      4239416    09-JUN-21 4241913    09-JUN-21
  
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
334     49.50K     DISK        00:00:00     09-JUN-21
        BP Key: 340   Status: AVAILABLE  Compressed: NO  Tag: FULL_DB
        Piece Name: +DG_FRA/CORTEX/BACKUPSET/2021_06_09/annnf0_full_db_0.262.1074748923
  
  List of Archived Logs in backup set 334
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    30      4241913    09-JUN-21 4242100    09-JUN-21

Caso precisemos alterar o SS, podemos realizar a mudança com o comando REPLACE:

1
2
3
4
5
6
7
8
9
10
RMAN> REPLACE SCRIPT FULL_DB_SCRIPT
COMMENT 'sem archivelog'
{ BACKUP DATABASE TAG 'FULL_DB'; }2> 3>
  
replaced script FULL_DB_SCRIPT
  
RMAN> PRINT SCRIPT FULL_DB_SCRIPT;
  
printing stored script: FULL_DB_SCRIPT
{ BACKUP DATABASE TAG 'FULL_DB'; }

Além de criar o SS digitando os comandos no prompt do RMAN, também podemos criá-lo a partir de um arquivo externo. Basta que esse arquivo possua os comandos entre chaves, para que o SS os reconheça corretamente. Vamos simular isso:

1
2
3
[oracle@oel7 ~]$ cd /home/oracle/
[oracle@oel7 ~]$ cat SS.txt
{ BACKUP DATABASE TAG 'FULL_DB'; }

Criando o SS a partir do arquivo acima:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
[oracle@oel7 ~]$ rman target / catalog CORTEX_CATALOG/CORTEX_CATALOG@RMANDB
  
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jun 9 05:32:43 2021
Version 19.3.0.0.0
  
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
  
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
  
RMAN> CREATE SCRIPT BSS FROM FILE '/home/oracle/SS.txt';
  
script commands will be loaded from file /home/oracle/SS.txt
created script BSS
  
RMAN> LIST SCRIPT NAMES;
  
List of Stored Scripts in Recovery Catalog
  
  
    Scripts of Target Database CORTEX
  
       Script Name
       Description
       -----------------------------------------------------------------------
       BSS
  
       FULL_DB_SCRIPT
       sem archivelog
  
RMAN> PRINT SCRIPT BSS;
  
printing stored script: BSS
{ BACKUP DATABASE TAG 'FULL_DB'; }
  
RMAN> run { execute script BSS;}
  
executing script: BSS
  
Starting backup at 09-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 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=00001 name=+DG_DATA/CORTEX/DATAFILE/system.256.1039033445
input datafile file number=00005 name=+DG_DATA/CORTEX/DATAFILE/soe.266.1065506205
input datafile file number=00003 name=+DG_DATA/CORTEX/DATAFILE/sysaux.257.1039033519
input datafile file number=00004 name=+DG_DATA/CORTEX/DATAFILE/undotbs1.258.1039033555
input datafile file number=00007 name=+DG_DATA/CORTEX/DATAFILE/users.259.1039033555
channel ORA_DISK_1: starting piece 1 at 09-JUN-21
channel ORA_DISK_1: finished piece 1 at 09-JUN-21
piece handle=+DG_FRA/CORTEX/BACKUPSET/2021_06_09/nnndf0_full_db_0.264.1074749701 tag=FULL_DB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:56
Finished backup at 09-JUN-21
  
Starting Control File and SPFILE Autobackup at 09-JUN-21
piece handle=+DG_FRA/CORTEX/AUTOBACKUP/2021_06_09/s_1074749816.265.1074749819 comment=NONE
Finished Control File and SPFILE Autobackup at 09-JUN-21

Um recurso interessante do SS é que o mesmo aceita variáveis que podem ser populadas em tempo de execução. Vamos criar um novo SS que nos permite testar isso. Importante dizer que, no momento de sua criação, o valor das variáveis é solicitado, mas o script não é disparado, e esses valores podem ser definidos depois em execução. Ou seja, não precisamos nos preocupar, mesmo que os valores informados sejam aleatórios:

1
2
3
4
5
6
7
8
RMAN> CREATE SCRIPT TBS_FULL_SCRIPT
{ BACKUP TABLESPACE &1 TAG &2 ;}2>
  
Enter value for 1: USERS
  
Enter value for 2: BSS
  
created script TBS_FULL_SCRIPT

Executando o SS e passando os valores dos parâmetros:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
RMAN> RUN { EXECUTE SCRIPT TBS_FULL_SCRIPT USING USERS 'SPFC';}
  
executing script: TBS_FULL_SCRIPT
  
Starting backup at 09-JUN-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=+DG_DATA/CORTEX/DATAFILE/users.259.1039033555
channel ORA_DISK_1: starting piece 1 at 09-JUN-21
channel ORA_DISK_1: finished piece 1 at 09-JUN-21
piece handle=+DG_FRA/CORTEX/BACKUPSET/2021_06_09/nnndf0_spfc_0.266.1074750197 tag=SPFC comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-JUN-21
  
Starting Control File and SPFILE Autobackup at 09-JUN-21
piece handle=+DG_FRA/CORTEX/AUTOBACKUP/2021_06_09/s_1074750198.267.1074750199 comment=NONE
Finished Control File and SPFILE Autobackup at 09-JUN-21

Listando o backup:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
RMAN> LIST BACKUPSET TAG 'SPFC';
  
  
List of Backup Sets
===================
  
  
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
394     Full    2.38M      DISK        00:00:01     09-JUN-21
        BP Key: 397   Status: AVAILABLE  Compressed: NO  Tag: SPFC
        Piece Name: +DG_FRA/CORTEX/BACKUPSET/2021_06_09/nnndf0_spfc_0.266.1074750197
  List of Datafiles in backup set 394
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 4243110    09-JUN-21              NO    +DG_DATA/CORTEX/DATAFILE/users.259.1039033555

Outro método de disparo é no momento de invocar o RMAN, conforme exemplo abaixo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[oracle@oel7 ~]$ rman target / catalog CORTEX_CATALOG/CORTEX_CATALOG@RMANDB script=TBS_FULL_SCRIPT USING USERS 'SPFC'
  
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jun 9 05:45:17 2021
Version 19.3.0.0.0
  
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
  
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
  
executing script: TBS_FULL_SCRIPT
  
Starting backup at 09-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 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=00007 name=+DG_DATA/CORTEX/DATAFILE/users.259.1039033555
channel ORA_DISK_1: starting piece 1 at 09-JUN-21
channel ORA_DISK_1: finished piece 1 at 09-JUN-21
piece handle=+DG_FRA/CORTEX/BACKUPSET/2021_06_09/nnndf0_spfc_0.268.1074750321 tag=SPFC comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-JUN-21
  
Starting Control File and SPFILE Autobackup at 09-JUN-21
piece handle=+DG_FRA/CORTEX/AUTOBACKUP/2021_06_09/s_1074750322.269.1074750323 comment=NONE
Finished Control File and SPFILE Autobackup at 09-JUN-21
  
Recovery Manager complete.
[oracle@oel7 ~]$

Para deletarmos os SS’s, basta usarmos o comando DELETE, vide abaixo:

1
2
3
4
5
6
7
8
9
10
11
RMAN> DELETE SCRIPT BSS;
  
deleted script: BSS
  
RMAN> DELETE SCRIPT FULL_DB_SCRIPT;
  
deleted script: FULL_DB_SCRIPT
  
RMAN> DELETE SCRIPT TBS_FULL_SCRIPT;
  
deleted script: TBS_FULL_SCRIPT

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish