Oracle Live

07/10/2016

RMAN – Manual Básico

Filed under: Backup & Recovery — mogukiller @ 8:08 am
Tags: ,

Comandos básicos de RMAN.

-- CONEXION
-- BACKUP
-- BACKUP INCREMENTAL
-- RESTORE
-- RECOVER
-- BLOQUES CORRUPTOS
-- CATALOG
-- LIST
-- REPORT
-- CROSSCHECK
-- DELETE
-- ALLOCATE CHANNEL
-- CHANGE
-- CONFIGURE
-- CHANGE INCARNATION
-- VISTAS
-- EJEMPLOS

--------------
-- CONEXION:
--------------
rman target / --NOCATALOG by default
rman target / CATALOG rman/temporal@catl
rman target sys/manager@pruebas2 CATALOG rman/temporal@catl --Conexion remota.

--------------
-- BACKUP
--------------

BACKUP <TIPO BACKUP> <TIPO DESTINO> <ORIGEN> <OPCIONES>;

<TIPO BACKUP>
	AS COPY
	AS BACKUPSET
	AS COMPRESSED BACKUPSET

<TIPO DESTINO>
	DEVICE TYPE DISK
	DEVICE TYPE SBT

<ORIGEN>
	CURRENT CONTROLFILE
	SPFILE
	DATABASE
	DATAFILE 1,2,3
	TABLESPACE
	ARCHIVELOG ALL
	

<OPCIONES>
	FORMAT '......'
	TAG = '.....'

NOTA: La configuracion para estos ejemplos ha sido:
	CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET
	
-- ARCHIVELOG
BACKUP ARCHIVELOG ALL;
BACKUP ARCHIVELOG ALL DELETE ALL INPUT;	-- Borra todos los archiver
BACKUP ARCHIVELOG FROM TIME 'SYSDATE-9' UNTIL TIME 'SYSDATE-7';
BACKUP ARCHIVELOG FROM SEQUENCE 100 UNTIL SEQUENCE 150;
BACKUP ARCHIVELOG FROM SEQUENCE 100 DELETE ALL INPUT;
BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

-- DATABASE 
BACKUP DATABASE PLUS ARCHIVELOG;
BACKUP DATABASE MAXSETSIZE = 10G; -- Limita tamaño maximo del backupset
BACKUP DATABASE NOEXCLUDE; -- Mete todos los tablespaces.
BACKUP DATABASE SKIPE READONLY SKIPE OFFLINE; -- No hace el backup de tablespace readonly o offline
BACKUP DURATION 4:00 DATABASE; -- Configura una ventana maxima de tiempo.

-- TABLESPACE
BACKUP DURATION 4:00 PARTIAL TABLESPACE USERS FILESPERSET 1; 
	#PARTIAL: Si se supera la ventana no devuelve un error. Informa de los ficheros que quedaron por hacer el backup.
	#FILEPERSET 1: Indica que ira un fichero por pieza.
BACKUP DURATION 4:00 PARTIAL MINIMIZE TIME TABLESPACE USERS FILESPERSET 1; -- Durante la ventana minimiza el tiempo del backup
BACKUP DURATION 4:00 PARTIAL MINIMIZE LOAD TABLESPACE USERS FILESPERSET 1; -- Durante la ventana minimiza la carga.

------------------------
-- BACKUP INCREMENTAL
------------------------

BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP INCREMENTAL LEVEL 1 DATABASE;	-- Incremental desde un Level 0 o 1
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE;  -- Incremental desde un Level 0
BACKUP INCREMENTAL LEVEL 1 TABLESPACE PRUEBA_RMAN;
BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 750983 DATABASE FORMAT '/tmp/incr_for_standby/bkup_%U';

BACKUP INCREMENTAL FROM SCN=n DATABASE; -- El backup lo deja en ?/dbs
BACKUP INCREMENTAL FROM SCN=n DATABASE FORMAT '/tmp/incr_standby_%U'; -- El backup lo deja en ?/dbs

-- Habilitamos Block Change Tracking

show parameters db_create_file_dest
ALTER SYSTEM SET db_create_file_dest='/export/oracle/app/oracle/diag/rdbms' SCOPE=BOTH SID='*';
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; 
---------------------------
-- RESTORE
---------------------------
RESTORE DATABASE PREVIEW;			-- Te dice que va ha hacer pra restaurar la base de datos sin hacerlo.
RESTORE DATABASE PREVIEW SUMMARY;	
RESTORE DATABASE PREVIEW RECALL;	-- Esta llamada es cuando hemos dejado los backups en cinta
RESTORE DATABASE VALIDATE; --Valida si podemos restaurar.
RESTORE CONTROLFILE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;
VALIDATE BACKUPSET 2,5; --Valida los backupset 2 y 5.

