Backup y Restore de una instancia Oracle 11g en Linux Centos 7

Las características son las siguientes…

 

Dos servidores con Centos 7

[oracle@correo tmp]$ more /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)
[oracle@correo tmp]$

Motor Oracle 11g

[oracle@correo tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 8 11:55:02 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

No necesariamente debe ser enterprise, esto funciona de la misma forma en standard edition.   La instancia tiene ORACLE_SID=glpi2

Arranquemos….

En el servidor fuente se realiza lo siguiente: Copiar el archivo orapwd de la instancia oracle al servidor nuevo…

[oracle@glpi2 ~]$ scp   /ora01/app/oracle/product/11.2.0/db_1/dbs/orapwglpi2 oracle@192.168.10.33:/ora01/app/oracle/product/11.2.0/db_1/dbs/
oracle@192.168.10.33's password: 
orapwglpi2                                                                                         100% 1536     1.4MB/s   00:00    
[oracle@glpi2 ~]$

Conectar al sqlplus del servidor fuente y crear el archivo spfile

SQL> create pfile from spfile;

File created.

SQL>

Lo enviamos al servidor nuevo

[oracle@glpi2 ~]$ scp   /ora01/app/oracle/product/11.2.0/db_1/dbs/initglpi2.ora oracle@192.168.10.33:/ora01/app/oracle/product/11.2.0/db_1/dbs/
oracle@192.168.10.33's password: 
initglpi2.ora                                                                                    100%  976     1.0MB/s   00:00    
[oracle@glpi2 ~]$

Después de esto, nos vamos al servidor nuevo y ejecutamos lo siguiente:

  • Configuro la variable de ambiente ORACLE_SID para que tenga el mismo id que la instancia original
[oracle@correo tmp]$ export ORACLE_SID=glpi2

Luego ingreso via sqlplus

[oracle@correo tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 8 10:33:48 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>

El siguiente paso es arrancar la instancia a partir del archivo spfile que se copió en el paso previo. Ojo, aquí nos va a generar error por que no hemos creado la estructura de directorios correspondiente a esta instancia,  las rutas para crearlas las encontramos en el archivo spfile que transferimos

SQL> startup nomount pfile='/ora01/app/oracle/product/11.2.0/db_1/dbs/initglpi2.ora'; 
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
SQL> quit
Disconnected
[oracle@correo tmp]$

 

Como se puede ver, genera un error, esto es por lo que mencioné anteriormente, no se ha creado la estructura de directorios a nivel de sistema operativo. Vamos a crear la estructura por lo menos para que se puedan generar los archivo de audit y asi la instancia pueda iniciar, aquí vemos que las rutas no se han creado…

[oracle@correo tmp]$ ls -ltr $ORACLE_BASE/admin/$ORACLE_SID/adump
ls: cannot access /ora01/app/oracle/admin/glpi2/adump: No such file or directory
[oracle@correo tmp]$

Procedemos a crear las rutas correspondientes….

[oracle@correo tmp]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
[oracle@correo tmp]$ mkdir -p /ora01/app/oracle/oradata/glpi2/
[oracle@correo tmp]$ mkdir -p /ora01/app/oracle/fast_recovery_area/glpi2/

Ok, ahora ingreso por sqlplus para verificar que ya puedo acceder y así empezar de nuevo, bajo la instancia por si las moscas, uno nunca sabe que pueda ocurrir…

[oracle@correo tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 8 10:46:44 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>

Listo , bajada la instancia, como pueden ver, se pudo acceder debido a que la instancia ya tiene donde depositar los archivos audit

De esto es lo que les hablo,

[oracle@correo tmp]$ ls -ltr $ORACLE_BASE/admin/$ORACLE_SID/adump
total 32
-rw-r----- 1 oracle oinstall 1005 May  8 10:47 glpi2_ora_11380_20210508104644337199143795.aud
-rw-r----- 1 oracle oinstall  795 May  8 10:48 glpi2_ora_11454_20210508104801552627143795.aud
-rw-r----- 1 oracle oinstall  802 May  8 10:48 glpi2_ora_11454_20210508104808130072143795.aud
-rw-r----- 1 oracle oinstall 1005 May  8 10:48 glpi2_ora_11497_20210508104808146713143795.aud
-rw-r----- 1 oracle oinstall  795 May  8 10:48 glpi2_ora_11524_20210508104834991635143795.aud
-rw-r----- 1 oracle oinstall  802 May  8 10:48 glpi2_ora_11524_20210508104850236974143795.aud
-rw-r----- 1 oracle oinstall  808 May  8 10:48 glpi2_ora_11582_20210508104850253462143795.aud
-rw-r----- 1 oracle oinstall  808 May  8 10:58 glpi2_ora_12137_20210508105833304735143795.aud
[oracle@correo tmp]$ 

Retomando entonces el tema, empezamos de nuevo

 

[oracle@correo tmp]$ sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 8 10:48:01 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/ora01/app/oracle/product/11.2.0/db_1/dbs/initglpi2.ora';
ORACLE instance started.

Total System Global Area 1870647296 bytes
Fixed Size		    2254304 bytes
Variable Size		  503319072 bytes
Database Buffers	 1358954496 bytes
Redo Buffers		    6119424 bytes
SQL>

 

Luego creo el archivo pfile

SQL> create spfile from pfile='/ora01/app/oracle/product/11.2.0/db_1/dbs/initglpi2.ora';

File created.

SQL>

Arranco la instancia nuevamente para que asuma el pfile creado

SQL> startup force nomount;
ORACLE instance started.

Total System Global Area 1870647296 bytes
Fixed Size		    2254304 bytes
Variable Size		  503319072 bytes
Database Buffers	 1358954496 bytes
Redo Buffers		    6119424 bytes
SQL>

Volvemos al servidor origen y realizamos un backup del control file

 

[oracle@glpi2 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 8 11:07:46 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GLPI2 (DBID=1876010378)

RMAN> backup as copy current controlfile format '/ora01/NuevoBackGlpi'; 

Starting backup at 08-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/ora01/NuevoBackGlpi tag=TAG20210508T110858 RECID=2 STAMP=1072004940
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 08-MAY-21

Starting Control File and SPFILE Autobackup at 08-MAY-21
piece handle=/ora01/app/oracle/fast_recovery_area/GLPI2/autobackup/2021_05_08/o1_mf_s_1072004942_j9ffx0wj_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAY-21

RMAN>

Salimos de rman y verificamos la creacion del archivo

 

[oracle@glpi2 ~]$ ls -ltr /ora01/NuevoBackGlpi 
-rw-r----- 1 oracle oinstall 9748480 May  8 11:09 /ora01/NuevoBackGlpi
[oracle@glpi2 ~]$

Lo mandamos al servidor nuevo

 

[oracle@glpi2 rman]$ scp /ora01/NuevoBackGlpi oracle@192.168.10.33:/ora01
NuevoBackGlpi                                                                                  100% 9520KB 106.5MB/s   00:00    
[oracle@glpi2 rman]$

Listo, ahora restauramos el control file en la nueva instancia

[oracle@correo tmp]$ rman target / 

Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 8 11:12:41 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GLPI2 (not mounted)

RMAN> restore controlfile from '/ora01/NuevoBackGlpi'; 

Starting restore at 08-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/ora01/app/oracle/oradata/glpi2/control01.ctl
output file name=/ora01/app/oracle/fast_recovery_area/glpi2/control02.ctl
Finished restore at 08-MAY-21

RMAN> 

Listo, sin problemas hasta aquí

Procedemos a montar la instancia con el siguiente comando….

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>

Sin errores por el momento….

Nos vamos al servidor origen y realizamos el backup

[oracle@glpi2 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat May 8 11:18:48 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GLPI2 (DBID=1876010378)


RMAN> sql "alter system switch logfile";

using target database control file instead of recovery catalog
sql statement: alter system switch logfile

RMAN> sql "alter system checkpoint";

sql statement: alter system checkpoint

RMAN> sql "alter system archive log current";

sql statement: alter system archive log current

RMAN>run{
2> allocate channel c1 device type disk format '/ora01/bac/%d_%T_%s_%p';
3> backup database plus archivelog;
4> }

allocated channel: c1
channel c1: SID=140 device type=DISK


Starting backup at 08-MAY-21
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=18 RECID=12 STAMP=1072005537
input archived log thread=1 sequence=19 RECID=13 STAMP=1072005551
input archived log thread=1 sequence=20 RECID=14 STAMP=1072005714
channel c1: starting piece 1 at 08-MAY-21
channel c1: finished piece 1 at 08-MAY-21
piece handle=/ora01/bac/GLPI2_20210508_21_1 tag=TAG20210508T112155 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-MAY-21

Starting backup at 08-MAY-21
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00006 name=/ora01/app/oracle/oradata/glpi2/rh_datos.dbf
input datafile file number=00001 name=/ora01/app/oracle/oradata/glpi2/system01.dbf
input datafile file number=00002 name=/ora01/app/oracle/oradata/glpi2/sysaux01.dbf
input datafile file number=00005 name=/ora01/app/oracle/oradata/glpi2/example01.dbf
input datafile file number=00003 name=/ora01/app/oracle/oradata/glpi2/undotbs01.dbf
input datafile file number=00004 name=/ora01/app/oracle/oradata/glpi2/users01.dbf
channel c1: starting piece 1 at 08-MAY-21
channel c1: finished piece 1 at 08-MAY-21
piece handle=/ora01/bac/GLPI2_20210508_22_1 tag=TAG20210508T112156 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:15
Finished backup at 08-MAY-21

Starting backup at 08-MAY-21
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=15 STAMP=1072005733
channel c1: starting piece 1 at 08-MAY-21
channel c1: finished piece 1 at 08-MAY-21
piece handle=/ora01/bac/GLPI2_20210508_23_1 tag=TAG20210508T112213 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-MAY-21

Starting Control File and SPFILE Autobackup at 08-MAY-21
piece handle=/ora01/app/oracle/fast_recovery_area/GLPI2/autobackup/2021_05_08/o1_mf_s_1072005735_j9fgosov_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAY-21
released channel: c1

RMAN> 

Listo, tenemos nuestro backup, ahora lo que hacemos es copiarlo al servidor nuevo

 

[oracle@glpi2 ora01]$ scp -Cpr  bac/ oracle@192.168.10.33:/ora01
GLPI2_20210508_21_1                                                                                        100%   15MB  15.0MB/s   00:01    
GLPI2_20210508_22_1                                                                                        100% 1154MB  28.0MB/s   00:41    
GLPI2_20210508_23_1                                                                                        100% 8192     6.6MB/s   00:00    
[oracle@glpi2 ora01]$

Una vez transferidos, volvemos al rman y empezamos la restauración, debemos indicar el directorio donde queda el backup del cual se va a restaurar

 

RMAN> catalog start with '/ora01/bac/';

Starting implicit crosscheck backup at 08-MAY-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 08-MAY-21

Starting implicit crosscheck copy at 08-MAY-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-MAY-21

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /ora01/bac/

List of Files Unknown to the Database
=====================================
File Name: /ora01/bac/GLPI2_20210508_21_1
File Name: /ora01/bac/GLPI2_20210508_22_1
File Name: /ora01/bac/GLPI2_20210508_23_1

Do you really want to catalog the above files (enter YES or NO)? yes     <------------------- Respondemos que si a la pregunta.... 
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /ora01/bac/GLPI2_20210508_21_1
File Name: /ora01/bac/GLPI2_20210508_22_1
File Name: /ora01/bac/GLPI2_20210508_23_1

RMAN> 

Finaliza el catalogado como se pudo ver anteriomente , sin problemas…   Ahora hagamos el restore…

RMAN> restore database;

Starting restore at 08-MAY-21
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 00001 to /ora01/app/oracle/oradata/glpi2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /ora01/app/oracle/oradata/glpi2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /ora01/app/oracle/oradata/glpi2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /ora01/app/oracle/oradata/glpi2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /ora01/app/oracle/oradata/glpi2/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /ora01/app/oracle/oradata/glpi2/rh_datos.dbf
channel ORA_DISK_1: reading from backup piece /ora01/bac/GLPI2_20210508_22_1
channel ORA_DISK_1: piece handle=/ora01/bac/GLPI2_20210508_22_1 tag=TAG20210508T112156
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 08-MAY-21

RMAN>

Sin problemas….. Sigamos con el recover….

RMAN> recover database;

Starting recover at 08-MAY-21
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=18
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=19
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_DISK_1: reading from backup piece /ora01/bac/GLPI2_20210508_21_1
channel ORA_DISK_1: piece handle=/ora01/bac/GLPI2_20210508_21_1 tag=TAG20210508T112155
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/ora01/app/oracle/fast_recovery_area/GLPI2/archivelog/2021_05_08/o1_mf_1_18_j9fhn7yh_.arc thread=1 sequence=18
channel default: deleting archived log(s)
archived log file name=/ora01/app/oracle/fast_recovery_area/GLPI2/archivelog/2021_05_08/o1_mf_1_18_j9fhn7yh_.arc RECID=14 STAMP=1072006713
archived log file name=/ora01/app/oracle/fast_recovery_area/GLPI2/archivelog/2021_05_08/o1_mf_1_19_j9fhn8jy_.arc thread=1 sequence=19
channel default: deleting archived log(s)
archived log file name=/ora01/app/oracle/fast_recovery_area/GLPI2/archivelog/2021_05_08/o1_mf_1_19_j9fhn8jy_.arc RECID=13 STAMP=1072006712
archived log file name=/ora01/app/oracle/fast_recovery_area/GLPI2/archivelog/2021_05_08/o1_mf_1_20_j9fhn888_.arc thread=1 sequence=20
channel default: deleting archived log(s)
archived log file name=/ora01/app/oracle/fast_recovery_area/GLPI2/archivelog/2021_05_08/o1_mf_1_20_j9fhn888_.arc RECID=12 STAMP=1072006712
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21
channel ORA_DISK_1: reading from backup piece /ora01/bac/GLPI2_20210508_23_1
channel ORA_DISK_1: piece handle=/ora01/bac/GLPI2_20210508_23_1 tag=TAG20210508T112213
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/ora01/app/oracle/fast_recovery_area/GLPI2/archivelog/2021_05_08/o1_mf_1_21_j9fhngg6_.arc thread=1 sequence=21
channel default: deleting archived log(s)
archived log file name=/ora01/app/oracle/fast_recovery_area/GLPI2/archivelog/2021_05_08/o1_mf_1_21_j9fhngg6_.arc RECID=15 STAMP=1072006718
unable to find archived log
archived log thread=1 sequence=22
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/08/2021 11:38:42
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 22 and starting SCN of 1078330

RMAN>

En este momento sale este mensaje de error, lo solucionamos indicando al rman que recuperamos con la secuencia e hilo indicado por el.

RMAN> run{                    
2> set until sequence 22 thread 1; 
3> recover database;
4> }

executing command: SET until clause

Starting recover at 08-MAY-21
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 08-MAY-21

RMAN>

Termina el proceso de recover…

Ejecutamos la ultima sentencia……

 

RMAN> alter database open resetlogs;

database opened

RMAN>

Y listo el pollo…..

 

Aclaro, esta información la he seguido de un artículo en Inglés, he aquí su fuente https://www.linkedin.com/pulse/restoring-oracle-database-new-server-sajjad-razmi/

Meritos al autor, lo que hice fue ejecutarlo en oracle y comprobar que el sistema había restaurado a satisfacción…