Cómo clonar una DB en Oracle 11g en el mismo servidor.

Para los que quieran realizar una clonación de una base de datos Oracle, en el mismo servidor en que se encuentra la original, es necesario seguir estos pasos.

En ambas bases de datos se debe apagar la instancia

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Con esto garantizamos la no alteración de datos en ambas instancias . (uno nunca sabe que pueda pasar…)

En la base de datos de producción (QUERYX).

  1. Ubicar los datafiles, control files y demás archivos requeridos.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/queryx/system01.dbf
/u01/app/oracle/oradata/queryx/sysaux01.dbf
/u01/app/oracle/oradata/queryx/undotbs01.dbf
/u01/app/oracle/oradata/queryx/users01.dbf
/u01/oradata/queryx/datos01.dbf
/u01/oradata/queryx/indices01.dbf

6 rows selected.

SQL>
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/queryx/redo03.log
/u01/app/oracle/oradata/queryx/redo02.log
/u01/app/oracle/oradata/queryx/redo01.log

SQL>
SQL> show parameter control_files

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_files			     string	 /u01/app/oracle/oradata/queryx
                                                 /control01.ctl, /u01/fast_reco
                                                 very_area/queryx/control02.ctl
SQL>

En la base de datos a Clonar. (Q7DEV)

La información anterior nos permite entender lo que debemos copiar para la otra instancia de base de datos.

En mi caso, he creado una base de datos llamada q7dev, lo que he hecho después de instalarla es sobrescribir los datafiles originales que se crearon en el momento de la creación, y reemplazarlos con los datafiles de la base de datos original.