RESTORE CONTROLFILE FROM AUTOBACKUP;
RESTORE CONTROLFILE FROM '......';

RESTORE ARCHIVELOG ALL;
RESTORE ARCHIVELOG FROM SEQUENCE XX UNTIL SEQUENCE YY;
RESTORE SPFILE TO '/tmp/...ora' FROM AUTOBACKUP;
RESTORE SPFILE TO PFILE ='/tmp/....ora' 

-- RESTORE SPFILE.
/* nota: Partimos de la instancia bajada y sin spfile */

RMAN> SET DBID 4125557522
RMAN> STARTUP FORCE NOMOUNT
/* SALIDA:
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/export/oracle/app/oracle/product/11.2.0.3/dbs/initPRUEBA.ora'

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

Total System Global Area     158662656 bytes

Fixed Size                     2226456 bytes
Variable Size                 92276456 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5439488 bytes
*/
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/recovery_area/PRUEBA_N2/autobackup/2013_08_08/%F';
RESTORE SPFILE TO PFILE '/tmp/initPRUEBA.ora' FROM AUTOBACKUP; -- Suele dar un error RMAN-06172 por que no encuentra el autobackup, por lo que se lo tenemos que indicar.
RESTORE SPFILE FROM AUTOBACKUP DB_RECOVERY_FILE_DEST='/recovery_area' DB_NAME='PRUEBA_N2';

-- CAMBIAR UBICACION DBF.
run{
	SQL 'ALTER TABLESPACE PRUEBA_RMAN OFFLINE IMMEDIATE';
	SET NEWNAME FOR DATAFILE 
		'/export/oracle/app/oracle/oradata/PRUEBA/dat_001/PRUEBA_RMAN_00.dbf' TO
		'/export/oracle/app/oracle/oradata/PRUEBA/dat_001/PRUEBA_RMAN_NEW_00.dbf';
	RESTORE TABLESPACE PRUEBA_RMAN;
	SWITCH DATAFILE ALL; #Actualiza el controfile
	RECOVER TABLESPACE PRUEBA_RMAN;
	SQL 'ALTER TABLESPACE PRUEBA_RMAN ONLINE';
}

-- Restaura los archive log de un backu

RUN
{
	SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore';
	RESTORE ARCHIVELOG ALL;
}

-- Restaura datafile a una nueva ubicacion
RUN
{
	SET NEWNAME FOR DATAFILE 'dbf1' TO '.......';
	RESTORE DATAFILE 
	SWITCH DATAFILE ALL; # Update control file with new location of datafiles.
	
}

---------------------------
-- RECOVER
---------------------------
RECOVER DATABASE;

------------------------
-- BLOQUES CORRUPTOS
------------------------
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
	SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

------------------------
-- CATALOG
------------------------
CATALOG BACKUPPIECE '....', '.....'; #Util cuando movemos backups de una maquina a otra
CATALOG START WITH 'Ruta de mi backup'; #Cataloga todos los ficheros de un directorio
CATALOG RECOVERY AREA; #Cataloga todo el contenido de la Recovery area.

CHANGE ... UNCATALOG #Descatalogamos elementos
CHANGE ARCHIVELOG .. UNCATALOG #Para descatalogar archivelog que han sido borrados desde el S.O
CHANGE DATAFILECOPY '....' UNCATALOG;
 
#Vistas:
	V$BACKUP_PIECE,
	V$BACKUP_SET,
	V$BACKUP_DATAFILE,
	V$BACKUP_REDOLOG,
	V$BACKUP_SPFILE
------------------------
-- LIST
------------------------
LIST BACKUP;
LIST COPY;
LIST BACKUP BY BACKUP;
LIST BACKUP BY FILE;
LIST BACKUP SUMMARY;
LIST BACKUP VERBOSE;

LIST EXPIRED BACKUP;
LIST INCARNATION; -- Muestra las incarnation de la base de datos

LIST BACKUP OF ARCHIVELOG ALL;
LIST ARCHIVELOG ALL;

-- VIEWs
	V$_BACKUP_FILES

------------------------
-- REPORT
------------------------
/*
 -- Que ficheros necesitan hacer backup.
 -- Que backups estan obsoletos y pueden borrarse.
 -- Esquema fisico de la base de datos.
*/
REPORT NEED BACKUP; -- Muestra aquellos ficheros que necesitan hacer backup.
REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS;

REPORT OBSOLETE; -- Muestra todos los backups que podemos borrar
REPORT OBSOLETE RECOVERY WINDOW OF n DAYS.
REPORT OBSOLETE REDUNDANCY 1;

REPORT SCHEMA; -- Devuelve la estructura de la base de datos
REPORT SCHEMA AT TIME 'SYSDATE - 1'; -- CON CATALOGO

---------------------------
-- CROSSCHECK
---------------------------
/* Actualiza el catalogo de rman */

