ORACLE 11G_ HOW TO KNOW THE DDL FOR SCHEDULER-JOBS OR JOB_CLASS

Sometimes we need to create in another environment, a job or job class that it exist in current environment, but we don't know the ddl sintax, then we need to extract the sentence from dual.

In this case we need to know the ddl of job class called HISTORICAL_JOB_CLASS.

If you execute like SYSDBA

SQL> select dbms_metadata.get_ddl('JOB','HISTORICAL_JOB_CLASS') from dual;

the system return this error

ERROR:
ORA-31604: invalid NAME parameter "NAME" for object type JOB in function
SET_FILTER
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1

or

If you execute like SYSDBA

SQL> select dbms_metadata.get_ddl('JOB_CLASS','HISTORICAL_JOB_CLASS') from dual;

ERROR:

ORA-31600: invalid input value JOB_CLASS for parameter OBJECT_TYPE in function

GET_DDL

ORA-06512: at "SYS.DBMS_METADATA", line 5805

ORA-06512: at "SYS.DBMS_METADATA", line 8344

ORA-06512: at line 

but if you execute like SYSDBA

SQL>  select dbms_metadata.get_ddl('PROCOBJ','HISTORICAL_JOB_CLASS') from dual;

the system return

BEGIN 

dbms_scheduler.create_job_class('"HISTORICAL_JOB_CLASS"',NULL,'HISTORICO',64,NULL,

'Default Job Class for Bath jobs'

);

COMMIT; 

END; 

That's the right way to know the ddl of job or job_class

ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []

Sometimes during a query (select) it appears this fatal error

ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []

I usually solve it recreating the index or indexes of the affected table

To know and generate the sentences to rebuild the index/indexes from the affected table use this sentence


select 'alter index ' ||owner|| '.' ||index_name|| ' rebuild;' from dba_indexes where table_name='affected table name';

For example, to rebuild all indexes from a table named test01 and owner Gorka, the steps will be....

1.- SQL> select 'alter index ' ||owner|| '.' ||index_name|| ' rebuild;' from dba_indexes where table_name='TEST01';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index gorka.test_index01 rebuild;
alter index gorka.test_index_PK rebuild;
alter index gorka.test_index02 rebuild;
alter index gorka.test_index03 rebuild;


4 rows selected.

2.- execute the sentences generated (copy the list and paste in sqlplus and then return)

SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL>
Index altered.

MATRIX: ORACLE DATABASE ROADMAP 2006-2022

MATRIX: ORACLE DATABASE UPGRADE

ORACLE CSSCAN: HOW TO INSTALL AND RUN

CSSCAN is a oracle utility to check the database character set conversion.

TO INSTALL:

The character set migration utility schema is installed by running the "$ORACLE_HOME/rdbms/admin/csminst.sql" script in SQL*Plus as the SYS user

TO RUN

csscan \"sys/password@sid AS SYSDBA\" full=y tochar=<new database character set> for example AL32UTF8

if you run with sintax csscan "sys/password@sid AS SYSDBA" it's appears the message

LRM-00108: invalid positional parameter value 'as'

failed to process command line parameters ç

Scanner terminated unsuccessfully

ORACLE: COMMAND HOW TO KNOW OPENED CURSORS CURRENT

This is a command to know opened cursors current order by user and sid

select b.sid, a.username, b.value Cursores_Abiertos from v$session a, v$sesstat b, v$statname c where c.name in ('opened cursors current') and b.statistic# = c.statistic# and a.sid = b.sid and a.username is not null and b.value >0 order by a.username,b.sid;

ORACLE HOW TO MANIPULATE LISTENER.LOG WITHOUT STOP THE LISTENER

Sometimes, you need manipulate the listener.log because it's too big or your disk device is full and you can't stop the service for your business then this is the solution, stop the generation of  entries into the logfile but the listener continue executing.

this is a script into crontab running each 2 weeks. In this script I stop the inserts into the listener.log, compress the file and start the inserts into a new  listener.log

lsnrctl << EOF
set current_listener LISTENER
set log_status off
exit
EOF
go to place of your listener.log and then, delete, move or compress....
gzip listener.log
lsnrctl << EOF
set current_listener LISTENER
set log_status on
exit
EOF

after this operation you can see in the directory

listener.log.gz -----> old file compressed

listener.log -----> new file generated

ORACLE UTL_SMTP: INSTALL AND EXAMPLE

---- To install UTL_SMTP ----

