Introducing Oracle RMAN

Backup & Recovery / 14/07/2022

Para abrir o utilitário RMAN e conectar-se ao banco de dados, temos as seguintes maneiras (com autenticação via S.O):

[oracle@oel8 ~]$ rman
 
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 10 13:00:51 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
RMAN> CONNECT TARGET /
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN>

[oracle@oel8 ~]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 10 13:02:33 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN>

Conectando via autenticação por usuário e senha:

[oracle@oel8 ~]$ rman
 
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 10 13:05:10 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
RMAN> connect target sys@RMANDB
 
target database Password:
connected to target database: RMANDB (DBID=3825250984)
 
RMAN> exit
 
 
Recovery Manager complete.
[oracle@oel8 ~]$ rman target sys@RMANDB
 
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 10 13:05:34 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
target database Password:
connected to target database: RMANDB (DBID=3825250984)
 
RMAN>

Criando um usuário no banco de dados com privilégio de sysbackup (ou seja, não terá acesso para administrar o banco de dados, como um sysdba, e sim apenas para atividades inerentes ao RMAN):

[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 10 13:06:09 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
 
SQL> CREATE USER BSS IDENTIFIED BY BSS;
 
User created.
 
SQL> GRANT SYSBACKUP TO BSS;
 
Grant succeeded.
 
SQL> GRANT CREATE SESSION TO BSS;
 
Grant succeeded.
 
SQL> exit

Realizando a autenticação com o usuário criado acima:

[oracle@oel8 ~]$ rman target "'BSS/BSS@RMANDB as sysbackup'"
 
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 10 13:09:29 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN>

Podemos gerar um script de log com todo o conteúdo gerado dentro do RMAN, conforme exemplo abaixo (a opção append não reescreve o arquivo, mas sim vai adicionando conteúdo ao mesmo):

[oracle@oel8 ~]$ rman target / log /oracle/rman.log append
RMAN> show all;
RMAN> exit
[oracle@oel8 ~]$ cat /oracle/rman.log
 
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 10 17:18:18 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN>
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/18.0.0/product/dbs/snapcf_RMANDB.f'; # default
 
RMAN>
 
Recovery Manager complete.
[oracle@oel8 ~]$

Utilizando o recurso de CMDFILE, podemos encapsular os comandos rman em um script e executá-lo conforme exemplo:

[oracle@oel8 ~]$ cat /oracle/SCRIPT.rcvd
run
{SHOW ALL;}
[oracle@oel8 ~]$ rman target / CMDFILE=/oracle/SCRIPT.rcvd
 
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 10 17:22:43 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN> run
2> {SHOW ALL;}
3>
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/18.0.0/product/dbs/snapcf_RMANDB.f'; # default
 
Recovery Manager complete.
RMAN> exit
 
Recovery Manager complete.
[oracle@oel8 ~]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 10 17:23:47 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN> @/oracle/SCRIPT.rcvd
 
RMAN> run
2> {SHOW ALL;}
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/18.0.0/product/dbs/snapcf_RMANDB.f'; # default
 
RMAN> **end-of-file**

Uma variável linux que é importante de definir para evitar confusões ao utilizar o RMAN é a NLS_DATE_FORMAT:

[oracle@oel8 ~]$ cat .bash_profile | grep NLS
NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS"
export ORACLE_SID ORACLE_BASE ORACLE_HOME GRID_HOME LD_LIBRARY_PATH PATH NLS_DATE_FORMAT
[oracle@oel8 ~]$

Conforme exemplos acima, dentro do RMAN é possível definir uma série de parâmetros que definem o comportamento que a ferramenta adotará. Como este banco de dados não teve nenhuma alteração ainda destes parâmetros, notamos que há uma flag \”# default\” no final de cada linha. Para consultarmos um parâmetro em específico, podemos usar o comando abaixo de exemplo:

RMAN> SHOW CONTROLFILE AUTOBACKUP;
 
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

Alterando o conteúdo do parâmetro, é possível ver que a flag default não aparece mais:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
 
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored
 
RMAN> SHOW CONTROLFILE AUTOBACKUP;
 
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
 
RMAN>

Utilizando a opção CLEAR vemos que o valor do parâmetro retorna ao padrão:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP CLEAR
2> ;
 
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
RMAN configuration parameters are successfully reset to default value
 
RMAN> SHOW CONTROLFILE AUTOBACKUP;
 
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

Quando um parâmetro do RMAN for alterado (exceto para o seu valor default com a opção clear), ele também estará presente na view v$RMAN_CONFIGURATION:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
 
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored
 
RMAN> exit
 
 
Recovery Manager complete.
[oracle@oel8 ~]$
[oracle@oel8 ~]$
[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 10 17:41:20 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
 
SQL> SELECT NAME,VALUE FROM v$RMAN_CONFIGURATION;
 
NAME
-----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
CONTROLFILE AUTOBACKUP
OFF
 
 
SQL>

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP CLEAR;
 
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
RMAN configuration parameters are successfully reset to default value
 
RMAN> exit
 
 
Recovery Manager complete.
[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 10 17:44:14 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
 
SQL> SELECT NAME,VALUE FROM v$RMAN_CONFIGURATION;
 
no rows selected

No RMAN, temos 2 categorias de comandos: Stand-alone e Jobs. Como os nomes sugerem, o primeiro refere-se aos comandos que são executados iterativa com o prompt, onde disparamos cada instrução (com o ; no final) e aguardamos a sua execução para executar o passo seguinte. Já o Job nos permite criar um bloco com uma série de comandos que serão executados de forma sequencial pela ferramenta, e em caso de algum erro, o processo é suspenso (não há rollback do que já foi executado):

[oracle@oel8 ~]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 10 17:53:22 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN> #COMENTARIO: STAND-ALONE COMMAND
2> SHOW CONTROLFILE AUTOBACKUP;
 
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
 
RMAN> #COMENTARIO: JOB COMMAND
2> RUN
3> {
4> SHOW CONTROLFILE AUTOBACKUP;
5> }
 
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
 
RMAN>

Por último: o controlfile abriga informações de metadados sobre o Oracle (como suas estruturas físicas, localização de datafiles e redologs, etc) e também sobre as informações de backup emitidas pelo RMAN. Por padrão, esses dados possuem retenção de 7 dias, mas caso não seja utilizado um catálogo de forma apartada, o indicado é que esta retenção seja maior:

[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 10 17:57:47 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
 
SQL> SHOW PARAMETER CONTROL_FILE_RECORD_KEEP_TIME;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
SQL> ALTER SYSTEM SET CONTROL_FILE_RECORD_KEEP_TIME=90 SCOPE=BOTH;
 
System altered.
 
SQL> SHOW PARAMETER CONTROL_FILE_RECORD_KEEP_TIME;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     90
SQL>

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