CROSSCHECK BACKUP;
CROSSCHECK BACKUP OF ARCHIVELOG ALL; #Te valida los archivelog que tienes en los backup
CROSSCHECK COPY;
CROSSCHECK ARCHIVELOG ALL; #Por si borramos archiver a mano que lo sepa RMAN
---------------------------
-- DELETE
---------------------------

DELETE BACKUP; #Borra todos los backup para esa base de datos
DELETE EXPIRED BACKUP; #Recomendable hacerlo despues de un CROSSCHECK
DELETE OBSOLETE;
delete force noprompt obsolete;
DELETE NOPROMPT ARCHIVELOG ALL; #Borra todos los archivelog de disco
DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE = 295; #Borra archiver de disco
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-3/2'
DELETE FORCE NOPROMPT BACKUPSET TAG '....' #Cuando no me deja borrarlo por las buenas.

---------------------------
-- ALLOCATE CHANNEL
---------------------------

ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK; #Canal pensado para labores de mantenimiento CROSSCHECK, DELETE ..
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT '/disk1/bck_%U.bck';

ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT '/disk1/bck_%U.bck' MAXPIECESIZE 2G;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/disk1/bck_%U.bck' MAXPIECESIZE 2G;
RELEASE ch1;

-- Opciones de Format
/*
	%d :DB_NAME
	%t :timestamp
	%s :backupset
	%p :backup piecenumber
*/
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT '/recovery_area/full_backup_%d_%s_%p.back';
/*SALIDA:
	/recovery_area/full_backup_PRUEBA_102_1.back
*/

'nota: para aumentar el grado de paralelismo podemos alocar canales en diferentes instancias'
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK CONNECT 'sys/oracle@PRUEBA_N2';
CONFIGURE CHANNEL ch1 DEVICE TYPE DISK CONNECT 'sys/oracle@PRUEBA_N1';

ALLOCATE CHANNEL FOR MAINTENANCE --Cuando se ejecuta desde el prompt

---------------------------
-- CHANGE ... [ UNAVAILABLE | AVAILABLE | KEEP | NOKEEP ]
---------------------------

CHANGE [ BACKUPSET | BACKUP OF CONTROLFILE ...] UNAVAILABLE;
CHANGE BACKUPSET 50 KEEP; #Impide que se borre

---------------------------------
-- CONFIGURE
---------------------------------
SHOW ALL -- Muestra los parametros.

-- RETENTION POLICY
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE RETENTION POLICY TO NONE; -- No considera ningun Backup como obsoleto.

-- RETENTION POLICY FOR ARCHIVELOG
CONFIGURE ARCHIVELOG DELETION POLICY

CONFIGURE BACKUP OPTIMIZATION ON | OFF;

-- CONTROLFILE AUTOBACKUP
CONFIGURE CONTROLFILE AUTOBACKUP ON | OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/.........';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK '/......'; --Lo configura dentro de la sesion de rman

CONFIGURE EXCLUDE FOR TABLESPACE users; -- Excluimos el tablespace user de futuros backups.
SHOW EXCLUDE;

-- Canales

CONFIGURE DEVICE TYPE DISK PARALLELISM 2; 		--Indica cuantos canales se van a levantar de forma automatica
CONFIGURE DEVICE TYPE DISK MAXPIECESIZE = 2M; -- Tamaño maximo de las piezas del backup
CONFIGURE DEVICE TYPE DISK CLEAR; 						-- Borramos la configuracion que le hemos metido

-----------------------------------------
-- CHANGE INCARNATION
-----------------------------------------
RMAN> list incarnation;

/*
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PRUEBA   4125557522       PARENT  1          25/01/13
2       2       PRUEBA   4125557522       PARENT  1291297    15/04/13
3       3       PRUEBA   4125557522       PARENT  2466469    29/04/13
4       4       PRUEBA   4125557522       ORPHAN  3293825    17/05/13
5       5       PRUEBA   4125557522       ORPHAN  3295195    17/05/13
6       6       PRUEBA   4125557522       PARENT  3589048    20/05/13
7       7       PRUEBA   4125557522       PARENT  5389331    05/08/13
8       8       PRUEBA   4125557522       PARENT  5393169    05/08/13
9       9       PRUEBA   4125557522       CURRENT 6723797    27/03/14
*/

RMAN>  reset database to incarnation 8;

SQL> startup mount;

