El blog de Nelson Javier Martinez Acosta: octubre 2015

viernes, 16 de octubre de 2015

AFTER SUSPEND

https://www.toadworld.com/platforms/oracle/w/wiki/2239.after-suspend

CREATE OR REPLACE TRIGGER after_suspend
AFTER SUSPEND
ON DATABASE
DECLARE

-- cursor to get the username for the current session
CURSOR curs_get_username IS
SELECT username
  FROM v$session
 WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID');
v_username VARCHAR2(30);

-- cursor to get the quota for the user/tablespace
CURSOR curs_get_ts_quota ( cp_tbspc VARCHAR2,
cp_user VARCHAR2 ) IS

SELECT max_bytes
  FROM dba_ts_quotas
 WHERE tablespace_name = cp_tbspc
   AND username = cp_user;
v_old_quota NUMBER;
v_new_quota NUMBER;

-- hold information from SPACE_ERROR_INFO
v_error_type     VARCHAR2(30);
v_object_type    VARCHAR2(30);
v_object_owner   VARCHAR2(30);
v_tbspc_name     VARCHAR2(30);
v_object_name    VARCHAR2(30);
v_subobject_name VARCHAR2(30);

-- SQL to fix things
v_sql            VARCHAR2(1000);

BEGIN

-- if this is a space related error...
IF ORA_SPACE_ERROR_INFO ( error_type => v_error_type,
object_type => v_object_type,
object_owner => v_object_owner,
table_space_name => v_tbspc_name,
object_name => v_object_name,
sub_object_name => v_subobject_name ) THEN

-- if the error is a tablespace quota being exceeded...
IF v_error_type = 'SPACE QUOTA EXCEEDED' AND
 v_object_type = 'TABLE SPACE' THEN
 -- get the username
 OPEN curs_get_username;
 FETCH curs_get_username INTO v_username;
 CLOSE curs_get_username;

 -- get the current quota for the username and tablespace
 OPEN curs_get_ts_quota(v_object_name,v_username);
 FETCH curs_get_ts_quota INTO v_old_quota;
 CLOSE curs_get_ts_quota;

 -- create an ALTER USER statement and send it off to
 -- the fixer job because if we try it here we will raise
 -- ORA-30511: invalid DDL operation in system triggers
 v_new_quota := v_old_quota + 40960;
 v_sql := 'ALTER USER ' || v_username || ' ' ||
'QUOTA ' || v_new_quota || ' ' ||
'ON ' || v_object_name;
 fixer.fix_this(v_sql);

END IF; -- tablespace quota exceeded

END IF; -- space related error

END;

viernes, 2 de octubre de 2015

RMAN RESTORE CASO1 Restauración de la base de datos producción en el mismo servidor.

Caso 1. Restauración de la base de datos producción en el mismo servidor.

Esta restauración asume que la base de datos fonpetp será recuperada en el mismo servidor usando un juego de copias completo con el mismo nombre de base de datos. A continuación se enumeran los pasos:

1. Definir el punto de restauración (PITR) que determinará las piezas de backup que se necesitan. En caso que sea necesario extraer las piezas de backup desde cinta deberá realizarse la respectiva actividad desde el gestor de medios.

2. Una vez definido el punto de restauración e identificadas la piezas de backup se procede a preparar la base de datos (puede que sea necesario bajar la instancia).

[oracle@bdprod ~]$ cd /u02/backups/rman/fonpetp/restaurar

[oracle@bdprod restaurar]$ . oraenv

ORACLE_SID = [fonpetp] ? fonpetp

The Oracle base has been set to /u01/app/oracle

[oracle@bdprod restaurar]$ sqlplus dba_fonpet as sysdba

SYSfonpetp> shutdown abort;

ORACLE instance shut down.

SYS@fonpetp> exit

3. Para evitar llenado de disco con la restauración de una segunda base de datos se procede con la eliminación de archivos existentes

[oracle@bdprod restaurar]$ . oraenv

ORACLE_SID = [fonpetp] ? +ASM

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@bdprod restaurar]$ asmcmd