[oracle@heimdal: queryx [queryx]   14:23:50 $ cp /u01/app/oracle/oradata/queryx/temp01.dbf /u01/app/oracle/oradata/q7dev/
[oracle@heimdal: queryx [queryx]   14:33:20 $ cp /u01/oradata/queryx/temp01.dbf /u01/app/oracle/oradata/q7dev/temp02.dbf
[oracle@heimdal: queryx [queryx]   14:34:24 $ cp /u01/app/oracle/oradata/queryx/redo0* /u01/app/oracle/oradata/q7dev/
[oracle@heimdal: queryx [queryx]   14:35:17 $ cp /u01/app/oracle/oradata/queryx/system01.dbf /u01/app/oracle/oradata/q7dev/
[oracle@heimdal: queryx [queryx]   14:36:07 $ cp /u01/app/oracle/oradata/queryx/undotbs01.dbf /u01/app/oracle/oradata/q7dev/
[oracle@heimdal: queryx [queryx]   14:37:11 $ cp /u01/app/oracle/oradata/queryx/users01.dbf /u01/app/oracle/oradata/q7dev/
[oracle@heimdal: queryx [queryx]   14:38:08 $ cp /u01/app/oracle/oradata/queryx/sysaux01.dbf /u01/app/oracle/oradata/q7dev/
[oracle@heimdal: queryx [queryx]   14:38:49 $ cp /u01/app/oracle/oradata/queryx/control01.ctl /u01/app/oracle/oradata/q7dev/
[oracle@heimdal: queryx [queryx]   14:43:27 $ cp /u01/oradata/queryx/indices01.dbf /u01/app/oracle/oradata/q7dev/
[oracle@heimdal: queryx [queryx]   14:45:34 $ cp /u01/oradata/queryx/datos01.dbf  /u01/app/oracle/oradata/q7dev/
dev/cle@heimdal: queryx [queryx]   14:48:03 $ cp /u01/fast_recovery_area/queryx/control02.ctl /u01/app/oracle/fast_recovery_area/q7dev/

Lo que se hizo anteriormente es copiar cada datafile de producción a la ubicación de los datafiles del clón.

Se esperaría que sin mas cosas, uno pudiera iniciar la instancia Q7DEV, si tratas de realizar este paso, entonces vas a encontrarte este mensaje de error

SQL> startup
ORACLE instance started.

Total System Global Area 1.8840E+10 bytes
Fixed Size		    2261128 bytes
Variable Size		 1.0872E+10 bytes
Database Buffers	 7918845952 bytes
Redo Buffers		   47456256 bytes
ORA-01103: el nombre de la base de datos 'QUERYX' en el archivo de control no es 'Q7DEV'

Esto ocurre por que no hemos editato el control file que usa la instancia, lo que el sistema intenta hacer es usar los parámetros definidos en la instancia original.

Para ello entonces creamos un nuevo archivo controlfile en formato sql para que cambie el nombre de la instancia y use los datafiles de la ubicación correspondiente a Q7DEV

Ejecutamos la siguiente instrucción en la base de datos de producción.

SQL> alter database backup controlfile to trace;

Database altered.

SQL>

Esto genera un archivo en el directorio trace de la instancia de producción.

 

[oracle@heimdal: queryx [/u01/app/oracle/diag/rdbms/queryx/queryx/trace]   16:10:03 $ ls -ltr queryx_ora_16228.trc
-rw-r-----. 1 oracle oinstall 10444 nov 28 15:06 queryx_ora_16228.trc

Revisemos el contenido (lo pego tal cual para que sepamos que debemos copiar en un script sql que luego debemos usar)

[oracle@heimdal: queryx [~/Instalacion_servidor_BD/Install-BD-Q7]   17:22:46 $ cat /tmp/queryx_ora_16228.trc 
Trace file /u01/app/oracle/diag/rdbms/queryx/queryx/trace/queryx_ora_16228.trc
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:	Linux
Node name:	heimdal
Release:	4.1.12-124.15.4.el6uek.x86_64
Version:	#2 SMP Wed Jun 6 07:43:42 PDT 2018
Machine:	x86_64
Instance name: queryx
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 16228, image: oracle@heimdal (TNS V1-V3)


*** 2018-11-28 15:06:18.185
*** SESSION ID:(237.3) 2018-11-28 15:06:18.185
*** CLIENT ID:() 2018-11-28 15:06:18.185
*** SERVICE NAME:(SYS$USERS) 2018-11-28 15:06:18.185
*** MODULE NAME:(sqlplus@heimdal (TNS V1-V3)) 2018-11-28 15:06:18.185
*** ACTION NAME:() 2018-11-28 15:06:18.185
 
kwqmnich: current time:: 20:  6: 17: 0
kwqmnich: instance no 0 repartition flag 1 
kwqmnich: initialized job cache structure 

*** 2018-11-28 15:06:35.263
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="queryx"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "QUERYX" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4672
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/queryx/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/queryx/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/queryx/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/queryx/system01.dbf',
  '/u01/app/oracle/oradata/queryx/sysaux01.dbf',
  '/u01/app/oracle/oradata/queryx/undotbs01.dbf',
  '/u01/app/oracle/oradata/queryx/users01.dbf',
  '/u01/oradata/queryx/datos01.dbf',
  '/u01/oradata/queryx/indices01.dbf'
CHARACTER SET WE8ISO8859P15
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/u01/backup/queryx/rman/full_%u_%s_%p''');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/fast_recovery_area/QUERYX/archivelog/2018_11_28/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/fast_recovery_area/QUERYX/archivelog/2018_11_28/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/queryx/temp01.dbf'
     SIZE 80740352  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE TS_RH_TEMPORAL ADD TEMPFILE '/u01/oradata/queryx/temp01.dbf'
     SIZE 1500M REUSE AUTOEXTEND ON NEXT 104857600  MAXSIZE 32767M;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "QUERYX" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4672
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/queryx/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/queryx/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/queryx/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/queryx/system01.dbf',
  '/u01/app/oracle/oradata/queryx/sysaux01.dbf',
  '/u01/app/oracle/oradata/queryx/undotbs01.dbf',
  '/u01/app/oracle/oradata/queryx/users01.dbf',
  '/u01/oradata/queryx/datos01.dbf',
  '/u01/oradata/queryx/indices01.dbf'
CHARACTER SET WE8ISO8859P15
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/u01/backup/queryx/rman/full_%u_%s_%p''');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/fast_recovery_area/QUERYX/archivelog/2018_11_28/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/fast_recovery_area/QUERYX/archivelog/2018_11_28/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/queryx/temp01.dbf'
     SIZE 80740352  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE TS_RH_TEMPORAL ADD TEMPFILE '/u01/oradata/queryx/temp01.dbf'
     SIZE 1500M REUSE AUTOEXTEND ON NEXT 104857600  MAXSIZE 32767M;
-- End of tempfile additions.
--

*** 2018-11-28 15:06:51.277
Stopping background process QMNC

*** 2018-11-28 15:06:52.276
Stopping background process MMNL

*** 2018-11-28 15:06:53.277
Stopping background process MMON

*** 2018-11-28 15:06:54.332
Process OS id : 16388 alive after kill
----------------------------------------
SO: 0xad8b464b8, type: 2, owner: (nil), flag: -/-/-/0x00 if: 0x3 c: 0x3
 proc=0xad8b464b8, name=process, file=ksu.h LINE:12721, pg=0
(process) Oracle pid:49, ser:1, calls cur/top: 0xaaba6b790/(nil)
          flags : (0x0) -
          flags2: (0x0),  flags3: (0x0) 
          intr error: 1089, call error: 0, sess error: 0, txn error 1089
          intr queue: 1089 
  ksudlp FALSE at location: 0
  (post info) last post received: 1089 72 12
              last post received-location: ksu.h LINE:14038 ID:ksusig
              last process to post me: 0xad8b369f0 2 0
              last post sent: 0 0 354
              last post sent-location: kkj.h LINE:935 ID:jslve_execute
              last process posted by me: 0xac0ac8360 2 0
  (latch info) wait_event=0 bits=0x0
  O/S info: user: oracle, term: UNKNOWN, ospid: 16388 (DEAD)
  OSD pid info: Unix process pid: 16388, image: oracle@heimdal (J004)
Short stack dump: ORA-00072: process "Unix process pid: 16388, image: oracle@heimdal (J004)" is not active
PSO child state object changes :
Dump of memory from 0x0000000AD8B16DF8 to 0x0000000AD8B17000
AD8B16DF0                   00000000 00000000          [........]
AD8B16E00 00000000 00000000 00000000 00000000  [................]
  Repeat 31 times
ksukia: Starting kill, flags = 1
ksukia: Attempt 1 to kill process oracle@heimdal, OS id=16356
ksukia: Attempt 1 to kill process oracle@heimdal, OS id=16358
ksukia: Attempt 1 to kill process oracle@heimdal, OS id=16351
ksukia: Attempt 1 to kill process oracle@heimdal, OS id=16349
ksukia: Attempt 1 to kill process oracle@heimdal, OS id=16346
ksukia: Attempt 1 to kill process oracle@heimdal, OS id=16341
ksukia: Attempt 1 to kill process oracle@heimdal, OS id=16283
ksukia: Attempt 1 to kill process oracle@heimdal, OS id=16344
ksukia: Attempt 1 to kill process oracle@heimdal, OS id=16278
ksukia: killed 9 out of 9 processes.

*** 2018-11-28 15:06:55.352
ksukia: Starting kill, flags = 1
ksukia: killed 0 out of 0 processes.

*** 2018-11-28 15:06:58.965
*** 2018-11-28 15:06:58.965 4329 krsh.c
ARCH: Archival disabled due to shutdown: 1089

*** 2018-11-28 15:06:59.966
*** 2018-11-28 15:06:59.966 4329 krsh.c
ARCH: Archival disabled due to shutdown: 1089

Este archivo, repito, es un archivo del ambiente de producción, vamos a usar los parámetros siguientes y debemos modificarlos para que coincidan con la configuración del clon (Q7DEV)

Esta es la información que requerimos….

CREATE CONTROLFILE REUSE DATABASE "QUERYX" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4672
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/queryx/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/queryx/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/queryx/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/queryx/system01.dbf',
  '/u01/app/oracle/oradata/queryx/sysaux01.dbf',
  '/u01/app/oracle/oradata/queryx/undotbs01.dbf',
  '/u01/app/oracle/oradata/queryx/users01.dbf',
  '/u01/oradata/queryx/datos01.dbf',
  '/u01/oradata/queryx/indices01.dbf'
CHARACTER SET WE8ISO8859P15
;

Y así la debemos modificar

CREATE CONTROLFILE SET DATABASE "q7dev" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4672
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/q7dev/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/q7dev/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/q7dev/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/q7dev/system01.dbf',
  '/u01/app/oracle/oradata/q7dev/sysaux01.dbf',
  '/u01/app/oracle/oradata/q7dev/undotbs01.dbf',
  '/u01/app/oracle/oradata/q7dev/users01.dbf',
  '/u01/app/oracle/oradata/q7dev/datos01.dbf',
  '/u01/app/oracle/oradata/q7dev/indices01.dbf'
CHARACTER SET WE8ISO8859P15
;

La siguiente información la tomé del url: https://rupeshanantghubade.blogspot.com/2018/02/how-to-clone-oracle-11g-r2-database-on.html

Es la explicación de para que se usa cada parámetro…

SET/REUSE DATABASE:

SET DATABASE: It is used to change the database name which can be maximum up to eight bytes. OR

REUSE DATABASE: It is used to indicate that existing control files identified by the CONTROL_FILES parameter can be reused and it overwrites any information they may currently contain. If you omit this clause and any of these control files already exists, then Oracle Database returns an error.

RESETLOGS/NORESETLOGS:

RESETLOGS: Use this option if you want Oracle Database to ignore the contents of the redo log files listed in the LOGFILE clause. These files do not have to exist. After using this clause, you must open the database using the RESETLOGS clause. It is used for creating new database.

NORESETLOGS: Use this option if you want Oracle Database to use all redo log files in the LOGFILE clause as they were when the database was last open. These files must exist and must be the current online redo log files rather than restored backups. The database reassigns the redo log file groups to the threads to which they were previously assigned and re-enables the threads as they were previously enabled.

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

Sigamos entonces con lo que nos atañe….

Una vez editado el contenido, debemos ejecutarlo vía sqlplus en el ambiente clón Q7DEV.

Si te aparecen errores como los siguientes

SQL> @ recreadb.sql
CREATE CONTROLFILE SET DATABASE "q7dev" RESETLOGS  ARCHIVELOG
*
ERROR en linea 1:
ORA-01503: CREATE CONTROLFILE ha fallado
ORA-00200: no se ha podido crear el archivo de control
ORA-00202: archivo de control: '/u01/app/oracle/oradata/q7dev/control01.ctl'
ORA-27038: el archivo creado ya existe
Additional information: 1


SQL>

Nos toca mover o renombrar los archivos de control

[oracle@heimdal: q7dev [~]   16:52:01 $ mv /u01/app/oracle/oradata/q7dev/control01.ctl /u01/app/oracle/oradata/q7dev/Original_control01.ctl

y el otro control file

[oracle@heimdal: q7dev [~] 16:52:08 $ mv /u01/app/oracle/fast_recovery_area/q7dev/control02.ctl  /u01/app/oracle/fast_recovery_area/q7dev/Origi_control02.ctl

Ejecutamos el archivo sql, en mi caso lo he nombrado recreadb.sql

SQL> @ recreadb.sql

Archivo de control creado.

SQL>

Revisamos que todo quede en orden…

SQL> set lines 300 pages 3000
col name for a50
col member for a50
select name from v$datafile;SQL> SQL> SQL> 

NAME
--------------------------------------------------
/u01/app/oracle/oradata/q7dev/system01.dbf
/u01/app/oracle/oradata/q7dev/sysaux01.dbf
/u01/app/oracle/oradata/q7dev/undotbs01.dbf
/u01/app/oracle/oradata/q7dev/users01.dbf
/u01/app/oracle/oradata/q7dev/datos01.dbf
/u01/app/oracle/oradata/q7dev/indices01.dbf

6 filas seleccionadas.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/q7dev/redo03.log
/u01/app/oracle/oradata/q7dev/redo02.log
/u01/app/oracle/oradata/q7dev/redo01.log

SQL>

 

Si llegase a generarse error en el momento de correr el script que recrea la base de datos, así como en este caso…

SQL> @ Queryx_recreadb.sql
CREATE CONTROLFILE REUSE DATABASE "QUERYX" NORESETLOGS	ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name Q7DEV in file header does not match given name of
QUERYX
ORA-01110: data file 1: '/u01/app/oracle/oradata/queryx/system01.dbf'


SQL> quit

Cambiamos el script de recreación así…

 

CREATE CONTROLFILE SET DATABASE "QUERYX" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4674
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/queryx/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/queryx/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/queryx/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/queryx/system01.dbf',
'/u01/app/oracle/oradata/queryx/sysaux01.dbf',
'/u01/app/oracle/oradata/queryx/undotbs01.dbf',
'/u01/app/oracle/oradata/queryx/users01.dbf',
'/u01/oradata/queryx/datos01.dbf',
'/u01/oradata/queryx/indices01.dbf'
CHARACTER SET WE8ISO8859P15
;

Cambiamos REUSE por SET.

Cambiamos NORESETLOGS por RESETLOGS

Esto entonces ya nos generaría el proceso sin errores

SQL> @ Queryx_recreadb.sql

Control file created.

SQL>

Con esto se crea el controlfile y procedemos a subir la instancia. En el caso en que se genere errores, como este…

SQL> ALTER DATABASE OPEN ;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL>

Ejecutamos la instrucción siguiente…

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL>

 

 

Hasta el momento , todo bien, todo en orden….se tomaron los directorios de la instancia que debe ser.

Listo, esto nos ajusta los parámetros y ya tenemos la instancia lista para usar, pero ojo, nos falta crear los tablespaces temporales, si ejecutamos la siguiente sententia SQL en Q7DEV…

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/q7dev/temp01.dbf' size 78M reuse; 

Tablespace modificado.

SQL> ALTER TABLESPACE TS_RH_TEMPORAL ADD TEMPFILE '/u01/app/oracle/oradata/q7dev/temp02.dbf' size 1500M reuse;

Tablespace modificado.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/q7dev/temp01.dbf
/u01/app/oracle/oradata/q7dev/temp02.dbf

SQL> 

 

Verificamos nuestra base de datos clonada

SQL> select name,open_mode from v$database;

NAME			     OPEN_MODE
---------------------------- --------------------
Q7DEV			     MOUNTED

SQL>

Listo, esto es todo…..

 

 

 

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *