SNAPSHOT STANDBY ORACLE 11G

SNAPSHOT STANDBY

Pruebas para poner una instancia en modo SNAPSHOTS STANDBY siguiendo la nota de metalink 11g Using Snapshot Standby Database. [ID 443720.1].

El objetivo es que se pueda abrir a escritura la STANDBY para poder hacer pruebas de contingencia de un banco y luego volver a la STANDBY sin tener que recrearla.


Para esta prueba utilizamos un motor Oracle 11g Enterprise Edition Release 11.1.0.7.0 - 64bit, montado sobre un HP-UX 11.31

Primero intente ejecutar el comando para convertir la base a SNAPSHOT STANDBY.


Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
srvprod02(oracle)@orcl1:/home/oracle>
srvprod02(oracle)@orcl1:/home/oracle>
srvprod02(oracle)@orcl1:/home/oracle>
srvprod02(oracle)@orcl1:/home/oracle> sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Aug 29 17:47:53 2011

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


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


SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------- ----------------
MOUNTED PHYSICAL STANDBY

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_08/26/2011
20:56:12'.
ORA-24081: compatible parameter needs to be 10.2.0.0.0 or greater


SQL> show parameters compati

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.1.0
plsql_v2_compatibility boolean FALSE


Al encontrar este error cambie el parámetro a 10.2.0

SQL> alter system set compatible='10.2.0' scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 864391168 bytes
Fixed Size 2142480 bytes
Variable Size 687869680 bytes
Database Buffers 167772160 bytes
Redo Buffers 6606848 bytes
SQL> alter database mount standby database;

Database altered.



Una vez cambiado el valor del parámetro vuelvo a intentar y me encuentro con el problema de la FLASH.


SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------- ----------------
MOUNTED PHYSICAL STANDBY

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_08/26/2011
21:05:05'.
ORA-38786: Flash recovery area is not enabled.

Bueno, le seteo la flash.

SQL> alter system set db_recovery_file_dest_size = 1024M scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='/bdatos01/oracle/orcl1/archive/flash_snapshot' scope=both;

System altered.


Entonces me encontré con este error porque el recover debe haber quedado trunco.

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------- ----------------
MOUNTED PHYSICAL STANDBY

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_08/26/2011
21:15:20'.
ORA-38788: More standby database recovery is needed


Al intentar el recover manual obtengo.


SQL> recover standby database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 83 is unknown - rename to correct file
ORA-01110: data file 83: '/opt/oracle11.1/pro/dbs/UNNAMED00083'
ORA-01157: cannot identify/lock data file 83 - see DBWR trace file
ORA-01111: name for data file 83 is unknown - rename to correct file
ORA-01110: data file 83: '/opt/oracle11.1/pro/dbs/UNNAMED00083'


Tuve problemas de espacio…..una vez solucionados y cuando terminé con el restore/recover seguí adelante.

Convertimos a SNAPSHOT STANDBY


Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
srvprod02(oracle)@orcl1:/home/oracle>
srvprod02(oracle)@orcl1:/home/oracle>
srvprod02(oracle)@orcl1:/home/oracle>
srvprod02(oracle)@orcl1:/home/oracle> sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Aug 29 17:47:53 2011

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


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

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------- ----------------
MOUNTED PHYSICAL STANDBY

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.


Subimos la base pero como si no fuera standby.

SQL> startup nomount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 864391168 bytes
Fixed Size 2142480 bytes
Variable Size 687869680 bytes
Database Buffers 167772160 bytes
Redo Buffers 6606848 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------- ----------------
READ WRITE SNAPSHOT STANDBY


Creamos una tabla para insertarle un dato solo pera probar que se puede escribir en la base.

SQL> create table hola(chau varchar2(50));

Table created.

SQL> insert into hola values(‘dato barato’);

1 row created.

SQL> commit;

Commit complete.

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

Montamos la base para convertirla en PHYSICAL STANDBY

SQL> startup nomount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 864391168 bytes
Fixed Size 2142480 bytes
Variable Size 687869680 bytes
Database Buffers 167772160 bytes
Redo Buffers 6606848 bytes
SQL> alter database mount;

Database altered.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

La subimos como STANDBY y la actualizamos.

SQL> startup nomount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 864391168 bytes
Fixed Size 2142480 bytes
Variable Size 687869680 bytes
Database Buffers 167772160 bytes
Redo Buffers 6606848 bytes
SQL> alter database mount standby database;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------- ----------------
MOUNTED PHYSICAL STANDBY



SQL> recover standby database until cancel;
ORA-00279: change 43883250039 generated at 08/29/2011 16:59:55 needed for
thread 1
ORA-00289: suggestion :
/bdatos01/oracle/orcl1/archive/orcl1_t1_s55758_669064782.arc
ORA-00280: change 43883250039 for thread 1 is in sequence #55758


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 43883265634 generated at 08/29/2011 17:14:55 needed for
thread 1
ORA-00289: suggestion :
/bdatos01/oracle/orcl1/archive/orcl1_t1_s55759_669064782.arc
ORA-00280: change 43883265634 for thread 1 is in sequence #55759
ORA-00278: log file
'/bdatos01/oracle/orcl1/archive/orcl1_t1_s55758_669064782.arc' no longer
needed for this recovery


ORA-00279: change 43883297159 generated at 08/29/2011 17:29:55 needed for
thread 1
ORA-00289: suggestion :
/bdatos01/oracle/orcl1/archive/orcl1_t1_s55760_669064782.arc
ORA-00280: change 43883297159 for thread 1 is in sequence #55760
ORA-00278: log file
'/bdatos01/oracle/orcl1/archive/orcl1_t1_s55759_669064782.arc' no longer
needed for this recovery


ORA-00279: change 43883326335 generated at 08/29/2011 17:44:56 needed for
thread 1
ORA-00289: suggestion :
/bdatos01/oracle/orcl1/archive/orcl1_t1_s55761_669064782.arc
ORA-00280: change 43883326335 for thread 1 is in sequence #55761
ORA-00278: log file
'/bdatos01/oracle/orcl1/archive/orcl1_t1_s55760_669064782.arc' no longer
needed for this recovery


ORA-00308: cannot open archived log
'/bdatos01/oracle/orcl1/archive/orcl1_t1_s55761_669064782.arc'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3


Siguiendo pasos de la nota de metalink :

11g Using Snapshot Standby Database. [ID 443720.1]