host@oracle]$ cd $ORACLE_HOME/rdbms/admin
[host@oracle]$sqlplus / as sysdba
SQL> @utlsmtp
SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
SQL> alter system set smtp_out_server='mailhost ip address:25' scope=both;


---- Example UTL_SMTP ----



DECLARE
v_From VARCHAR2 := 'aaaaa@gmail.com';
v_Recipient VARCHAR2 := 'bbbb@gmail.com';
v_Recipient2 VARCHAR2 := 'ccccc@gmail.com';
v_Subject VARCHAR2 := 'this is a test of utl_smtp';
v_Mail_Host VARCHAR2 := 'mailhost name or mailhost IP address';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2 := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient2);
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || ';' || v_Recipient2 || crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/

ORACLE UTL_MAIL: INSTALL AND EXAMPLE

----- To install utl_mail -----

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
grant execute on UTL_MAIL to public;


----- Example of utl_mail -----

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => 'xxxxx@gmail.com',
            recipients => 'bbbbbbb@gmail.com,ccccccc@gmail.com',
               subject => 'This is the subject of message',
               message => 'this is the message',
             mime_type => 'text; charset=us-ascii');
END;
/

LINUX HOW TO CONFIGURE SENDMAIL. EXAMPLE OF SEND MESSAGE USING MAILX

This summary is not available. Please click here to view the post.

HOW TO SOLVE COMPATIBILITY MISTAKES DURING INSTALLATION ORACLE CLIENT 10G IN WINDOWS 7 MACHINE

To solve the compatibility mistake during installation of oracle client 10g in windows 7 machines

1.- you must add the following lines to all ...\stage\...\refhost.xml


  <CERTIFIED_SYSTEMS>
    <OPERATING_SYSTEM>
    <!--Microsoft Windows 2000-->
      <VERSION VALUE="5.0"/>
      <SERVICE_PACK VALUE="1"/>
    </OPERATING_SYSTEM>
    <OPERATING_SYSTEM>
    <!--Microsoft Windows XP-->
      <VERSION VALUE="5.1"/>
      <SERVICE_PACK VALUE="1"/>
    </OPERATING_SYSTEM>
    <OPERATING_SYSTEM>
    <!--Microsoft Windows 2003-->
      <VERSION VALUE="5.2"/>
    </OPERATING_SYSTEM>
    <!--Microsoft Windows Vista-->
    <OPERATING_SYSTEM>
      <VERSION VALUE="6.0"/>
    </OPERATING_SYSTEM>
    <!--Microsoft Windows 7-->
    <OPERATING_SYSTEM>
      <VERSION VALUE="6.1"/>
    </OPERATING_SYSTEM>
  </CERTIFIED_SYSTEMS>

2.- but we must add 6.1 also in the file install/oraparam.ini

[Certified Versions]
#You can customise error message shown for failure, provide value for CERTIFIED_VERSION_FAILURE_MESSAGE
Windows=5.0,5.1,5.2,6.0,6.1

Then you can install oracle client now without compatibility mistakes

ORACLE 11G R2: RESTORE A OLD DATABASE SPFILE FROM RMAN (USING BACKUP PIECE)

Database: ORCL
ORCL DBID: 3343811387
RMAN Database: RMANDB

If you need restore a old spfile from rman backup

1.- First startup ORCL database with pfile, if any, and enter in RMAN to see that backup piece can be used


Rman target / catalog=rman/temporal@rmandb

RMAN> Set dbid 3343811387;

RMAN> list backup of spfile;

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7730769 Full 17.75M SBT_TAPE 00:00:31 09-AUG-11
BP Key: 7730771 Status: AVAILABLE Compressed: NO Tag: TAG20110809T122115
Handle: 03mjicct_1_1 Media: 0186L4
SPFILE Included: Modification time: 09-AUG-11
SPFILE db_unique_name: ORCL

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7730787 Full 256.00K SBT_TAPE 00:00:30 09-AUG-11
BP Key: 7730790 Status: AVAILABLE Compressed: NO Tag: TAG20110809T122330
Handle: 04mjice4_1_1 Media: 0186L4
SPFILE Included: Modification time: 09-AUG-11
SPFILE db_unique_name: ORCL

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7741855 Full 17.75M SBT_TAPE 00:01:11 10-AUG-11
BP Key: 7741858 Status: AVAILABLE Compressed: NO Tag: TAG20110810T101732
Handle: 06mjkpgu_1_1 Media: 0186L4
SPFILE Included: Modification time: 10-AUG-11
SPFILE db_unique_name: ORCL

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7741878 Full 256.00K SBT_TAPE 00:00:36 11-AUG-11
BP Key: 7741881 Status: AVAILABLE Compressed: NO Tag: TAG20110810T102027
Handle: 07mjkpjd_1_1 Media: 0186L4
SPFILE Included: Modification time: 11-AUG-11
SPFILE db_unique_name: ORCL
RMAN> exit

2.- Then shutdown ORCL database and realize the restore from specific bakup piece.

Rman target / catalog=rman/temporal@rmandb
RMAN> Set dbid 3343811387;
RMAN> Startup force nomount;
RMAN> run {
2> allocate channel c1 device type 'SBT_TAPE';
3> RESTORE SPFILE to '+DATA' FROM '04mjice4_1_1';
}
RMAN> shutdown immediate;
RMAN> exit

Then you can startup the ORCL database normally with the restored spfile.

ORACLE 11G R2: HOW DO YOU CREATE CONNECTIVITY WITHIN THE CLUSTER (KEYGEN DSA,RSA)

CREATE CONNECTIVITY WITHIN THE CLUSTER

We have a cluster with two members (Host1, Host2).
The same steps for DSA and RSA.
In ORACLE_HOME using oracle user.

1.- Generate the keys.

First we generate in Host1

[oracle@Host1]$ ssh-keygen -t dsa
(all by default)

      Generating public/private dsa key pair.
      Enter file in which to save the key (/home/oracle/.ssh/id_dsa):
      Created directory '/home/oracle/.ssh'.
      Enter passphrase (empty for no passphrase):
     Enter same passphrase again:
     Your identification has been saved in /home/oracle/.ssh/id_dsa.
     Your public key has been saved in /home/oracle/.ssh/id_dsa.pub.
     The key fingerprint is:
     5d:8c:42:97:eb:42:ae:52:52:e9:59:20:2a:d3:6f:59 oracle@Host1.dbsconsult.com

2.- Transfer the keys


The public key on each node is copied to both nodes. Execute the following on each node.

In Host2

[oracle@Host2]$ ssh Host1 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys


       The authenticity of host 'Host1 (192.168.0.184)' can't be established.

       RSA key fingerprint is 00:d9:70:08:bc:fd:b5:e4:e3:df:a3:c7:d8:46:1e:a5.

      Are you sure you want to continue connecting (yes/no)? yes

      Warning: Permanently added 'Host1,192.168.0.184' (RSA) to the list of known hosts.

      oracle@Host1's password:


In Host1

[oracle@Host1]$ ssh Host2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

     The authenticity of host 'Host2 (192.168.0.185)' can't be established.

     RSA key fingerprint is 00:d9:70:08:bc:fd:b5:e4:e3:df:a3:c7:d8:46:1e:a5.

    Are you sure you want to continue connecting (yes/no)? yes

    Warning: Permanently added 'Host2,192.168.0.185' (RSA) to the list of known hosts.

    oracle@Host2's password:

TESTING THE CONECTIVITY

Execute the command in both nodes:

./cluvfy comp nodecon -n Host1,Host2

Verifying node connectivity

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Node connectivity passed for subnet "172.21.54.0" with node(s) Host2,Host1

TCP connectivity check passed for subnet "172.21.54.0"

Node connectivity passed for subnet "10.10.10.0" with node(s) Host2,Host1

TCP connectivity check passed for subnet "10.10.10.0"

Interfaces found on subnet "172.21.54.0" that are likely candidates for VIP are:

Host2 eth0:172.21.54.31

Host1 eth0:172.21.54.30

Interfaces found on subnet "10.10.10.0" that are likely candidates for a private interconnect are:

Host2 eth1:10.10.10.31

Host1 eth1:10.10.10.30

Node connectivity check passed

Verification of node connectivity was successful.

ORA-01578: HOW TO KNOW WHICH OBJECT (TABLE, INDEX....) IS CORRUPTED

        Sometimes, in alert log file it's appears this message (ORA-01578: ORACLE data block corrupted). You can know which object is corrputed whit these steps and to decide if fix it deleting/recreating object (index, table.. ) or restore object using RMAN BLOCKRECOVER.