ASMCMD> rm +DG_DATA/fonpetp/datafile/*

You may delete multiple files and/or directories.

Are you sure? (y/n) y

ASMCMD> rm +DG_DATA/fonpetp/controlfile/*

You may delete multiple files and/or directories.

Are you sure? (y/n) y

ASMCMD> ls -l DG_DATA/FONPETP/*/*

TEMP.286.816122593

group_1.277.816122561

group_2.275.816122561

group_3.271.816122563

group_4.314.818624373

group_5.315.818624381

group_6.316.818624381

group_7.317.818624381

redo01a.rdo

redo02a.rdo

redo03a.rdo

redolog1.stb

redolog3.stb

redolog4.stb

spfile.284.816123925

spfilefonpetp.ora

temp01.dbf

ASMCMD> exit

4. Se prepara la base de datos para la restauración. Se desactiva la traza de auditoria (audit trail) para evitar que más adelante la base de datos genere un error de escritura en la auditoria al abrirse en modo solo lectura.

[oracle@bdprod restaurar]$ export NLS_DATE_FORMAT="DD/MM/YYYY HH24:MI:SS"

[oracle@bdprod restaurar]$ . oraenv

ORACLE_SID = [+ASM] ? fonpetp

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@bdprod restaurar]$ sqlplus / as sysdba

Connected to an idle instance.

SYS@fonpetp> alter system set standby_file_management=manual;

System altered.

SYS@fonpetp> alter system set audit_trail=none scope=spfile;

System altered.

SYS@fonpetp> startup force nomount;

ORACLE instance started.

Total System Global Area 1.0422E+10 bytes

Fixed Size 2237448 bytes

Variable Size 5670702072 bytes

Database Buffers 4731174912 bytes

Redo Buffers 18120704 bytes

SYS@fonpetp> exit;

5. Se realiza la restauración del archivo de control. Este archivo debe corresponder a una fecha levemente posterior al punto de restauración seleccionado. Se ajusta el parámetro standby_file_management para permitir que sean conservados los nombres de archivo en la restauración. Nótese la variable de ambiente NLS_DATE_FORMAT que posteriormente facilitará especificar la fecha y hora del punto de restauración.

[oracle@bdprod ~]$ rman target /

connected to target database: FONPETP (not mounted)

RMAN> restore controlfile from '/u03/backups/rman/fonpetp/controlfile_FONPETP_bfp0kn6m_1_1_20140214.bak';

Starting restore at 16/02/2014 08:21:18

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=393 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DG_DATA/fonpetp/controlfile/control01.ctl

output file name=+DG_FRA/fonpetp/controlfile/control02.ctl

Finished restore at 16/02/2014 08:21:19

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

6. Hasta este punto la base de datos se encuentra montada, es requerido quitar piezas inválidas del inventario de backups que se encuentra en el archivo de control, este procedimiento es conocido como crosscheck.

RMAN> crosscheck backup;

Starting implicit crosscheck backup at 16/02/2014 08:22:08

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=587 device type=DISK

Crosschecked 30 objects

Finished implicit crosscheck backup at 16/02/2014 08:22:09

Starting implicit crosscheck copy at 16/02/2014 08:22:09

using channel ORA_DISK_1

Crosschecked 15 objects

Finished implicit crosscheck copy at 16/02/2014 08:22:10

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: +dg_fra/FONPETP/AUTOBACKUP/2014_02_14/s_839463387.2125.839467823

File Name: +dg_fra/FONPETP/AUTOBACKUP/2014_02_16/s_839530221.2406.839639325

File Name: +dg_fra/FONPETP/ARCHIVELOG/2014_02_15/thread_1_seq_1505.2398.839628347

File Name: +dg_fra/FONPETP/ARCHIVELOG/2014_02_15/thread_1_seq_1508.2401.839633481

...

using channel ORA_DISK_1

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=+DG_FRA/fonpetp/autobackup/2014_02_14/s_839533169.1263.839533171 RECID=350 STAMP=839533171

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u03/backups/rman/fonpetp/full_FONPETP_bdp0kn34_1_1_20140214.bak RECID=351 STAMP=839539812

crosschecked backup piece: found to be 'EXPIRED'

...

Crosschecked 30 objects

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=587 device type=DISK

validation succeeded for archived log

archived log file name=+DG_FRA/fonpetp/archivelog/2014_02_14/thread_1_seq_240.2144.839470615 RECID=1 STAMP=839665330

validation succeeded for archived log

archived log file name=+DG_FRA/fonpetp/archivelog/2014_02_14/thread_1_seq_241.2302.839484053 RECID=2 STAMP=839665330

...

archived log file name=+DG_FRA/fonpetp/archivelog/2014_02_16/thread_1_seq_1516.2128.839664035 RECID=14 STAMP=839665330

Crosschecked 121 objects

7. Normalmente las piezas de backup de los archivo de datos ya se encuentran catalogadas en el archivo de control restaurado, en caso que no se encuentren –ya sea porque se restauró un archivo de control muy posterior al punto objetivo de restauración o porque las piezas están en otra ruta distinta a donde fueron respaldadas-, es necesario catalogarlas.

RMAN> catalog start with '/u03/backups/rman/fonpetp/restaurar';

searching for all files that match the pattern /u03/backups/rman/fonpetp/

List of Files Unknown to the Database

=====================================

File Name: /u03/backups/rman/fonpetp/archivelog_FONPETP_brp0fseo_1_1_20140213.bak

File Name: /u03/backups/rman/fonpetp/incr_cdp0m51s_1_1.bak

File Name: /u03/backups/rman/fonpetp/bd_5corellu_1_1.bkp

...

File Name: /u03/backups/rman/fonpetp/archivelog_FONPETP_c0p0igns_1_1_20140214.bak

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u03/backups/rman/fonpetc/archivelog_FONPETP_brp0fseo_1_1_20140213.bak

File Name: /u03/backups/rman/fonpetc/incr_cdp0m51s_1_1.bak

File Name: /u03/backups/rman/fonpetc/bd_5corellu_1_1.bkp

...

File Name: /u03/backups/rman/fonpetc/al_5horenab_1_1.bkp

8. Una vez se encuentra restaurado el archivo de control y catalogadas las piezas requeridas se identifica el punto exacto de restauración. Para este ejemplo se busca recuperar la base de datos lo más cerca al punto de falla ocurrido el 14 de febrero de 2014 a las 09:40 p.m., que en este caso sería 14/02/2014 21:32:06 o el SCN 47297534. El archivo de control debe ser también el indicado en el punto objetivo de restauración.

RMAN> list backup of controlfile;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

347 Full 20.52M DISK 00:00:02 14/02/2014 19:39:31

BP Key: 350 Status: EXPIRED Compressed: NO Tag: TAG20140214T193929

Piece Name: +DG_FRA/fonpetp/autobackup/2014_02_14/s_839533169.1263.839533171

Control File Included: Ckp SCN: 47272578 Ckp time: 14/02/2014 19:39:29

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

350 Full 20.48M DISK 00:00:02 14/02/2014 21:32:08

BP Key: 353 Status: EXPIRED Compressed: NO Tag: TAG20140214T213205

Piece Name: /u03/backups/rman/fonpetp/controlfile_FONPETP_bfp0kn6m_1_1_20140214.bak

Control File Included: Ckp SCN: 47297534 Ckp time: 14/02/2014 21:32:06

Si se cuenta un controlfile de las 22:00 y sus respectivos archive logs puede restaurarse la base de datos por completo hasta el momento de la falla. En este caso que se ilustra, la fecha del archivo de control es de las 21:32

9. Se procede con la restauración de los archivos de datos. RMAN determina que el conjunto de copias de seguridad requerido es el más reciente hacia atrás respecto a la fecha del archivo de control. Al finalizar la restauración de los archivos de datos debe validarse que estos hayan quedado en la ubicación correcta y con tamaño mayor a cero bytes.

RMAN> restore database;

Starting restore at 07-JAN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00003 to +DG_DATA/fonpeta/datafile/undotbs101.dbf

channel ORA_DISK_1: restoring datafile 00005 to +DG_DATA/fonpeta/datafile/indic_fonpet_movim_01.dbf

channel ORA_DISK_1: restoring datafile 00008 to +DG_DATA/fonpeta/datafile/indic_fonpet_movim_reorg0.dbf

