Como fazer um backup inconsistente
Neste manual, será mostrado o passo à passo de como realizar backup inconsistente utilizando o Controlfile como repositório e posteriormente, em outro artigo, mostraremos como restaurar este backup.
— Conectando com o usuário do Banco de Dados
# su - oracle— Criando e exportando a variável ORACLE_SID
$ export ORACLE_SID=orcl— Conectando no Banco de Dados
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 8 21:05:08 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.— Inicializando o Banco de dados
SQL> startup;
ORACLE instance started.
Total System Global Area 339738624 bytes
Fixed Size 2924112 bytes
Variable Size 260047280 bytes
Database Buffers 71303168 bytes
Redo Buffers 5464064 bytes
Database mounted.
Database opened.— Verificando o modo do Archive Log
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence1Podemos notar que o Banco de Dados não está no modo Archive.
— Verificando parâmetros de localização do Archive Log
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
db_recovery_file_dest string /u01/app/oracle /fast_recovery_
area
db_recovery_file_dest_size big integer 4560M— Verificando como será construído o nome do archive Log
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ -----------
log_archive_format string %t_%s_%r.dbf— Alterando o nome do archive log
SQL> alter system set log_archive_format = '%t_%s_%r.arc' scope=spfile;
System altered.
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ -----------
log_archive_format string %t_%s_%r.dbfObs: Este parâmetro será alterado somente no próximo startup do Banco de Dados.
— Finalizando o Banco de Dados.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down— Inicializando o Banco de Dados
SQL> startup;
ORACLE instance started.
Total System Global Area 339738624 bytes
Fixed Size 2924112 bytes
Variable Size 260047280 bytes
Database Buffers 71303168 bytes
Redo Buffers 5464064 bytes
Database mounted.
Database opened.— Verificanndo o novo valor do parâmetro do Banco de Dados.
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- --------------------- ---------
log_archive_format string %t_%s_%r.arc
Valores:
%t= Número da tread
%s= Número da sequência do log
%r= Resetlogs ID, assegura que um numero único se gerado para cada arquivo de log.
.arc= Extensão do arquivo, descrição livre.— Colocando o Banco de dados em modo Archive Log
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instanceNão podemos alterar o modo do Archive Log com o Banco de Dados aberto.
— Finalizando o Banco de Dados.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down. — Inicializando o Banco de Dados no estagio Mount.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 339738624 bytes
Fixed Size 2924112 bytes
Variable Size 260047280 bytes
Database Buffers 71303168 bytes
Redo Buffers 5464064 bytes
Database mounted. — Colocando o Banco de dados em modo Archive Log
SQL> alter database archivelog;
Database altered. — Abrindo o Banco de Dados
SQL> alter database open;
Database altered.— Verificando o modo do Archive Log
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1Podemos observar que agora, o Banco de Dados está em modo Archive.
— Verificando os arquivos gerados no Sistema Operacional
SQL> !ls -l /u01/app/oracle/fast_recovery_area/ORCL/archivelog
total 0 Obs: Podemos verificar que como acabamos de alterar o modo do archive log, ainda não foi criado nenhum arquivo.
— Forçando a geração de um archive log
SQL> alter system switch logfile;
System altered.— Verificando os arquivos gerados no Sistema Operacional
SQL> !ls -l /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_08_08
total 4
drwxr-x--- 2 oracle oinstall 4096 Aug 8 21:44 2015_08_08Agora, podemos notar que já existe um arquivo no Sistema Operacional
— Saindo da ferramenta SQLPLUS
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
-- Conectando-se ao RMAN
[oracle@vmdb ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sat Aug 8 21:45:38 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1411380092)— Exibindo os parâmetros pré-configurados no RMAN.
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # 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 '/u01/app/oracle/product/12.1.0.2/db_1/dbs/snapcf_orcl.f'; # default— Alterando o parâmetro CONTROLFILE AUTOBACKUP do RMAN
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully storedObs: Se o valor do parâmetro CONTROLFILE AUTOBACKUP for OFF, o backup do controlfile será realizado de forma automática juntamente com o backup do Banco de Dados, caso for ON, o backup do Controlfile será realizado de forma automática, porém, separado do backup do Banco de Dados.
— Alterando o parâmetro CONTROLFILE AUTOBACKUP FORMAT do RMAN
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/autobackup/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/autobackup/%F';
new RMAN configuration parameters are successfully storedO parâmetro CONTROLFILE AUTOBACKUP FORMAT nada mais é do que a localização do backup do controlfile, desde que o parâmetro CONTROLFILE AUTOBACKUP esteja com o valor ON. Este parâmetro foi alterado para a fácil localização do backup. No caso acima, foi criado/configurado o diretório backup/autobackup para receber o backup do controlfile.
— Alterando o parâmetro CHANNEL DEVICE TYPE DISK FORMAT do RMAN
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/repositorio/%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/repositorio/%U';
new RMAN configuration parameters are successfully stored No parâmetro CHANNEL DEVICE TYPE DISK FORMAT configura-se a localização do backup do Banco de Dados.
— Exibindo os novos parâmetros configurados no RMAN.
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/autobackup/%F';
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 CHANNEL DEVICE TYPE DISK FORMAT '/backup/repositorio/%U';
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 '/u01/app/oracle/product/12.1.0.2/db_1/dbs/snapcf_orcl.f'; # default— Verificando se o Banco de Dados está realmente aberto.
RMAN> select log_mode, open_mode from v$database;
LOG_MODE OPEN_MODE
------------ --------------------
ARCHIVELOG READ WRITE— Realizando o Backup do Banco de Dados mais os archive logs.
RMAN> backup database plus archivelog;
Starting backup at 08-AUG-15
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 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=1 RECID=1 STAMP=887233487
input archived log thread=1 sequence=2 RECID=2 STAMP=887233626
channel ORA_DISK_1: starting piece 1 at 08-AUG-15
channel ORA_DISK_1: finished piece 1 at 08-AUG-15
piece handle=/backup/repositorio/04qe472s_1_1 tag=TAG20150808T214707 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 08-AUG-15
Starting backup at 08-AUG-15
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=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-AUG-15
channel ORA_DISK_1: finished piece 1 at 08-AUG-15
piece handle=/backup/repositorio/05qe4730_1_1 tag=TAG20150808T214711 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 08-AUG-15
Starting backup at 08-AUG-15
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=3 RECID=3 STAMP=887233737
channel ORA_DISK_1: starting piece 1 at 08-AUG-15
channel ORA_DISK_1: finished piece 1 at 08-AUG-15
piece handle=/backup/repositorio/06qe476a_1_1 tag=TAG20150808T214858 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-AUG-15
Starting Control File and SPFILE Autobackup at 08-AUG-15
piece handle=/backup/autobackup/c-1411380092-20150808-00 comment=NONE
Finished Control File and SPFILE Autobackup at 08-AUG-15— Saindo do RMAN
RMAN> exit
Recovery Manager complete.— Verificando os arqui vos criados pelo backup
$ ls -l /backup/repositorio/
total 1318304
-rw-r----- 1 oracle oinstall 22401024 Aug 8 21:47 04qe472s_1_1
-rw-r----- 1 oracle oinstall 1327529984 Aug 8 21:48 05qe4730_1_1
-rw-r----- 1 oracle oinstall 5120 Aug 8 21:48 06qe476a_1_— Backup Banco de Dados e Archive Logs
$ ls -l /backup/autobackup/
total 10208
-rw-r----- 1 oracle oinstall 10452992 Aug 8 21:49 c-1411380092-20150808-00— Backup Controlfile
Pronto! Backup realizado.
No próximo artigo, será mostrado como restaurar o Backup inconsistente.
