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 :)
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:
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 YES
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)
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:00
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)'
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
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
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'
;
}
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> exit
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
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.
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
----------
1
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 65
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 0
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
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;
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
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.
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
TESTDB1 >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
TESTDB1 >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
oracle $ 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.
TESTDB1 >DELETE FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP' AND SCHEMA= 'SYS';
3 rows deleted.
TESTDB1 >commit;
Commit complete.
Starting "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/unloadedDB_FILE_NAME_CONVERT '+DATA1/SOURCE','DATA1/DUP1'
DB_FILE_NAME_CONVERT '+DATA1/SOURCE','+DATA1/DUP1','+DATA2/TEST','+DATA1/DUP1'
RMAN-06169: could not read file header for datafile 148 error reason 9
RMAN-06056: could not access datafile 148
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
DUP21 >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
DUP21 >oradebug setmypid
Statement processed.
DUP21 >oradebug unlimit
Statement processed.
DUP21 >oradebug dump file_hdrs 3
Statement processed.
DUP21 >oradebug tracefile_name
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=4005607769=0xeec0b959, Db Name='DUP1'
DUP11 >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
DUP21 >ALTER TABLESPACE TEST1 OFFLINE IMMEDIATE;
Tablespace altered.
DUP21 >DROP TABLESPACE TEST1;
Tablespace dropped.
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.
.
.
.
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
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>
.
.
.
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"
******************************************************************************************
.
.
.
__________________________________________________________________________________________
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
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:
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 datos
TESTDB> 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
RMAN> 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
RMAN> list restore point all;
SCN RSP Time Type Time Name
---------------- -------------------- ---------- -------------------- ----
234073 Oct 25 2012 08:41:58 BEFORE_UPGRADE
SQL> 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
oracle@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
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
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 :
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"
root@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
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
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
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
RMAN> 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
RMAN> 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
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 proxy
TESTDB> 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.
TESTDB> 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
TESTDB> create table prueba (id number);
Table created.
TESTDB> select owner from all_tables where table_name='PRUEBA';
OWNER
---------------
USUARIO_PRINCI
TESTDB> 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
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**
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
TESTDB21 >select dbid,name from v$database;
DBID NAME
---------- --------
3024984012 TESTDB2
TESTDB21 >select FHDBI,FHDBN from X$KRCFH
2 /
FHDBI FHDBN
---------- --------
8573487567 TESTDB2
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
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 error
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/dba01/oracle/TESTDB/audit
oracle $ rm ./*.aud
ksh: /usr/xpg4/bin/rm: arg list too long
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
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
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
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
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ón
ORA-00600: internal error code, arguments: [2662], [2910], [829087674], [2910], [829528307], [1451708231], [], [], [], [], [], []
TESTDB> 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
TESTDB> Select name ,file#,ts# from v$datafile where rfile#=138 ;
dbv file=<NOMBRE_DEL_DATAFILE_OBTENIDO_ARRIBA> blocksize=<db_block_size>
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
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 400
oracle@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
. ${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
oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /mount/dba01/oracle/TESTDB/scripts
oracle $ nohup ./test.sh &
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 prendido
TESTDB> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
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
TESTDB> 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
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: 3
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
Recovery 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.
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.
ALTER DISKGROUP GBS_HRMS_DATA RENAME ALIAS '+GBS_HRMS_DATA/DBATEST/CONTROLFILE/control02.ctl'
TO '+GBS_HRMS_DATA/DBATEST/CONTROLFILE/control02.ctl.old';
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: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
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_784500232
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.
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
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
oracle@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
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';
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 OH
oracle@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 .
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
oracle@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
ORACLE_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
oracle@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
oracle@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
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
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;
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
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
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:
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.
oracle@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;
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
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
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'
DB_FILE_NAME_CONVERT '+DATA1/SOURCE','+DATA1/DUP1','+DATA2/TEST','+DATA1/DUP1'
RMAN-06169: could not read file header for datafile 148 error reason 9
RMAN-06056: could not access datafile 148
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
DUP21 >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
DUP21 >oradebug setmypid
Statement processed.
DUP21 >oradebug unlimit
Statement processed.
DUP21 >oradebug dump file_hdrs 3
Statement processed.
DUP21 >oradebug tracefile_name
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=4005607769=0xeec0b959, Db Name='DUP1'
DUP11 >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
DUP21 >ALTER TABLESPACE TEST1 OFFLINE IMMEDIATE;
Tablespace altered.
DUP21 >DROP TABLESPACE TEST1;
Tablespace dropped.
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.
.
.
.
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
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>
.
.
.
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"
******************************************************************************************
.
.
.
__________________________________________________________________________________________
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
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
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 .
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 :
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:
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"
root@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
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
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
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
RMAN> 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
RMAN> 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
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
TESTDB21 >select dbid,name from v$database;
DBID NAME
---------- --------
3024984012 TESTDB2
TESTDB21 >select FHDBI,FHDBN from X$KRCFH
2 /
FHDBI FHDBN
---------- --------
8573487567 TESTDB2
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
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 database
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
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
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
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 present
ORA-00600: internal error code, arguments: [2662], [2910], [829087674], [2910], [829528307], [1451708231], [], [], [], [], [], []
TESTDB> 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
TESTDB> Select name ,file#,ts# from v$datafile where rfile#=138 ;
dbv file=<NAME_OF_DATAFILE_OBTAINED_ABOVE> blocksize=<db_block_size>
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
#!/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
oracle $ 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
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.
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.
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
[pythian@servidor1.localdomain ~]$ echo "Hi" Hi
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 :)
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:
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 …
One of my faves of the 70s – Preview it on Path.
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
Have an awesome middle of the week – Preview it on Path.
Great song for this Tuesday, have a great day whatever you make of it – Preview it on Path.
Upbeat song for this snowy Friday – Preview it on Path.
Your past is not your present, and your present is not your future, you just give life all you got – Read on Path.
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
I feel a little bit nervous and I cant Relax #LoveYourData – Preview it on Path.
God put a smile upon my face, how about you? – Preview it on Path.