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:
[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:
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:
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:
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:
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:
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:
[oracle@oel7 ~]$ cd /home/oracle/
[oracle@oel7 ~]$ cat SS.txt
{ BACKUP DATABASE TAG 'FULL_DB'; }
Criando o SS a partir do arquivo acima:
[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:
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:
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:
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:
[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:
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.
Portuguese
English
Spanish