channel ORA_DISK_1: restoring datafile 00010 to +DG_DATA/fonpeta/datafile/ts_fonpet_movim_01.dbf

channel ORA_DISK_1: restoring datafile 00011 to +DG_DATA/fonpeta/datafile/indic_fonpet_movim_02.dbf

channel ORA_DISK_1: restoring datafile 00012 to +DG_DATA/fonpeta/datafile/ts_fonpet_movim_02.dbf

channel ORA_DISK_1: restoring datafile 00013 to +DG_DATA/fonpeta/datafile/ts_fonpet_rep.dbf

channel ORA_DISK_1: reading from backup piece /u02/backup/rman/fonpetp/bd_26eotff00_1_1.bak

channel ORA_DISK_1: piece handle=/u02/backup/rman/fonpetp/bd_26eotff00_1_1.bak tag=TAG20140107T115231

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:10:22

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DG_DATA/fonpeta/datafile/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to +DG_DATA/fonpeta/datafile/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00004 to +DG_DATA/fonpeta/datafile/users01.dbf

channel ORA_DISK_1: restoring datafile 00006 to +DG_DATA/fonpeta/datafile/indic_fonpet_param.dbf

channel ORA_DISK_1: restoring datafile 00007 to +DG_DATA/fonpeta/datafile/indic_fonpet_movim.dbf

channel ORA_DISK_1: restoring datafile 00009 to +DG_DATA/fonpeta/datafile/ts_fonpet_param.dbf

channel ORA_DISK_1: restoring datafile 00014 to +DG_DATA/fonpeta/datafile/ts_fonpet_movim.dbf

channel ORA_DISK_1: reading from backup piece /u02/backup/rman/fonpetp/bd_16dotff00_1_1.bak

channel ORA_DISK_1: piece handle=/u02/backup/rman/fonpetp/bd_16dotff00_1_1.bak tag=TAG20140107T115231

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:16:55

Finished restore at 07-MAR-14

RMAN> sql 'select name, bytes, status from v$datafile';

NAME BYTES STATUS

------------------------------------------------------------ ---------- -------

+DG_DATA/fonpeta/datafile/system01.dbf 1073741824 SYSTEM

+DG_DATA/fonpeta/datafile/sysaux01.dbf 1073741824 ONLINE

+DG_DATA/fonpeta/datafile/undotbs101.dbf 3191865344 ONLINE

+DG_DATA/fonpeta/datafile/users01.dbf 5242880 ONLINE

+DG_DATA/fonpeta/datafile/indic_fonpet_movim_01.dbf 3355443200 ONLINE

+DG_DATA/fonpeta/datafile/indic_fonpet_param.dbf 104857600 ONLINE

+DG_DATA/fonpeta/datafile/indic_fonpet_movim.dbf 2097152000 ONLINE

+DG_DATA/fonpeta/datafile/indic_fonpet_movim_reorg0.dbf 2621440000 ONLINE

+DG_DATA/fonpeta/datafile/ts_fonpet_param.dbf 104857600 ONLINE

+DG_DATA/fonpeta/datafile/ts_fonpet_movim_01.dbf 8388608000 ONLINE

+DG_DATA/fonpeta/datafile/indic_fonpet_movim_02.dbf 3670016000 ONLINE

+DG_DATA/fonpeta/datafile/ts_fonpet_movim_02.dbf 3145728000 ONLINE

+DG_DATA/fonpeta/datafile/ts_fonpet_rep.dbf 5242880000 ONLINE

+DG_DATA/fonpeta/datafile/ts_fonpet_movim.dbf 3.4318E+10 ONLINE

+DG_DATA/fonpeta/datafile/ts_fonpet_movim_2.dbf 5242880000 ONLINE

10. Se continúa con el recovery hasta la fecha indicada.

RMAN> run

> {

> SET UNTIL TIME '14/02/2014 21:32:06';

> recover database;

> }

Starting recover at 07/03/2014 08:27:40

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=394 device type=DISK

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: +DG_DATA/fonpetc/datafile/system01.dbf

