Gustavo Rene Antunez
DBA, Movie Fanatic, Music Lover and Bringing the best from México (Mexihtli) to the rest of the world and in the process photographing it :)
Lee mi Blog de Oracle en Español- Read my blog about Oracle in English
- My Blog and events at Pythian
- Connect Professionally with me
- Follow my Opinions
- Look at my Photos
- A Bit of My Life
-
Posts
- May 18, 13
Este tema ha sido escrito por otros administradores de bases de datos, pero quería que estuviera disponible en español, y sobre todo porque en los sitios que he buscado de cómo se hizo esto, o bien no son muy claros los pasos que se hicieron y asume que ya sabes lo que estás haciendo, o en realidad ejecutan los pasos demasiado rápido sin dar alguna explicación.
Antes de continuar, sólo te quiero decir de que se trata de una entrada larga, y no digas que no te lo advertí :)
Así que para continuar y antes de entrar en cualquiera de los pasos en la creación de tu base de datos Standby Fisica, hay algunos términos que necesitas saber de antemano, que te ayudará a entender mejor el entorno y lo que DataGuard está haciendo, en lugar de copiar una serie de pasos. Estas son sólo las definiciones en la documentación de Oracle, pero te evitarás de ir a buscar por todas partes para entenderlas.
LOG_ARCHIVE_DEST_n .- Controla diferente aspectos de como los servicios de transporte de los Redo, transfiere los datos de redo de la base de datos primaria
a su destino de standby. Este parámetro tiene varios atributos que son necesarios para configurar tu ambiente de Dataguard, aquí nada mas voy a mencionar
los que son críticos para esto:- ASYNC (Default) .- Los datos generados de redo por transacción no tienen que haber sido recibidos en cada uno de los destinos habilitados antes de cometerse ésta.
o - SYNC .-Los datos generados de redo por transacción tienen que haber sido recibidos en cada uno de los destinos habilitados antes de cometerse ésta.
- AFFIRM y NOAFFIRM .- Controla si un destino de transporte de redo acusa de recibo los datos de redo antes o despues de escribir estos al standby redo log.
- DB_UNIQUE_NAME .- Especifica un nombre unico para la base de datos que va a recibir los datos de redo. Tienes que especificar este nombre, no hay un valor por default.
- VALID_FOR .- Identifica cuando el servicio de transporte de redo puede transmitir datos de redo a los destinos, esto se basa en los siguiente factores:
- redo_log_type .-Si los archivos de Online Redo Log, Standby Redo log o ambos este siendo archivados en la base de datos destinada.
- database_role .-Si el rol de la base de datos es la Primaria o la base de datos en Stanby.
FAL_SERVER .- Especifica el servidor FAL (Fetch Archive Log) para un base de datos en Standby. Este valor es un nombre de servicio de Oracle Net.FAL_CLIENT .- Especifica el nombre del cliente FAL (Fetch Archive Log) que es usado por el servicio FAL, que este es configurado a través del parametro FAL_SERVER para referir al cliente.Este valor es un nombre de servicio de Oracle Net.LOG_ARCHIVE_CONFIG .- Este parámetro habilita o deshabilita el envió de redo los a destinos remotos y el recipiente de estos. Este parámetro tiene varios atributos, aquí te menciono el mas importante para este ejercicio:- DG_CONFIG .- Especifica hasta 30 nombres de bases de datos únicas (Definidas con el parámetro DB_UNIQUE_NAME) para todas las bases de datos en tu configuración de Data Guard.
Ufff , esto estuvo largo, y si te interesa saber mas, lo puedes encontrar en la documentación oficial de Oracle 11gR2 . Pero ahora que quitamos las definiciones de encima, vamos a empezar con la configuracion de nuestra Base de Datos Standby Fisica.Para este ejercicio tengo lo siguiente :Primary : testgg1 Server : dlabvm13Standby : testgg2 Server : dlabvm14DB_NAME: testgg1Lo primero que tienes que hacer, es encontrar la ubicación en donde tienes los Online Redo Logs, Datafiles que residen en la base de datos primaria y donde van a residir estos en la base de datos Standby para que puedas establecer los parámetros LOG_FILE_NAME_CONVERT y DB_FILE_NAME_CONVERT correctamente. Asegúrate de que estos directorios y que tienes el espacio necesario para contener la base de datos primaria, si no tienes este espacio en el servidor de la Standby, entonces ¿cuál es el caso de continuar? :)A continuación, asegúrate de que estás en modo archivelog y que force logging está activado en tu base de datos primaria.Si tu base de datos no está en modo archivelog, haz lo siguiente:SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 163580104 bytes
Database Buffers 364904448 bytes
Redo Buffers 3747840 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG YESAhora que estamos en modo archivelog y establecida force logging para la base de datos primaria, asegúrate de que las entradas del Listener/Tns están configurados correctamente y que puedes hacer un tnsping ambos desde el servidor de la Primaria / Standby.En esto si no voy a entrar a detalle pero puedes checar la entrada de Kamran en el blog de Pythian (Paso 2) para ver como se hace esto. Si tienes una pregunta de como se hace esto, no dudes en hacermela.Pero el resultado en ambos servidores debe de ser algo similar a esto:oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ tnsping testgg1
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-MAY-2013 09:44:34
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dlabvm13.dlab.pythian.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testgg1)))
OK (0 msec)
oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ tnsping testgg2
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-MAY-2013 09:44:40
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dlabvm14.dlab.pythian.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testgg2)))
OK (10 msec)A continuación, debes crear y replicar el archivo de contraseñas de la base de datos primaria que se encuentra en $ORACLE_HOME/dbs y cambiarle el nombre para que refleje el nombre de la Standby. El nombre del archivo de la contraseña de la Standby debe coincidir con el ORACLE_SID utilizado, no el DB_NAME.oracle@dlabvm13.dlab.pythian.com [testgg1] /u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle $ orapwd file=orapwtestgg1 password=test entries=5
oracle@dlabvm13.dlab.pythian.com [testgg1] /u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle $ scp orapwtestgg1 dlabvm14:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestgg2
orapwtestgg1 100% 2048 2.0KB/s 00:00Ahora que has creado el archivo de contraseñas, vamos a configurar el archivo de parámetros init para la base de datos Primaria, como se puede ver al final de este ejemplo, son los parámetros que explicamos y definimos al principio de esta entrada.SQL> !cat dg.ora
*.audit_file_dest='/u01/app/oracle/oradata/dump/testgg1/adump'
*.audit_trail='db'
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/testgg1/control/control01.ctl','/u01/app/oracle/oradata/testgg1/control/control02.ctl'
*.db_block_size=8192
*.db_cache_size=128M
*.db_domain=''
*.db_name='testgg1'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=2G
*.db_unique_name='testgg1'
*.diagnostic_dest='/u01/app/oracle/oradata/dump/testgg1'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testgg1'
*.log_archive_dest_2='SERVICE=testgg2 ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testgg2'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.open_cursors=300
*.pga_aggregate_target=128M
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=512M
testgg11.UNDO_TABLESPACE='UNDOTBS1'
FAL_SERVER=testgg2;
FAL_CLIENT=testgg1;
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/','/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/','/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(testgg1,testgg2)'
Una de las cosas mas padres o cheveres acerca de este método, es que casi todo el trabajo que haré será en el servidor de base de datos Primaria, lo único que tengo que hacer en el servidor de la base de datos Standby es crear las ubicaciones en donde van a residir mis archivos de diagnóstico / redo / datafiles / control files y verificar la conectividad entre el servidor Primario y el Standby y solamente arrancar la instancia de nuestra base de datos Standby, que es nuestro siguiente paso.Lo que voy a hacer ahora es establecer el ORACLE_SID, ORACLE_HOME y ORACLE_BASE para la instancia Standby y abrirla con las mínimas opciones posibles:oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ export ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"
oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ export ORACLE_BASE=/u01/app/oracle
oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ export ORACLE_SID=testgg2
oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ cat dg.ora
DB_NAME=testgg1
DB_UNIQUE_NAME=testgg2
DB_BLOCK_SIZE=8192
oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 14 09:53:30 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/bin/dg.ora';
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Ahora vamos a volver al servidor de la base de datos Primaria, donde lo siguiente que voy a hacer es agregar los Standby Redo Logs, en mi caso he creado dos con el mismo tamaño que los Online Redo Logs que tengo en la Primaria.SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg1 dlabvm13.dlab.pythian.com
SQL> select name from v$database;
NAME
---------
TESTGG1
SQL> select GROUP#,THREAD#,MEMBERS,BYTES FROM V$LOG;
GROUP# THREAD# MEMBERS BYTES
---------- ---------- ---------- ----------
1 1 1 104857600
2 1 1 104857600
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 104857600;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 104857600;
Database altered.
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
GROUP# BYTES
---------- ----------
3 104857600
4 104857600
Una vez que he creado los Standby Redo Logs en mi base de datos Primaria, creo los siguientes comandos de RMAN y también los cambios que se van a hacer en spfile de la Standby en este bloque de RMAN.oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ cat duplicate.rmn
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'testgg1','testgg2'
set db_unique_name='testgg2'
set db_file_name_convert='/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/','/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'
set log_file_name_convert='/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/','/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'
set control_files='/u01/app/oracle/oradata/testgg2/control/control01.ctl','/u01/app/oracle/oradata/testgg2/control/control02.ctl'
set log_archive_max_processes='10'
set fal_client='testgg2'
set fal_server='testgg1'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(testgg1,testgg2)'
set log_archive_dest_2='service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'
;
}Ahora que he creado el archivo de comandos de RMAN, sólo necesito ejecutar este comando en la base de datos target, que es la base de datos Primaria, que se conectara a la Standby que en este caso es la auxiliary.oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ rman target sys/test@testgg1 auxiliary sys/test@testgg2
Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 16 08:42:46 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTGG1 (DBID=839852638)
connected to auxiliary database: TESTGG1 (not mounted)
RMAN> @duplicate.rmn
RMAN>
RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate auxiliary channel stby type disk;
7> duplicate target database for standby from active database
8> spfile
9> parameter_value_convert 'testgg1','testgg2'
10> set db_unique_name='testgg2'
11> set db_file_name_convert='/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/','/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'
12> set log_file_name_convert='/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/','/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'
13> set control_files='/u01/app/oracle/oradata/testgg2/control/control01.ctl','/u01/app/oracle/oradata/testgg2/control/control02.ctl'
14> set log_archive_max_processes='10'
15> set fal_client='testgg2'
16> set fal_server='testgg1'
17> set standby_file_management='AUTO'
18> set log_archive_config='dg_config=(testgg1,testgg2)'
19> set log_archive_dest_2='service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'
20> ;
21> }
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=32 device type=DISK
allocated channel: prmy2
channel prmy2: SID=33 device type=DISK
allocated channel: prmy3
channel prmy3: SID=34 device type=DISK
allocated channel: prmy4
channel prmy4: SID=35 device type=DISK
allocated channel: stby
channel stby: SID=19 device type=DISK
Starting Duplicate Db at 16-MAY-13
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestgg1' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestgg2' targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg1.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg2.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg2.ora''";
}
executing Memory Script
Starting backup at 16-MAY-13
Finished backup at 16-MAY-13
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg2.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/oradata/dump/testgg2/adump'' comment=
'''' scope=spfile";
sql clone "alter system set diagnostic_dest =
''/u01/app/oracle/oradata/dump/testgg2'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testgg2'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''testgg2'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/'', ''/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/'', ''/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/testgg2/control/control01.ctl'', ''/u01/app/oracle/oradata/testgg2/control/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
10 comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''testgg2'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''testgg1'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(testgg1,testgg2)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/oradata/dump/testgg2/adump'' comment= '''' scope=spfile
sql statement: alter system set diagnostic_dest = ''/u01/app/oracle/oradata/dump/testgg2'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testgg2'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''testgg2'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/'', ''/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/'', ''/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/testgg2/control/control01.ctl'', ''/u01/app/oracle/oradata/testgg2/control/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 10 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''testgg2'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''testgg1'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(testgg1,testgg2)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 163580104 bytes
Database Buffers 364904448 bytes
Redo Buffers 3747840 bytes
allocated channel: stby
channel stby: SID=18 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/testgg2/control/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/testgg2/control/control02.ctl' from
'/u01/app/oracle/oradata/testgg2/control/control01.ctl';
}
executing Memory Script
Starting backup at 16-MAY-13
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_testgg1.f tag=TAG20130516T084319 RECID=7 STAMP=815561001
channel prmy1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 16-MAY-13
Starting restore at 16-MAY-13
channel stby: copied control file copy
Finished restore at 16-MAY-13
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_temp_8qbok7fk_.tmp";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_temp_8qbok7fk_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 16-MAY-13
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_system_8ocl7bho_.dbf
channel prmy2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_sysaux_8ocl7k6n_.dbf
channel prmy3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf
channel prmy4: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_users_8qbolh3g_.dbf
output file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf tag=TAG20130516T084335
channel prmy3: datafile copy complete, elapsed time: 00:00:45
output file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf tag=TAG20130516T084335
channel prmy4: datafile copy complete, elapsed time: 00:00:55
output file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf tag=TAG20130516T084335
channel prmy1: datafile copy complete, elapsed time: 00:01:05
output file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf tag=TAG20130516T084335
channel prmy2: datafile copy complete, elapsed time: 00:01:05
Finished backup at 16-MAY-13
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=815561103 file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=815561104 file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=815561104 file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=815561104 file name=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf
Finished Duplicate Db at 16-MAY-13
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN> **end-of-file**
RMAN> exitComo puedes ver, es pan comido, ahora sólo inicio el proceso de recuperación de la base de datos Standby, en mi caso he usado la opción de Active Dataguard para que te pueda demostrar que realmente funciona, pero ten cuidado que es un Opción Licenciable.SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database disconnect;
Database altered.
SQL> select protection_mode,protection_level,database_role,name from v$database;
PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME
-------------------- -------------------- ---------------- ---------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY TESTGG1
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg2 dlabvm14.dlab.pythian.com
SQL> select * from sender.test;
select * from sender.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
Ahora me regreso a la base de datos Primaria, y creo una tabla llamada test en el esquema sender, y como se puede ver arriba, esta tabla no existe en la base de datos Standby.SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg1 dlabvm13.dlab.pythian.com
SQL> select protection_mode,protection_level,database_role,name from v$database;
PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME
-------------------- -------------------- ---------------- ---------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY TESTGG1
SQL> create table sender.test(id number);
Table created.
SQL> insert into sender.test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
De nuevo me regreso a la base de datos Standby y podemos ver que tenemos nuestra tabla test que acabamos de crear en la primaria.SQL> select protection_mode,protection_level,database_role,name from v$database;
PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME
-------------------- -------------------- ---------------- ---------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY TESTGG1
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg2 dlabvm14.dlab.pythian.com
SQL> select * from sender.test;
ID
----------
1Ahora bien, hay varias manera de ver si esto funciona o no, puedes utilizar el siguiente query para ver cual fue el último archivo recibido/se aplico en la base de datos Standby.SQL> SELECT 'Last Applied : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES'
)
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log );
LOGS TIME THREAD# SEQUENCE#
---------------- ------------------ ---------- ----------
Last Applied : 16-MAY-13:11:42:52 1 65
Last Received : 16-MAY-13:11:42:52 1 65Mientras que en la base de datos Primaria, se puede comprobar cuál es la secuencia actual y cuál es la secuencia en espera de ser aplicada.SQL> SELECT
(SELECT name FROM V$DATABASE
) name,
(SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
) Current_primary_seq,
(SELECT MAX (sequence#)
FROM v$archived_log
WHERE TRUNC(next_time) > SYSDATE - 1
AND dest_id = 2
) max_stby,
(SELECT NVL (
(SELECT MAX (sequence#) - MIN (sequence#)
FROM v$archived_log
WHERE TRUNC(next_time) > SYSDATE - 1
AND dest_id = 2
AND applied = 'NO'
), 0)
FROM DUAL
) "To be applied",
(
(SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
) -
(SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 2
)) "To be Shipped"
FROM DUAL;
NAME CURRENT_PRIMARY_SEQ MAX_STBY To be applied To be Shipped
--------- ------------------- ---------- ------------- -------------
TESTGG1 65 65 0 0Espero que esta pequeña guía te pueda ayudar cuando estés tratando de construir una base de datos Standby Fisica con RMAN Active Duplicate, y como siempre, prueba todo lo ,que he dicho o se ha mencionado, antes de ejecutarlo en un entorno de producción y sobre todo para que puedas entender lo que está sucediendo . - ASYNC (Default) .- Los datos generados de redo por transacción no tienen que haber sido recibidos en cada uno de los destinos habilitados antes de cometerse ésta.
- Apr 22, 13
No es raro que Oracle le haga falta detalles a su documentación , y esto es sólo otro caso de esta enfermedad.
Tenemos una base de datos en la que tenemos dos copias del mismo backup set, uno lo tenemos en el FRA, la otra en un disco externo, que de vez en cuando la copia se elimina de este segundo disco. Pero, ¿qué sucede después de hacer un crosscheck y una de las 2 copias no se encuentra? El del FRA está disponible y el que está en el disco externo ha expirado, como vas a ver esto cuando hagas un LIST en RMAN?
En cuanto a la documentación de 11.2 para el comando LIST, sólo dice que el estado de un respaldo puede estar AVAILABLE, UNAVAILABLE, o EXPIRED, pero como puedes ver a continuación, este no es el caso de nuestra base de datos :)RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1986 B F A DISK 17-APR-13 1 2 NO *
1987 B F M DISK 17-APR-13 1 2 NO *
1988 B F A DISK 17-APR-13 1 2 NO *
1989 B F A DISK 19-APR-13 1 1 NO TAG000661
1990 B A A DISK 19-APR-13 1 1 YES BUP_ARCH_FRA
La primera vez que vi esto, se me hizo curioso en cuanto a lo que significa el sentido del status M (y es al día de hoy que todavía no sé el significado exacto, pero tengo mis suposiciones). Si hago un trace de mi comando LIST BACKUP SUMMARY, puedo ver exactamente lo que yo sabía que estaba ocurriendo:pythian@oracleenespanol.local /home/pythian/working/antunez
pythian $ rman target / debug trace rman.trc
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Apr 19 16:02:56 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: TESTDB (DBID=1834739652)
RMAN> list backup summary;
Este es un pequeño resultado del trace que hice :DBGMISC: 339 BS (datafile) key=1987 recid=1987 stamp=812962808 setstamp=812962807 setcount=2021
DBGMISC: level=1 level_i=-1 piececount=1 keepopts=0, site_key=0 [16:03:27.544]
DBGMISC: site_key=0 [16:03:27.544]
DBGMISC: chid=NIL parm=NIL [16:03:27.544]
DBGMISC: flags= [16:03:27.544]
DBGMISC: valid backup set list is [16:03:27.544]
DBGMISC: 1 VBS copy#=1 tag=BACKUP_CONTROLFILE_FRA deviceType=DISK status=A
DBGMISC: 1 BPIECEX key=3502 recid=3502 stamp=812962808
DBGMISC: bskey=1987 set_stamp=812962807 set_count=2021 site_key=0
DBGMISC: pieceno=1 handle=+FRA/test/backupset/2013_04_17/ncnnf0_backup_controlfile_fra_0.297.812962809
DBGMISC: device=DISK krmkch { count=0 found=FALSE }
DBGMISC: 2 VBS copy#=2 tag=BACKUP_OF_BACKUPSET deviceType=DISK status=X
DBGMISC: 1 BPIECEX key=3538 recid=3538 stamp=813024283
DBGMISC: bskey=1987 set_stamp=812962807 set_count=2021 site_key=0
DBGMISC: pieceno=1 handle=/backupdisk/TEST_bck/db_bckset_backup_TEST2_04-18-2013_v5o79kvn_1_2.rman
DBGMISC: device=DISK krmkch { count=0 found=FALSE }
DBGMISC: restore target list is [16:03:27.545]
DBGMISC: 1 ACT type=full fromSCN=0 toSCN=307822519 fno=0
DBGMISC: CURCF
Como puedes ver arriba, una copia ha expirado y la otra está disponible, también cuando el resultado del comando LIST tiene un asterisco "*", se debe a que cada copia tiene un TAG diferente, para la etiqueta del backupset con clave 1987.
Ambas conductas son perfectamente normales, pero no vas a encontrar esto en cualquier lugar de la documentación de Oracle, por lo que la próxima vez que veas esto en tu comando LIST, conocerás el significado exacto de este estado. ¿Has visto esto antes en tus listados de RMAN?
P.D. Para mí significa Múltiple o Mixta. - Apr 13, 13
Una de las razones principales por las que he estado un poco inactivo en mi blog, ha sido por que en el último mes y medio he cambiado de trabajo, cambie de países y di una conferencia en Collaborate de IOUG en Denver, así que sólo quería compartir este vinculo para cualquier persona que no tuvo la oportunidad de asistir a este impresionante encuentro mentes en Oracle.
Mi presentación se orientó hacia usuarios principiantes o intermedios de la utilidad RMAN de Oracle, aquí esta mi papel técnico basado en esta presentación, espero que te sea útil, como les fue a las personas que me se acercaron de la presentación. Lo unico es que se encuentra en ingles, ya que la presentación fue en ingles, pero si requieres ayuda, no dudes en decirme.
How to Survive a Disaster with RMAN - Mar 13, 13
Una de las cosas que siempre me ha sorprendido es la forma en que todas nuestras decisiones, grandes o pequeñas, tienen un impacto en nuestra vida. A veces no se nota que estas fueron un parteaguas en nuestra vida, pero a veces estas decisiones son tan claras como el agua.
Para mí, un simple DBA de Oracle, una de estas decisiones de vida era tener una charla con un gran amigo del gran potencial que tienen las redes sociales y la razón de por qué tengo que abrir una cuenta de Twitter. Esto me llevó a seguir Yury Velikanov, quien tuiteó una conversación sobre cómo compartir tu blog con Oracle para poder asistir a Oracle Open World 2012 (OOW) como blogger.
Así que hice exactamente eso y mi propuesta fue aceptada. Esto me dio la oportunidad de llegar a San Francisco. Durante esos días en el OOW, tuve la oportunidad de escuchar al fundador de Pythian, Paul Vallée en una platica de Ted. Después de esos 15 minutos y una charla en el evento de "bloggeros" con Greg Leger, tuve la inquietud a unirme a esta compañía de la que me acababa de enterar que existia en este viaje.Ahora bien, esto no iba a ser un camino facil, en realidad iba a ser un arduo proceso de contratación y entrevistas. Esto es debido al hecho de que Pythian esta compuesto de los mejores talentos de todo el mundo, y se esfuerza por tenerlos.Brincando 6 meses al 04 de Marzo del 2013, mi primer día en Pythian, y qué semana ha sido, llena desde los primeros minutos con los entrenamientos en el modo de vida de un "Pythianense". Y todo lo que puedo decir al respecto es que, se ha cumplido y se ha superado todo lo que me había imaginado que iba a ser.Y aquí están algunas razones de por qué:- Quiero saber si el primer día que llegaste a tu nuevo trabajo, te sentaste con el CEO de la empresa y tuvise una conversación de una hora con él, para ver la misión y visión de la organización y la ruta que está tomando.
- El dueño de la empresa te animó para iniciar a "bloggear" y comenzar a subir tu nivel de juego.
- Tienes la oportunidad de estar , hablar y sentarte alrededor de la mesa y compartir ideas con las personas que están consideradas como el mejor 5% en todo el mundo en la materia que te encuentras.
- Ves que se esfuerzan para evitar en todos sus empleados, la famosa frase de Stephen King de El Resplandor "Mucho trabajo y poca diversión hacen de Jack un tipo aburrido".
- Empiezas a creer el punto de vista Pythian de que "la calidad de nuestro trabajo es esencial para el éxito de nuestros clientes y la calidad de nuestro servicio es esencial para nuestro éxito", y con esto, es importante saber que se trata de un esfuerzo de equipo, no es un héroe independiente. De ahí que la empresa haga todo lo posible desde el principio, para que te sientas como parte de esta familia en crecimiento.
Así que para el Viernes, yo ya llevaba el lema "Love your data" (Ama a tus datos) en la camisa, compartiendo donas en la oficina de Ottawa y muy emocionado de estar aquí para dar lo mejor de mi, ser una parte esencial de esta organización y ser exitoso en el proceso.
Y ahora todo lo que puedo decir después de que la primera semana se ha ido, y mirando hacia atrás en todos los sacrificios y las elecciones que he hecho para estar aquí en Pythian (que yo no voy a entrar en detalles en este post, eso es para platicar cara a cara con un café si quieres oír la historia), es que tengo muchas ganas de que llegue la semana dos, tres, cuatro, y el resto por venir ... - Jan 28, 13
Esta entrada realmente la estoy creando para tener yo un acceso mas rapido, sin tener que pasar por MOS, en nuestro ambiente tuvimos una situacion en donde en nuestra base de datos se instalaron todos los componentes de Oracle, como OLAP y Oracle Text, como no era necesario tenerlos, se tomo la decision de removerlos, pero cuando estabamos corriendo un export , esta fallando con el siguiente error:
ORA-39127: unexpected error from call to "SYS"."DBMS_CUBE_EXP"."SCHEMA_INFO_EXP"
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316
ORA-06512: at "SYS.DBMS_METADATA", line 9409
Primero verificamos que ya no existen componentes de OLAP en la base de datos, en este caso, nada mas tenemos RAC y los catalogos necesariosTESTDB1 >DBA_REGISTRY CONTENTS (VIEW DOES NOT EXISIT IN VERSIONS < 9.2.0)
TESTDB1 >================================================================
TESTDB1 >COMP_ID COMP_NAME VERSION STATUS
-------------------- ---------------------------------------- ---------- ---------------CATALOG Oracle Database Catalog Views 11.2.0.3.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.3.0 VALID
RAC Oracle Real Application Clusters 11.2.0.3.0 VALID
Y con este query vemos que queda todavia existe informacion en el diccionario de datos refiriendose a OLAPTESTDB1 >SELECT * FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP';
PACKAGE SCHEMA CLASS LEVEL#------------------------------ ------------------------------ ---------- ----------
DBMS_CUBE_EXP SYS 2 1050
DBMS_CUBE_EXP SYS 4 1050
DBMS_CUBE_EXP SYS 6 1050
La manera de resolverlo es crear primero un respaldo de la tabla SYS.EXPPKGACT$, por si algo llegara a salir mal, esto lo haces como el usuario SYSoracle $ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 27 02:46:38 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
TESTDB1 >CREATE TABLE SYS.EXPPKGACT$_BACKUP AS SELECT * FROM SYS.EXPPKGACT$;
Table created.
Una vez que hayas creado el respaldo de la tabla, vas a borrar los datos, con el siguiente deleteTESTDB1 >DELETE FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP' AND SCHEMA= 'SYS';
3 rows deleted.
TESTDB1 >commit;
Commit complete.
Y ahora si vas a poder realizar tu respaldo con exito, esto lo puedes encontrar en el documento de MOS 1328829.1Starting "SYSTEM"."DATAPMP__USERS": system/******** dumpfile=node_TESTDB_users_%U.dmp logfile=node_TESTDB_users.log parfile=/mount/dba01/oracle/recover/export_dp_users.par
Processing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SYNONYM/SYNONYMProcessing object type SCHEMA_EXPORT/DB_LINKProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/FUNCTION/FUNCTIONProcessing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTIONProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODYProcessing object type SCHEMA_EXPORT/EVENT/TRIGGERProcessing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJORA-39165: Schema TEST01 was not found.Master table "SYSTEM"."DATAPMP_USERS" successfully loaded/unloaded - Dec 04, 12
En primer lugar quiero empezar diciendo que tengo una idea de cómo sucedió esto, pero no he sido capaz de recrear esta situación, ya que esto me fue heredado, de igual manera tuve la suerte de que el tablespace que tenía el problema era un tablespace DUMMY por lo que no causó un problema.
Ahora si a la descripción del problema, lo que pasa es que tengo una Base de Datos fuente con una ubicación de los datafiles en + DATA1/SOURCE, pero en algún momento alguien añadió una segunda ubicación para la realizar algunas pruebas en un tablespace TEST1 en +DATA2/TEST, esta base de datos fuente se duplicó a través de RMAN hacia 2 bases de datos, llameseles a ellas DUP1 y DUP2. En el comando duplicate de RMAN , se uso el parámetro DB_FILE_NAME_CONVERT para cambiar la ubicación de los datafiles de la base de datos de origen hacia DUP1 o DUP2, pero en este parámetro solo se puso para la ubicacion de +DATA1/SOURCE y no para +DATA2/TEST .
Lo que se puso en el comando RMAN fue lo siguiente:DB_FILE_NAME_CONVERT '+DATA1/SOURCE','DATA1/DUP1'
Como debería de haber sido:DB_FILE_NAME_CONVERT '+DATA1/SOURCE','+DATA1/DUP1','+DATA2/TEST','+DATA1/DUP1'
Y porque el diskgroup y directorio de + DATA2/TEST existen donde DUP1 y DUP2 residiría, no causo ningún problema cuando se corrió el comando DUPLICATE, y esto es algo que todavía es desconocido para mí, el duplicado termino con éxito para ambas bases de datos y no surgió ningún problema al abrirlas con resetlogs, como lo hace el comando.
Como probablemente ya saben, cuando se emite el comando DUPLICATE, la DBID es implicitamente cambiada, y lo que hace esto es que actualiza el controlfile con el dbid nuevo y también las cabeceras de los datafiles, de esa manera es como sabrá que estos XX datafiles pertenecen a la base de datos NN.
Una vez que ambas bases de datos estaban arriba y corriendo, corri un respaldo de las bases de datos, DUP1 termino exitosamente, el respaldo de DUP2 falló con el siguiente error :RMAN-06169: could not read file header for datafile 148 error reason 9
RMAN-06056: could not access datafile 148
Una de las primeras cosas que hice fue correr un VALIDATE DATABASE, y me mostraba el mismo error:RMAN> VALIDATE DATABASE;
Starting validate at 29-NOV-12using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=145 instance=DUP21 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=164 instance=DUP21 device type=DISK RMAN-06169: could not read file header for datafile 148 error reason 9RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of validate command at 11/29/2012 01:40:41RMAN-06056: could not access datafile 148
Lo siguiente que hice fue ver a donde pertenecía el datafile 148 y de igual manera el DBID de DUP2, ya que el error de la cabecera me llevó a buscar por ahiDUP21 >select DBID,open_mode from v$database;
DBID OPEN_MODE
---------- ------------------------------------------------------------
2211912429 READ WRITE
DUP21 >select file_name from dba_data_files where TABLESPACE_NAME='TEST1';
FILE_NAME
--------------------------------------------------------------------------------
+DATA2/test/test01.dbf
DUP21 >select STATUS from dba_tablespaces where TABLESPACE_NAME='TEST1';
STATUS
---------------------------
ONLINE
Debido al error 06169 RMAN me decía que no podía leer la cabecera del datafile, hice un volcado de la esta.DUP21 >oradebug setmypid
Statement processed.
DUP21 >oradebug unlimit
Statement processed.
DUP21 >oradebug dump file_hdrs 3
Statement processed.
DUP21 >oradebug tracefile_name
Y en efecto vi que el DBID de test01.dbf pertenecía a DUP1, que había sido la primera de las dos bases de datos duplicadasV10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=4005607769=0xeec0b959, Db Name='DUP1'
Así que, para nada mas estar seguro , fui a DUP1 a verificar, y sí, era el mismo que el DBID del volcado de la cabecera , y de igual manera el tablespace TEST1 estaba en línea, así como en DUP2DUP11 >select DBID,open_mode from v$database;
DBID OPEN_MODE
---------- ------------------------------------------------------------
4005607769 READ WRITE
DUP11 >select STATUS from dba_tablespaces where TABLESPACE_NAME='TEST1';
STATUS
---------------------------
ONLINE
DUP11 >select file_name from dba_data_files where TABLESPACE_NAME='TEST1';
FILE_NAME
--------------------------------------------------------------------------------
+DATA2/test/test01.dbf
DUP11 >select STATUS from dba_tablespaces where TABLESPACE_NAME='TEST1';
STATUS
---------------------------
ONLINE
Porque ese tablespace en particular era de ninguna utilidad para la aplicación, simplemente lo tire.DUP21 >ALTER TABLESPACE TEST1 OFFLINE IMMEDIATE;
Tablespace altered.
DUP21 >DROP TABLESPACE TEST1;
Tablespace dropped.
Lo que te puedes llevar de esta entrada
Tengo que decir que esto es con un OH en 11.2.0.3 con un GI en 11.2.0.3 en RHEL 5 x86_64, lo que te diria es que cada vez que haces un duplicado y utilizas el DB_FILE_NAME_CONVERT, asegúrate de que todos los lugares están cubiertos. Pero lo realmente me intriga es la forma en que DUP2 fue capaz de abrirse con resetlogs sin que esto fuera un problema, voy a tratar de recrear esto y actualizar este post, siempre y cuando lo logre hacer. - Nov 14, 12
El otro día estaba haciendo una instalación de los binarios del Clusterware 11.2.0.3 y me tope que ahora con la herramienta cluvfy en 11.2.0.3 viene con una opción de verificar la "salud" de la Base de Datos.Para que esta opcion funcione tienes que instalar el usuario cvusys asi como el rol cvusapp , existe un script para poder hacer esto y se encuentra en el directorio CLUVFY_HOME/cv/admin.
Como el usuario oracle corre el script $CLUVFY_HOME/cv/admin/cvusys.sql para crear el usuario, el rol y los grants necesarios para que funcione. Este usuario tendra el tablespace por defacto que tiene la Base de Datos, si no quieres que tenga ese tablespace modifica este script para que lo cree en el tablespace que deseas..oracle@servidor1.localdomain [TESTDB1] /home/oracle
oracle $ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),501(vboxsf),54322(dba)TESTDB1> @cvusys.sql
User dropped.
Role dropped.
Enter password for user cvusys
'Creating user cvusys...'
User created.
Grant succeeded.
Role created.
Grant succeeded.
.
.
.
Una vez que hayas inicializado el ambiente para el usuario de la Base de Datos cvusys verifica que como el usuario de OS grid tengas la variable de ambiente $ORACLE_HOME con el GRID_HOME correcto.grid@servidor1.localdomain /home/grid
oracle $ id
uid=54324(grid) gid=54321(oinstall) groups=54321(oinstall),501(vboxsf),54322(dba),54323(asmadmin),54325(asmoper)
grid@servidor1.localdomain /home/grid
oracle $ echo $ORACLE_HOME
/mount/oracle/11.2.0.3/grid
Una de las cosas que note fue que si no tienes el GRID_HOME correcto o le pasas el nombre de tu Base Datos en mayúsculas cuando el nombre de la Base de Datos es en minúsculas, te puede arrojar el error PRVG-11005, esto aunque cuando veas el log en $CLUVFY_HOME/cv/log el comando srvctl config database se esta ejecutando correctamente.grid@servidor1.localdomain /home/grid
oracle $ cluvfy comp healthcheck -collect database -db TESTDB -deviations -save -savedir /home/grid/audit
ERROR:
PRVG-11005 : Database "TESTDB" is not defined in this cluster
grid@servidor1.localdomain /home/grid/cv/log
root $ more cvutrace.log.0
.
.
.
[17455@servidor1.localdomain] [Worker 0] [ 2012-11-04 22:41:33.595 EST ] [VerificationCommand.execute:232] Formatted exectask output is:
<CV_CMD>/mount/oracle/11.2.0.3v1/rdbms/bin/srvctl config database -d TESTDB -a </CV_CMD><CV_VAL>Database unique name: TESTDB
Database name: TESTDB
Oracle home: /mount/oracle/11.2.0.3v1/rdbms
Oracle user: oracle
Spfile: +DATA/TESTDB/PARAMETERFILE/spfileTESTDB.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TESTDB
Database instances: TESTDB1,TESTDB2
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
</CV_VAL><CV_VRES>0</CV_VRES>Exectask: runexe was successful</CV_LOG><CV_ERES>0</CV_ERES>
.
.
.
Cuando usas esta utilería , puedes correrla contra todas las Base de Datos en el cluster si no usas la opción de , lo único que si te digo es que tienes que crear el usuario cvusys en cada una de las Base de Datos del cluster. De la misma manera puedes verificar cuales son las mejores practicas recomendadas por Oracle y como tu Base de datos se desvian de estas con las opciones [-bestpractice|-mandatory] [-deviations].
En este caso , corri la utileria con el nombre de la Base de datos en minúsculas con la opción de mejores practicas (-bestpractice) y termino correctamente.grid@servidor1.localdomain /home/grid/cv/log
root $ cluvfy comp healthcheck -collect database -db testdb -bestpractice -deviations -save -savedir /home/grid/audit/
Verifying Database "testdb"
Please specify password for user "cvusys" :
Verifying Database Best Practice for "testdb"
Verifying JVM configuration for database ...not met
Verifying Java Role Count ...not met
Verifying Duplicate SYS or SYSTEM Schema Objects ...not met
Verifying Users Granted CONNECT Role ...not met
Verifying DB Log Mode ...not met
******************************************************************************************
Summary of environment
******************************************************************************************
Date (mm/dd/yyyy) : 11/05/2012
Time (hh:mm:ss) : 22:37:28
Cluster name : cluster-test
Clusterware version : 11.2.0.3.0
Grid home : /mount/oracle/11.2.0.3/grid
Grid User : grid
Operating system : Linux2.6.18-238.el5
Database1 : Database name - testdb
Database version - 11.2.0.3
Database home -
/mount/oracle/11.2.0.3v1/rdbms
******************************************************************************************
Database recommendation checks for "testdb"
******************************************************************************************
.
.
.
Ahora lo que tienes que hacer es ir al directorio donde guardaste el reporte y ver las desviaciones de las mejores practicas recomendadas por Oracle. Aquí es donde yo te diria que verifiques y tengas cuidado con las recomendaciones que te arroja, ya que una de las recomendaciones era que estaba corriendo en modo ARCHIVE_LOG vs la recomendación NOARCHIVELOG, espero que puedas ver en donde no estoy de acuerdo con este reporte, pero si no estas verificando la salud de tus Base de Datos después de una instalación nueva, esta es una gran manera de empezar y te ayudara en un futuro a ver que es lo que puedo hacer para mejorar mi ambiente.__________________________________________________________________________________________
Verification Check : Duplicate SYS or SYSTEM Schema Objects
Verification Description : Checks for duplicate SYS or SYSTEM schema objects
Verification Result : NOT MET
Verification Summary : Check for Duplicate SYS or SYSTEM Schema Objects failed
Additional Details : If any duplicate objects were found in the SYS and SYSTEM
schemas, refer to articles in the references section. Read
the exceptions carefully before taking action.
References (URLs/Notes) : https://support.oracle.com/CSP/main/article?cmd=show&type=N
OT&id=1030426.6
Node Status Expected Value Actual Value
------------------------------------------------------------------------------------------
testdb FAILED sys_duplicate_obj = 0 sys_duplicate_obj = 4
__________________________________________________________________________________________
Verification Check : INVALID objects in the application related schemas
Verification Description : Checks for the presence of INVALID objects in the
application related schemas (non SYS and SYSTEM)
Verification Result : NOT MET
Verification Summary : Check for INVALID objects in the application related
schemas failed
Additional Details : Investigate invalid objects in the application related
schemas (non SYS and SYSTEM).
Node Status Expected Value Actual Value
------------------------------------------------------------------------------------------
testdb FAILED app_invalid_obj = 0 app_invalid_obj = 5 - Nov 05, 12
Para cualquiera que haya trabajado con la versión 11gR2 del Clusterware de Oracle se ha topado con el diagrama de arranque de esta tecnología, y la verdad desde mi punto de vista es realmente difícil de ver y entender, y no es "KISS" , Keep It Simple Stupid por sus siglas en ingles.
Luego hay un segundo diagrama, que ese esta mucho mejor, pero de igual manera, la calidad de este segundo diagrama es de muy mala calidad , y para mi muy mala vista ,no me ayuda en nada.
Si quiero mencionar que no estoy poniendo nada nuevo en este post que no puedas encontrar en el documento de MOS 1053147.1, simplemente lo estoy poniendo en un formato que sea mas legible y fácil de entender.
Dale click a la imagen para verla en su tamaño real:
Las descripciones de abajo son una traducción del mismo documento de MOS
Unix INIT manda a llamar a init.ohasd (con respwan) , que a su vez manda a llamar los procesos OHASD (Oracle High Availability Services Deamon, por sus siglas en ingles) . Este demonio genera 4 procesos:
Nivel 1 .- OHASD genera :
cssdagent - Agente responsable de generar CSSD.
orarootagent - Agente responsable de administrar todos los recursos ohasd del usuario root.
oraagent - Agente responsable de administrar todos los recursos ohasd del usuario oracle o grid
(Dependiendo bajo que usuario hayas instalado los binarios del clusterware).
cssdmonitor - Monitorea el CSSD y la "salud" del nodo (así como el agente cssdagent).
Nivel 2 .- El agente orarootagent de OHASD genera:
CSDD (ora.cssd) - Servicios de Sincronizacion del Cluster (Cluster Synchronization Services)
CRSD(ora.crsd) - Demonio primario responsable de administrar los recursos del cluster.
CTSSD(ora.ctssd) - Demonio de Sincronizacion de Servicios del Tiempo del Cluster
Diskmon(ora.diskmon)
ACFS (ASM Cluster File System) Drivers
Nivel 3 .- CRSD genera
orarootagent - Agente responsable de administrar todos los recursos crsd del usuario root.
oraagent - Agente responsable de managing todos los recursos crsd del usuario oracle o grid.
Nivel 4 .- CRSD orarootagent genera:
Network resource - Para monitorear la Red Publica
SCAN VIP(s) - Single Client Access Name Virtual IPs
Node VIPs - IP Virtual del Nodo,Uno por Nodo
ACFS Registery - Para montar el Sistema de Archivos Cluster ASM (ASM Cluster File System
GNS VIP (opcional) - VIP para GNS
Nivel 4 .- CRSD oraagent spawns:
ASM Resource - Recurso de Instancia(s) de ASM
Diskgroup - Usado para administrar/monitorear grupos de discos de ASM .
DB Resource - Usado para administrar/monitorear las BD's y las instancias
SCAN Listener - Listener para SCAN, escuchando la IP Virtual del SCAN
Listener - Listener del Nodo, escuchando la VIP del Nodo.
Services - Usado para administrar y monitorear servicios
ONS - Servicio de Notificación de Oracle (Oracle Notification Service)
eONS - Servicio de Notificación de Oracle Mejorado (Enhanced Oracle Notification Service)
GSD - Para compatibilidad hacia atrás con 9i
GNS (opcional) - Servicio de Nombres de Gird (Grid Naming Service) - Realiza resolución de nombres - Oct 25, 12
Una característica que tiene RMAN en 11g es que tienes la posibilidad de crear un punto de restauración en el mismo respaldo, este método es muy útil, sobre todo cuando vas a hacer una actualización importante en tu Base de Datos y tienes que crear un respaldo antes, este es el mejor método desde mi punto de vista.
Lo primero , es que verifico que no hay un punto de restauración para mi base de datosTESTDB> set linesize 121
TESTDB> col name format a15
TESTDB> col time format a32
TESTDB> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size
2 FROM gv$restore_point;
no rows selected
Ahora lo que hago es que corro un respaldo con la opción de KEEP UNTIL y RESTORE POINTRMAN> RUN
2> {
3> BACKUP AS COMPRESSED BACKUPSET
4> INCREMENTAL LEVEL = 0
5> DATABASE
6> FORMAT '/mount/copy01/TESTDB/oracle/TESTDB/incr/%d_HOT_%M%D%Y_%p_%s'
7> TAG TESTDB
8> KEEP UNTIL TIME 'SYSDATE + 5'
9> RESTORE POINT BEFORE_UPGRADE;
10> }
Starting backup at Oct 25 2012 08:41:49
current log archived
using channel ORA_DISK_1
backup will be obsolete on date Oct 30 2012 08:41:50
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/mount/u01/oracle/TESTDB/data/system01.dbf
input datafile file number=00003 name=/mount/u01/oracle/TESTDB/data/undotbs1_01.dbf
input datafile file number=00004 name=/mount/u01/oracle/TESTDB/data/users01.dbf
input datafile file number=00002 name=/mount/u01/oracle/TESTDB/data/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at Oct 25 2012 08:41:50
channel ORA_DISK_1: finished piece 1 at Oct 25 2012 08:41:57
piece handle=/mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_10252012_1_24 tag=TESTDB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
using channel ORA_DISK_1
backup will be obsolete on date Oct 30 2012 08:41:57
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at Oct 25 2012 08:41:57
channel ORA_DISK_1: finished piece 1 at Oct 25 2012 08:41:58
piece handle=/mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_10252012_1_25 tag=TESTDB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
current log archived
using channel ORA_DISK_1
backup will be obsolete on date Oct 30 2012 08:41:58
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=19 STAMP=797589718
channel ORA_DISK_1: starting piece 1 at Oct 25 2012 08:41:58
channel ORA_DISK_1: finished piece 1 at Oct 25 2012 08:41:59
piece handle=/mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_10252012_1_26 tag=TESTDB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
using channel ORA_DISK_1
backup will be obsolete on date Oct 30 2012 08:41:59
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at Oct 25 2012 08:42:00
channel ORA_DISK_1: finished piece 1 at Oct 25 2012 08:42:01
piece handle=/mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_10252012_1_27 tag=TESTDB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at Oct 25 2012 08:42:01
Para verificar que se creo el punto de restauración corro los siguientes comandos en RMANRMAN> list restore point all;
SCN RSP Time Type Time Name
---------------- -------------------- ---------- -------------------- ----
234073 Oct 25 2012 08:41:58 BEFORE_UPGRADE
y en SqlplusSQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size
2 FROM gv$restore_point;
NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------- ---------- -------------------------------- --------------------- --- ------------
BEFORE_UPGRADE 234073 25-OCT-12 08.41.58.000000000 AM 1 NO 0
Si te fijas, el SCN es el mismo , 234073, que es lo que andamos buscando. En dado caso de que tengas que hacer un rollback, a este SCN seria al que regresarias.
Este respaldo de igual manera lo podemos utilizar para clonar una base de datos hasta el punto de restauración que creaste con la opción TO RESTORE POINT, no puse toda la salida, pero si te fijas abajo, esta recuperando hasta el punto de restauración que creamos en la Base de Datos TESTDBoracle@servidor1.oracleenespanol.blogspot.com [TESTDB] /mount/dba01/oracle/TESTCOPY/scripts
oracle $ rman target / auxiliary sys@TESTCOPY
RMAN> RUN
2> {
3> ALLOCATE AUXILIARY CHANNEL newdb DEVICE TYPE disk;
4> DUPLICATE TARGET DATABASE TO testcopy
5> TO RESTORE POINT BEFORE_UPGRADE
6> DB_FILE_NAME_CONVERT ('/mount/u01/oracle/TESTDB/','/mount/u01/oracle/TESTCOPY/','/mount/u02/oracle/TESTDB/','/mount/u02/oracle/TESTCOPY/')
7> LOGFILE
8> GROUP 1 ('/mount/u01/oracle/TESTCOPY/log/redo01_1.f',
9> '/mount/u02/oracle/TESTCOPY/log/redo01_2.f') SIZE 4M,
10> GROUP 2 ('/mount/u01/oracle/TESTCOPY/log/redo02_1.f',
11> '/mount/u02/oracle/TESTCOPY/log/redo02_2.f') SIZE 4M,
12> GROUP 3 ('/mount/u01/oracle/TESTCOPY/log/redo03_1.f',
13> '/mount/u02/oracle/TESTCOPY/log/redo03_2.f') SIZE 4M REUSE;
14> }
using target database control file instead of recovery catalog
allocated channel: newdb
channel newdb: SID=19 device type=DISK
Starting Duplicate Db at Oct 25 2012 08:50:53
contents of Memory Script:
{
set to restore point 'BEFORE_UPGRADE';
sql clone "alter system set db_name =
''TESTDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''TESTCOPY'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
.
.
.
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at Oct 25 2012 08:51:57
released channel: newdb
Espero que este método te sirva en tus próximas actualizaciones. - Oct 15, 12
Este año tuve la oportunidad de asistir al Oracle Open World, y desde mi punto de vista es algo que todo mundo que esta relacionado en el ambiente de las tecnologias de Oracle tiene que hacer por lo menos una vez en su vida, ya que no es nada mas lo que aprendes en el evento, sino como lo dice el titulo , es toda una experiencia y en todos los sentidos.
Vas a tener la oportunidad de escuchar el rumbo que tiene la empresa y sus productos , que si estas leyendo este blog es, por que estas interesado en esta tecnologia o ya llevas varios años manejandola. Esta vez toco el anuncio de la nueva version de la Base de Datos, 12c , que como puedes imaginar la "c" es por "Cloud" (Nube en ingles).
Y viene un cambio bastante significante en lo que es el concepto que tenemos en la relacion de Base de Datos e Instancia. Ya que hasta la version 11.2.0.3, toda Base de Datos podia tener una o mas Instancias, pero una Instancia solo podia tener una Base de Datos.
Esto va a cambiar en 12c , ya que ahora va a existir un nuevo tipo de Base de Datos, llamada Base de Datos Contenedor , esta Base de Datos va a tener una instancia ( procesos y memoria) y a esta Base de Datos/Instancia le vas a "conectar" tus otras Bases de Datos, estas se llaman Bases de Datos con capacidad de Conectarse (Pluggable Databases o PDB).
Regresando al tema del OOW, vas a tener la oportunidad de conocer, platicar y hasta hecharte un cafe o cerveza si es tu gusto con las personas mas respetadas del ambito y escuchar que tienen que decir al respecto del rumbo que esta tomando Oracle.
Ya que estas ahi vas a poder conocer una ciudad que esta llena de opciones y aventuras para todos los gustos. Asi que el proximo año toma la iniciativa y nos vemos en el OOW 2013.
Aqui otras entradas de las experiencias del Oracle Open World 2012 , estas entradas estan en ingles
Oracle OpenWorld 2012: A photographic experience
Favourite Oracle 12c database features of OpenWorld Bloggers
The Fun of Oracle OpenWorld
Oracle OpenWorld - Swim in the Bay 2012 - Sep 25, 12
Este fin de semana pasado estuve involucrado en un plan de desastres y recuperación, DRP (Disaster Recovery Plan) por sus siglas en ingles, básicamente lo que se hace en un DRP es planear lo peor, como por ejemplo que pasaría si tu site principal se cae, y que acciones tomar en caso de que suceda.
Y aunque se puede decir que en un DRP tienes un control de lo que esta sucediendo, si te ayuda a ver que pasos te están fallando en tus procedimientos estándares de operación, y poder actualizarlos, ya que a la mejor pensabas que tu manera de tomar un respaldo era correcta, pero al ejecutar el DRP te das cuenta de que no puedes recuperar tu base de datos, estas pruebas te sirven para encontrar esos hoyos en tu plan.
Buscando un poco mas de datos acerca de la cantidad de empresas que llevan a cabo un DRP, me tope con una encuesta ejercida por el IOUG (Independent Oracle Users Group) efectuada este 2012 a mas de 350 profesionistas en donde el 51% fueron administradores de base datos, en donde resalta para mi lo siguiente :- El 18% de los encuestados nunca han llevado a cabo un DRP
- El 37 % de los encuestados solamente hace un DRP parcial de sus datos.
- El 43 % de los encuestados mencionan que el financiamiento es una de las mas fuertes restricciones para llevar a cabo un DRP.
- El 12% de los encuestados no sabe cuanto tiempo se puede llevar en restaurar los datos en caso de un "apagón" de operaciones.
Es un poco preocupante ver números arriba del 10% en estas categorías, ya que desde mi punto de vista, como DBA, lo mas importante es tener los datos disponibles, si tus datos no están disponibles, realmente estas en una situación no deseable por ningún DBA.
A final de cuentas, si tu cliente se tarda en recibir los datos que necesita cuando lanza un Query , puede de alguna manera en vivir con ese tiempo en que se esta tardando en recibir los datos, no va a estar contento, pero va a obtener los datos que anda buscando, pero que pasa si necesita los datos y nunca los va a poder obtener?
A lo que quiero llegar es que si al día de hoy no tienes entre tus estándares un DRP en operacion, ve planeando uno, este te va servir para detectar , corregir y hasta probablemente prevenir un "apagón" de tus operaciones y/o perdida de datos. De igual manera, un DRP te va ayudar a establecer un SLA de tus tiempos en caso de un desastre,
Así que ve planeando un DRP y encuentra las debilidades en tu arquitectura para en lugar de salir como perdedor en un desastre seas el héroe y de pasada mantengas tu trabajo :) . - Sep 07, 12
Cuando manejas RAC, existe un herramienta llamada crsctl, esta herramienta es una interfase para manejar los recursos pertenecientes al clusterware. Pero al igual esta herramienta te puede ayudar para que tus aplicaciones estén disponibles cuando tu cluster esta disponible y viceversa.
Esto nos sirve a nosotros no nada mas para aplicaciones de usuario , si no también para herramientas que nos sirven para monitorear nuestros nodos, en este caso OSWbb (OS Watcher Black Box 461053.1) y aunque existe un documento para arrancar OSWbb como un servicio del sistema operativo,580513.1, aquí lo vamos a poner con crsctl
Como siempre, esto lo debes de ensayar en un ambiente de pruebas antes de moverlo a un ambiente de producciony de igual manera verificar los scripts que te estoy mencionando.
Lo primero que tienes que hacer es bajar OSWbb e instalarlo en la misma dirección en todos los nodos que le pertenece a tu granja de servidores RAC, yo lo puse en la siguiente dirección:
/mount/dba01/oracle/oswbb_51
De ahí tienes que crear un script que llame a los cuatro comandos necesarios para que funcione con crsctl (START,STOP,CHECK y CLEAN) y que de la misma manera sea visible para todos los nodos en la misma locación , en este caso el script que voy a utilizar lleva el nombre de rac_verificar_oswbb.sh , y lo puede encontrar dándole click al nombre anterior.
Lo único que cambiar en el script que te acabo de mencionar es cambiar la variable OSWPATH a donde tu descomprimiste la ultima versión de OSWbb.
OSWPATH=/mount/dba01/oracle/oswbb_51
Asegurate que el usuario con el que vas a crear el recurso, ya sea root o grid ,tenga permisos de escritura y ejecución sobre rac_verificar_oswbb.sh y OSWPATH
Una vez que hiciste lo anterior , con el usuario root o el usuario grid, dependiendo con que usuario vayas a crear el recurso, en mi caso use el usuario root, tienes que verificar que los siguiente cuatro comandos corran sin ningún error, si alguno no sirve o corre con error, no va a funcionar lo que estamos haciendo.
Una vez que hayas resuelto y/o verificado que no tienes ningún problema, hay que crear el recurso, asegurate que el recurso no tenga el prefijo ora.,ya que este es para los recursos manejados por Oracle, en este caso utilice un prefijo llamado usr.root@servidor1.oracleenespanol.blogspot.com /home/oracle
root $ crsctl add resource usr.oswbb -type ora.local_resource.type -attr "AUTO_START=always,RESTART_ATTEMPTS=2, START_TIMEOUT=100,STOP_TIMEOUT=100,CHECK_INTERVAL=60,ACTION_SCRIPT=/mount/dba01/oracle/admin/rac_verificar_oswbb.sh"
Ya creado el recurso, ya nada mas tienes que iniciarlo y verificar que esta corriendoroot@servidor1.oracleenespanol.blogspot.com /home/oracle
root $ crsctl start resource usr.oswbb
CRS-2672: Attempting to start 'usr.oswbb' on 'servidor1'
CRS-2672: Attempting to start 'usr.oswbb' on 'servidor2'
CRS-2676: Start of 'usr.oswbb' on 'servidor1' succeeded
CRS-2676: Start of 'usr.oswbb' on 'servidor2' succeeded
root@servidor1.oracleenespanol.blogspot.com /home/oracle
root $ crsctl status resource usr.oswbb
NAME=usr.oswbb
TYPE=ora.local_resource.type
TARGET=ONLINE , ONLINE
STATE=ONLINE on servidor1, ONLINE on servidor2
Conclusión
Este método no nada mas sirve para OSWbb , si no para cualquier aplicación que este ligado a tu cluster, y ahora si la imaginación y lo complejo que quieres que sea es tuyo. - Sep 04, 12
El otro día me hicieron una pregunta de que como podía verificar que el respaldo que había tomado . Para esto RMAN tiene unos comandos que nos ayudan a verificar que respaldos tenemos y la integridad de ellos
Todos lo comandos que utilice no van a restaurar los respaldos, solamente lee y los valida, lo que si te debo de decir es que tengas cuidado con los comandos que vas a lanzar, ya que en varios , si la palabra VALIDATE no se encuentra presente, en lugar de validar el respaldo, empieza a restaurar el respaldo.
Aquí esta un resumen de los comandos que vamos a usar y estos son validos para un canal SBT o DISK- RESTORE DATABASE PREVIEW ;
- RESTORE DATABASE VALIDATE;
- RESTORE ARCHIVELOG FROM sequence xx UNTIL SEQUENCE yy THREAD nn VALIDATE;
- RESTORE CONTROLFILE VALIDATE;
- RESTORE SPFILE VALIDATE;
RMAN> RUN
2> {
3> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
4> allocate channel ch1 device type disk ;
5> RESTORE DATABASE PREVIEW ;
6> }
executing command: SET until clause
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
Starting restore at 03-SEP-2012 22:54:18
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
6 Full 75.34M DISK 00:00:19 03-SEP-2012 22:01:04
BP Key: 6 Status: AVAILABLE Compressed: YES Tag: TESTDB_HOT_0904_2100
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
1 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/system01.dbf
2 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/sysaux01.dbf
3 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/undotbs1_01.dbf
4 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/undotbs2_01.dbf
5 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/users_01.dbf
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
9 482.00K DISK 00:00:00 03-SEP-2012 22:01:39
BP Key: 9 Status: AVAILABLE Compressed: YES Tag: TESTDB_ARCH_0904_2100
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499
List of Archived Logs in backup set 9
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 8 222449 03-SEP-2012 21:57:53 224442 03-SEP-2012 22:01:33
2 4 222452 03-SEP-2012 21:59:38 224448 03-SEP-2012 22:03:17
1 9 224442 03-SEP-2012 22:01:33 224456 03-SEP-2012 22:01:36
2 5 224448 03-SEP-2012 22:03:17 224459 03-SEP-2012 22:03:21
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
12 389.50K DISK 00:00:01 03-SEP-2012 22:21:50
BP Key: 12 Status: AVAILABLE Compressed: YES Tag: TESTDB_ARCH_0904_22_20
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1
List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 10 224456 03-SEP-2012 22:01:36 225574 03-SEP-2012 22:21:01
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
11 25.00K DISK 00:00:00 03-SEP-2012 22:21:50
BP Key: 11 Status: AVAILABLE Compressed: YES Tag: TESTDB_ARCH_0904_22_20
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
2 6 224459 03-SEP-2012 22:03:21 225577 03-SEP-2012 22:22:45
Media recovery start SCN is 224235
Recovery must be done beyond SCN 224235 to clear datafile fuzziness
Finished restore at 03-SEP-2012 22:55:15
released channel: ch1
El siguiente paso, que es RESTORE DATABASE VALIDATE, va a leer las piezas del respaldo y si llega a encontrar algún error , va a reportar este errorRMAN> RUN
2> {
3> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
4> allocate channel ch1 device type disk ;
5> restore database validate;
6> }
executing command: SET until clause
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
Starting restore at 03-SEP-2012 22:51:44
channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445 tag=TESTDB_HOT_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:25
Finished restore at 03-SEP-2012 22:52:38
released channel: ch1
Pero si te fijaste, esto solamente leyó el backupset del respaldo completo, así que hace falta ver si los backupsets de los archivelogs estan integros para hacer en dado caso de ser necesario restaurar tu base de datos, para esto, lo que te recomiendo yo es que del resultado del primer comando, de allí sacar los valores de la secuencias de archive logs que requieres y de una vez que tienes estos valores ,correr el comando RESTORE ARCHIVELOG. . . VALIDATERMAN> RUN
2> {
3> allocate channel ch1 type disk;
4> restore archivelog from sequence 8 until sequence 10 thread 1 validate;
5> restore archivelog from sequence 4 until sequence 6 thread 2 validate;
6> }
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
Starting restore at 03-SEP-2012 23:15:11
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499 tag=TESTDB_ARCH_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1 tag=TESTDB_ARCH_0904_22_20
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:15:15
Starting restore at 03-SEP-2012 23:15:17
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499 tag=TESTDB_ARCH_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1 tag=TESTDB_ARCH_0904_22_20
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:15:21
released channel: ch1
Ya por ultimo vamos a validar que podemos restaurar el controlfile y nuestro archivo de parámetros binarioRMAN> RUN
2> {
3> allocate channel ch1 type disk;
4> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
5> restore controlfile validate;
6> restore spfile validate;
7> }
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
executing command: SET until clause
Starting restore at 03-SEP-2012 23:23:14
channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-03
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-03 tag=TAG20120903T220143
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:23:15
Starting restore at 03-SEP-2012 23:23:16
channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-04
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-04 tag=TAG20120903T222152
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:23:18
released channel: ch1
Conclusión
Estos comandos aunque sencillos, son muy útiles para validar tus respaldos, mi recomendación es que los corras una vez a la semana, ya que no te quieres enfrentar a una situación en donde tengas que restaurar tus respaldo y saber que tenias algún error y no poder hacerlo - Aug 14, 12
El RDBMS de Oracle tiene dentro de sus métodos de autenticación una manera llamada usuario proxy, este usuario lo que te permite hacer es poder conectarte con las credenciales que tu tienes (usuario y contraseña que tu ya conoces) y ejercer acciones en un usuario en el que tu no tienes contraseña.
Y te vas a preguntar, pues para que me sirve esto si ya soy un super usuario, bueno una de las primeras cosas que se me viene a la mente es crear un database link privado, ya que la unica manera de crearlo es si estas conectado como el usuario que requiere el database link :)
Aquí te voy a dar un ejemplo de como crear un usuario proxy y como usarlo, ya tu te divertirás con las cosas que puedes hacer con este método de autenticación.
Lo primero que voy a hacer es crear un llamado usuario_princi y un usuario llamado usuario_proxy, después de crear el usuario proxy, vas a ver que altero al usuario para darle un grant connect through, esto es lo que nos va a permitir conectarnos como un usuario proxyTESTDB> create user usuario_princi identified by 1234;
User created.
TESTDB> create user usuario_proxy identified by proxy1234;
User created.
TESTDB> grant create session, create table to usuario_princi;
Grant succeeded.
TESTDB> alter user usuario_princi grant connect through usuario_proxy;
User altered.
Una vez que creamos al usuario proxy, nos conectamos de la siguiente manera, vas a ver que aunque estoy usando el usuario y contraseña de usuario_proxy, mi esquema de contexto es usuario_princiTESTDB> connect usuario_proxy[usuario_princi]/proxy1234
Connected.
USER: USUARIO_PRINCI
SESSION ID: 45
CURRENT_SCHEMA: USUARIO_PRINCI
INSTANCE NAME: TESTDB
DATABASE ROLE: PRIMARY
OS USER: oracle
CLIENT IP ADDRESS:
SERVER HOSTNAME: servidor1
CLIENT HOSTNAME: servidor1.oracleenespanol.blogspot.com
PL/SQL procedure successfully completed.
TESTDB> select sys_context('userenv','current_user') from dual;
SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
USUARIO_PRINCI
TESTDB> select sys_context('userenv','proxy_user') from dual;
SYS_CONTEXT('USERENV','PROXY_USER')
--------------------------------------------------------------------------------
USUARIO_PROXY
Ahora como ejemplo, vamos a crear una tabla y vas a ver que esta tabla se crea en el usuario_princi, no en el usuario_proxy que fue con el que nos conectamosTESTDB> create table prueba (id number);
Table created.
TESTDB> select owner from all_tables where table_name='PRUEBA';
OWNER
---------------
USUARIO_PRINCI
Ya nada mas para verificar , audite a la sesión y las acciones de crear una tabla para los usuarios y el resultado es esteTESTDB> AUDIT CREATE TABLE BY usuario_proxy ON BEHALF OF usuario_princi;
Audit succeeded.
TESTDB> AUDIT CREATE TABLE BY usuario_princi BY ACCESS;
Audit succeeded.
TESTDB> SELECT
2 tr2.username PROXY,
3 tr1.username USERNAME,
4 tr1.extended_timestamp,
5 tr1.owner,
6 tr1.obj_name,
7 tr1.action_name,
8 tr1.returncode ,
9 tr1.SESSIONID,
10 tr1.PROXY_SESSIONID
11 FROM dba_audit_trail tr1,
12 dba_audit_trail tr2
13 WHERE tr1.obj_name not in ( 'DUAL','SQLPLUS_PRODUCT_PROFILE','PRODUCT_PRIVS')
14 AND tr1.proxy_sessionid=tr2.sessionid
15 AND tr2.action_name='PROXY AUTHENTICATION ONLY' and tr2.username='USUARIO_PROXY' and tr1.owner='USUARIO_PRINCI'
16 ORDER BY tr1.timestamp;
PROXY USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME RETURNCODE SESSIONID PROXY_SESSIONID
--------------- --------------- ------------------------- --------------- ---------- --------------- ---------- ---------- ---------------
USUARIO_PROXY USUARIO_PRINCI 14-AUG-12 04.31.30.650906 USUARIO_PRINCI PRUEBA CREATE TABLE 0 14900441 14900440
AM -07:00
Como te puedes dar cuenta este método te puede ayudar para crear un database link en el usuario o de igual manera le puede ayudar a tu desarrollador para que se conecte a un usuario y no tener que crear sinónimos. - Aug 01, 12
El otro día me tope con una situación en donde migre una base de datos, pero al migrarla todavía apuntaba a respaldos obsoletos que se encontraban en SBT, pero como ya estaba en el nuevo servidor, no podía borrar esta información, así que una manera de hacerlo es emular que tienes una librería de SBT, esto funciona para borrar o para hacer un respaldo, cualquiera de los dos, espero y te sirva este tip y lo uses para sacarle algún provecho.
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Aug 1 06:10:02 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (DBID=1229390655)
RMAN> @backup_tape.rmn
RMAN>
RMAN> run {
2> allocate channel t1 device type 'sbt'
3> parms='SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/backup)';
4> backup database;
5> }
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=31 device type=SBT_TAPE
channel t1: WARNING: Oracle Test Disk API
Starting backup at 01-AUG-12
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/testdb/datafile/sysaux01.dbf
input datafile file number=00001 name=+DATA/testdb/datafile/system01.dbf
input datafile file number=00004 name=+DATA/testdb/datafile/users01.dbf
input datafile file number=00003 name=+DATA/testdb/datafile/undotbs01.dbf
input datafile file number=00005 name=+DATA/testdb/datafile/example01.dbf
input datafile file number=00006 name=+DATA/testdb/datafile/APEX_1930613455248703.dbf
channel t1: starting piece 1 at 01-AUG-12
channel t1: finished piece 1 at 01-AUG-12
piece handle=03nhhf26_1_1 tag=TAG20120801T061014 comment=API Version 2.0,MMS Version 8.1.3.0
channel t1: backup set complete, elapsed time: 00:02:37
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel t1: starting piece 1 at 01-AUG-12
channel t1: finished piece 1 at 01-AUG-12
piece handle=04nhhf73_1_1 tag=TAG20120801T061014 comment=API Version 2.0,MMS Version 8.1.3.0
channel t1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-AUG-12
released channel: t1
RMAN> **end-of-file** - Jul 24, 12
El otro día estaba corriendo un respaldo de una base de datos que recientemente acababa ser clonada de otra base de datos. Y me aparece el siguiente error:
DBGRPC: EXITED krmqgns with status 1
DBGRPC: krmxpoq - returning rpc_number: 77 with status: FINISHED16 for channel CH10
DBGRPC: krmxr - channel CH10 calling peicnt
DBGRPC: krmxrpc - channel CH10 kpurpc2 err=19583 db=target proc=SYS.DBMS_BACKUP_RESTORE.BACKUPPIECECREATE excl: 187
DBGRPC: krmxrpc - caloing krmxtrim: with message of length 187: @@@ORA-19583: conversation terminated due to error
DBGRPC: ORA-19764: database id 8573487567 does not match database id 3024984012 in control file
DBGRPC: ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 1381
Y lo primero que hice fue verificar precisamente que el DBID de la base de datos fuera el correcto, ya que si haces una clonacion sin RMAN, el DBID se mantiene de la base de datos de origen y en el catalogo se crean conflictos, el cual no fue el caso.TESTDB21 >select dbid,name from v$database;
DBID NAME
---------- --------
3024984012 TESTDB2
La verdad me quebré un poco la cabeza, ya que la información que tenia el diccionario de datos, apuntaba a que el DBID era el correcto (3024984012) . Como tenia un poco el tiempo encima , no tuve mas tiempo para investigar el porque, ya que en el archivo trace que genere con RMAN, no aparece que esta información erronea viene del archivo de cambios de bloques (block change tracking file).TESTDB21 >select FHDBI,FHDBN from X$KRCFH
2 /
FHDBI FHDBN
---------- --------
8573487567 TESTDB2
Una vez que vi que era este archivo era el que estaba causando la falla, lo único que hice fue recrearlo y con esto se resolvió mi problema.TESTDB21 >alter database disable block change tracking;
Database altered.
TESTDB21 >select FHDBI,FHDBN from X$KRCFH;
no rows selected
TESTDB21 >alter database enable block change tracking using FILE '/copy01/TESTDB2/oracle/TESTDB2/ctf/TESTDB2.ctf';
Database altered.
TESTDB21 >select FHDBI,FHDBN from X$KRCFH;
FHDBI FHDBN
---------- --------
3024984012 TESTDB2
Espero que te sirva este tip si te llegas a enfrentar a este problema, de igual manera te anexo un enlace de un muy buen articulo de Alex Gorbachev del grupo Pythian describiendo el archivo BCT, te puede ayudar como me ayudo a mi :)
Oracle 10G Block Change Tracking Inside Out (INGLES) - Jul 16, 12
Este tip es un poco mas para mi, para tenerlo en un lugar que se que es fácil de encontrar, esto lo digo ya que puedes encontrar mucha información al respecto en Internet.
Pero si te sirve, pues que mejor :)
Cuando tratas de borrar en Unix todos los archivos de auditoría de una base de datos que acabas de decomisar, y te topas con el siguiente errororacle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/dba01/oracle/TESTDB/audit
oracle $ rm ./*.aud
ksh: /usr/xpg4/bin/rm: arg list too long
Lo que tienes que hacer es pasarle los archivos como argumento para evitar el error y de esta manera no tendrás este error. Nada mas asegurate que estas en el directorio correcto y cuidar mucho los espacios, no vayas a borrar algo que no quieres.oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ cd /mount/dba01/oracle/TESTDB/audit
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/dba01/oracle/TESTDB/audit
oracle $ find ./ -name "*.aud" -type f | xargs rm - Jul 12, 12
En un ambiente en donde tienes que estar refrescando base de datos seguido, una de las mejores maneras y mas seguras de borrar los archivos pertenecientes a la base de datos que vas a refrescar es con el comando DROP DATABASE.
Si estas en un ambiente de RAC, lo primero que tienes que hacer es cambiar el parámetro de cluster_database a FALSO y una vez que lo hayas hecho, apaga la base de datos.oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 11 22:30:21 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
TESTDB1 >alter system set cluster_database=FALSE scope=spfile;
System altered.
TESTDB1 >exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ srvctl stop database -d TESTDB
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ srvctl status database -d TESTDB
Instance TESTDB1 is not running on node servidor1
Instance TESTDB2 is not running on node servidor2
Ya que cerraste la base de datos,asegurate de que tienes las variables de ambiente correctas y vamos a abrirla en modo MOUNT y de ahí a habilitar una sesión restringida.oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ . $HOME/TESTDB
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 11 22:36:08 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 2622255104 bytes
Fixed Size 2231232 bytes
Variable Size 637535296 bytes
Database Buffers 1962934272 bytes
Redo Buffers 19554304 bytes
RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
using target database control file instead of recovery catalog
sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION
Ya lo unico que nos queda por hacer es tirar la base de datos, a mi no me gusta hacerlo con NOPROMPT, ya que es la ultima oportunidad que vas a tener para asegurarte de que estas borrando la base de datos correcta, pero ahora si en gustos se rompen generos.RMAN> DROP DATABASE INCLUDING BACKUPS;
database name is "TESTDB" and DBID is 1743232258
Do you really want to drop all backups and the database (enter YES or NO)?YES
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=82 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_3_788480833
2 2 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_4_788480833
3 3 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_5_788480834
4 4 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_2_788480832
5 5 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_6_788480835
6 6 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_7_788480837
7 7 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_8_788480838
8 8 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_9_788480839
9 9 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_1_788480832
10 10 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_10_788480839
11 11 1 1 AVAILABLE DISK /copy01/TESTDB/control/TESTDB_07_01_2012_788480872.ctl
12 12 1 1 AVAILABLE DISK /copy01/TESTDB/control/c-1743232258-20120701-00
13 13 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_16_788480924
14 14 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_17_788480924
15 15 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_18_788480926
16 16 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_15_788480923
17 17 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_13_788480923
18 18 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_14_788480923
19 19 1 1 AVAILABLE DISK /copy01/TESTDB/control/c-1743232258-20120701-01
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_3_788480833 RECID=1 STAMP=788480834
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_4_788480833 RECID=2 STAMP=788480834
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_5_788480834 RECID=3 STAMP=788480836
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_2_788480832 RECID=4 STAMP=788480833
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_6_788480835 RECID=5 STAMP=788480838
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_7_788480837 RECID=6 STAMP=788480838
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_8_788480838 RECID=7 STAMP=788480839
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_9_788480839 RECID=8 STAMP=788480839
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_1_788480832 RECID=9 STAMP=788480832
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_10_788480839 RECID=10 STAMP=788480840
deleted backup piece
backup piece handle=/copy01/TESTDB/control/TESTDB_07_01_2012_788480872.ctl RECID=11 STAMP=788480875
deleted backup piece
backup piece handle=/copy01/TESTDB/control/c-1743232258-20120701-00 RECID=12 STAMP=788480881
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_16_788480924 RECID=13 STAMP=788480924
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_17_788480924 RECID=14 STAMP=788480924
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_18_788480926 RECID=15 STAMP=788480926
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_15_788480923 RECID=16 STAMP=788480924
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_13_788480923 RECID=17 STAMP=788480923
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_14_788480923 RECID=18 STAMP=788480923
deleted backup piece
backup piece handle=/copy01/TESTDB/control/c-1743232258-20120701-01 RECID=19 STAMP=788480947
Deleted 19 objects
released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=82 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name TESTDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
14 2 4 A 11-JUL-12
Name: +ARCH/TESTDB/archivelog/TESTDB_4_2_788478082.dbf
deleted archived log
archived log file name=+ARCH/TESTDB/archivelog/TESTDB_4_2_788478082.dbf RECID=14 STAMP=788481092
Deleted 1 objects
database name is "TESTDB" and DBID is 1743232258
database dropped - Jul 10, 12
Aquí un tip rápido para cuando te topas con el error ORA-00600: [2662], este error significa que el SCN de un bloque tiene un numero mayor al del SCN de la base de datos , esto puede significar que existe una corrupción física en tu base de datos , aquí no te voy a decir como solventarlo, ya que es mas bien para un SR con Oracle dependiendo del caso y versión de RDBMS que tienes.
Pero algo que te puede ayudar a ver donde estas teniendo el error es lo siguiente, cuando te aparece el error, el quinto argumento te puede dar esta informaciónORA-00600: internal error code, arguments: [2662], [2910], [829087674], [2910], [829528307], [1451708231], [], [], [], [], [], []
Si en el error tienes el quinto argumento, ya que hay casos en el que no lo vas a tener, puedes usar la siguiente función para localizar el datafile al que se esta refiriendoTESTDB> select dbms_utility.data_block_address_file(1451708231) "fileno",dbms_utility.data_block_address_block(1451708231) from dual ;
fileno DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(1451708231)
---------- -------------------------------------------
138 3
Con este puedes identificar que archivo es el que te esta causando el problemaTESTDB> Select name ,file#,ts# from v$datafile where rfile#=138 ;
Y con la utileria dbverify puedes identificar si es que realmente esta corrupto o nodbv file=<NOMBRE_DEL_DATAFILE_OBTENIDO_ARRIBA> blocksize=<db_block_size>
Como te decía al principio, lo que te recomiendo es abrir un SR con Oracle y poder llegar al fondo del problema, pero esto te da armas para poder localizar donde esta el error. - Jul 05, 12
Se que esto no tiene mucho que ver con base de datos, pero es un tip que quiero mantener a la mano y que me ha ayudado para resolver rápidamente un problema de concatenar muchas celdas y no meterme en muchos embrollos.
Vamos a decir que quieres concatenar en una celda el rango de A1 a A5 separados por una coma- Posicionate en la celda B1.
- Teclea =TRANSPOSE(A1:A5)&", " en la barra de formulas
- Aprieta la tecla F9.
- Borra los brackets que se pusieron al principio y al final.
- Teclea =Concatenate( al principio de la barra de formulas
- Teclea ) al final de la barra de formulas
- Aprieta Enter
- Jul 05, 12
Uno de las grandes desventajas que tenemos como DBAs es que mucho de nuestro trabajo son a deshoras, y de igual manera requieren de scripts que toman a veces dos o tres horas en correr, asi que si son las dos de la mañana, tres horas de poder dormir a nadie le caen nada mal, especialmente si lo unico que tienes que es estar monitoreando y asegurandote que un script corra bien.
Asi que una manera para que te puedas ir a dormir y nada mas despertarte es si llega a fallar el script
Lo primero que ahi que hacer, y esto es importante que en el ambiente que lo hagas, tengas el permiso para poder hacerlo, es poner el password en un archivo de texto y con permisos 400oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/dba01/oracle/TESTDB/scripts
oracle $ ls -ltr passfile.txt
-r-------- 1 oracle oinstall 13 Jun 30 20:58 passfile.txt
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/dba01/oracle/TESTDB/scripts
oracle $ cat passfile.txt
hr:12345
En un script de shell de unix vas a poner lo que esta abajo. ${HOME}/bin/TESTDB
HR_PASS=`cat /mount/dba01/oracle/DBATEST/admin/passfile.txt | grep hr | cut -d: -f2`
LOG_DIR=${SID_HOME}/audit
sqlplus /nolog <<EOF > ${LOG_DIR}/test.log
connect hr/${HR_PASS}
select sysdate from dual;
exit
EOF
oracle_error=`cat ${LOG_DIR}/test.log | grep "ORA-" | wc -l`
if [ ${oracle_error} -gt 0 ]; then
echo "###############################################################"
echo "## ERROR ====> Unable to get the Date"
cat ${LOG_DIR}/test.log | grep ORA- | awk '{print "##", $0}'
echo "###############################################################"
mailx -s "Error in Getting the Time" rene.antunez@blogspot.com < ${LOG_DIR}/test.log
exit 1
fi
Ya una vez que tengas tu shell script, ya nada mas lo tienes que mandar en modo nohup y te puedes ir a dormir o comer en lo que corre tu script, si llega a fallar, te llegaria un correo con el error y donde es que fallo. De igual manera, puedes cambiar el select por un script de sql.oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/dba01/oracle/TESTDB/scripts
oracle $ nohup ./test.sh & - Jun 19, 12
Peoplesoft tiene una peculiaridad que no me gusto mucho, una de las maneras que tienen para modificar la estructura de una tabla es que crea una tabla con un nombre temporal, de ahí hace un insert into de un select , y de aquí lo que hace es tira la tabla original y renombra la tabla temporal al nombre de la tabla original.
Antes de hacer todo esto es altamente recomendable tener un buen respaldo de tu base de datos, si es una tabla pequeña , con un export puede bastar, pero si es una tabla bastante grande un export puede ser tardado y un poco latoso.
El otro día tuve que estar al pendiente de unos cambios hechos de esta manera, como eran pocas tablas con un tamaño relativamente chico de 10 millones de entradas cada una , así que me asegure que el respaldo de la base de datos fuera consistente y también para mi salud mental, tome un export, aunque me tome un poco de espacio y tiempo, quería que cualquier cosa poder recuperar rápido y sin tener que recuperar toda la base de datos, cabe decir que eran catálogos, así que el equipo funcional y yo sabíamos que los datos no iban a cambiar mientras los exportabamos.
Antes de empezar , me asegure que el recyclebin estuviera prendidoTESTDB> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
Al correr uno de los scripts, la Ley de Murphy entro en accion, este trono a la mitad por que uno de los cambios estructurales que se estaba haciendo era pasar un campo a que tuviera la restricción de NOT NULL, pero el script continua aunque haya fallado ahí, así que tiro la tabla original , y renombro la tabla con nombre temporal al que tenia la original, quedandos con una tabla vacía, como se ve abajo :TESTDB> create table temporal(id number,name varchar2(30) not null);
Table created.
TESTDB> insert into temporal (id,name) select id,name from test;insert into temporal (id,name) select id,name from test
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("RENE"."TEMPORAL"."NAME")
TESTDB> drop table TEST;Table dropped.
TESTDB> rename TEMPORAL to TEST;Table renamed.
TESTDB> select count(1) from TEST;
COUNT(1)
----------
0
En lugar de ponerme a restaurar la tabla del respaldo que habia tomado antes de empezar, lo unico que tuve que hacer es identificar en el recyclebin la tabla que habia tirado, tirar la tabla que se habia renombrado de TEMPORAL a TEST en el script anterior, ya que esta era la tabla sin datos y de ahi hacer un flashback before drop para que TEST volviera a tener los datos antes del comando DROPTESTDB> SELECT object_name,original_name,droptime
2 FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ -------------------------------- -------------------
BIN$wtQeQ7eoQJ7gQAAAAAAbEQ==$0 TEST_IDX 2012-06-19:06:27:01
BIN$wtQeQ7epQJ7gQAAAAAAbEQ==$0 TEST 2012-06-19:06:27:01
TESTDB> drop table TEST;
Table dropped.
TESTDB> FLASHBACK TABLE "BIN$wtQeQ7epQJ7gQAAAAAAbEQ==$0" TO BEFORE DROP;
Flashback complete.
TESTDB> select count(1) from TEST;
COUNT(1)
----------
3
En versiones anteriores a 11.2.0.2 si mal no recuerdo, todavia si existia un indice de la tabla , tambien tenias que sacarlo del recyclebin, y es tan facil como renombrar al indice, en 11.2.0.2, los indices se recuperan con el flashback.
Esta tecnica es muy buena para cuando haz hecho un drop de una tabla y rapidamente necesitas recuperarla, espero te ayude como a mi lo ha hecho en ciertas ocasiones. - Feb 17, 13
El otro día tratando de recuperar una Base de Datos con RMAN en 11.2.0.3 y RHEL 5 hasta un punto en el tiempo, todo iba funcionando como debería de ser, los datafiles se recuperaron, se aplico los archived redo logs y en el momento de tratar de abrir la base de datos con open resetlogs, me salio, como diria "Chabelo", una espantosisima y tremenda "X", o sea un error ORA-00600.
Y de ahí ya no pude abrir la base de datos, hasta que hice los pasos que hago abajo :(RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 05/31/2012 20:40:47
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2898], [6], [12], [], [], [], [], [], [], [], [], []
Process ID: 17480
Session ID: 443 Serial number: 3Aquí no voy a entrar a detalle de como sucedió el error, si no mas bien como lo puedes solucionar si se te llega a presentar.
Lo que si te voy a decir es que tiene que ver que tiene que ver con las encarnaciones que se encuentran presentes en el controfile y este error lo he podido replicar cuando después de tener varias encarnaciones, hago un reset database to incarnation N;RMAN-06246: List of Database Incarnations
RMAN-06247: DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
RMAN-06248: ------- ------- -------- ---------------- --- ---------- ----------
RMAN-06249: 1 1 TESTDB 3889289065 PARENT 12296983831137 16-APR-2012 23:58:59
RMAN-06249: 2 2 TESTDB 3889289065 PARENT 12462184333086 09-MAY-2012 22:50:31
RMAN-06249: 3 3 TESTDB 3889289065 ORPHAN 12462184341387 10-MAY-2012 20:16:41
RMAN-06249: 5 5 TESTDB 3889289065 PARENT 12462184341387 11-MAY-2012 02:57:04
RMAN-06249: 4 4 TESTDB 3889289065 ORPHAN 12462184344923 10-MAY-2012 22:11:55
RMAN-06249: 6 6 TESTDB 3889289065 CURRENT 12462187527879 28-MAY-2012 22:20:17
RMAN-08066: database reset to incarnation 5
RMAN-06246: List of Database Incarnations
RMAN-06247: DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
RMAN-06248: ------- ------- -------- ---------------- --- ---------- ----------
RMAN-06249: 1 1 TESTDB 3889289065 PARENT 12296983831137 16-APR-2012 23:58:59
RMAN-06249: 2 2 TESTDB 3889289065 PARENT 12462184333086 09-MAY-2012 22:50:31
RMAN-06249: 3 3 TESTDB 3889289065 ORPHAN 12462184341387 10-MAY-2012 20:16:41
RMAN-06249: 5 5 TESTDB 3889289065 CURRENT 12462184341387 11-MAY-2012 02:57:04
RMAN-06249: 4 4 TESTDB 3889289065 ORPHAN 12462184344923 10-MAY-2012 22:11:55
RMAN-06249: 6 6 TESTDB 3889289065 ORPHAN 12462187527879 28-MAY-2012 22:20:17
Como te decía no me voy a concentrar a decirte como replicarlo, si no como tener una manera de solucionarlo, ya que no te vas a encontrar con mucha información con respecto a este error, ni siquiera en MOS.
Lo primero que hay que hacer es restaurar la base de datos hasta el punto en el tiempo que necesitas, en el ejemplo que te presento también recupere el archivo de parámetros así como el controlfile del respaldo.
No se te olvide antes de iniciar con RMAN tener la variable de ambiente de UNIX NLS_DATE_FORMAT puesta,
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
De igual manera, te va a servir esta variable
export ORA_RMAN_SGA_TARGET=1000
Para ponerlo por puntos separados esto fue lo que hice en la primera parte, en un archivo llamado restore_TESTDB_A.rmn:- Restaurara el archivo de parametros del respaldo.
- Reiniciar la Base de datos después de restaurar el archive de parametros para que tomara el spfile que acabo de restablecer.
- Cambiar el parámetro cluster_database a FALSE (Solamente RAC)
- Restaurar el controlfile del respaldo
- Reiniciar la base de datos para que el parámetro de cluster_database se tome en cuenta, ya que no es un parametro dinamico.
- Cambiar a la Quinta encarnación de la Base de Datos, ya que ahi es donde es a donde quiero llegar
- Restaurar la Base de Datos al punto en el tiempo que quiero llegar
Aquí te presento el log de la primera etapa, así como el script que se corrió, lo corte un poco para que no tomara tanto espacio, pero si lo quise poner para que supieras que pasoRecovery Manager: Release 11.2.0.3.0 - Production on Sat Jun 2 20:43:25 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-06193: connected to target database (not started)
RMAN> run
2> {
3> set dbid 3889289065;
4> startup nomount ;
5> restore spfile from '/mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120528-02';
6> shutdown immediate;
7> startup nomount ;
8> sql "alter system set cluster_database = FALSE scope = spfile";
9> restore controlfile from '/mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120528-02';
10> alter database mount;
11> shutdown immediate;
12> startup mount;
13> }
14>
15> list incarnation;
16> reset database to incarnation 5;
17> list incarnation;
18> list backup summary;
19> crosscheck backup device type disk;
20>
21>
22> RUN
23> {
24> sql "alter session set max_dump_file_size=''UNLIMITED''";
25> sql "alter session set events ''10046 trace name context forever, level 12''";
26> set until time "to_date('28-MAY-2012 20:46:43','dd-MON-yyyy hh24:mi:ss')";
27> ALLOCATE CHANNEL CH1 DEVICE TYPE DISK ;
28> ALLOCATE CHANNEL CH2 DEVICE TYPE DISK ;
29> ALLOCATE CHANNEL CH3 DEVICE TYPE DISK ;
30> ALLOCATE CHANNEL CH4 DEVICE TYPE DISK ;
31> ALLOCATE CHANNEL CH5 DEVICE TYPE DISK ;
32> RESTORE DATABASE;
33> }
34>
RMAN-03023: executing command: SET DBID
RMAN-04014: startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+GBS_HRMS_DATA/TESTDB/PARAMETERFILE/spfileTESTDB.ora'
ORA-17503: ksfdopn:2 Failed to open file +GBS_HRMS_DATA/TESTDB/PARAMETERFILE/spfileTESTDB.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +GBS_HRMS_DATA/TESTDB/parameterfile/spfileTESTDB.ora
ORA-15173: entry 'spfileTESTDB.ora' does not exist in directory 'parameterfile'
ORA-06512: at line 4
RMAN-04024: starting Oracle instance without parameter file for retrieval of spfile
RMAN-06196: Oracle instance started
Total System Global Area 1043886080 bytes
Fixed Size 2234960 bytes
Variable Size 452986288 bytes
Database Buffers 566231040 bytes
Redo Buffers 22433792 bytes
RMAN-03090: Starting restore at 02-JUN-2012 20:43:44
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=1861 device type=DISK
RMAN-08554: channel ORA_DISK_1: restoring spfile from AUTOBACKUP /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120528-02
RMAN-08541: channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
RMAN-03091: Finished restore at 02-JUN-2012 20:43:47
RMAN-06402: Oracle instance shut down
RMAN-06193: connected to target database (not started)
RMAN-06196: Oracle instance started
Total System Global Area 2622255104 bytes
Fixed Size 2231232 bytes
Variable Size 1409287232 bytes
Database Buffers 1174405120 bytes
Redo Buffers 36331520 bytes
RMAN-06162: sql statement: alter system set cluster_database = FALSE scope = spfile
RMAN-03090: Starting restore at 02-JUN-2012 20:44:14
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08605: channel ORA_DISK_1: SID=521 instance=TESTDB1 device type=DISK
RMAN-08021: channel ORA_DISK_1: restoring control file
RMAN-08180: channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
RMAN-08505: output file name=+GBS_HRMS_DATA/TESTDB/controlfile/control01.ctl
RMAN-08505: output file name=+GBS_HRMS_DATA/TESTDB/controlfile/control02.ctl
RMAN-03091: Finished restore at 02-JUN-2012 20:44:17
RMAN-06199: database mounted
RMAN-08031: released channel: ORA_DISK_1
RMAN-06404: database dismounted
RMAN-06402: Oracle instance shut down
RMAN-06193: connected to target database (not started)
RMAN-06196: Oracle instance started
RMAN-06199: database mounted
Total System Global Area 2622255104 bytes
Fixed Size 2231232 bytes
Variable Size 1409287232 bytes
Database Buffers 1174405120 bytes
Redo Buffers 36331520 bytes
RMAN-06246: List of Database Incarnations
RMAN-06247: DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
RMAN-06248: ------- ------- -------- ---------------- --- ---------- ----------
RMAN-06249: 1 1 TESTDB 3889289065 PARENT 12296983831137 16-APR-2012 23:58:59
RMAN-06249: 2 2 TESTDB 3889289065 PARENT 12462184333086 09-MAY-2012 22:50:31
RMAN-06249: 3 3 TESTDB 3889289065 ORPHAN 12462184341387 10-MAY-2012 20:16:41
RMAN-06249: 5 5 TESTDB 3889289065 PARENT 12462184341387 11-MAY-2012 02:57:04
RMAN-06249: 4 4 TESTDB 3889289065 ORPHAN 12462184344923 10-MAY-2012 22:11:55
RMAN-06249: 6 6 TESTDB 3889289065 CURRENT 12462187527879 28-MAY-2012 22:20:17
RMAN-08066: database reset to incarnation 5
RMAN-06246: List of Database Incarnations
RMAN-06247: DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
RMAN-06248: ------- ------- -------- ---------------- --- ---------- ----------
RMAN-06249: 1 1 TESTDB 3889289065 PARENT 12296983831137 16-APR-2012 23:58:59
RMAN-06249: 2 2 TESTDB 3889289065 PARENT 12462184333086 09-MAY-2012 22:50:31
RMAN-06249: 3 3 TESTDB 3889289065 ORPHAN 12462184341387 10-MAY-2012 20:16:41
RMAN-06249: 5 5 TESTDB 3889289065 CURRENT 12462184341387 11-MAY-2012 02:57:04
RMAN-06249: 4 4 TESTDB 3889289065 ORPHAN 12462184344923 10-MAY-2012 22:11:55
RMAN-06249: 6 6 TESTDB 3889289065 ORPHAN 12462187527879 28-MAY-2012 22:20:17
RMAN-03090: Starting implicit crosscheck backup at 02-JUN-2012 20:45:05
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=456 device type=DISK
RMAN-08030: allocated channel: ORA_DISK_2
RMAN-08500: channel ORA_DISK_2: SID=469 device type=DISK
RMAN-06206: Crosschecked 25 objects
RMAN-03091: Finished implicit crosscheck backup at 02-JUN-2012 20:45:13
RMAN-03090: Starting implicit crosscheck copy at 02-JUN-2012 20:45:13
RMAN-12016: using channel ORA_DISK_1
RMAN-12016: using channel ORA_DISK_2
RMAN-03091: Finished implicit crosscheck copy at 02-JUN-2012 20:45:14
RMAN-07501: searching for all files in the recovery area
RMAN-07507: cataloging files...
RMAN-07512: no files cataloged
RMAN-08031: released channel: ORA_DISK_1
RMAN-08031: released channel: ORA_DISK_2
RMAN-08030: allocated channel: CH1
RMAN-08500: channel CH1: SID=456 device type=DISK
RMAN-08030: allocated channel: CH2
RMAN-08500: channel CH2: SID=469 device type=DISK
RMAN-08030: allocated channel: CH3
RMAN-08500: channel CH3: SID=482 device type=DISK
RMAN-08030: allocated channel: CH4
RMAN-08500: channel CH4: SID=495 device type=DISK
RMAN-08030: allocated channel: CH5
RMAN-08500: channel CH5: SID=508 device type=DISK
RMAN-03090: Starting restore at 02-JUN-2012 20:45:26
RMAN-08016: channel CH1: starting datafile backup set restore
.
.
.
RMAN-08180: channel CH3: restore complete, elapsed time: 00:03:45
RMAN-03091: Finished restore at 03-JUN-2012 20:53:01
RMAN-08031: released channel: CH1
RMAN-08031: released channel: CH2
RMAN-08031: released channel: CH3
RMAN-08031: released channel: CH4
RMAN-08031: released channel: CH5
Recovery Manager complete.Ya cuando haya finalizado la restauración de los datafiles, lo que tenemos que hacer es recrear el control file tomando un respaldo hacia un archivo plano. Aquí es una parte muy delicada que yo te recomendaría hacer un listado de tus respaldos en RMAN (list backup) y guardar un log, ya que al recrear el controlfile, vas a perder esta información si es que estas usando tu controlfile para respaldos.oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/dba01/oracle/TESTDB/scripts
oracle $ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 2 21:03:24 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
TESTDB1 >ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/mount/dba01/oracle/TESTDB/scripts/control_TESTDB.sql' RESETLOGS;
Database altered.
TESTDB1 >shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.Yo lo que hago en lugar de borrar el controlfile actual lo renombro ,si no estas usando ASM, seria con el comando mv, si estas usando ASM seria de la siguiente manera con el usuario grid y entrando a la instancia de ASMALTER DISKGROUP GBS_HRMS_DATA RENAME ALIAS '+GBS_HRMS_DATA/DBATEST/CONTROLFILE/control02.ctl'
TO '+GBS_HRMS_DATA/DBATEST/CONTROLFILE/control02.ctl.old';Una vez que moviste los controlfiles , modifica el archivo plano del conrolfile que creamos arriba, y corre el script en sqlplusoracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/dba01/oracle/TESTDB/scripts
oracle $ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 2 21:06:39 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
TESTDB1 >@control_TESTDB.sql
ORACLE instance started.
Total System Global Area 2622255104 bytes
Fixed Size 2231232 bytes
Variable Size 1409287232 bytes
Database Buffers 1174405120 bytes
Redo Buffers 36331520 bytes
Control file created.
TESTDB1 >exit
Ahora catalogamos el respaldo, ya que como te mencionaba anteriormente, esta información se perdióoracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/dba01/oracle/TESTDB/scripts
oracle $ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jun 2 21:13:10 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (DBID=3889289065, not open)
RMAN> catalog start with '/mount/copy01/TESTDB/oracle/TESTDB' noprompt;
using target database control file instead of recovery catalog
searching for all files that match the pattern /mount/copy01/TESTDB/oracle/TESTDB
List of Files Unknown to the Database
=====================================
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_64_784500419
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_65_784500419
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_66_784500419
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_67_784500419
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_68_784500420
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_69_784500421
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_70_784500437
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_71_784500437
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_72_784500494
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_73_784500510
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/TESTDB_05_28_2012_784500375.ctl
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120528-00
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120528-01
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120510-04
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120510-00
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120511-00
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120528-02
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120531-00
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_52_784500223
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_53_784500224
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_54_784500224
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_55_784500225
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_56_784500226
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_57_784500226
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_58_784500228
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_59_784500230
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_60_784500231
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_61_784500232
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_64_784500419
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_65_784500419
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_66_784500419
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_67_784500419
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_68_784500420
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_69_784500421
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_70_784500437
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_71_784500437
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_72_784500494
File Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_05282012_1_73_784500510
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/TESTDB_05_28_2012_784500375.ctl
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120528-00
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120528-01
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120510-04
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120510-00
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120511-00
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120528-02
File Name: /mount/copy01/TESTDB/oracle/TESTDB/control/c-3889289065-20120531-00
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_52_784500223
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_53_784500224
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_54_784500224
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_55_784500225
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_56_784500226
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_57_784500226
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_58_784500228
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_59_784500230
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_60_784500231
File Name: /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_05282012_1_61_784500232Y por ultimo, lo que tenemos que hacer es recuperar la base de datos al mismo punto en tiempo que restauramos los datafiles y como ves marcado en rojo abajo, pudimos abrir la base de datos y regresamos a un estado de paz y tranquilidad :)RMAN> RUN
2> {
3> set until time "to_date('28-MAY-2012 20:46:43','dd-MON-yyyy hh24:mi:ss')";
4> ALLOCATE CHANNEL CH1 DEVICE TYPE DISK ;
5> ALLOCATE CHANNEL CH2 DEVICE TYPE DISK ;
6> ALLOCATE CHANNEL CH3 DEVICE TYPE DISK ;
7> ALLOCATE CHANNEL CH4 DEVICE TYPE DISK ;
8> ALLOCATE CHANNEL CH5 DEVICE TYPE DISK ;
9> RECOVER DATABASE;
10> ALTER DATABASE OPEN RESETLOGS;
11> }
archived log file name=+GBS_HRMS_DATA/TESTDB/archivelog/1_32_782967424.dbf thread=1 sequence=0
archived log file name=+GBS_HRMS_DATA/TESTDB/archivelog/2_16_782967424.dbf thread=2 sequence=16
archived log file name=+GBS_HRMS_DATA/TESTDB/archivelog/1_33_782967424.dbf thread=1 sequence=33
media recovery complete, elapsed time
Finished recover at 02-JUN-2012 21:18:49
database opened
released channel
released channel
released channel
released channel
released channel
Recovery Manager complete. - May 31, 12
El día de hoy , un compañero me contacto que estaba teniendo un problema al tratar de correr nuestros scripts de respaldo, que han estado funcionando sin problema en otros servidores.
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ rman target system@TESTDB
Recovery Manager: Release 11.2.0.3.0 - Production on Wed May 30 21:23:56 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
target database Password:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12537: TNS:connection closed
Lo primero que pensé es que el archivo de password estaba mal en el $RDBMS_ORACLE_HOME/dbs, lo cual no fue el caso.
Cabe decir que esta granja de servidores es nuevo, así que era la primera vez que estábamos haciendo pruebas de respaldos aquí.
Después de quebrarme un rato la cabeza buscando el problema en el sqlnet.ora , tnsnames.ora y el listener, me di cuenta de que el problema se encontraban en los permisos del binario de "oracle".
Ya que como sabes en RAC, el listener del SCAN pertenece al dueño del GI_HOME, que normalmente es el usuario grid, y de igual manera entre el usuario grid y el usuario oracle , el grupo de los binarios es el asmadmin.
Para diagnosticar el problema fue que como el usuario grid, tratamos de correr el comando de unix "ls", y me di cuenta que el listener no podía acceder a este archivo binario:grid@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/grid
grid $ ls -l $RDBMS_ORACLE_HOME/bin/oracle
ls: /mount/oracle/product/11.2.0.3v1/bin/oracle: Permission denied
Una vez que me di cuenta de este problema, lo único que tenia que hacer es cambiar el grupo y los permisos del archivo binario "oracle" y de igual manera asegurarme que el bit suid del archivo sea el correcto, teniendo en si permisos 6751, y esto tiene que ser en todos los RBDMS_ORACLE_HOME de la granja de servidores que tienesoracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ cd $ORACLE_HOME/bin
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/oracle/product/11.2.0.3v1/bin
oracle $ ls -lart oracle
-rwsr-s--x 1 oracle dba 232399431 Mar 17 07:57 oracle
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/oracle/product/11.2.0.3v1/bin
oracle $ chgrp asmadmin oracle
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/oracle/product/11.2.0.3v1/bin
oracle $ chmod 6751 oracle
Cuando cambiamos los permisos de este archivo, no tuvimos ningún problema en acceder vía el listener, así que no se te olvide este paso cuando estas instalando manualmente el RDBMS_ORACLE_HOME:oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ rman target system@TESTDB
Recovery Manager: Release 11.2.0.3.0 - Production on Wed May 30 21:23:56 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: TESTDB (DBID=643988434)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TESTDB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 35 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mount/copy01/TESTDB/oracle/TESTDB/control/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/mount/copy01/TESTDB/oracle/TESTDB/full/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/mount/copy01/TESTDB/oracle/TESTDB/control/snapcf_TESTDB.f'; - May 24, 12
Hace como año y medio escribi una entrada de como clonar un ORACLE_HOME en Solaris, y ahora que ando con RAC, me tope que clonar el OH para esta tecnologia cambia un poco.
Como siempre, lo primero que te voy a decir es que cumplas con todos los prerequisitos antes de cualquier instalacion
Prerequisitos para instalar Oracle RDBMS 11gR2 Linux
Lo primero que tienes que hacer es un archivo TAR de tu OHoracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ cd /mount/oracle/product/11.2.0.3v1
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/oracle/product/11.2.0.3v1
oracle $tar -cvf /mount/copy01/11.2.0.3v1_GOLD.tar .
Una vez que tienes tu archivo TAR, copialo al servidor en donde vayas a hacer tu clonacion, en mi caso nada mas voy a hacerlo en el mismo servidor, pero crear un segundo OH.
Esto lo tienes que hacer para cada nodo en donde vayas a clonar tu OH, o sea que si tienes 10 nodos, en cada uno de los 10 nodos lo tienes que hacer :)oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ cd /mount/oracle/product/11.2.0.3_CLONED
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/oracle/product/11.2.0.3_CLONED
oracle $ tar -xvf /mount/copy01/11.2.0.3v1_GOLD.tar
Ya que descomprimiste tu OH en cada uno de los nodos, asegurate de cambiar los permisos al usuario y al grupo que pertenece la instalacion, en mi caso es oracle:oinstalloracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/oracle/product/11.2.0.3_CLONED
oracle $ cd /mount/oracle/product
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/oracle/product
oracle $ chown -R oracle:oinstall 11.2.0.3_CLONED
No se te olvide, si es necesario, borrar los *.ora de $ORACLE_HOME/network/admin y los init/spfiles y orapw de $ORACLE_HOME/dbs
Ahora lo que tenemos que hacer es construir un pequeño archivo llamado clonar_oh.sh y correrlo, va contener lo siguienteORACLE_BASE=/mount/oracle
ORACLE_HOME=/mount/oracle/product/11.2.0.3_CLONED
THISNODE=`hostname -s`
cd $ORACLE_HOME/clone/bin
E01=ORACLE_HOME=/mount/oracle/product/11.2.0.3_CLONED
E02=ORACLE_HOME_NAME=ORA11g203_RDBMS_v1
E03=ORACLE_BASE=/mount/oracle
E04=OSDBA_GROUP=dba
E05=OSOPER_GROUP=oper
C01="CLUSTER_NODES={servidor1.oracleenespanol.blogspot.com,servidor2.oracleenespanol.blogspot.com}"
C02="LOCAL_NODE=$THISNODE"
echo $E01
echo $E02
echo $E03
echo $E04
echo $E05
echo $C01
echo $C02
perl $ORACLE_HOME/clone/bin/clone.pl $E01 $E02 $E03 $E04 $E05 $C01 $C02
Cuando finaliza el script clonar_oh.sh, no se te olvide correr en cada nodo el script $ORACLE_HOME/root.sh que genera la clonacion., esto tiene que ser como el usuario root
De igual manera tenemos que actualizar los nodos del OH en dado caso de que haya cambiado la locacion de tu OHoracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ cd $ORACLE_HOME/oui/bin
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/oracle/product/11.2.0.3_CLONED/oui/bin
oracle $./runInstaller -updateNodeList CLUSTER_NODES=servidor1.oracleenespanol.blogspot.com,servidor2.oracleenespanol.blogspot.com ORACLE_HOME=/mount/oracle/product/11.2.0.3_CLONED
Ya para terminar, lo unico que tenemos que hacer, es cambiar los permisos y dueño para el binario "oracle" del RDBMSoracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ su -grid
grid@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/grid
grid $ $GRID_ORACLE_HOME/bin/setasmgidwrap o=/mount/oracle/product/11.2.0.3_CLONED/bin/oracle
Espero que esto te sirva para cuando quieras clonar tu ORACLE_HOME de tu RDBMS en RAC.
-
Posts
- Apr 22, 13
It is not uncommon for Oracle to have something undocumented , and this is just another case of this ailment.
We have a database in which we have two copies of the same backup, one we keep in the FRA, the other one in an external disk, which from time to time gets deleted from this second disk. But what happens after you do a crosscheck and one of those 2 backups is expired? The one in the FRA is available and the one in the external disk is expired for RMAN, but how will you see it in your listing?
Looking at the 11.2 documentation for the LIST command , it only tells you that the Status of a backup can be AVAILABLE, UNAVAILABLE, or EXPIRED, but as you can see below that is not the case with our database :)RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1986 B F A DISK 17-APR-13 1 2 NO *
1987 B F M DISK 17-APR-13 1 2 NO *
1988 B F A DISK 17-APR-13 1 2 NO *
1989 B F A DISK 19-APR-13 1 1 NO TAG000661
1990 B A A DISK 19-APR-13 1 1 YES BUP_ARCH_FRA
When I first faced this, it got me curious as to what the meaning of the M status meant (and I still don’t know the exact meaning, but I have my supposition) . If I did a trace of my list backup summary command , I could see exactly what I knew it was happening:pythian@oracleenespanol.local /home/pythian/working/antunez
pythian $ rman target / debug trace rman.trc
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Apr 19 16:02:56 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: TESTDB (DBID=1834739652)
RMAN> list backup summary;
This is a small result of the trace done above :DBGMISC: 339 BS (datafile) key=1987 recid=1987 stamp=812962808 setstamp=812962807 setcount=2021
DBGMISC: level=1 level_i=-1 piececount=1 keepopts=0, site_key=0 [16:03:27.544]
DBGMISC: site_key=0 [16:03:27.544]
DBGMISC: chid=NIL parm=NIL [16:03:27.544]
DBGMISC: flags= [16:03:27.544]
DBGMISC: valid backup set list is [16:03:27.544]
DBGMISC: 1 VBS copy#=1 tag=BACKUP_CONTROLFILE_FRA deviceType=DISK status=A
DBGMISC: 1 BPIECEX key=3502 recid=3502 stamp=812962808
DBGMISC: bskey=1987 set_stamp=812962807 set_count=2021 site_key=0
DBGMISC: pieceno=1 handle=+FRA/test/backupset/2013_04_17/ncnnf0_backup_controlfile_fra_0.297.812962809
DBGMISC: device=DISK krmkch { count=0 found=FALSE }
DBGMISC: 2 VBS copy#=2 tag=BACKUP_OF_BACKUPSET deviceType=DISK status=X
DBGMISC: 1 BPIECEX key=3538 recid=3538 stamp=813024283
DBGMISC: bskey=1987 set_stamp=812962807 set_count=2021 site_key=0
DBGMISC: pieceno=1 handle=/backupdisk/TEST_bck/db_bckset_backup_TEST2_04-18-2013_v5o79kvn_1_2.rman
DBGMISC: device=DISK krmkch { count=0 found=FALSE }
DBGMISC: restore target list is [16:03:27.545]
DBGMISC: 1 ACT type=full fromSCN=0 toSCN=307822519 fno=0
DBGMISC: CURCF
As you can see above, one copy is EXPIRED and the other one is AVAILABLE, also because each copy has a different tag name, when you list them, you have an asterisk “*” for a tag of the backupset with Key 1987.
Both of these behaviours are perfectly normal, but you won’t find them anywhere in Oracle’s Documentation, so the next time you see this in your list command, you will know the exact meaning of this status. Have you seen this before in your RMAN listings ?
P.S. For me it means Multiple or Mixed. - Apr 13, 13
One of the main reasons I have been a little inactive has been that in the last month and a half I have switched jobs, switched countries and had a conference to give at Collaborate in Denver , so I just wanted to share this for anyone who didn't have the chance to attend this awesome gathering of Oracle minds.
My presentation was oriented towards beginner or intermediate users of Oracle's RMAN utility, so here is the white paper on this presentation, I hope it is helpful to you as it was to a few people who came up to me after the presentation.
How to Survive a Disaster with RMAN - Mar 13, 13
One thing that has always amazed me is the way that all of our decisions, big or small, have an impact in our life. Sometimes you can’t notice the fork in the roads that those decisions have caused, but sometimes they are as clear as rain.
For me, a simple Oracle DBA, one of those life decisions was to have a talk with a great friend about your own empowerment in social media and why I should open a Twitter account. This led me to follow Yury Velikanov, who then tweeted a conversation about how to share your blog with Oracle to be able to attend 2012 Oracle Open World (OOW) as a blogger.
So I did exactly that and my proposal got accepted. This this gave me the opportunity to make my way to San Francisco. During those days at OOW, I had the opportunity to hear Pythian’s founder, Paul Vallée’s Ted Talk. After those 15 minutes and an evening talk with Greg Leger, I had the inquietude to join this company that I had just heard of on this trip.
Now this wasn’t going to be a smooth ride, it was actually going to be an arduous interview and hiring process. This is due to the fact that Pythian is made up of the top talent from around the world, and it strives for that.
Fast forward 6 months later: March 4th, 2013 was my first day at Pythian and what a week it has been. Filled since the first minutes with training in the way of life of a Pythianite, and all I can say is that it has lived up to and exceeded everything that I had imagined it was going to be.
Here are a few insights into why:- Let me know if the first day that you got to your new job, you sat down with the CEO of your company and had a one hour talk with him, regarding its mission and the vision and route the organization is taking.
- You were already encouraged by the owner of the company to start blogging and start raising your game.
- You get to be with, talk, sit around the dinner table and trade ideas with the people considered to be the top 5% worldwide of the field you are in.
- You see that they strive to avoid the pitfall in all of their employees, the famous phrase in Stephen King’s The Shining "All work and no play makes Jack a dull boy".
- You start believing Pythian’s point of view "that the quality of our work is essential to our clients’ success and the quality of our service is essential to our success", and with this, it is critical to know that it is a team effort , not a stand alone hero. Hence they make every effort from the beginning, to make you feel like a part of the this growing family.
So by Friday, I was already wearing the “Love Your Data” shirt, sharing doughnuts around the Ottawa office and really excited to be here to give it my best to succeed and be an intricate part of this organization.
And now all I can say after week one has come and gone, and looking back at all the sacrifices and choices I made to be here at Pythian (which I won’t go into in this post, that is for a one on one coffee if you want to hear about it), is that I’m looking forward to week two, three, four and the rest to come … - Dec 13, 12
Recovery Manager or better known as RMAN, is an Oracle client utility that is installed with the Enterprise or Standard edition, you can also find it with the Admin option when installing the Oracle Client.
In it's most basic form, the RMAN client connects, an it needs to be with a "sysdba" privileged user, to the database that is being backed up, called a TARGET database, this client, is an executable that is normally found in $ORACLE_HOME/bin.
By being a client side utility, it allows you to use one RMAN executable version to backup current and previous versions of the Oracle Database, there are some restrictions to this though, which can be verified in MOS document RMAN Compatibility Matrix [ID 73431.1].
This RMAN executable uses a file called recover.bsq , this file is located in $ORACLE_HOME/rdbms/admin , basically what the executable does, is to interpret the commands you give it , direct server sessions to execute those commands, and record its activity in the TARGET database control file that is being backed up.
There are two main SYS packages that do the work of backup and recovery, which are DBMS_RCVMAN, this has the procedures which list your database incarnations, the set until time recovery window, list your backups, to name a few, and DBMS_BACKUP_RESTORE , which as you might have guessed is the one who does the backup and recovery operations, like create the control file snapshot , backup the datafiles , backup the spfile to name some.
As mentioned above, the way that the RMAN client directs the server sessions to execute the commands are through channels , a channel represents one stream of data to a device, and corresponds to one database server session. The channel reads data into PGA memory, processes it, and writes it to the output device.The work of each channel, whether of type disk or System Backup Tape (SBT), is subdivided into the following distinct phases:- Read Phase
A channel reads blocks from disk into input I/O buffers. The allocation of these buffers depend on the number of datafiles being read simultaneously from disk and written to the same backup piece. One way to control the numbers of files is the backup parameter FILESPERSET - Copy Phase
A channel copies blocks from input buffers to output buffers and performs additional processing on the blocks, like the validation of the data blocks, as it verifies that it's not backing up corrupt data blocks, it's also the phase where it does the binary compression and the backup encryption - Write Phase
A channel writes the blocks from output buffers to storage media. The write phase can be either to SBT or to disk, and these are mutually exclusive, meaning you write to one or the other, not both.
As you can see by the phases above, and what distinguishes RMAN from any other method, is that the backup is at the block level, as to the user managed backups, it brings great advantages , as it wont have to backup empty blocks.One great way to see how your backup works, is to do a trace, here you will see everything that has been mentioned aboveoracle@servidor1.oracleenespanol.blogspot.com [TESTDB] /home/oracle/bin
oracle $ rman target / debug trace=backup.trc
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 13 09:42:01 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: TESTDB (DBID=2580834116)
RMAN> backup database plus archivelog;
Once the backup finishes, you can search the trace file and see what it is actually doing, here is an example of the RCVMAN fetching the incarnation information:DBGRCVMAN: Fetching incarnation records
DBGRCVMAN: incarnation record id=0
DBGRCVMAN: icprs=0,icprc=
DBGRCVMAN: icrls=1,icrlc=Oct 24 2012 09:26:28
DBGRCVMAN: icrlc_i=797505988
DBGRCVMAN: icpinc=0
DBGRCVMAN: icflg=CURRENT
DBGRCVMAN: number of incarnation=1
DBGRCVMAN: canApplyAnyRedo is set to TRUE
DBGRCVMAN: canConvert_Cf is set to TRUE
DBGRCVMAN: canHandleTransportableTbs is set to TRUE
Below you can see the RPC calls being done by DBMS_BACKUP_RESTORE and the IO information by the INPUT buffers and the OUTPUT buffers.DBGRPC: krmxr - channel ORA_DISK_1 calling peicnt
DBGRPC: krmxrpc - channel ORA_DISK_1 kpurpc2 err=0 db=target proc=SYS.DBMS_BACKUP_RESTORE.BACKUPPIECECREATE excl: 0
DBGRPC: krmxrpc - channel ORA_DISK_1 kpurpc2 err=0 db=target proc=SYS.DBMS_BACKUP_RESTORE.PIECECONTEXTGETNUMBER excl: 0
DBGRPC: krmxrpc - channel ORA_DISK_1 kpurpc2 err=0 db=target proc=SYS.DBMS_BACKUP_RESTORE.BACKUPCANCEL excl: 0
DBGIO: Type %Comp Blocks Tot Blocks Blksize ElpTime(s) IO Rt(b/s) Name [09:43:14.946] (krmkqio)
DBGIO: ---- ----- ---------- ---------- ---------- ---------- ---------- ----- [09:43:14.946] (krmkqio)
DBGIO: IN 3.6 2304 64000 8192 0 0 /mount/u01/oracle/TESTDB/data/undotbs1_01.dbf [09:43:14.946] (krmkqio)
DBGIO: IN 90.0 11520 12800 8192 0 0 /mount/u01/oracle/TESTDB/data/sysaux01.dbf [09:43:14.947] (krmkqio)
DBGIO: IN 51.9 33216 64000 8192 2 136052736 /mount/u01/oracle/TESTDB/data/system01.dbf [09:43:14.947] (krmkqio)
DBGIO: IN 59.7 38208 64000 8192 2 156499968 /mount/u01/oracle/TESTDB/data/users01.dbf [09:43:14.947] (krmkqio)
DBGIO: OUT 39224 8192 2 160661504 /mount/oracle/copy01/flash_recovery_area/TESTDB/TESTDB/backupset/2012_12_13/o1_mf_nnndf_TAG20121213T094259_8dmtcn0w_.bkp [09:43:14.947] (krmkqio)
DBGIO: AGG 85248 8192 2 349175808 [09:43:14.947] (krmkqio)
DBGMISC: ENTERED krmzlog [09:43:14.948]
RMAN-08044: channel ORA_DISK_1: finished piece 1 at Dec 13 2012 09:43:14
I hope this helps you understand more how your backup works, and this is just the super condensed version, there is a lot of information missing, like how RMAN allocates the IN and OUT buffers, or how the disk writes are differentiated from the SBT writes, so go out there and see it yourself.
Also some of the information for this entry came from these two great books:
Oracle RMAN 11g Backup and Recovery
Oracle® Database Backup and Recovery User's Guide 11g Release 2 (11.2) - Read Phase
- Nov 30, 12
First I want to start disclosing that I have an idea on how this happened, but haven't been able to recreate this issue, also that I was lucky enough that the tablespace that had the issue was a DUMMY tablespace so it didn't cause an issue.
So now that the above disclosure is done, what happened is that I have a SOURCE database with 1 location for it's datafiles +DATA1/SOURCE, but at some point someone added a second location for testing for a TEST1 tablespace in +DATA2/TEST, this SOURCE database was duplicated via RMAN to 2 databases, call them DUP1 and DUP2. In the RMAN duplicate command , the parameter DB_FILE_NAME_CONVERT was used to change the location of the datafiles of the SOURCE database to the DUP1 or DUP2, but it was only input for the known location, not for the testing location.
What it was input in the RMAN command:DB_FILE_NAME_CONVERT '+DATA1/SOURCE','DATA1/DUP1'
How it should have been:DB_FILE_NAME_CONVERT '+DATA1/SOURCE','+DATA1/DUP1','+DATA2/TEST','+DATA1/DUP1'
And because the diskgroup and directory of +DATA2/TEST exist where the DUP1 and DUP2 would reside, it didn't cause an issue when it ran the duplicate, and this is something that is still yet unknown to me, the duplicate finished successfully for both databases and no issues arose from the open resetlogs.
As you might already know when you issue the DUPLICATE command the DBID is implicitly changed , and what this does is it updates the controlfile with the new DBID and also the datafile headers , that way is how it will know that these datafiles XX belong to database NN.
So when both databases where up and running, I ran a backup from both databases, DUP1 finished successfully, DUP2 failed with the following error:RMAN-06169: could not read file header for datafile 148 error reason 9
RMAN-06056: could not access datafile 148
One of the first things I did was run a VALIDATE DATABASE, and indeed it was showing me the same error:RMAN> VALIDATE DATABASE;
Starting validate at 29-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=145 instance=DUP21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=164 instance=DUP21 device type=DISK
RMAN-06169: could not read file header for datafile 148 error reason 9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 11/29/2012 01:40:41
RMAN-06056: could not access datafile 148
The next thing I did was to verify what that file was and the DBID of the DUP2 database, as the error of the header led me that wayDUP21 >select DBID,open_mode from v$database;
DBID OPEN_MODE
---------- ------------------------------------------------------------
2211912429 READ WRITE
DUP21 >select file_name from dba_data_files where TABLESPACE_NAME='TEST1';
FILE_NAME
--------------------------------------------------------------------------------
+DATA2/test/test01.dbf
DUP21 >select STATUS from dba_tablespaces where TABLESPACE_NAME='TEST1';
STATUS
---------------------------
ONLINE
Because RMAN error 06169 was telling me that it couldn't read the header, I did a dump of the headerDUP21 >oradebug setmypid
Statement processed.
DUP21 >oradebug unlimit
Statement processed.
DUP21 >oradebug dump file_hdrs 3
Statement processed.
DUP21 >oradebug tracefile_name
And indeed saw that the DBID of test01.dbf belonged to DUP1, which had been the first of the 2 databases duplicatedV10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=4005607769=0xeec0b959, Db Name='DUP1'
So just to be sure, I went to DUP1 and verified, and yes it was the same DBID that the header dump told me ,and yes the tablepsace was online as well in DUP1DUP11 >select DBID,open_mode from v$database;
DBID OPEN_MODE
---------- ------------------------------------------------------------
4005607769 READ WRITE
DUP11 >select STATUS from dba_tablespaces where TABLESPACE_NAME='TEST1';
STATUS
---------------------------
ONLINE
DUP11 >select file_name from dba_data_files where TABLESPACE_NAME='TEST1';
FILE_NAME
--------------------------------------------------------------------------------
+DATA2/test/test01.dbf
DUP11 >select STATUS from dba_tablespaces where TABLESPACE_NAME='TEST1';
STATUS
---------------------------
ONLINE
Because that particular tablespace was of no use for the application, I just went ahead and dropped itDUP21 >ALTER TABLESPACE TEST1 OFFLINE IMMEDIATE;
Tablespace altered.
DUP21 >DROP TABLESPACE TEST1;
Tablespace dropped.
Takeways
I, have to say it's in 11.2.0.3 with a GI of 11.2.0.3 in RHEL 5 x86_64, but it goes to say that whenever you do a duplicate and you use the DB_FILE_NAME_CONVERT , be sure that all of your locations are covered . But what is really bugging me is how DUP2 was able to open resetlogs without this being an issue, I will try to recreate this and update this post if and when I do. - Nov 05, 12
So the other day I was doing an install of the 11.2.0.3 Clusterware binaries and came around that with the cluvfy tool in 11.2.0.3 comes now with a health check option for the Database. For this option to work you have to install or setup the cvusys user as well as the cvusapp role, a script for this, is in the CLUVFY_HOME/cv/admin directory
As the oracle user run the $CLUVFY_HOME/cv/admin/cvusys.sql script to setup the mentioned user and role as well as the grants needed. This user will use the Database default tablespace, so if you want to set it up in a different tablespace you would need to modify this script.oracle@servidor1.localdomain [TESTDB1] /home/oracle
oracle $ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),501(vboxsf),54322(dba)TESTDB1> @cvusys.sql
User dropped.
Role dropped.
Enter password for user cvusys
'Creating user cvusys...'
User created.
Grant succeeded.
Role created.
Grant succeeded.
.
.
.
Once you have setup the cluvfy healthcheck user, as the grid user, verify that your environment is setup for the correct $GRID_HOME.grid@servidor1.localdomain /home/grid
oracle $ id
uid=54324(grid) gid=54321(oinstall) groups=54321(oinstall),501(vboxsf),54322(dba),54323(asmadmin),54325(asmoper)
grid@servidor1.localdomain /home/grid
oracle $ echo $ORACLE_HOME
/mount/oracle/11.2.0.3/grid
As I came up that if the $GRID_HOME is setup incorrectly or that you pass the database name in the wrong letter case you can receive the following error PRVG-11005, even though that you will see that in the $CLUVFY_HOME/cv/log the srvctl config database is finishing successfully.grid@servidor1.localdomain /home/grid
oracle $ cluvfy comp healthcheck -collect database -db TESTDB -deviations -save -savedir /home/grid/audit
ERROR:
PRVG-11005 : Database "TESTDB" is not defined in this cluster
grid@servidor1.localdomain /home/grid/cv/log
root $ more cvutrace.log.0
.
.
.
[17455@servidor1.localdomain] [Worker 0] [ 2012-11-04 22:41:33.595 EST ] [VerificationCommand.execute:232] Formatted exectask output is:
<CV_CMD>/mount/oracle/11.2.0.3v1/rdbms/bin/srvctl config database -d TESTDB -a </CV_CMD><CV_VAL>Database unique name: TESTDB
Database name: TESTDB
Oracle home: /mount/oracle/11.2.0.3v1/rdbms
Oracle user: oracle
Spfile: +DATA/TESTDB/PARAMETERFILE/spfileTESTDB.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TESTDB
Database instances: TESTDB1,TESTDB2
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
</CV_VAL><CV_VRES>0</CV_VRES><CV_LOG>Exectask: runexe was successful</CV_LOG><CV_ERES>0</CV_ERES>
.
.
.
Also you can run this against all the Databases in the cluster by leaving out the <db_unique_name> out, but just take in mind that you will need to setup the cvusys user for all the Databases in the cluster, as well you can verify the best practices or what is mandatory for the Database and how does your Database deviates from this [-bestpractice|-mandatory] [-deviations].
So in this case I run it with the lower case name and with the option of deviations from the best practice, and it finished successfully.grid@servidor1.localdomain /home/grid/cv/log
root $ cluvfy comp healthcheck -collect database -db testdb -bestpractice -deviations -save -savedir /home/grid/audit/
Verifying Database "testdb"
Please specify password for user "cvusys" :
Verifying Database Best Practice for "testdb"
Verifying JVM configuration for database ...not met
Verifying Java Role Count ...not met
Verifying Duplicate SYS or SYSTEM Schema Objects ...not met
Verifying Users Granted CONNECT Role ...not met
Verifying DB Log Mode ...not met
******************************************************************************************
Summary of environment
******************************************************************************************
Date (mm/dd/yyyy) : 11/05/2012
Time (hh:mm:ss) : 22:37:28
Cluster name : cluster-test
Clusterware version : 11.2.0.3.0
Grid home : /mount/oracle/11.2.0.3/grid
Grid User : grid
Operating system : Linux2.6.18-238.el5
Database1 : Database name - testdb
Database version - 11.2.0.3
Database home -
/mount/oracle/11.2.0.3v1/rdbms
******************************************************************************************
Database recommendation checks for "testdb"
******************************************************************************************
.
.
.
Now just head over to the location where you saved your report and see the deviations from Oracle's best practice, now here is where I would have to say that you need to verify before doing some of these changes as a recommendation I got was that I was running in ARCHIVELOG_MODE vs recommended NONARCHIVELOG mode, I hope you see the irony here. But if you are not doing a health check of your databases after you do a fresh install, this is a great start and will get you thinking how I can make my environment better.__________________________________________________________________________________________
Verification Check : Duplicate SYS or SYSTEM Schema Objects
Verification Description : Checks for duplicate SYS or SYSTEM schema objects
Verification Result : NOT MET
Verification Summary : Check for Duplicate SYS or SYSTEM Schema Objects failed
Additional Details : If any duplicate objects were found in the SYS and SYSTEM
schemas, refer to articles in the references section. Read
the exceptions carefully before taking action.
References (URLs/Notes) : https://support.oracle.com/CSP/main/article?cmd=show&type=N
OT&id=1030426.6
Node Status Expected Value Actual Value
------------------------------------------------------------------------------------------
testdb FAILED sys_duplicate_obj = 0 sys_duplicate_obj = 4
__________________________________________________________________________________________
Verification Check : INVALID objects in the application related schemas
Verification Description : Checks for the presence of INVALID objects in the
application related schemas (non SYS and SYSTEM)
Verification Result : NOT MET
Verification Summary : Check for INVALID objects in the application related
schemas failed
Additional Details : Investigate invalid objects in the application related
schemas (non SYS and SYSTEM).
Node Status Expected Value Actual Value
------------------------------------------------------------------------------------------
testdb FAILED app_invalid_obj = 0 app_invalid_obj = 5 - Oct 25, 12
For whoever that has worked with the 11gR2 version of RAC , has ran into the startup sequence diagram, this diagram is just disturbing to see, I don't know about you, but for me is just unhelpful and not KISS.
Then there is the second diagram available, which is way more clear, but in a very bad quality, you actually have to squint your eyes, and for me, and my very bad eyesight this is not good, even not understanding what it said.
Now, I'm not inventing the wheel with this diagram, I'm just making it more readable for whoever wants to look at it.The full description, the really unreadable diagram and/or any updates to this you can find it in MOS Document 1053147.1
Click on the image below to see the full size
This description is also from the MOS note stated above
Short summary of the startup sequence: INIT spawns init.ohasd (with respawn) which in turn starts the OHASD process (Oracle High Availability Services Daemon). This daemon spawns 4 processes.
Level 1: OHASD Spawns:
cssdagent - Agent responsible for spawning CSSD.
orarootagent - Agent responsible for managing all root owned ohasd resources.
oraagent - Agent responsible for managing all oracle owned ohasd resources.
cssdmonitor - Monitors CSSD and node health (along wth the cssdagent).
Level 2: OHASD rootagent spawns:
CSDD (ora.cssd) - Cluster Synchronization Services
CRSD(ora.crsd) - Primary daemon responsible for managing cluster resources.
CTSSD(ora.ctssd) - Cluster Time Synchronization Services Daemon
Diskmon(ora.diskmon)
ACFS (ASM Cluster File System) Drivers
Level 2: OHASD oraagent spawns:
MDNSD(ora.mdnsd) - Used for DNS lookup
GIPCD(ora.gipcd) - Used for inter-process and inter-node communication
GPNPD(ora.gpnpd) - Grid Plug & Play Profile Daemon
EVMD(ora.evmd) - Event Monitor Daemon
ASM(ora.asm) - Resource for monitoring ASM instances
Level 3: CRSD spawns:
orarootagent - Agent responsible for managing all root owned crsd resources.
oraagent - Agent responsible for managing all oracle owned crsd resources.
Level 4: CRSD rootagent spawns:
Network resource - To monitor the public network
SCAN VIP(s) - Single Client Access Name Virtual IPs
Node VIPs - One per node
ACFS Registery - For mounting ASM Cluster File System
GNS VIP (optional) - VIP for GNS
Level 4: CRSD oraagent spawns:
ASM Resouce - ASM Instance(s) resource
Diskgroup - Used for managing/monitoring ASM diskgroups.
DB Resource - Used for monitoring and managing the DB and instances
SCAN Listener - Listener for single client access name, listening on SCAN VIP
Listener - Node listener listening on the Node VIP
Services - Used for monitoring and managing services
ONS - Oracle Notification Service
eONS - Enhanced Oracle Notification Service
GSD - For 9i backward compatibility
GNS (optional) - Grid Naming Service - Performs name resolution - Dec 13, 12
Due to several dinners and a few concerts, I didn't post on day three , but here is more data regarding the new version of Oracle's Database 12c .
Regarding backup and recovery in this new version, you will see a great new feature in which you can now recover an object from a RMAN backup without having to restore the backup you took. Also it will enhance the ACTIVE DUPLICATE functionality as now it can be done via backupsets and also it will allow multi sectioning and compression , making a cloning of a database much faster.
From a RAC perspective , it introduces the concept of FlexASM , this concept will have the capability to NOT have an ASM instance in every node, this doesn't mean that you will not have an ASM instance , it just means that in some nodes you can now connect to a remote ASM instance from one of your nodes, reducing the instance overhead in your servers. It was also mentioned the concept of Oracle Grid Home Server, this will allow you to have a golden copy of your ORACLE_HOME and only have to patch it once , your nodes will have a GRID HOME client , this will reduce the work when patching . One other thing that is going to be introduced is the concept of Hub Nodes and leaf Nodes, the Hub nodes are the nodes you currently know, which are tightly coupled via network and storage, the leaf nodes are going to be your application nodes, these won't have a voting disk, but it will allow you to control the full stack, meaning that you can now shutdown an application (Database and Application Servers) from within the clusterware.
These are just a couple of thing that have come from Oracle Open World 2012, as you can see there are great new features to keep this job interesting and exciting . - Oct 04, 12
So the second day of Oracle Open World has come and gone, and the news about the 12c Version of Oracle's signature Database keep coming in, in a General session in which Oracle Tech Guru Tom Kyte did a 15 minute feature presentation, a few things popped up that grabbed my attention.
One of them was the introduction of adaptive execution plans , this is going to be real interesting performance wise, and also talking about performance they are enhancing the database statistics with the introduction of hybrid histograms , with these I know several will be happy as even though the data might be skewed, they have several skewed data.
Also one thing that has me thinking and that will get people thinking , is that the definition of the instance - database relation will change , as with the introduction of the PDB (Plugabble Database) you will now have the container database which will be the one who has the current instance definition (memory and processes) and to that you will be consolidating your databases to, so this means that the relation now , by my understanding, will be one instance to many databases, so how will RAC be in these is going to be from a container to container database which both of these containers will have one or many databases.I have still yet to see a session about how RMAN will play it's role in this new paradigm of PDBs.
A few other things that were mentioned through out the day was the introduction of the following:
Sequence type row
A top N query will now be done in one line
Temporary undo for temporary tables
Another thing was the introduction to far sync server for dataguard which will allow you to have a synchronous redo ship configuration while being farther than 300 miles with a low latency, as you will ship to a closer in distance light weight instance in synchronous mode and that will then ship to your standby in asynchronous mode.
These are a few things that have come out of OOW 2012, I hope you are getting as excited as I am for the next version . - Oct 01, 12
So today was my first time attending Oracle Open World (OOW) , and I can't say anything but good things about the whole experience , I don't know if the upcoming sessions how will they be, but the User Groups Sessions rocked.
A little bit of information about 12c Database was dropped here and there within the sessions, and one thing that I heard and really loved was RMAN will have the capability to recover only a table, Im just dying to try out that functionality, but not only that, 12c Database as announced by Larry Ellison, the database will be multitenant capable, which will bring a whole new paradigm into how we manage today our DBs , as we will plug our Databases into a container database, still a lot more data to come, but this sounds interesting to try out.
The main four things mentioned by Larry Ellison at his key note and which we will see at OOW are :- Oracle Cloud Infrastructure as a Service
- Oracle Owned and Managed Private Data cloud
- Oracle Database 12c
- Exadata x3 Database In Memory Machine
As the core days of OOW will start I will bring more info on what has come out of this years sessions - Sep 26, 12
Last weekend I was involved in a DRP/BCP (Disaster Recovery Plan/Business Continuity Plan) for a client, which is basically planning out your worst case scenario , e.g. your Data site being hit by a tornado and loosing everything there, and what actions would you take in case this should happen.
And even though you might already have a DRP , putting it into action will help you find out which steps are failing in your SOP (Standard Operation Procedure) and only then you will be able to update them and take a corrective action before your put into a real scenario and you can't meet your client's SLA (Service Level Agreement).
Searching for a little more data on the percentages of enterprises that put into action a DRP , I came to find this survey by IOUG (Independent Oracle Users Group) taken this 2012 to more than 350 data professionals , in which 51% of them where database administrators, and what stands out to me are the following regarding a DRP:- 37% have a Partial formal DRP/BCP in place, only for some type of data.
- 18% have never tested and/or reviewed their BCP/DRP.
- 43% mention that funding and budget constraints is the greatest challenge to managing and planning a DRP/BCP.
- 12% don't how long,in hours, it will take to restore their data from an unplanned outage.
To me is a little concerning to see anything above 5 or 10%, as from a point of view of a DBA, the most important thing we need to have is the data available, if the data is not available, you are really in a situation that I wouldn't want to be in.
At the end, if for your client it takes more time to receive or process their data , he can somehow live with this delay of processing, he won't be happy as it can cause him a loss of money and/or time, but he will be able to manipulate his data, but the question is, what happens if he can never get or process his data, ever?
What I want to get to, is that , if today you don't have a DRP/BCP in place and tested, get moving and push to get one done, it will help you to detect, correct and even prevent a data outage in your operations and/or data loss, and as well it can help you establish a time SLA in case of a disaster.
So start planning your DRP and find those weaknesses in your architecture, so that instead of coming out a loser you end up being a hero and as well keep your much loved job :) . - Sep 19, 12
When you manage a RAC database, there is a tool called crsctl , this tool is an interface to manage all the resources belonging to the clusterware. At the same time this tool can help you to manage and have available your user applications when the cluster is running and viceversa.
This not only helps us to have available our user applications , but also to have up and running monitoring tools when the cluster us running, in this case we will setup OSWbb (OS Watcher Black Box 461053.1) as resource, and even though there is a document to start OSWbb as an OS service, Document ID 580513.1, we will set it up with crsctl.
As always , first try this with a testing environment, before moving it to a production environment, and also verify the scripts mentioned below.
The first thing that you have to do is download OSWbb and install it in the same location across all nodes in your RAC farm, in my case I set it up in the following location:
/mount/dba01/oracle/oswbb_51
Now you have to create a script that calls all four necessary commands (START,STOP,CHECK and CLEAN) that work and are called by crsctl , this script has to be visible in all nodes in the same location as well, in this case I'm going to use a script called rac_verificar_oswbb.sh, and you can find it clicking the name of it.
One thing that you will have to do is change the variable OSWPATH and point it to where you installed OSWbb, and also because this script is from my blog in spanish, you will have to change the messages as they are in spanish. In my case I changed it to the value below:
OSWPATH=/mount/dba01/oracle/oswbb_51
Make sure that you the user with which you create the resource, either root or grid , has read write permissions over rac_verificar_oswbb.sh and OSWPATH.
What you have to do now us run the script rac_verificar_oswbb.sh from the command line and verify that all four commands run cleanly before setting up the CRS resource, if one of the four commands (START,STOP,CHECK and CLEAN) doesn't work or run with an error, this setup won't work.
Once you have verified/corrected that you have no errors running the script, you can go ahead and setup the resource, the only thing that you have to verify is the resource doesn't have the prefix .ora, as this type of prefix are used and managed by Oracle, in my case I used a prefix .usr to be able to distinguish it.root@servidor1.oracleenespanol.blogspot.com /home/oracle
root $ crsctl add resource usr.oswbb -type ora.local_resource.type -attr "AUTO_START=always,RESTART_ATTEMPTS=2, START_TIMEOUT=100,STOP_TIMEOUT=100,CHECK_INTERVAL=60,ACTION_SCRIPT=/mount/dba01/oracle/admin/rac_verificar_oswbb.sh"
Now that you have create the resource , you have to initiate it and verify that it's runningroot@servidor1.oracleenespanol.blogspot.com /home/oracle
root $ crsctl start resource usr.oswbb
CRS-2672: Attempting to start 'usr.oswbb' on 'servidor1'
CRS-2672: Attempting to start 'usr.oswbb' on 'servidor2'
CRS-2676: Start of 'usr.oswbb' on 'servidor1' succeeded
CRS-2676: Start of 'usr.oswbb' on 'servidor2' succeeded
root@servidor1.oracleenespanol.blogspot.com /home/oracle
root $ crsctl status resource usr.oswbb
NAME=usr.oswbb
TYPE=ora.local_resource.type
TARGET=ONLINE , ONLINE
STATE=ONLINE on servidor1, ONLINE on servidor2
Conclusion
This method not only works for OSWbb, but for any user application that you need it to be tied to your cluster. - Sep 05, 12
The other day I was asked How can I verify that the backup I took was a good backup. For this RMAN has several commands to verify the integrity of these.
All the commands used below do not perform an actual recovery , it only reads and validates the backups , what I do have to say though is that is the word VALIDATE is missing in some commands and depending on which state you have your DB , it will actually start to do the restore process, so be very careful that you include that word.
Here are the commands I used for this exercise, also note that it doesn't matter if the channel you are allocating is to DISK or to SBT, in this case I used DISK- RESTORE DATABASE PREVIEW ;
- RESTORE DATABASE VALIDATE;
- RESTORE ARCHIVELOG FROM sequence xx UNTIL SEQUENCE yy THREAD nn VALIDATE;
- RESTORE CONTROLFILE VALIDATE;
- RESTORE SPFILE VALIDATE;
The first thing that you have to do is, if you are not going to use the latest backup, define the time that you want to validate your backup, the first command that we are going to use is RESTORE . . . PREVIEW, this command identifies the needed backup(s) to execute the restore process as well as the Archived Redo Logs needed.RMAN> RUN
2> {
3> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
4> allocate channel ch1 device type disk ;
5> RESTORE DATABASE PREVIEW ;
6> }
executing command: SET until clause
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
Starting restore at 03-SEP-2012 22:54:18
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
6 Full 75.34M DISK 00:00:19 03-SEP-2012 22:01:04
BP Key: 6 Status: AVAILABLE Compressed: YES Tag: TESTDB_HOT_0904_2100
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
1 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/system01.dbf
2 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/sysaux01.dbf
3 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/undotbs1_01.dbf
4 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/undotbs2_01.dbf
5 Full 224235 03-SEP-2012 22:00:46 +DATA/TESTDB/datafile/users_01.dbf
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
9 482.00K DISK 00:00:00 03-SEP-2012 22:01:39
BP Key: 9 Status: AVAILABLE Compressed: YES Tag: TESTDB_ARCH_0904_2100
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499
List of Archived Logs in backup set 9
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 8 222449 03-SEP-2012 21:57:53 224442 03-SEP-2012 22:01:33
2 4 222452 03-SEP-2012 21:59:38 224448 03-SEP-2012 22:03:17
1 9 224442 03-SEP-2012 22:01:33 224456 03-SEP-2012 22:01:36
2 5 224448 03-SEP-2012 22:03:17 224459 03-SEP-2012 22:03:21
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
12 389.50K DISK 00:00:01 03-SEP-2012 22:21:50
BP Key: 12 Status: AVAILABLE Compressed: YES Tag: TESTDB_ARCH_0904_22_20
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1
List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 10 224456 03-SEP-2012 22:01:36 225574 03-SEP-2012 22:21:01
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
11 25.00K DISK 00:00:00 03-SEP-2012 22:21:50
BP Key: 11 Status: AVAILABLE Compressed: YES Tag: TESTDB_ARCH_0904_22_20
Piece Name: /mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
2 6 224459 03-SEP-2012 22:03:21 225577 03-SEP-2012 22:22:45
Media recovery start SCN is 224235
Recovery must be done beyond SCN 224235 to clear datafile fuzziness
Finished restore at 03-SEP-2012 22:55:15
released channel: ch1
The next step, which is RESTORE DATABASE VALIDATE, it will read the pieces of the backup and if it finds an error it will report it.RMAN> RUN
2> {
3> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
4> allocate channel ch1 device type disk ;
5> restore database validate;
6> }
executing command: SET until clause
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
Starting restore at 03-SEP-2012 22:51:44
channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/TESTDB_HOT_09032012_1_6_793058445 tag=TESTDB_HOT_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:25
Finished restore at 03-SEP-2012 22:52:38
released channel: ch1
If you looked closely at the result above, this only read the backupset which contain the datafiles, not the Archived Redo Logs, so now we have to verify the integrity of these, as these will also be needed if the backup was an inconsistent backup (Hot Backup). What I recommend is that you get the value of the needed Archived Redo Logs from result of the RESTORE...PREVIEW, once you have these values, run the command RESTORE ARCHIVELOG. . . VALIDATERMAN> RUN
2> {
3> allocate channel ch1 type disk;
4> restore archivelog from sequence 8 until sequence 10 thread 1 validate;
5> restore archivelog from sequence 4 until sequence 6 thread 2 validate;
6> }
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
Starting restore at 03-SEP-2012 23:15:11
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499 tag=TESTDB_ARCH_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/0bnka8bt_1_1 tag=TESTDB_ARCH_0904_22_20
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:15:15
Starting restore at 03-SEP-2012 23:15:17
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/arch/TESTDB_ARCH_09032012_1_9_793058499 tag=TESTDB_ARCH_0904_2100
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
channel ch1: starting validation of archived log backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/full/0cnka8bu_1_1 tag=TESTDB_ARCH_0904_22_20
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:15:21
released channel: ch1
To end this , we just need to validate that we can restore the control file and our binary parameter fileRMAN> RUN
2> {
3> allocate channel ch1 type disk;
4> set until time "to_date('03-SEP-201222:20:00','dd-mm-yyyyhh24:mi:ss')";
5> restore controlfile validate;
6> restore spfile validate;
7> }
allocated channel: ch1
channel ch1: SID=158 instance=TESTDB1 device type=DISK
executing command: SET until clause
Starting restore at 03-SEP-2012 23:23:14
channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-03
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-03 tag=TAG20120903T220143
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:23:15
Starting restore at 03-SEP-2012 23:23:16
channel ch1: starting validation of datafile backup set
channel ch1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-04
channel ch1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/control/c-3899479525-20120903-04 tag=TAG20120903T222152
channel ch1: restored backup piece 1
channel ch1: validation complete, elapsed time: 00:00:01
Finished restore at 03-SEP-2012 23:23:18
released channel: ch1
Conclusion
These commands even though they are simple, they are very powerful to validate your backups, mi recommendation is that you run this at least once a week , as you don't want to be in a situation in which you need to perform a restore and in that moment find out that you don't have a valid backup or a valid backup strategy. - Aug 01, 12
So the other day I was running an incremental backup in a Database that had been recently cloned, when I was running the backup, the following error showed up:
DBGRPC: EXITED krmqgns with status 1
DBGRPC: krmxpoq - returning rpc_number: 77 with status: FINISHED16 for channel CH10
DBGRPC: krmxr - channel CH10 calling peicnt
DBGRPC: krmxrpc - channel CH10 kpurpc2 err=19583 db=target proc=SYS.DBMS_BACKUP_RESTORE.BACKUPPIECECREATE excl: 187
DBGRPC: krmxrpc - caloing krmxtrim: with message of length 187: @@@ORA-19583: conversation terminated due to error
DBGRPC: ORA-19764: database id 8573487567 does not match database id 3024984012 in control file
DBGRPC: ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 1381
As you might remember if you clone a Database without RMAN, you have to use the new DBID utility to change the Database ID, so just to be on the safe side the first thing I did was try and verify that DBID for the cloned Database didn't match the source Database, which was the case.TESTDB21 >select dbid,name from v$database;
DBID NAME
---------- --------
3024984012 TESTDB2
What really had me going was exactly this, that the information in the data dictionary was showing me that the the DBID (3024984012) was the correct one, in the trace file nothing was pointing me besides the error shown at the beginning of the post, that the DBID was incorrect.
So digging a little bit more, I came to find out that the erroneous DBID was coming from the block change tracking file (BCT)TESTDB21 >select FHDBI,FHDBN from X$KRCFH
2 /
FHDBI FHDBN
---------- --------
8573487567 TESTDB2
Once I noticed that it was this file that it was causing the error, the only thing I needed to do was recreate the BCT file and voilà, problem solved :)TESTDB21 >alter database disable block change tracking;
Database altered.
TESTDB21 >select FHDBI,FHDBN from X$KRCFH;
no rows selected
TESTDB21 >alter database enable block change tracking using FILE '/copy01/TESTDB2/oracle/TESTDB2/ctf/TESTDB2.ctf';
Database altered.
TESTDB21 >select FHDBI,FHDBN from X$KRCFH;
FHDBI FHDBN
---------- --------
3024984012 TESTDB2
I hope this tip helps you along if you ever face it, also I want to annex to this post a great article that helped me to sort this issue out by Alex Gorbachev from the Pythian group, it can really help out when trying to understand the BCT file
Oracle 10G Block Change Tracking Inside Out - Jul 28, 12
- Jul 16, 12
If you have an environment in which you have to constantly delete databases, or even if you just want to decommission one Database, the most secure way to remove the corresponding files instead of using the rm command is to use the RMAN command DROP DATABASE
If you are using a RAC environment, the first thing you have to do is set the cluster_database parameter to FALSE, once you have done that, shutdown the databaseoracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 11 22:30:21 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
TESTDB1 >alter system set cluster_database=FALSE scope=spfile;
System altered.
TESTDB1 >exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ srvctl stop database -d TESTDB
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ srvctl status database -d TESTDB
Instance TESTDB1 is not running on node servidor1
Instance TESTDB2 is not running on node servidor2
Now that you have shutdown your Database,make sure you have the correct environment variable set, you don't want to drop a wrong database, and then we will open it in MOUNT mode and set a restricted sessionoracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ . $HOME/TESTDB
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 11 22:36:08 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 2622255104 bytes
Fixed Size 2231232 bytes
Variable Size 637535296 bytes
Database Buffers 1962934272 bytes
Redo Buffers 19554304 bytes
RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
using target database control file instead of recovery catalog
sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION
The only thing pending after this, is to drop the database, I don't like to do it with the NOPROMPT option, as this will be your last chance to repente before doing dropping it.RMAN> DROP DATABASE INCLUDING BACKUPS;
database name is "TESTDB" and DBID is 1743232258
Do you really want to drop all backups and the database (enter YES or NO)?YES
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=82 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_3_788480833
2 2 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_4_788480833
3 3 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_5_788480834
4 4 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_2_788480832
5 5 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_6_788480835
6 6 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_7_788480837
7 7 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_8_788480838
8 8 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_9_788480839
9 9 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_1_788480832
10 10 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_10_788480839
11 11 1 1 AVAILABLE DISK /copy01/TESTDB/control/TESTDB_07_01_2012_788480872.ctl
12 12 1 1 AVAILABLE DISK /copy01/TESTDB/control/c-1743232258-20120701-00
13 13 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_16_788480924
14 14 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_17_788480924
15 15 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_18_788480926
16 16 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_15_788480923
17 17 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_13_788480923
18 18 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_14_788480923
19 19 1 1 AVAILABLE DISK /copy01/TESTDB/control/c-1743232258-20120701-01
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_3_788480833 RECID=1 STAMP=788480834
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_4_788480833 RECID=2 STAMP=788480834
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_5_788480834 RECID=3 STAMP=788480836
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_2_788480832 RECID=4 STAMP=788480833
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_6_788480835 RECID=5 STAMP=788480838
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_7_788480837 RECID=6 STAMP=788480838
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_8_788480838 RECID=7 STAMP=788480839
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_9_788480839 RECID=8 STAMP=788480839
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_1_788480832 RECID=9 STAMP=788480832
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_10_788480839 RECID=10 STAMP=788480840
deleted backup piece
backup piece handle=/copy01/TESTDB/control/TESTDB_07_01_2012_788480872.ctl RECID=11 STAMP=788480875
deleted backup piece
backup piece handle=/copy01/TESTDB/control/c-1743232258-20120701-00 RECID=12 STAMP=788480881
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_16_788480924 RECID=13 STAMP=788480924
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_17_788480924 RECID=14 STAMP=788480924
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_18_788480926 RECID=15 STAMP=788480926
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_15_788480923 RECID=16 STAMP=788480924
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_13_788480923 RECID=17 STAMP=788480923
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_14_788480923 RECID=18 STAMP=788480923
deleted backup piece
backup piece handle=/copy01/TESTDB/control/c-1743232258-20120701-01 RECID=19 STAMP=788480947
Deleted 19 objects
released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=82 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name TESTDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
14 2 4 A 11-JUL-12
Name: +ARCH/TESTDB/archivelog/TESTDB_4_2_788478082.dbf
deleted archived log
archived log file name=+ARCH/TESTDB/archivelog/TESTDB_4_2_788478082.dbf RECID=14 STAMP=788481092
Deleted 1 objects
database name is "TESTDB" and DBID is 1743232258
database dropped
Update : A great reminder and precaution given by Leighton Nelson ( TW : @leight0nn) is to do a backup ... keep just as a safe guard before doing any of theses steps - Jul 13, 12
- Jul 11, 12
Here is a quick tip for when you run into error ORA-00600: [2662], this error points to that a block's SCN is higher than the Database SCN, this can mean that there can exist a physical corruption in your Database, and I won't say here on how to solve this, I would suggest opening an SR with Oracle for this.
But something that can help you out to troubleshoot this error is, when the error appears , if the 5th argument shows up it can give you the information of which datafile the error is presentORA-00600: internal error code, arguments: [2662], [2910], [829087674], [2910], [829528307], [1451708231], [], [], [], [], [], []
If the error as above, has the 5th argument, you can use the following query to identify the datafile that is probable causing errorTESTDB> select dbms_utility.data_block_address_file(1451708231) "fileno",dbms_utility.data_block_address_block(1451708231) from dual ;
fileno DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(1451708231)
---------- -------------------------------------------
138 3
Now that you have the file number , you can query v$datafile to identify the physical fileTESTDB> Select name ,file#,ts# from v$datafile where rfile#=138 ;
And with the utility dbverify , you can check wether this file is corrupt or notdbv file=<NAME_OF_DATAFILE_OBTAINED_ABOVE> blocksize=<db_block_size>
As I mentioned at the beginning, what I recommend is to open an SR when this error presents itself so that you can get to the bottom of this error, but this will give you more power and information to localize the error and feed it to the SR. - Jul 05, 12
So RAC has a pretty cool tool called Server Control or better known as srvctl, with this you can manage the services in a RAC farm, and one of the sometimes and much needed is when you have several ORACLE_HOMEs and you only need to shutdown one, as you are going to apply a patch to that one, no problem
srvctl stop home -o <oracle_home> -s <state_file> -n <node_name> [-t <stop_options>] [-f]
But what happens to the rest of the people who don't have this tool and are still in a single node environment, you can still do this with this simple Unix shell script. I won't say this more than once, use at your own discretion and revise before using it, this should always be tested before using it on a production environment.
The most important thing is you need to have a directory in which you have you environment variables for each of your Databases that are in the host, in this case is in ~/environment_vars/.
This script accepts 4 inputs- DB_LIST .- This can be the name of the Database that you will shutdown or you can input oratab to cycle through the oratab file and execute the same command
- INIT_COMMAND.- This is either STARTUP or SHUTDOWN
- INIT_MODE.- This depends on the previous command, as if its STARTUP you can have NOMOUNT, MOUNT, OPEN or if its SHUTDOWN it can be IMMEDIATE , TRANSACTIONAL , ABORT
- HOME_TO_FILTER.- This will filter the oratab by the type of ORACLE_HOME that you want to shutdown
#!/bin/ksh
#set -x
#########################################################################################
# cycle_sid_list ()
# This function will cycle through the DBs in the oratab or set the DB to start/shutdown
#########################################################################################
cycle_sid_list()
{
if [ ${DB_LIST} = "oratab" ]; then
dblist=`cat $ORATABLOC | grep -v \# | grep -v \* | grep "${HOME_TO_FILTER}" | grep ":Y" | awk -F: '{print $1}' `
else
dblist=${DB_LIST}
fi
for dbn in ${dblist}
do
echo "${dbn}"
. ~/environment_vars/${dbn}
echo "*******"
start_shut ${dbn} ${INIT_COMMAND} ${INIT_MODE}
done
}
################################################################################
# start_shut ()
# This function will stop/start the DB via sqlplus
################################################################################
start_shut()
{
ORACLE_UNQNAME=$1
. ~/environment_vars/${ORACLE_UNQNAME}
echo "========>${INIT_COMMAND} ${ORACLE_UNQNAME} in ${INIT_MODE} mode "
echo
sqlplus /nolog <<EOF >${LOG_DIR}/ora.error.$$
connect / as sysdba
${INIT_COMMAND} ${INIT_MODE};
exit
EOF
if [ ${INIT_COMMAND} = "SHUTDOWN" ]; then
oracle_error=`cat ${LOG_DIR}/ora.error.$$ | grep -v "ORA-01507" | grep -v "ORA-01034" | grep -v "ORA-27101" | grep "ORA-" | wc -l `
else
oracle_error=`cat ${LOG_DIR}/ora.error.$$ | grep "ORA-" | wc -l`
fi
if [ ${oracle_error} -gt 0 ]; then
echo "###############################################################"
echo "## ERROR ====> ${INIT_COMMAND} of Oracle failed "
cat ${LOG_DIR}/ora.error.$$ | grep ORA- | awk '{print "##", $0}'
echo "###############################################################"
else
echo "========> ${INIT_COMMAND} of ${ORACLE_UNQNAME} Succesfull "
echo
fi
}
print_header()
{
echo " *************************************************"
echo " Database ORACLE HOME IN `hostname` STATUS"
echo " `date '+DATE: %m/%d/%y TIME:%H:%M:%S'`"
echo " *************************************************"
}
################################################################################
## ------------------------------------------------------------------------ ##
## MAIN SCRIPT EXECUTION ##
## ------------------------------------------------------------------------ ##
################################################################################
DB_LIST=$1
typeset -u INIT_COMMAND=$2
typeset -u INIT_MODE=$3
HOME_TO_FILTER=$4
LOG_DIR=/audit
if [ ${DB_LIST} = "oratab" ]; then
if [ "`uname -a | cut -c1-3`" = "Sun" ]; then
ORATABLOC="/var/opt/oracle/oratab"
else
ORATABLOC="/etc/oratab"
fi
fi
export DB_LIST INIT_COMMAND INIT_MODE HOME_TO_FILTER ORATABLOC LOG_DIR
print_header
cycle_sid_list
You will see a result similar to this oneoracle $ start_stop.sh TESTDB STARTUP NOMOUNT 11.2.0.3
*************************************************
CLIENT ORACLE HOME IN servidor1.oracleenespanol.blogspot.com STATUS
DATE: 07/05/12 TIME:08:47:11
*************************************************
TESTDB
*******
========>STARTUP TESTDB in NOMOUNT mode
========> STARTUP of TESTDB Succesfull - Jul 13, 12
- Jul 04, 12
- Jul 03, 12
-
Posts
- May 13, 13
When I started my work in IT, I used to be in a very small shop, and even though we had people in several places in the same state, everything used to be very centralized and from 9 to 5, and because we were basically only 2 people , our action plan used to be a talk over the lunch table and that would be it, we would go ahead and execute it after 5 PM, and I won’t lie sometimes before 5 :) .
Over the years I have understood that even if you are a 2 guy shop or a team of 15 separated by oceans and being miles apart, communication is the most important thing to have on your team, and one of the means of communication is having an action plan in place for any major/medium change you do in your organization. First this will generate discussions amongst your teammates and it will reduce the possibility of errors when you are faced with time and pressure constraints when implementing it.
This might sometimes feel like a mundane and boring task, as it will take an effort to come up with it and it will take time to verify it, but when game day comes along you will see the great benefit of having an action plan.
Another great benefit of having an action plan is that you also have a road map if you need to rollback your change, and that is also critical, because normally any major change or rollback is not done only by one person, take for example a change that takes about 7 or 8 hours to be done, and at the end when the UAT (User Application Testing) is done, 1 or 2 more hours ,the application team decides that a rollback is needed, you are probably not in a good state of mind to do the rollback after 8 hours of continuous work, if you have an action plan, one of your teammates can step in and you can have a rest, even if it is to go to the kitchen and have a sandwich and a coke and forget 10 minutes about that pressure.
As with life and with us being human, having an action plan doesn’t mean that everything will go smoothly or you won’t have an error in there, but believe me, it will reduce in a big way the possibility of an error if you execute it by memory or by doing one yourself without revision.
I do hope that you already have an action plan as part of your major/medium changes, but if you don’t, it is time to get FIT-ACER, here is an example of one (Kudos to Cesar Sanchez as it is his Action Plan Template), use it and modify it to your needs, it is a good start.
- Apr 22, 13
It is not uncommon for Oracle to have something undocumented , and this is just another case of this ailment.
We have a database in which we have two copies of the same backup, one we keep in the FRA, the other one in an external disk, which from time to time gets deleted from this second disk. But what happens after you do a crosscheck and one of those 2 backups is expired? The one in the FRA is available and the one in the external disk is expired for RMAN, but how will you see it in your listing?
Looking at the 11.2 documentation for the LIST command , it only tells you that the Status of a backup can be AVAILABLE, UNAVAILABLE, or EXPIRED, but as you can see below that is not the case with our database :)
RMAN> list backup summary; using target database control file instead of recovery catalog List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 1986 B F A DISK 17-APR-13 1 2 NO * 1987 B F M DISK 17-APR-13 1 2 NO * 1988 B F A DISK 17-APR-13 1 2 NO * 1989 B F A DISK 19-APR-13 1 1 NO TAG000661 1990 B A A DISK 19-APR-13 1 1 YES BUP_ARCH_FRA
When I first faced this, it got me curious as to what the meaning of the M status meant (and I still don’t know the exact meaning, but I have my supposition) . If I did a trace of my list backup summary command , I could see exactly what I knew it was happening:
pythian@oracleenespanol.local /home/pythian/working/antunez pythian $ rman target / debug trace rman.trc Recovery Manager: Release 11.2.0.3.0 - Production on Fri Apr 19 16:02:56 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. RMAN-06005: connected to target database: TESTDB (DBID=1834739652) RMAN> list backup summary;
This is a small result of the trace done above :
DBGMISC: 339 BS (datafile) key=1987 recid=1987 stamp=812962808 setstamp=812962807 setcount=2021
DBGMISC: level=1 level_i=-1 piececount=1 keepopts=0, site_key=0 [16:03:27.544]
DBGMISC: site_key=0 [16:03:27.544]
DBGMISC: chid=NIL parm=NIL [16:03:27.544]
DBGMISC: flags= [16:03:27.544]
DBGMISC: valid backup set list is [16:03:27.544]
DBGMISC: 1 VBS copy#=1 tag=BACKUP_CONTROLFILE_FRA deviceType=DISK status=A
DBGMISC: 1 BPIECEX key=3502 recid=3502 stamp=812962808
DBGMISC: bskey=1987 set_stamp=812962807 set_count=2021 site_key=0
DBGMISC: pieceno=1 handle=+FRA/test/backupset/2013_04_17/ncnnf0_backup_controlfile_fra_0.297.812962809
DBGMISC: device=DISK krmkch { count=0 found=FALSE }
DBGMISC: 2 VBS copy#=2 tag=BACKUP_OF_BACKUPSET deviceType=DISK status=X
DBGMISC: 1 BPIECEX key=3538 recid=3538 stamp=813024283
DBGMISC: bskey=1987 set_stamp=812962807 set_count=2021 site_key=0
DBGMISC: pieceno=1 handle=/backupdisk/TEST_bck/db_bckset_backup_TEST2_04-18-2013_v5o79kvn_1_2.rman
DBGMISC: device=DISK krmkch { count=0 found=FALSE }
DBGMISC: restore target list is [16:03:27.545]
DBGMISC: 1 ACT type=full fromSCN=0 toSCN=307822519 fno=0
DBGMISC: CURCFAs you can see above, one copy is EXPIRED and the other one is AVAILABLE, also because each copy has a different tag name, when you list them, you have an asterisk “*” for a tag of the backupset with Key 1987.
Both of these behaviours are perfectly normal, but you won’t find them anywhere in Oracle’s Documentation, so the next time you see this in your list command, you will know the exact meaning of this status. Have you seen this before in your RMAN listings ?
P.S. For me it means Multiple or Mixed.
- Apr 05, 13
One of the things I love about my career (Oracle DataBase Administrator) , is how fragile it is on a day in and day out basis. I can say with certainty , that every day I turn on my laptop to start working, it can probably be my last day working as a DBA, and this is not because I want it to be, it’s because unlike many jobs out there, the smallest mistake I make, can be my last mistake in my current job.
Believe me, I am not under appreciating other jobs or professions out there, but the level of stress that this job has at its peak moments, is comparable to being a surgeon performing an open heart surgery or an air traffic controller with two airplanes that are heading for a collision.
Just imagine this, if we make a mistake, a surgeon might not have the correct data he needs for the surgery, or the traffic controller may not have the electric power to co-ordinate his or her manoeuvres. Putting it more lightly, we are responsible to having your bank account statements up to date, basically your money. Of course we are the ones who have helped you maintain the history of those online games you played, so you can claim yourself the king of Warcraft or Madden.
What I want to get to, is if you are starting in this job, or you have been doing it for so long, you are only as good as your last 5 minutes, because the last mistake can be your last mistake. So what you want to do is take the necessary steps to avoid any mistake in the environment you are working, this is what we at Pythian call being a FIT-ACER DBA.
The best way that I have found to achieve or being closer to this, is by doing the following in the tools I work with:
PUTTY
The following configuration will help you put your words in Red with a black background, helping you distinguish a Development to a Production environment
Windows --> Colours --> Default Foreground Red ->255, GREEN ->0 , BLUE->0
Toad
This tool is a little more discreet , but the best thing you can do is in your connection window and search for the “Color” column, and switch all your production connections to the color red.
SQLPLUS
The best thing that you can do is in the glogin.sql, that is in $ORACLE_HOME/sqlplus/admin, add the following line
SET SQLPROMPT '&_connect_identifier >'
Also you can do what Uwe Hesse uses
Put this is a script called whoami.sql
set serveroutput on begin dbms_output.put_line('USER: '||sys_context('userenv',- 'session_user')); dbms_output.put_line('SESSION ID: '||sys_context('userenv',- 'sid')); dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv', - 'current_schema')); dbms_output.put_line('INSTANCE NAME: '||sys_context('userenv', - 'instance_name')); dbms_output.put_line('DATABASE ROLE: '||sys_context('userenv', - 'database_role')); dbms_output.put_line('OS USER: '||sys_context('userenv', - 'os_user')); dbms_output.put_line('CLIENT IP ADDRESS: '||sys_context('userenv', - 'ip_address')); dbms_output.put_line('SERVER HOSTNAME: '||sys_context('userenv', - 'server_host')); dbms_output.put_line('CLIENT HOSTNAME: '||sys_context('userenv', - 'host')); end; /And your result will be the following:
USER: SYS SESSION ID: 23 CURRENT_SCHEMA: SYS INSTANCE NAME: TESTDB DATABASE ROLE: PRIMARY OS USER: oracle CLIENT IP ADDRESS: SERVER HOSTNAME: servidor1.localdomain CLIENT HOSTNAME: servidor1.localdomain
UNIX
For us who handle unix and linux environments, one of our best friends to help us identify where we are and which is the current ORACLE_SID we have set in our environment is with the PS1 variable. With the example below, you can see several things which can help you identifying where you are at:
PS1=" `echo ${LOGNAME}`@`hostname` [\${ORACLE_SID}] \${PWD} `echo ${LOGNAME}` $ " export PS1- Example without PS1 environment set as above
[pythian@servidor1.localdomain ~]$ echo "Hi" Hi
- Example with PS1 environment set as above
pythian@servidor1.localdomain [TESTDB] /home/pythian/working/antunez pythian $ echo "Hi" Hi
SQL Developer
This Oracle tool allows you to put all your connections into folders, which will allow you to easily identify which type of connections you have
Right Click on the connection name --> Add To Folder
All of these tips might not seem like a lot, but believe me that when you are facing one of those stressful moments , you want to make sure that you are working where you should be working, and have faith in me that this is a little grain of salt that will help you maintain your job, at least for one more day :)
- Mar 11, 13
One thing that has always amazed me is the way that all of our decisions, big or small, have an impact in our life. Sometimes you can’t notice the fork in the roads that those decisions have caused, but sometimes they are as clear as rain.
For me, a simple Oracle DBA, one of those life decisions was to have a talk with a great friend about your own empowerment in social media and why I should open a Twitter account. This led me to follow Yury Velikanov, who then tweeted a conversation about how to share your blog with Oracle to be able to attend 2012 Oracle Open World (OOW) as a blogger.
So I did exactly that and my proposal got accepted. This this gave me the opportunity to make my way to San Francisco. During those days at OOW, I had the opportunity to hear Pythian’s founder, Paul Vallée’s Ted Talk. After those 15 minutes and an evening talk with Greg Leger, I had the inquietude to join this company that I had just heard of on this trip.
Now this wasn’t going to be a smooth ride, it was actually going to be an arduous interview and hiring process. This is due to the fact that Pythian is made up of the top talent from around the world, and it strives for that.
Fast forward 6 months later: March 4th, 2013 was my first day at Pythian and what a week it has been. Filled since the first minutes with training in the way of life of a Pythianite, and all I can say is that it has lived up to and exceeded everything that I had imagined it was going to be.
Here are a few insights into why:
- Let me know if the first day that you got to your new job, you sat down with the CEO of your company and had a one hour talk with him, regarding its mission and the vision and route the organization is taking.
- You were already encouraged by the owner of the company to start blogging and start raising your game.
- You get to be around, talk, sit around the dinner table and trade ideas with the people considered to be the top 5% worldwide of the field you are in.
- You see that they strive to avoid the pitfall in all of their employees, the famous phrase in Stephen King’s The Shinning “All work and no play makes Jack a dull boy”.
- You start believing Pythian’s point of view “that the quality of our work is essential to our clients’ success and the quality of our service is essential to our success“, and with this, it is critical to know that it is a team effort , not a stand alone hero. Hence they make every effort from the beginning, to make you feel like a part of the this growing family.
So by Friday, I was already wearing the “Love Your Data” shirt, sharing doughnuts around the Ottawa office and really excited to be here to give it my best to succeed and be an intricate part of this organization.
And now all I can say after week one has come and gone, and looking back at all the sacrifices and choices I made to be here at Pythian (which I won’t go into in this post, that is for a one on one coffee if you want to hear about it), is that I’m looking forward to week two, three, four and the rest to come …
-
Info
- CoE Database Leader en SofttekInformation Technology and Services | Mexico, MXExperience
- Jan 2010 - Mar 2013CoE Database Leader and Delivery Manager / Softtek China
- Mar 2007 - Jan 2010PeopleSoft Oracle DBA / Softtek
- Dec 2004 - Mar 2007Oracle DBA / ICHISAL, Chihuahua, Mex.
Additional Information-
Websites:
-
Tweets
- @OsamaOracle Im not saying I approve , Im just saying Ive seen worse
- It might seem strange to someone who hasn't lived in China, but I have seen worse things done to employees over there http://t.co/n9cv3zb8vaMay 18, 13 from web Reply, Retweet, Favorite
- perhaps database administration is a career you should consider." Goff, Leslie. Computerworld 26. 46 (Nov 16, 1992): 179.May 18, 13 from web Reply, Retweet, Favorite
- If you're a glutton for work with a high tolerance for stress and an interest in avoiding the limelight,May 18, 13 from web Reply, Retweet, Favorite
- Bear with me as this is a several tweet due to the length :May 18, 13 from web Reply, Retweet, Favorite
- So I relented , had to google "How to Shutdown Windows 8"
- @leight0nn Ill never go back to windows but all kidding aside where do you shut it off, I just killed the VM instead
- @informatux :) kill -9 vmwin8
- @leight0nn rofl, the good thing is that is a VM, so can kill it quickly
- Trying Win 8 for the first time, qq how do you turn off the freaking OS ?
- @MichaelAbbeyCAN one of the best places in Ottawa that I went to
- @MichaelAbbeyCAN ahhh , hehehe Monday Ill do that, for now Ill let Andre handle the weekend ;)
- @MichaelAbbeyCAN :) I cant today , on my way to my MBA class, but hope the Sens can rebound from that previous game
- @leight0nn That is one nasty looking snake
- Moores law applied perfectly today, no pages all day , 15 mins before I had to leave 3 pages >|
- @DBAKevlar you and your family are in my prayers
- @MartinDBA @orcldoug seems @UKOUG is going downhill after @debralilley left,"The group tends to talk about very technical subjects" funny
- Because we are a community in the good times and not so good,let give Kellyn´s @DBAKevlar sister a hand this time http://t.co/Eaxk3D2x5SMay 17, 13 from web Reply, Retweet, Favorite
- @DBAKevlar @curtisbl294 Thank you Kellyn, as always , great pres, learned a couple of things
-
Photos
-
Posts
- Listening to Who Loves You by Frankie Valli & The Four Seasons
One of my faves of the 70s – Preview it on Path.
- Listening to It's the End of the World As We Know It (And I Feel Fine) by R.E.M.
Just to get in the mood ;) Happy Thursday – Preview it on Path.
Fear is not real, It’s a product of thoughts you create, Don’t misunderstand,Danger is very real but fear is a choice
- Listening to Only Human by Jason Mraz
Have an awesome middle of the week – Preview it on Path.
- Listening to Running On Faith by Eric Clapton
Great song for this Tuesday, have a great day whatever you make of it – Preview it on Path.
- Listening to Moonshine by Bruno Mars
Upbeat song for this snowy Friday – Preview it on Path.
- Thought via Path
Your past is not your present, and your present is not your future, you just give life all you got – Read on Path.
- Listening to I Can't Wait by Stevie Nicks
Day 1 of a brand new year – Preview it on Path.
…this is all I know, this is why so hard I go,
I swear to God I put my heart and soul into this more than anybody knows- Listening to Hurts Like Heaven (Live) by Coldplay
I feel a little bit nervous and I cant Relax #LoveYourData – Preview it on Path.
- Listening to God Put a Smile Upon Your Face (Live) by Coldplay
God put a smile upon my face, how about you? – Preview it on Path.