RMAN> reset database to incarnation 8;
/*
using target database control file instead of recovery catalog
database reset to incarnation 8
*/
RMAN> list incarnation;
/*
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PRUEBA   4125557522       PARENT  1          25/01/13
2       2       PRUEBA   4125557522       PARENT  1291297    15/04/13
3       3       PRUEBA   4125557522       PARENT  2466469    29/04/13
4       4       PRUEBA   4125557522       ORPHAN  3293825    17/05/13
5       5       PRUEBA   4125557522       ORPHAN  3295195    17/05/13
6       6       PRUEBA   4125557522       PARENT  3589048    20/05/13
7       7       PRUEBA   4125557522       PARENT  5389331    05/08/13
8       8       PRUEBA   4125557522       CURRENT 5393169    05/08/13
9       9       PRUEBA   4125557522       ORPHAN  6723797    27/03/14
*/

-----------------------------------------
-- VISTAS
-----------------------------------------
GV$PROCESS
GV$SESSION
GV$SESSION_LONGOPS
GV$SESSION_WAIT
GV$BACKUP_SYNC_IO

col filename FOR a40
col open_time FOR A20
SELECT
     a.device_type, a.TYPE, b.status, b.mbytes_processed AS "Total MB", a.filename, ROUND(a.bytes/(1024*1024)) AS "Size MB", TO_CHAR(a.open_time,'yyyy/mm/dd hh24:mi:ss') AS open_time,
     ROUND(a.elapsed_time/100) AS "Time (s)", ROUND(a.elapsed_time/100) AS "Elapsed (s)", ROUND(a.effective_bytes_per_second/(1024*1024)) AS "MB/s"
     FROM v$backup_async_io a, v$rman_status b
     WHERE a.rman_status_recid = b.recid
     ORDER BY a.use_count;
-----------------------------------------
-- EJEMPLOS
-----------------------------------------

-- EJEMPLO1 DISASTER AND RECOVERY.

-- #Se han perdido todos los ficheros de la base de datos.

# Start RMAN and connect to the target database
% rman TARGET SYS/oracle@trgt
# Set the DBID for the target database
RMAN> SET DBID 676549873;
RMAN> STARTUP FORCE NOMOUNT; # Genera que la base de datos se reinicie y coja un template del init
RUN
{
ALLOCATE CHANNEL t1 DEVICE TYPE sbt;
RESTORE SPFILE FROM AUTOBACKUP;
}
# Restart instance with restored server parameter file
RMAN> STARTUP FORCE NOMOUNT;
RMAN> RUN
{
# Manually allocate a channel to the media manager
ALLOCATE CHANNEL t1 DEVICE TYPE sbt;
# Restore autobackup of the control file. This example assumes that you have
# accepted the default format for the autobackup name.
RESTORE CONTROLFILE FROM AUTOBACKUP;
# The set until command is used in case the database structure has changed in the most recent backups, and you wish to
# recover to that point-in-time. In this way RMAN restores the database
# to the same structure that the database had at the specified time.
ALTER DATABASE MOUNT;
SET UNTIL SEQUENCE 1124 THREAD 1;
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS; # Reset the online logs after recovery completes

-- EJEMPLO 2 Cambia Ubicacion

RMAN> RUN
{
# If you need to restore the files to new locations:
SET NEWNAME FOR DATAFILE 1 TO '/dev/vgd_1_0/rlvt5_500M_1';
SET NEWNAME FOR DATAFILE 2 TO '/dev/vgd_1_0/rlvt5_500M_2';
SET NEWNAME FOR DATAFILE 3 TO '/dev/vgd_1_0/rlvt5_500M_3';

SET UNTIL SEQUENCE 124 THREAD 1;
RESTORE DATABASE;
SWITCH DATAFILE ALL; # Update control file with new location of datafiles.
RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

--------------------------------------------------
-- EJEMPLO 3 Trabajamos con rman incrmentales
--------------------------------------------------

RUN{
	ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT '/recovery_area/backup_ejemplo3_%d_%s_%p_%T.bck' MAXPIECESIZE 100M;
	BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'BCK_EJEMPLO3_LEVEL0' PLUS ARCHIVELOG;
	DELETE FORCE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-3/2'; 
	CROSSCHECK ARCHIVELOG ALL;
}

--------------------------------------------------
-- EJEMPLO 4 Script produccion
--------------------------------------------------
RUN
{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
ALLOCATE CHANNEL CH1 DEVICE TYPE DISK;
ALLOCATE CHANNEL CH2 DEVICE TYPE DISK;
ALLOCATE CHANNEL CH3 DEVICE TYPE DISK;
ALLOCATE CHANNEL CH4 DEVICE TYPE DISK;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
BACKUP AS COMPRESSED BACKUPSET TAG "BCK_INCR0_${DATE_LOG}" INCREMENTAL LEVEL 0 DATABASE INCLUDE CURRENT CONTROLFILE;
BACKUP AS COMPRESSED BACKUPSET TAG "BCK_ARC_${DATE_LOG}" ARCHIVELOG ALL NOT BACKED UP 1 TIMES DELETE INPUT;
RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
RELEASE CHANNEL CH3;
RELEASE CHANNEL CH4;
}

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: