miércoles, 30 de mayo de 2012

BAJAR SERVICIOS DATAGUARD

ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database disconnect;
alter database recover managed standby database disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> alter system set dg_broker_start=FALSE;


System altered.

SQL> SQL>
SQL> alter database recover managed standby database disconnect;
alter database recover managed standby database disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL>

miércoles, 23 de mayo de 2012

DATAGUARD SCRIPTS SQL PARA VALIDAR ESTADO



Startup commands
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;


To remove a delay from a standby
alter database recover managed standby database cancel;
alter database recover managed standby database nodelay disconnect;






Cancel managed recovery
alter database recover managed standby database cancel;






Register a missing log file
alter database register physical logfile '';






If FAL doesn't work and it says the log is already registered
alter database register or replace physical logfile '';


If that doesn't work, try this...
shutdown immediate
startup nomount
alter database mount standby database;
alter database recover automatic standby database;


wait for the recovery to finish - then cancel
shutdown immediate
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;






Check which logs are missing
Run this on the standby...
select local.thread#
, local.sequence# from
(select thread#
, sequence#
from v$archived_log
where dest_id=1) local
where local.sequence# not in
(select sequence#
from v$archived_log
where dest_id=2 and
thread# = local.thread#)
/






Disable/Enable archive log destinations
alter system set log_archive_dest_state_2 = 'defer';
alter system set log_archive_dest_state_2 = 'enable';






Turn on fal tracing on the primary db
alter system set LOG_ARCHIVE_TRACE = 128;






Stop the Data Guard broker
alter system set dg_broker_start=false
/






Show the current instance role
select database_role
from v$database
/






Logical standby apply stop/start
Stop...
alter database stop logical standby apply;


Start...
alter database start logical standby apply;






See how up to date a physical standby is
Run this on the primary
set numwidth 15
select max(sequence#) current_seq
from v$log
/


Then run this on the standby
set numwidth 15
select max(applied_seq#) last_seq
from v$archive_dest_status
/






Display info about all log destinations
To be run on the primary
set lines 100
set numwidth 15
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4

select ds.dest_id id
, ad.status
, ds.database_mode db_mode
, ad.archiver type
, ds.recovery_mode
, ds.protection_mode
, ds.standby_logfile_count "SRLs"
, ds.standby_logfile_active active
, ds.archived_seq#
from v$archive_dest_status ds
, v$archive_dest ad
where ds.dest_id = ad.dest_id
and ad.status != 'INACTIVE'
order by
ds.dest_id
/






Display log destinations options
To be run on the primary
set numwidth 8 lines 100
column id format 99
select dest_id id
, archiver
, transmit_mode
, affirm
, async_blocks async
, net_timeout net_time
, delay_mins delay
, reopen_secs reopen
, register,binding
from v$archive_dest
order by
dest_id
/






List any standby redo logs
set lines 100 pages 999
col member format a70
select st.group#
, st.sequence#
, ceil(st.bytes / 1048576) mb
, lf.member
from v$standby_log st
, v$logfile lf
where st.group# = lf.group#
/



SCRIPTS PARA VALIDAR ESTADO DEL DATA GUARD

export ORACLE_SID=DEUDA
df -h
sqlplus / as sysdba @monitor_dataguard.sql
rman target / LOG = dataguard.log append cmdfile=list_backup.rcv


@monitor_dataguard.sql

spool dataguard.log
@dbmap.sql
@@dataguard.sql
spool off
exit


@dbmap.sql

set serveroutput on size 10000

COLUMN name FORMAT A80
COLUMN member FORMAT A80
COLUMN dest_name FORMAT A40
COLUMN destination FORMAT A40
COLUMN datafile FORMAT A55
COLUMN tbs FORMAT A20
COLUMN file FORMAT A60
COLUMN highwater FORMAT 999999999999
COLUMN last_value FORMAT 999999999999
SET PAGESIZE 24
set linesize 180


PROMPT DATABASE INFO ...
select dbid, current_scn, db_unique_name, created, log_mode, open_mode, protection_mode, protection_level, dataguard_broker from v$database;

PROMPT ARCHIVOS DE CONTROLFILE ...
select name from v$controlfile;

PROMPT ARCHIVOS REDOLOGS ...
select group#, type, member from v$logfile;


prompt

prompt

PROMPT ARCHIVOS DE ARCHIVELOG ...
select dest_id, dest_name, destination from v$archive_dest where status='VALID' and schedule='ACTIVE' ;

PROMPT INFORMACION DE TABLESPACES Y DATAFILES...

select file#, name "DATAFILE", status, enabled, bytes/1024/1024 "SIZE Mb" from v$datafile order by file# ;



@dataguard.sql

set lines 150
col name format a50
col group format a10
col client_pid format a15
col client_dbid format a15
col message format a100
col member format a80
col status format a20

select * from v$instance;

show parameter control

show parameter config

show parameter db_recovery

select * from v$recovery_file_dest;

select * from v$flash_recovery_area_usage;

select
round((a.space_limit / 1024 / 1024 / 1024), 2) as flash_in_gb,
round((a.space_used / 1024 / 1024 / 1024), 2) as flash_used_in_gb,
round((a.space_reclaimable / 1024 / 1024 / 1024), 2) as flash_reclaimable_gb,
sum(b.percent_space_used) as percent_of_space_used
from
v$recovery_file_dest a,
v$flash_recovery_area_usage b
group by
space_limit,
space_used ,
space_reclaimable ;


select THREAD# "THREAD", (select max(sequence#) from v$archived_log where applied='YES') MAX_APPLIED,
(select max(sequence#) from v$archived_log where archived='YES') MAX_ARCHIVED, max(FIRST_TIME) "DATE"
from v$archived_log group by THREAD#;


PROMPT ESTADO DATAGUARD

select timestamp, to_char ( TIMESTAMP, 'dd-mon-yyyy hh24:mi:ss'), message
from v$dataguard_status
where to_date(timestamp,'dd-mon-yyyy') =to_date (sysdate,'dd-mon-yyyy');

PROMPT GAP DE ARCHIVOS DE LOG

select * from v$archive_gap;

PROMPT ESTADO DE LOS DESTINOS DE ARCHIVELOG

select dest_id, status, error from v$archive_dest where dest_id in (1,2,3);

PROMPT ARCHIVOS DE LOG

select * from v$logfile;

PROMPT MODO DE RECUPERACION

COL DEST_NAME FORMAT A50

SELECT DEST_ID, DEST_NAME, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

PROMPT PROCESOS DATAGUARD

select * from v$managed_standby;

exit


list_backup.rcv

Show all;
list backup summary;



martes, 22 de mayo de 2012

Repositorio Oracle Linux

[root@oraclelinux62 ~]# wget http://public-yum.oracle.com/public-yum-ol6.repo \
-P /etc/yum.repos.d/
--2012-03-23 00:18:25-- http://public-yum.oracle.com/public-yum-ol6.repo
Resolving public-yum.oracle.com... 141.146.44.34
Connecting to public-yum.oracle.com|141.146.44.34|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1461 (1.4K) [text/plain]
Saving to: “/etc/yum.repos.d/public-yum-ol6.repo”

100%[=================================================>] 1,461 --.-K/s in 0s

2012-03-23 00:18:26 (37.1 MB/s) - “/etc/yum.repos.d/public-yum-ol6.repo” saved [1461/1461]

For Oracle Linux 5, the file name would be public-yum-ol5.repo in the URL above instead. The "_latest" repositories that contain the errata packages are already enabled by default — you can simply pull in all available updates by running "yum update" next:

[root@oraclelinux62 ~]# yum update
Loaded plugins: refresh-packagekit, security
ol6_latest | 1.1 kB 00:00
ol6_latest/primary | 15 MB 00:42
ol6_latest 14643/14643
Setting up Update Process
Resolving Dependencies
--> Running transaction check
---> Package at.x86_64 0:3.1.10-43.el6 will be updated
---> Package at.x86_64 0:3.1.10-43.el6_2.1 will be an update
---> Package autofs.x86_64 1:5.0.5-39.el6 will be updated
---> Package autofs.x86_64 1:5.0.5-39.el6_2.1 will be an update
---> Package bind-libs.x86_64 32:9.7.3-8.P3.el6 will be updated
---> Package bind-libs.x86_64 32:9.7.3-8.P3.el6_2.2 will be an update
---> Package bind-utils.x86_64 32:9.7.3-8.P3.el6 will be updated
---> Package bind-utils.x86_64 32:9.7.3-8.P3.el6_2.2 will be an update
---> Package cvs.x86_64 0:1.11.23-11.el6_0.1 will be updated
---> Package cvs.x86_64 0:1.11.23-11.el6_2.1 will be an update

miércoles, 9 de mayo de 2012

DATAPROTECTOR HP

SERVICIO CLIENTE DATAPROTECTOR HP S.O

[root@mhbdorasa07 ~]# /etc/init.d/xinetd status
xinetd (pid 4776) is running...
[root@mhbdorasa07 ~]# /etc/init.d/xinetd stop
Stopping xinetd: [ OK ]
[root@mhbdorasa07 ~]# /etc/init.d/xinetd status
xinetd is stopped
[root@mhbdorasa07 ~]# /etc/init.d/xinetd start
Starting xinetd: [ OK ]

# /etc/init.d/xinetd restart

jueves, 3 de mayo de 2012

RESTORE TAPE

RECUPERACION DE NOMINA VERITAS CATALOGO RMAN SENA

connect catalog rman/rman@CATALOGO;
connect target sys/sena@NOMINA_SENA;
connect auxiliary sys/password;
resync catalog;
RUN {
ALLOCATE AUXILIARY CHANNEL ch00
TYPE 'SBT_TAPE' parms 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1,ENV=(NB_ORA_CLIENT=sdigbogsisg021-bck,NB_ORA_POLICY=sdigbogsisg021_DB_NOMINA_F_DS,NB_ORA_SERV=bksrv01,NB
_ORA_SCHED=Default-Application-Backup)';

ALLOCATE AUXILIARY CHANNEL ch01
TYPE 'SBT_TAPE' parms 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1,ENV=(NB_ORA_CLIENT=sdigbogsisg021-bck,NB_ORA_POLICY=sdigbogsisg021_DB_NOMINA_F_DS,NB_ORA_SERV=bksrv01,NB
_ORA_SCHED=Default-Application-Backup)';

SET NEWNAME FOR DATAFILE 1 to '/u02/oradata/NOMINA/sistema/system01.dbf' ;
SET NEWNAME FOR DATAFILE 2 to '/u02/oradata/NOMINA/sistema/undotbs01.dbf' ;
SET NEWNAME FOR DATAFILE 3 to '/u02/oradata/NOMINA/sistema/sysaux01.dbf' ;
SET NEWNAME FOR DATAFILE 4 to '/u02/oradata/NOMINA/sistema/users01.dbf' ;
SET NEWNAME FOR DATAFILE 5 to '/u02/oradata/NOMINA/sistema/cwmlite01.dbf' ;
SET NEWNAME FOR DATAFILE 6 to '/u02/oradata/NOMINA/sistema/drsys01.dbf' ;
SET NEWNAME FOR DATAFILE 7 to '/u02/oradata/NOMINA/sistema/indx01.dbf' ;
SET NEWNAME FOR DATAFILE 8 to '/u02/oradata/NOMINA/datos/KACTUS_DAT02.DBF' ;
SET NEWNAME FOR DATAFILE 9 to '/u02/oradata/NOMINA/datos/KACTUS_DAT01.DBF' ;
SET NEWNAME FOR DATAFILE 10 to '/u02/oradata/NOMINA/indices/KACTUS_NDX01.DBF' ;
SET NEWNAME FOR DATAFILE 11 to '/u02/oradata/NOMINA/datos/KACTUS_RBK01.DBF' ;
SET NEWNAME FOR DATAFILE 12 to '/u02/oradata/NOMINA/sistema/odm01.dbf' ;
SET NEWNAME FOR DATAFILE 13 to '/u02/oradata/NOMINA/sistema/tools01.dbf' ;
SET NEWNAME FOR DATAFILE 14 to '/u02/oradata/NOMINA/sistema/xdb01.dbf' ;
SET NEWNAME FOR DATAFILE 15 to '/u02/oradata/NOMINA/sistema/example01.dbf' ;
SET NEWNAME FOR DATAFILE 16 to '/u02/oradata/NOMINA/sistema/UNDOTBS201.dbf' ;
DUPLICATE TARGET DATABASE TO NOMINA
UNTIL TIME 'SYSDATE-2'
PFILE=/u1/app/oracle/product/10.2.0/dbs/initNOMINA.ora
NOFILENAMECHECK
LOGFILE
GROUP 1 ('/u02/oradata/NOMINA/redologs/redo01B.log') size 200M reuse,
GROUP 2 ('/u02/oradata/NOMINA/redologs/redo02B.log') size 200M reuse,
GROUP 3 ('/u02/oradata/NOMINA/redologs/redo03B.log') size 200M reuse ;
RELEASE CHANNEL ch00 ;
RELEASE CHANNEL ch01 ;
}



connect catalog rman/rmo3344@CATRMAN;
connect target sys/migracion00@credipro;
connect auxiliary sys/oracle;
RUN {
ALLOCATE AUXILIARY CHANNEL mi_cinta TYPE 'SBT_TAPE' parms 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64,ENV=(NB_ORA_CLIENT=omega-bck,NB_ORA_POLICY=CRVRS_OMEGA_DB_F_DIA,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';
SET NEWNAME FOR DATAFILE 1 to '/u00/mantenim/SYSTEM01.DBF' ;
SET NEWNAME FOR DATAFILE 2 to '/u11/mantenim/OPCPRO_MVTO_SDOCTBLE_01.DBF' ;
SET NEWNAME FOR DATAFILE 3 to '/u11/mantenim/SYSAUX01.DBF' ;
SET NEWNAME FOR DATAFILE 4 to '/u11/mantenim/USERS01.DBF' ;
SET NEWNAME FOR DATAFILE 5 to '/u11/mantenim/OPC_DATNORMAL' ;
SET NEWNAME FOR DATAFILE 6 to '/u11/mantenim/OPCPRO_CLIENTES01' ;
SET NEWNAME FOR DATAFILE 7 to '/u11/mantenim/OPCPRO_CUOTAS_FIJAS_01' ;
SET NEWNAME FOR DATAFILE 8 to '/u11/mantenim/OPCPRO_INDEX_02_01' ;
SET NEWNAME FOR DATAFILE 9 to '/u11/mantenim/OPCPRO_INDEX_MVTO_HIST_01' ;
SET NEWNAME FOR DATAFILE 10 to '/u11/mantenim/OPCPRO_INT_DIARIO_01' ;
SET NEWNAME FOR DATAFILE 11 to '/u11/mantenim/OPCPRO_LINEAS_MONEDAS_01' ;
SET NEWNAME FOR DATAFILE 12 to '/u11/mantenim/OPCPRO_MVTO_HISTORICO_01' ;
SET NEWNAME FOR DATAFILE 13 to '/u11/mantenim/OPCPRO_MVTO_SDO_CTBLE_01' ;
SET NEWNAME FOR DATAFILE 14 to '/u11/mantenim/OPCPRO_MVTO_SDO_DETALLE_01' ;
SET NEWNAME FOR DATAFILE 15 to '/u11/mantenim/OPCPRO_PEQ_0101' ;
SET NEWNAME FOR DATAFILE 16 to '/u11/mantenim/OPCPRO_SALDOS_CLIENTES_01' ;
SET NEWNAME FOR DATAFILE 17 to '/u11/mantenim/OPCPRO_CUOTAS_FIJAS_02' ;
SET NEWNAME FOR DATAFILE 18 to '/u11/mantenim/OPCPRO_CUOTAS_FIJAS_03' ;
SET NEWNAME FOR DATAFILE 19 to '/u11/mantenim/OPCPRO_DIRECCIONES01' ;
SET NEWNAME FOR DATAFILE 20 to '/u11/mantenim/OPCPRO_INDEX_01_02' ;
SET NEWNAME FOR DATAFILE 21 to '/u11/mantenim/OPCPRO_INDEX_02_02' ;
SET NEWNAME FOR DATAFILE 22 to '/u11/mantenim/OPCPRO_INDEX_MVTO_HIST_02' ;
SET NEWNAME FOR DATAFILE 23 to '/u11/mantenim/OPCPRO_INT_DIARIO_02' ;
SET NEWNAME FOR DATAFILE 24 to '/u11/mantenim/OPCPRO_MVTO_HISTORICO_02' ;
SET NEWNAME FOR DATAFILE 25 to '/u11/mantenim/OPCPRO_MVTO_SDO_CTBLE_02' ;
SET NEWNAME FOR DATAFILE 26 to '/u77/mantenim/OPCPRO_MVTO_SDO_CTBLE_03' ;
SET NEWNAME FOR DATAFILE 27 to '/u77/mantenim/OPCPRO_MVTO_SDO_DETALLE_02' ;
SET NEWNAME FOR DATAFILE 28 to '/u77/mantenim/OPCPRO_MVTO_SDO_DETALLE_03' ;
SET NEWNAME FOR DATAFILE 29 to '/u77/mantenim/OPCPRO_PEQ-0201' ;
SET NEWNAME FOR DATAFILE 30 to '/u77/mantenim/OPCPRO_TARJETAS01' ;
SET NEWNAME FOR DATAFILE 31 to '/u77/mantenim/OPCPRO_INDEX_01_01' ;
SET NEWNAME FOR DATAFILE 32 to '/u77/mantenim/OPCPRO_INT_DIARIO_03' ;
SET NEWNAME FOR DATAFILE 33 to '/u77/mantenim/OPCPRO_MVTO_HISTORICO_03' ;
SET NEWNAME FOR DATAFILE 34 to '/u77/mantenim/OPCPRO_PEQ-0202' ;
SET NEWNAME FOR DATAFILE 35 to '/u77/mantenim/OPCPRO_BI' ;
SET NEWNAME FOR DATAFILE 36 to '/u77/mantenim/OPCPRO_PEQ_0203' ;
SET NEWNAME FOR DATAFILE 37 to '/u77/mantenim/PERFSTAT01.DBF' ;
SET NEWNAME FOR DATAFILE 38 to '/u77/mantenim/OPCPRO_INDEX_01_03' ;
SET NEWNAME FOR DATAFILE 39 to '/u77/mantenim/OPCPRO_PEQ_0204' ;
SET NEWNAME FOR DATAFILE 40 to '/u77/mantenim/OPCPRO_INDEX_02_03' ;
SET NEWNAME FOR DATAFILE 41 to '/u88/mantenim/OPCPRO_MVTO_SDO_DETALLE_04' ;
SET NEWNAME FOR DATAFILE 42 to '/u88/mantenim/OPCPRO_INDEX_MVTO_HIST_03' ;
SET NEWNAME FOR DATAFILE 43 to '/u88/mantenim/OPCPRO_MVTO_SDO_CTBLE_04' ;
SET NEWNAME FOR DATAFILE 44 to '/u88/mantenim/OPCPRO_INDEX_01_04' ;
SET NEWNAME FOR DATAFILE 45 to '/u88/mantenim/OPCPRO_INT_DIARIO_04' ;
SET NEWNAME FOR DATAFILE 46 to '/u88/mantenim/OPC_DATNORMAL_2' ;
SET NEWNAME FOR DATAFILE 47 to '/u88/mantenim/CRED_DEPURA01.ORA' ;
SET NEWNAME FOR DATAFILE 48 to '/u88/mantenim/CRED_DEPURA02.ORA' ;
SET NEWNAME FOR DATAFILE 49 to '/u88/mantenim/OPCPRO_MVTO_SDO_DET_01' ;
SET NEWNAME FOR DATAFILE 50 to '/u88/mantenim/OPCPRO_MVTO_SDO_DET_02' ;
SET NEWNAME FOR DATAFILE 51 to '/u88/mantenim/OPC_CUSTOM_CV01.ORA' ;
SET NEWNAME FOR DATAFILE 52 to '/u88/mantenim/OPC_CUSTOM_CV02.ORA' ;
SET NEWNAME FOR DATAFILE 53 to '/u88/mantenim/OPCPRO_MVTO_SDO_DET_03' ;
SET NEWNAME FOR DATAFILE 54 to '/u88/mantenim/UNDOTBS_01' ;
SET NEWNAME FOR DATAFILE 55 to '/u88/mantenim/OPCPRO_MVTO_SDOCTBLE_02.DBF' ;
SET NEWNAME FOR DATAFILE 56 to '/u88/mantenim/OPCPRO_MVTO_SDOCTBLE_03.DBF' ;
SET NEWNAME FOR DATAFILE 57 to '/u88/mantenim/OPCPRO_MVTO_SDO_DETALLE_05' ;
SET NEWNAME FOR DATAFILE 58 to '/u99/mantenim/OPCPRO_MVTO_DIARIO_01' ;
SET NEWNAME FOR DATAFILE 59 to '/u99/mantenim/OPCPRO_MVTO_DIARIO_02' ;
SET NEWNAME FOR DATAFILE 60 to '/u99/mantenim/cpro_datafile00.ora' ;
SET NEWNAME FOR DATAFILE 61 to '/u99/mantenim/OPCPRO_MVTO_SDO_DETALLE_06.ORA' ;
SET NEWNAME FOR DATAFILE 62 to '/u99/mantenim/OPCPRO_INDEX_01_05' ;
SET NEWNAME FOR DATAFILE 63 to '/u99/mantenim/OPCPRO_INDEX_MVTO_HIST_04' ;
SET NEWNAME FOR DATAFILE 64 to '/u99/mantenim/OPCPRO_INT_DIARIO_05' ;
SET NEWNAME FOR DATAFILE 65 to '/u99/mantenim/OPCPRO_MVTO_DIARIO_03' ;
SET NEWNAME FOR DATAFILE 66 to '/u99/mantenim/OPC_DATNORMAL_3' ;
SET NEWNAME FOR DATAFILE 67 to '/u99/mantenim/OPCPRO_MVTO_SDOCTBLE_04.DBF' ;
DUPLICATE TARGET DATABASE TO mantenim
PFILE=/u01/oracle/db/10.2.0/dbs/initmantenim.ora
LOGFILE
GROUP 1 ('/u00/mantenim/redo01.log') size 200M reuse,
GROUP 2 ('/u00/mantenim/redo02.log') size 200M reuse,
GROUP 3 ('/u00/mantenim/redo03.log') size 200M reuse ;
RELEASE CHANNEL mi_cinta ;
}

CAMBIAR PROMPT SQLPLUS

Cambiar el Prompt de Sqlplus
publicado a la‎(s)‎ 14/09/2011 13:05 por Ivan Acosta [ actualizado el 29/09/2011 12:14 ]






Esto lo podemos conseguir creando un archivo llamado login.sql e indicar lo siguiente, o mejor ponerlo en glogin.sql para que nos pueda servir desde cualquier directorio. Este archivo glogin.sql se encuentra en $ORACLE_HOME/sqlplus/admin.
Lo que necesitamos indicar es:




-- Edit By Ivan Acosta

set sqlprompt "_user'@'_connect_identifier > "


set time on
define _editor=vi