destination for restore of datafile 00002: +DG_DATA/fonpetc/datafile/sysaux01.dbf

destination for restore of datafile 00004: +DG_DATA/fonpetc/datafile/users01.dbf

destination for restore of datafile 00006: +DG_DATA/fonpetc/datafile/indic_fonpet_param.dbf

destination for restore of datafile 00007: +DG_DATA/fonpetc/datafile/indic_fonpet_movim.dbf

destination for restore of datafile 00009: +DG_DATA/fonpetc/datafile/ts_fonpet_param.dbf

destination for restore of datafile 00014: +DG_DATA/fonpetc/datafile/ts_fonpet_movim.dbf

channel ORA_DISK_1: reading from backup piece /u03/backups/rman/fonpetc/incr_cbp0m3mt_1_1.bak

...

archived log for thread 1 with sequence 9442 is already on disk as file +DG_FRA/fonpeta/archivelog/2014_02_14/thread_1_seq_9442.1164.836223561

archived log for thread 1 with sequence 1 is already on disk as file +DG_FRA/fonpeta/archivelog/2014_02_14/thread_1_seq_1.1199.836223755

archived log for thread 1 with sequence 2 is already on disk as file +DG_FRA/fonpeta/archivelog/2014_02_14/thread_1_seq_2.693.836223755

archived log for thread 1 with sequence 3 is already on disk as file +DG_DATA/fonpeta/onlinelog/redo03a.rdo

archived log file name=+DG_FRA/fonpeta/archivelog/2014_02_14/thread_1_seq_1.1199.836223755 thread=1 sequence=1

archived log file name=+DG_FRA/fonpeta/archivelog/2014_02_14/thread_1_seq_2.693.836223755 thread=1 sequence=2

archived log file name=+DG_DATA/fonpeta/onlinelog/redo03a.rdo thread=1 sequence=3

media recovery complete, elapsed time: 00:00:03

Finished recover at 07/03/2014 09:11:12

RMAN> exit

Alternativamente puede usarse un solo bloque de comandos para restaurar y realizar recover ya sea especificando la fecha o el SCN del punto objetivo de recuperación

RMAN> run

{

restore database until scn 47297534;

alter database mount;

recover database until scn 47297534;

alter database open resetlogs;

}

RMAN> exit

11. Finalmente la base de datos puede abrirse. Es recomendable realizar primero la apertura en modo solo lectura, para luego abrirla en modo escritura definitivamente. Nótese que igualmente se restablecen los parámetros standby_file_management y audit_trail

[oracle@bdprod logs]$ sqlplus dba_fonpet as sysdba

Password:

Connected.

SYS@fonpetp> alter database open read only;

Database altered.

SYS@fonpetp> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@fonpetp> startup mount;

ORACLE instance started.

Total System Global Area 1.0422E+10 bytes

Fixed Size 2237448 bytes

Variable Size 5670702072 bytes

Database Buffers 4731174912 bytes

Redo Buffers 18120704 bytes

Database mounted.

SYS@fonpetp> alter database open resetlogs;

Database opened.

SYS@fonpetp> alter system switch logfile;

System altered.

SYS@fonpetp> alter system set standby_file_management=auto;

System altered.

SYS@fonpetp> alter system set audit_trail=db scope=spfile;

System altered.

SYS@fonpetp> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@fonpetp> startup;

ORACLE instance started.

Total System Global Area 1.0422E+10 bytes

Fixed Size 2237448 bytes

Variable Size 5670702072 bytes

Database Buffers 4731174912 bytes

Redo Buffers 18120704 bytes

Database mounted.

Database opened.

El blog de Nelson Javier Martinez Acosta

viernes, 16 de octubre de 2015

AFTER SUSPEND

viernes, 2 de octubre de 2015

RMAN RESTORE CASO1 Restauración de la base de datos producción en el mismo servidor.

Vistas de página en total

Buscar este blog

Seguidores

Datos personales

Archivo del blog

INSTRUCTOR WDP

Entradas populares

Oracle Seguridad

Etiquetas

Archivo del blog

OCP 11G

OCA11G

SQL

WEBLOGIC

OCA 10G

OCP 10G

Formulario de contacto