ORA-01578: ORACLE data block corrupted (file # 15, block # 608562)
ORA-01110: data file 15: '/software/oracle/wind11/oradata/ORCL/dbfiles/ORCLusers03.dbf'

1.- DBVERIFY

      Using oracle dbverify tool in system prompt to confirm corruption

dbv file=/software/oracle/wind11/oradata/ORCL/dbfiles/ORCLusers03.dbf'


DBVERIFY: Release 11.2.0.2.0 - Production on Tue Oct 16 09:45:07 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /software/oracle/wind11/oradata/ORCL/dbfiles/ORCLusers03.dbf'

DBV-00200: Block, DBA 63523122, already marked corrupt
csc(0x0000.3de058f1) higher than block scn(0x0000.00000000)
Page 608562 failed with check code 6054


DBVERIFY - Verification complete

Total Pages Examined         : 2621440
Total Pages Processed (Data) : 2268458
Total Pages Failing   (Data) : 1
Total Pages Processed (Index): 45544
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 8958
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 298480
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1109032205 (0.1109032205)

2.-  Script SQLPLus to confirm the block marked as corrupted with dbverify is the same it's appears in alert log file

select dbms_utility.data_block_address_file(&&rdba) RFN,
  2  dbms_utility.data_block_address_block(&&rdba) BL
   3  from dual;
 Enter value for rdba: 63523122
old   1: select dbms_utility.data_block_address_file(&&rdba) RFN,
new   1: select dbms_utility.data_block_address_file( 63523122) RFN,
old   2: dbms_utility.data_block_address_block(&&rdba) BL
new   2: dbms_utility.data_block_address_block( 63523122) BL

       RFN         BL
---------- ----------
        15     608562

3.- Script SQLPlus to confirm the tablespace

select file_id AFN, relative_fno, tablespace_name
 from dba_data_files
 where relative_fno=&RFN;
Enter value for rfn: 15
old   3:  where relative_fno=&RFN
new   3:  where relative_fno=15

       AFN RELATIVE_FNO TABLESPACE_NAME
---------- ------------ ------------------------------
        15           15 USERS

4.- Script SQLPlus to know the object corrupted

select * from dba_extents where file_id = &AFN and &BL between block_id AND block_id + blocks - 1;
Enter value for afn: 15
Enter value for bl: 608562
old   1: select * from dba_extents where file_id = &AFN and &BL between block_id AND block_id + blocks - 1
new   1: select * from dba_extents where file_id = 15 and 608562 between block_id AND block_id + blocks - 1

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
ORCLUSER
PAGERESULTS
                               TABLE              USERS
         0         15     607872    8388608       1024           15

 In this case it's a table, now you must to decide if restore the block using RMAN, or if the table is used as repository temporary table in operations and if it can be deleted and recreated or if the data contained can be restore with oracle utility data pump.

ORACLE 11G R2: COMO HACER RESTORE RMAN DE BBDD RAC

Para restaurar la BBDD almacenada dentro en ASM, recuperamos tanto los controlfiles como los datafiles de la BBDD. En su momento, cuando creamos la BBDD RAC, tanto los datafiles como los controlfiles los creamos en el diskgroup de asm +DATA

1.- Paramos la BBDD
    srvctl stop database -d ORCL
    Arrancamos una instancia en modo nomount
    sql> startup nomount;

2.- Restaurar desde rman un controlfile (nos restaura solo un controlfile)

rman target=/ catalog=rman/temporal@rmandb
rman >run {
           2> allocate channel c1 device type 'SBT_TAPE';
           3> send 'NB_ORA_CLIENT=Host1, NB_ORA_SCHED=Backup.ORCL.BBDD.Int.offline.RMAN';
           4> restore controlfile to '+DATA';
           5> release channel c1;
           6> }
           7> exit;

3.- Multiplexar los controlfiles (para que coincida con el numero de controlfiles especificado en el fichero spfile).
Miramos desde asmcmd el nombre del fichero controlfile restaurado en +DATA
ASMCMD> cd +data/orcl/controlfile
ASMCMD> ls
current.259.729258021

rman nocatalog
rman > connect target

connected to target database: ORCL (DBID=3671654340)
using target database control file instead of recovery catalog

rman > restore controlfile from ‘+data/orcl/controlfile/current.259.729258021’;
rman > exit;

si volvemos a asmcmd veremos que nos ha generado los dos controlfile que nos faltan para asi tener un total de 3 que son los que teniamos especificados en el fichero spfile
ASMCMD> cd +data/orcl/controlfile
ASMCMD> ls
current.259.729258021
        current.273.729258023
        current.274.729258021

4.- Restaurar la BBDD.
rman target=/ catalog=rman/temporal@rmandb
rman >run {
                   2> allocate channel c1 device type 'SBT_TAPE';
                   3> send 'NB_ORA_CLIENT=Host1, NB_ORA_SCHED=Backup.ORCL.BBDD.Int.offline.RMAN';
                   4> sql ‘alter database mount’;
                   5> restore database;
                   6> recover database;
                   7> sql 'alter database open resetlogs';
                   8> release channel c1;
                   9> }
5.- Parada de la instancia arrancada y arranque de la base de datos (todas las instancias del cluster)
Sql> shutdown immediate
$ srvctl start database –d ORCL

ORACLE 11G R2: SCRIPT BACKUP EN FRIO DE BBDD RAC (Backup cold)

Este script es para realizar un backup en frio mediante RMAN de una BBDD en modo RAC. La BBDD se llama ORCL y contiene dos instancias llamadas ORCL1 y ORCL2. La base de datos de RMAN se llama RMANDB.

El script que generaremos y lo lanzaran desde Netbackup  cuando toque en la planificación lo llamaremos /home/Oracle/scripts/rman/cold_ORCL.sh. El contenido es


LOGFILE=/home/oracle/scripts/rman/logs/out_backup_cold.log
DATE=`date '+%d%m%y'`
export DATE

# DETENEMOS LA BASE DE DATOS
echo Intentando detener la consola >>$LOGFILE
emctl stop dbconsole
echo Intentando parar la base de datos completa  >>$LOGFILE
srvctl stop database -d ORCL  >>$LOGFILE
echo Se ha parado la base de datos >>$LOGFILE

# Abrimos en modo mount (*SOLO* esta instancia)
echo Intentando levantar instancia local en modo MOUNT >>$LOGFILE
srvctl start instance -i ORCL1 -d ORCL -o mount >>$LOGFILE

echo Instancia montada >>$LOGFILE

# Lanzamos el backup
echo Lanzando backup >>$LOGFILE
Las tres lineas siguientes, son solo una linea de ejecución
$ORACLE_HOME/bin/rman target / catalog rman/password@rmandb CMDFILE=/home/oracle/scripts/rman/cold_ORCL_nbu.rcv LOG=/home/oracle/scripts/rman/logs/cold_ORCL_${DATE}.log

# Derribamos la instancia *local*
echo Derribando instancia local >>$LOGFILE
srvctl stop instance -i ORCL1  -d ORCL  -o immediate >>$LOGFILE
echo Instancia ORCL1 parada >>$LOGFILE

# Volvemos a levantar todo
echo Levantando el cluster >>$LOGFILE
srvctl start database -d ORCL >>$LOGFILE
echo La bd del cluster esta levantada >>$LOGFILE
echo Levantando la consola >> $LOGFILE
emctl start dbconsole

El contenido del script rcv, lanzado en el backup es

run {

# Total de das que mantenemos las copias de seguridad en el medio fsico

configure retention policy to recovery window of 30 days;

# Establecemos el canal de copias de seguridad;

Estos datos os los tienen que suministrar los que se encargan del software del backup

allocate channel c1 device type 'SBT_TAPE';

send 'NB_ORA_CLIENT=Host1, NB_ORA_SCHED=Backup.ORCL.BBDD.Int.offline.RMAN';

# Hacemos un backup completo

backup database include current controlfile;

# Backup del spfile

backup spfile;

sql 'alter database open';

sql 'alter database backup controlfile to trace';

sql 'create pfile from spfile';

# Liberamos el canal

release channel c1;

}

ORACLE 11g R2: RMAN y NetBackup

If you realize the backups using Netbackup, then so that you could be execute from RMAN, in spite of having the client installed in the host where from you will proceed to execute RMAN, it is not sufficient. You need to link to library of the client Netbackup with this command

ln -sf /usr/openv/netbackup/bin/libobk.so64 $ORACLE_HOME/lib/libobk.so

This is valid for all the oracle versions. But also, specially for the version Oracle 11G, it is necessary to install a patch in Netbackup

NB_6.5.5_ET1940073_1_347227.zip

As soon as the client NetBackup was installed, linked the Oracle library and installed the NetBackup patch we can already proceed to realize backups of Oracle using  RMAN.

ORACLE 11G R2: SCRIPTS RESTORE ASM

         Este es un ejemplo de diferentes tipos de restores de ASM de un RAC llamado ORCL. Partiendo de la base que tenemos realizados backups mediante este comando

            date=`date '+%d%m%y'`

            export date

sid="+ASM1"
export sid

asmcmd md_backup /software/oracle/backup/ORCL/asm/Respaldo_DiskGroup_$sid.$date

Procederemos a la restauración del ASM

Caso 1: Creacion del diskgroup y restauración de los metadatos.

ASMCMD> md_restore –-full –G data –-silent /software/oracle/backup/ORCL/asm/ Respaldo_DiskGroup_+ASM1.090910

Caso 2: Restauración de los metadatos sobre un diskgroup ya existente.

ASMCMD> md_restore –-nodg –G data –-silent /software/oracle/backup/ORCL/asm/ Respaldo_DiskGroup_+ASM1.090910

Caso 3: Recreación del Diskgroup

Nos conectamos a la instancia asm como sysasm

           sqlplus '/as sysasm'  

           sqlplus> CREATE DISKGROUP data EXTERNAL REDUNDANCY DISK ‘ORCL:ASMD1’;

Esta es una tabla de opciones de md_restore

ORACLE 11G R2: SCRIPTS BACKUP DEL ASM (METADATOS, SPFILE, RECREACION DE DISKGROUPS)

Ejemplo de backup de la configuración ASM de un RAC llamado ORCL

Realizamos copia de los metadatos y del spfile de asm mediante el comando md_backup de asmcmd

Script /home/oracle/scripts/asm/backasm.sh

##################################################

# Copia de los metadatos de la instancia ASM1 #

##################################################

#!/bin/bash

date=`date '+%d%m%y'`

export date

sid="+ASM1"
export sid

asmcmd md_backup /software/oracle/backup/ORCL/asm/Respaldo_DiskGroup_$sid.$date

asmcmd spbackup +DATA/ORCL/ASMPARAMETERFILE/REGISTRY.253.725461119 /software/oracle/backup/ORCL/asm

Generamos tambien script para recrear el diskgroup

Script /home/oracle/scripts/asm/ backup_diskgroup.sh

# -Script info diskgroups -----------------------------

#!/usr/bin/ksh

# Crash scenario

# Casos :

# ASM disk(s) is not visible on the operating system.

# asm_diskstring parameter is not set correctly on ASM instance(s)

# ASM metadata in disk is overwritten or corrupted

echo "Chequeo el espacio en "$ORACLE_SID

# Para Recrear un Diskgroup....

# CREATE DISKGROUP name EXTERNAL REDUNDANCY DISK 'path1', 'path2', 'path3', ....;

sqlplus '/as sysasm' @/home/oracle/scripts/asm/ASMDiskGroups.sql

cp -p /home/oracle/scripts/asm/ASMDiskGroups.txt /software/oracle/backup/ORCL/asm

Script /home/oracle/scripts/asm/ASMDiskGroups.sql

spool /home/oracle/scripts/asm/ASMDiskGroups.txt

select instance_name from v$instance

/

set lines 130

col path for a35

col Diskgroup for a15

col DiskName for a20

col disk# for 999

col total_mb for 999,999,999

col free_mb for 999,999,999

compute sum of total_mb on DiskGroup

compute sum of free_mb on DiskGroup

set pages 255

select a.name DiskGroup, b.disk_number Disk#, b.name DiskName, b.total_mb, b.free_mb,

b.path, b.header_status

from v$asm_disk b, v$asm_diskgroup a

where a.group_number (+) =b.group_number

order by b.group_number, b.disk_number, b.name

/

exit

/

spool off

ORACLE 11G R2: BACKUP DEL OLR

El OLR file (Oracle Local Registry) es el fichero de configuración de cada nodo del cluster

Script generado en /home/oracle/scripts/ocr/backolr.sh

Se lanza con el usuario root (desde el crontab del usuario)

La ubicación de dichas copias esta en /software/oracle/product/11.2.0/crs/cdata/Host1

Para sacar un listado de los backups realizados se ejecuta el comando

ocrconfig –local –showbackup

En el script realizamos una copia manual que la deja en la ubicación antes dicha

/software/oracle/product/11.2.0/crs/cdata/Host1/backup_20100908_120001.olr

Una vez generada esa copia manual, hacemos copia de todos los olr a la ubicacion centralizada de copias de esa maquina para llevarla a cinta.

El script /home/oracle/scripts/ocr/backolr.sh

ocrconfig -local -manualbackup

cp -p /software/oracle/product/11.2.0/crs/cdata/Host1/*.* /rutadestino/olr