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;
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
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;
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;
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;
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;
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]
En esta ubicación se debe generar un initXXX.ora con el nombre que declaremos en el listener, para no complicar las cosas es conveniente poner el mismo nomre tanto en el listener como en el tnsnames.
[iSeries Access ODBC Driver] Description = iSeries Access for Linux ODBC Driver Driver = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so Driver64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so Setup = /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so Setup64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so UsageCount = 1 CPTimeout = CPReuse = System = 192.181.13.2 User = username Password = password NOTE1 = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's, NOTE2 = the following Driver64/Setup64 keywords will provide that support. Threading = 2 DontDLClose = 1
luego de hacer todo esto me encontre con un error porque esta apuntando al driver en vez de apuntar al ODBC en el archivo de confuguración en el ORACLE_HOME/hs/admin
SQL> / *select * from display@db2database* *** ERROR en línea 1: ORA-28500: la conexión de ORACLE a un sistema no Oracle ha devuelto este mensaje: [Generic Connectivity Using ODBC][C077] Could not find symbol 'SQLAllocConnect' in dynamic library [C079] Failed to load dynamic library '/opt/ibm/iSeriesAccess/lib/libcwbodbc.so' ORA-02063: 3 lines precediendo a DB2DATABASE
Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.
Restoring Control Files
In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;
In an ideal world you'll never use RMAN to restore a control file. But if something catastrophic happens, and you lose all control files, here are the steps for getting them back:
The following examples assume that you are using a catalog. First, here's the simplest Oracle9i syntax for restoring a control file:
Set the database id (DBID) with the following command.This is a 10-digit number that RMAN uses to uniquely identify this database in the recovery log.The number can be obtained from any previous RMAN backup log file.
RMAN> set dbid = xxxxxxxxxx;
RMAN> restore controlfile;
SQL> alter database mount; SQL> alter database open;
If this fails with ...
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
... then you must perform a recover database:
SQL> shutdown abort; SQL> startup mount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> reset database;
Note, that all offline archivelogs are now useless, perform a full back as soon as possible.
Restoring Online Redologs
In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;
Set the database id (DBID) with the following command.This is a 10-digit number that RMAN uses to uniquely identify this database in the recovery log.The number can be obtained from any previous RMAN backup log file.
RMAN-03002: failure of restore command at 07/11/2005 14:25:22
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-06023: no backup or copy of datafile 9 found to restore
RMAN-06023: no backup or copy of datafile 8 found to restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
…use the following directions to recover (recreate the controlfile):
With the database mounted, execute ‘alter database backup controlfile to trace resetlogs;’
Perform a shutdown abort on the database, but remain at the SQL> prompt.
In another telnet session, go to the udump directory to retrieve the resulting trace file and copy it to another location to edit it.
Edit the file and add the phrase “until cancel” to the recover database command at the end.The phrase should read “recover database until cancel using backup controlfile”.Remove the “alter database open” command after the recover command.Save the file with a .sql extension.
Back at the SQL> prompt, execute the modified trace file.When prompted for an archived log, type in “cancel” and the reply should be “media recovery cancelled”.
Issue “alter database open resetlogs”.The database should open after a few moments.
Connect to the RMAN recovery catalog and issue the “reset database” command.
Time-Based, Change-Based, or SCN-based Incomplete Recovery
Incomplete recovery uses a backup to produce a non-current version of the database. In other words, you
do not apply all of the redo records generated after the most recent backup.
You usually perform incomplete recovery of the whole database in the following situations:
Media failure destroys some or all of the online redo logs.
A user error causes data loss, for example, a user inadvertently drops a table.
You cannot perform complete recovery because an archived redo log is missing.
You lose your current control file and must use a backup control file to open the database.
To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database; in other words, a database with a new stream of log sequence numbers starting with log sequence 1.
NOTE – Start every RMAN incomplete recovery with the following commands:
RMAN> restore database until time "to_date('07/12/05 12:0:00','MM/DD/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('07/12/05 12:0:00','MM/DD/YY HH24:MI:SS')";
media recovery complete. SQL> alter database open resetlogs;
--For SCN-based recovery, user these commands:
RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000;
media recovery complete. SQL> alter database open resetlogs;
--For change-based recovery, user these commands:
RMAN> restore database until sequence 9923;--Archived log sequence number
RMAN> recover database until sequence 9923;--Archived log sequence number
media recovery complete. SQL> alter database open resetlogs;
Once the recovery has been completed, execute the following steps:
Delete prior backups with this command (from the RMAN prompt):
RMAN> delete force backup;
This command removes all prior backups from the RMAN catalog as they can no longer be used once the database has been restarted with the resletlogs option.After completing this command, create a new RMAN backup to establish a new baseline.
Recovering Archived Logs only
In the event that you want to recover the database archived redo logs until a desired time, you can use the following commands:
$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
or
RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')
#!/bin/ksh ################################################################################ # AUTHOR : MOGE ALEJANDRO # # CREATED: 13/04/2009 # # PROGRAM: checkarchives.sh # # PURPOSE: Ejecuta un bkp de archive logs ante # # determionado procentaje del filesystem # # Database should be online. # # USAGE : checkarchive.sh SID UMBRAL # # PARAMETROS: # # SID: Nombre de la instancia de base de datos # # UMBRAL: Porcentaje máximo al cual puede llegar un filsystem. # ################################################################################
