Modifying SCAN Configuration in Oracle 11g Release 2 RAC

• Rename SCAN

Rename SCAN

The original name was "ol6-112-scan" and I want to rename it to "ol6-112-scan2". Check the new name is configured in the DNS.

$ nslookup ol6-112-scan2
Server:  192.168.0.4
Address: 192.168.0.4#53

Name: ol6-112-scan2.localdomain
Address: 192.168.0.117
Name: ol6-112-scan2.localdomain
Address: 192.168.0.116
Name: ol6-112-scan2.localdomain
Address: 192.168.0.115

$

Display the current configuration using the following command as the oracle/grid user.

$ export GRID_HOME=/u01/app/11.2.0.3/grid
$ $GRID_HOME/bin/srvctl config scan 
SCAN name: ol6-112-scan, Network: 1/192.168.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /ol6-112-scan/192.168.0.115
SCAN VIP name: scan2, IP: /ol6-112-scan/192.168.0.116
SCAN VIP name: scan3, IP: /ol6-112-scan/192.168.0.117
$

Turn off the SCAN and SCAN listeners using the following commands as the oracle/grid user.

$ export GRID_HOME=/u01/app/11.2.0.3/grid
$ $GRID_HOME/bin/srvctl stop scan_listener 
$ $GRID_HOME/bin/srvctl stop scan

Modify the SCAN name using the following commands as the root user.

# export GRID_HOME=/u01/app/11.2.0.3/grid
# $GRID_HOME/bin/srvctl modify scan -n ol6-112-scan2   
# $GRID_HOME/bin/crsctl modify type ora.scan_vip.type -attr "ATTRIBUTE=SCAN_NAME,DEFAULT_VALUE=ol6-112-scan2"

Turn on the SCAN and SCAN listeners again using the following commands as the oracle/grid user.

$ $GRID_HOME/bin/srvctl modify scan_listener -u 
$ $GRID_HOME/bin/srvctl start scan_listener

Display the changed configuration.

$ $GRID_HOME/bin/srvctl config scan 
SCAN name: ol6-112-scan2, Network: 1/192.168.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /ol6-112-scan2/192.168.0.115
SCAN VIP name: scan2, IP: /ol6-112-scan2/192.168.0.116
SCAN VIP name: scan3, IP: /ol6-112-scan2/192.168.0.117
$

Borrado de Archive Logs aplicados en un Standby Database

 A través de la vista ( DBA_LOGMNR_PURGED_LOG ) podemos determinar los archive logs de los cuales el standby puede prescindir pero es requisito indispensable ejecutar previo el siguiente package para que la información de la misma este actualizada y disponible.

 SQL> EXECUTE DBMS_LOGSTDBY.PURGE_SESSION;
 Una vez ejecutada la unidad de programa DBMS_LOGSTDBY.PURGE_SESSION, se podrá disponer de la información y se podrá obtener una salida similar a la siguiente:

 SQL> SELECT * FROM DBA_LOGMNR_PURGED_LOG;
FILE_NAME
————————————
/Stdbybdd/arc_dest/arc_1_40_509538672.log
/Stdbybdd/arc_dest/arc_1_41_509538672.log
/Stdbybdd/arc_dest/arc_1_42_509538672.log
/Stdbybdd/arc_dest/arc_1_43_509538672.log
/Stdbybdd/arc_dest/arc_1_44_509538672.log
/Stdbybdd/arc_dest/arc_1_45_509538672.log
/Stdbybdd/arc_dest/arc_1_46_509538672.log
/Stdbybdd/arc_dest/arc_1_47_509538672.log

 Con la salida obtenida se podrán crear scripts de sistema operativo para remover los respectivos archivos. Si los archives se encuentran en filesystems se podrá llevar a cabo un simple rm ( Unix/Linux ) o del ( Windows ). Si los mismos se encuentran dentro de una capa de storage a nivel de ASM

 A través de la vista ( DBA_LOGMNR_PURGED_LOG ) podemos determinar los archive logs de los cuales el standby puede prescindir pero es requisito indispensable ejecutar previo el siguiente package para que la información de la misma este actualizada y disponible.

 SQL> EXECUTE DBMS_LOGSTDBY.PURGE_SESSION;
 Una vez ejecutada la unidad de programa DBMS_LOGSTDBY.PURGE_SESSION, se podrá disponer de la información y se podrá obtener una salida similar a la siguiente:

 SQL> SELECT * FROM DBA_LOGMNR_PURGED_LOG;
FILE_NAME
————————————
/Stdbybdd/arc_dest/arc_1_40_509538672.log
/Stdbybdd/arc_dest/arc_1_41_509538672.log
/Stdbybdd/arc_dest/arc_1_42_509538672.log
/Stdbybdd/arc_dest/arc_1_43_509538672.log
/Stdbybdd/arc_dest/arc_1_44_509538672.log
/Stdbybdd/arc_dest/arc_1_45_509538672.log
/Stdbybdd/arc_dest/arc_1_46_509538672.log
/Stdbybdd/arc_dest/arc_1_47_509538672.log

 Con la salida obtenida se podrán crear scripts de sistema operativo para remover los respectivos archivos. Si los archives se encuentran en filesystems se podrá llevar a cabo un simple rm ( Unix/Linux ) o del ( Windows ). Si los mismos se encuentran dentro de una capa de storage a nivel de ASM

AutoNumber And Identity Functionality in Oracle Databases (Pre 12c)

Oracle Database 12c has introduced two new features that make the trigger-based solution for populating numeric ID columns redundant. If you are using Oracle 12c onward, you should consider one of the following options.

• Identity Columns in Oracle Database 12c Release 1 (12.1)
• DEFAULT Values Using Sequences in Oracle Database 12c Release 1 (12.1)

If you are using a version of the database prior to Oracle 12c, this article describes how to implement AutoNumber/Identity type functionality in Oracle.
Developers who are used to AutoNumber columns in MS Access or Identity columns in SQL Server often complain when they have to manually populate primary key columns using sequences in Oracle. This type of functionality is easily implemented in Oracle using triggers.
Create a table with a suitable primary key column and a sequence to support it.

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;

Create a trigger to populate the ID column if it's not specified in the insert.

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

Test it using the automatic and manual population methods.

SQL> INSERT INTO departments (description)
  2  VALUES ('Development');

1 row created.

SQL> SELECT * FROM departments;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Development

1 row selected.

SQL> INSERT INTO departments (id, description)
  2  VALUES (dept_seq.NEXTVAL, 'Accounting');

1 row created.

SQL> SELECT * FROM departments;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Development
         2 Accounting

2 rows selected.

SQL>

The trigger can be modified to give slightly different results. If the insert trigger needs to perform more functionality than this one task you may wish to do something like the following.

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
BEGIN
  SELECT NVL(:new.id, dept_seq.NEXTVAL)
  INTO   :new.id
  FROM   dual;
  
  -- Do more processing here.
END;
/

To overwrite any values passed in you should do the following.

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

To error if a value is passed in you should do the following.

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
BEGIN
  IF :new.id IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'ID cannot be specified');
  ELSE
    SELECT dept_seq.NEXTVAL
    INTO   :new.id
    FROM   dual;
  END IF;
END;
/

Cargar Images, documents, etc

rem -----------------------------------------------------------------------
rem Filename:   loadlob.sql
rem Purpose:    Load a binary file (images, documents, etc) into a
rem             database table.
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on
DROP TABLE lob_table;
DROP SEQUENCE lob_seq;

CREATE OR REPLACE DIRECTORY my_dir AS '/app/oracle/';

CREATE TABLE lob_table (id NUMBER, fil BLOB);
CREATE SEQUENCE lob_seq;

CREATE OR REPLACE PROCEDURE load_file(p_file VARCHAR2)
IS
    src_lob  BFILE := BFILENAME('MY_DIR', p_file);
    dest_lob BLOB;
BEGIN
   INSERT INTO lob_table VALUES(lob_seq.nextval, EMPTY_BLOB())
      RETURNING fil INTO dest_lob;

   DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
                          SRC_LOB  => src_lob,
                          AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );
   DBMS_LOB.CLOSE(src_lob);

   COMMIT;
END;
/
show errors

-- Let's test it
exec load_file('pic1.gif');
SELECT id, DBMS_LOB.GETLENGTH(fil) AS bytes_loaded
  FROM lob_table;


rem -----------------------------------------------------------------------
rem Filename:   savelob.sql
rem Purpose:    Save a binary file (images, documents, etc) from database
rem             to a flat file.
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE save_file(p_id NUMBER, p_file VARCHAR2)
IS
   v_lob_loc      BLOB;
   v_lob_len      NUMBER;
   v_buffer       RAW(32767);
   v_buffer_size  BINARY_INTEGER := 32767;
   v_offset       NUMBER         := 1;
   v_out_file     UTL_FILE.FILE_TYPE;
BEGIN
    SELECT fil INTO v_lob_loc FROM lob_table WHERE id = p_id;
    v_lob_len := DBMS_LOB.GETLENGTH(v_lob_loc);

    DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);
    v_out_file := UTL_FILE.FOPEN(location      => 'MY_DIR', 
                                 filename      => p_file, 
                                 open_mode     => 'w',
                                 max_linesize  => 32767);

    WHILE (v_offset <= v_lob_len) LOOP
      dbms_output.put_line('v_start : ' || to_char(v_offset));
      DBMS_LOB.READ(lob_loc => v_lob_loc,
                    amount  => v_buffer_size,
                    offset  => v_offset,
                    buffer  => v_buffer);
      v_offset := v_offset + v_buffer_size;
      UTL_FILE.PUT_RAW(file   => v_out_file,
                       buffer => v_buffer);
    END LOOP;

    UTL_FILE.FCLOSE(v_out_file);
    DBMS_LOB.CLOSE(v_lob_loc);
END;
/
show errors

-- Let's test it
exec save_file(1, 'pic2.gif');
! ls -l /app/oracle/pic*.gif

Creating a Standby Database using RMAN and Tivoli Storage Manager

Creating a Standby Database using RMAN and Tivoli Storage Manager

 

Confirm that TSM is installed on the host/hosts and tdpo.opt file has been configured.

 

Contact the TSM administrator and confirm that all pre-requisite tasks related to the TSM set-up have been completed on BOTH primary as well as standby machines.

 

For example on Linux machines the tdpo.opt file should exist in the following location :

 

/opt/tivoli/tsm/client/oracle/bin/

 

or

 

/opt/tivoli/tsm/client/oracle/bin64/

 

If the ORACLE_SID is dgtest9i, then the tdpo.opt file is set up with the name tdpo.dgtest9id.opt

 

We can test the TDP set up using the sbttest command

 

export TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt

 

[oracle@itlinuxdevblade08 bin64]$ sbttest test

 

The sbt function pointers are loaded from libobk.so library.

-- sbtinit succeeded

Return code -1 from sbtinit, bsercoer = 0, bsercerrno = 0

Message 0 not found;  product=RDBMS; facility=SBT

 

 

Pre-requisite tasks to be completed on the Primary Database

 

Ensure that the database has been registered with the RMAN catalog and that the database is also running in ARCHIVELOG mode.

 

If already not created, create a password file. Please make a note of the password used as it should be the same one used when creating the password file on the standby site.

 

Also ensure that the parameter remote_login_password_file is set to EXCLUSIVE

 

Ensure that the Primary database is functioning in force logging mode.

 

It is a best practice to place the primary database in force logging mode so that all operation are captured in the redo stream. To place the primary database in force logging mode issue the following SQL

SQL > alter database force logging;

 

 

Take a backup of the Primary Database

 

Once we have tested the TSM setup on the machine, we can take an online backup of the Primary database using RMAN which will be restored on the standby site to create the standby database.

 

Ensure that the database has been registered with the RMAN catalog and that the database is also running in ARCHIVELOG mode.

 

Make a note of the current log sequence number. This is will be used as a starting point when we specify the range of archive log files that need to be backed up to tape.

 

$ rman target / catalog rman/rman@rmanp cmdfile=backup_primarydb.rcv

 

backup_primarydb.rcv

 

run {

allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.mrhbld.opt)';

backup database  ;

release channel ch1 ;

}

 

Create the Standby Control File

 

Using RMAN we can take a backup of the current control file which will be restored on the standby machine as a standby control file.

 

$ rman target / catalog rman/rman@rmanp cmdfile=backup_primarycf.rcv

 

backup_primarycf.rcv

 

run {

allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.mrhbld.opt)';

set limit channel ch1 kbytes=3145728 readrate 200;

backup current controlfile for standby;

release channel ch1 ;

}

 

 

READ RATE specifies the number of buffers to be read per second by RMAN backup or copy operations

 

 

 

 

Backup the Archive Log Files

 

While performing the recovery on the standby database after restoring the primary database backup, we need to go to a point in time after which we created the standby control file. We then need to record the last sequence number from V$ARCHIVED_LOG and ensure that the archivelogs until that particular sequence are backed up via RMAN.

 

SQL> alter system switch logfile;

 

SQL > alter system switch logfile;

 

SQL > select max(sequence#) from v$archived_log;

 

Make a note of this log sequence number. This will be used as the end point when we specify the range of archive log files that need to be backed up via RMAN to tape.

 

This can be verified by issuing the ‘archive log list’ command as well as physically checking the log_archive_dest location to crosscheck the last online redo log that has been archived.

 

We will then backup the range of archive log files that have been generated since the time the online backup of database was performed. These will be applied to the standby database as part of the recovery process.

 

$ rman target / catalog rman/rman@rmanp cmdfile=backup_primarydb_arch.rcv

 

backup_primarydb_arch.rcv

 

run {

allocate channel ch1 type 'SBT_TAPE'  parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.mrhbld.opt)';

backup   archivelog from logseq 16 until logseq  35 thread 1 ;

release channel ch1;

}

 

 

Preparing the Standby site for Data Guard

 

Create the directory structure on the standby site keeping in mind the mount points and directory structure on the primary node.

 

Remember while creating the $BDUMP, $UDUMP, $CDUMP and $PFILE directories that the ORACLE_SID will be the same as the primary database.

 

Also try and ensure that the size of the mount points exactly mirror the primary node otherwise we will face issues both while creating the standby database as well as when we perform housekeeping tasks on the primary like adding or resizing a datafile.

 

We then need to create the password file using the same password for the user SYS as what we have used while creating the password file on the primary node.

 

Copy the init.ora file from the primary node to $ORACLE_HOME/dbs on the standby node.

 

Create a pfile from this spfile

 

$ export ORACLE_SID=

 

SQL> create pfile from spfile;

 

 

In this example, we are making the following assumptions:

 

• ORACLE_SID=dba01

 

• Primary Node = itlinux01

 

• Standby Node = itlinux02

 

• Mount Points on the primary node are /itlinux01db01 and /itlinux01db02

 

• Mount points on the standby node are /itlinux02db01 and /itlinux02db02

 

Important:

 

Please ensure that the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT  parameters are carefully entered and every mount point which has a datafile or logfile of the primary database is mentioned in the string list.

 

We will now edit the pfile to include parameters specific to the standby site ….

 

FAL_SERVER=dba01_itlinux01

FAL_CLIENT=dba01_itlinux02

DB_FILE_NAME_CONVERT=(‘/itlinux01db01’,’/itlinux02db01’,’/itlinux01db02’,’/itlinux02db02’) 

LOG_FILE_NAME_CONVERT=(‘/itlinux01db01’,’/itlinux02db01’,’/itlinux01db02’,’/itlinux02db02’) 

STANDBY_ARCHIVE_DEST=/itlinux02db01/ORACLE/dba01/arch

STANDBY_FILE_MANAGEMENT=AUTO

 

The following parameters will only apply when the standby database assumes the role of a primary database after a switchover is performed. They can be excluded at this point from the init.ora file, but a best practice is to include it now itself so that when a switchover is made in the future, no further changes to the init.ora need to be made.

 

LOG_ARCHIVE_DEST_2='SERVICE=dba01_itlinux01 ARCH’

LOG_ARCHIVE_DEST_STATE_2=DEFER

LOG_ARCHIVE_DEST_1='LOCATION=/itlinux02db01/ORACLE/dba01/arch’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_FORMAT=%d_%t_%s.arc

 

 

Note: if we are using Oracle 10g then we can use the following parameters:

 

*.log_archive_dest_1='location=/itlinux02db01/ORACLE/dba01/arch','valid_for=(ALL_LOGFILES,ALL_ROLES)'

 

*.log_archive_dest_2='SERVICE=dba01_itlinux01 VALID_for=(ONLINE_LOGFILES, PRIMARY_ROLE) ARCH’

 

 

After making  the required changes to the init.ora create a spfile from this.

 

SQL > create spfile from pfile;

 

 

Network Configuration Files On Standby Machine

 

We will be configuring TNS aliases dba01_itlinux01 and dba01_itlinux02 in the tnsnames.ora file – these are nothing but the FAL_SERVER and FAL_CLIENT parameters that we are defining in the init.ora file.

j

The tnsnames.ora and listener.ora are defined keeping the following in mind ….

 

• Client connections use the alias ‘dba01’ accessing via port 1526

 

• Log shipping is using port 1522

 

• On the primary machine there are two listeners running – one for accepting client connections to the database and the other for the log shipping. ( ports 1526 and 1522 respectively)

 

• On the standby machine we have only one listener running which is being used for the log shipping between the two sites ( port 1522). The listener listening on port 1526 will ONLY be started when the standby site assumes the role of a primary site after a failover or switchover is performed.

 

This is a sample copy of the tnsnames.ora and listener.ora files – we need to ensure that tnsnames.ora file on BOTH sites have the same entries for FAL_SERVER and FAL_CLIENT as well.

 

listener.ora

 

# LISTENER.ORA Network Configuration File: /opt/oracle/product9206/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

ITLINUX02 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = itlinux02.hq.emirates.com)(PORT = 1522))

      )

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = extproc))

      )

    )

  )

 

#LISTENER.ORA Network Configuration File

#Created by Oracle Enterprise Manager Clone Database tool

 

SID_LIST_ITLINUX02 =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /opt/oracle/product9206)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ANY")

    )

  

    (SID_DESC =

      (GLOBAL_DBNAME = dba01)

      (ORACLE_HOME = /opt/oracle/product9206)

      (SID_NAME = dba01)

    )

  )

 

TRACE_LEVEL_ITLINUX02 = OFF

LOGGING_ITLINUX02 = OFF

TRACE_LEVEL_PRIMARY = OFF

LOGGING_PRIMARY = OFF

 

PRIMARY =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = itlinux02.hq.emirates.com)(PORT = 1526))

  )

 

SID_LIST_PRIMARY =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME=dba01)

      (ORACLE_HOME = /opt/oracle/product9206)

      (SID_NAME = dba01)

    )

  )

 

 

tnsnames.ora

 

 

dba01_itlinux01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

        (ADDRESS =

          (COMMUNITY = TCP)

          (PROTOCOL = TCP)

          (HOST = itlinux01.hq.emirates.com)

          (PORT = 1522)

        )

    )

    (CONNECT_DATA =

      (SID = dba01)

     )

  )

 

dba01_itlinux02 =

  (DESCRIPTION =

    (ADDRESS_LIST =

        (ADDRESS =

          (COMMUNITY = TCP)

          (PROTOCOL = TCP)

          (HOST = itlinux02.hq.emirates.com)

          (PORT = 1522)

        )

    )

    (CONNECT_DATA =

      (SID = dba01)

     )

  )

 

 

dba01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

        (ADDRESS =

          (COMMUNITY = TCP)

          (PROTOCOL = TCP)

          (HOST = itlinux01.hq.emirates.com)

          (PORT = 1526)

        )

    )

    (CONNECT_DATA =

      (SID = dba01)

     )

  )

 

 

 

Start the Standby Database in NOMOUNT mode

 

RMAN will restore the controlfile backup that we had taken on the primary site to the locations specified by the CONTROL_FILES parameter in the init.ora file.

 

We need to start the standby database in NOMOUNT mode.

 

SQL> startup nomount;

 

 

Restore and recover the Standby Database

 

 

On the PRIMARY Site we will start the standby database restore and recovery via RMAN using the TSM tape backup that we had earlier taken.

 

Any RMAN ‘duplicate’ command requires us to initiate an auxiliary channel as well.

 

The auxiliary channel will establish a connection to the remote standby database using the TNS alias that we earlier created i.e dba01_itlinux02

 

The ‘set until logseq’ should include the log sequence number which is one higher than the last log sequence pertaining to the archive log files that we had backed up earlier. So if we have backed up archive log files until sequence 1215 then we should include the command ‘set until logseq=1216’.

 

 

$ rman target / catalog rman/rman@rmanp \

auxiliary sys/syspassword @dba01_itlinux02 cmdfile=create_standby.rcv

 

create_standby.rcv

 

run {

allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.mrhbld.opt)';

allocate  auxiliary channel aux1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.mrhbld.opt)';

set until logseq=35 thread 1; 

duplicate  target database for standby

dorecover;

release channel ch1 ;

release channel aux1 ;

}

 

 

 

These are the last few lines from the output of the above command. Note the last archive log sequence number applied was 1215 and NOT log sequence number 1216.

 

 

……………………..

………………………………………..

 

channel clone_default: deleting archive log(s)

archive log filename=/itlinux21ar01/ORACLE/cisl/arch/arch1213.log recid=3 stamp=583459445

archive log filename=/itlinux21ar01/ORACLE/cisl/arch/arch1214.log thread=1 sequence=1214

channel clone_default: deleting archive log(s)

archive log filename=/itlinux21ar01/ORACLE/cisl/arch/arch1214.log recid=2 stamp=583459433

archive log filename=/itlinux21ar01/ORACLE/cisl/arch/arch1215.log thread=1 sequence=1215

channel clone_default: deleting archive log(s)

archive log filename=/itlinux21ar01/ORACLE/cisl/arch/arch1215.log recid=1 stamp=583459425

media recovery complete

Finished recover at 27-FEB-06

Finished Duplicate Db at 27-FEB-06

 

released channel: ch1

 

released channel: aux1

 

Recovery Manager complete.

 

 

Start Managed Recovery on the Standby Database

 

We will then put the standby database in managed recovery mode by issuing the following command :

 

SQL> recover managed standby database disconnect;

 

Confirm that managed recovery is running by checking if the MRP process is running in the background.

 

SQL> !ps -ef |grep mrp

oracle   17740     1  0 07:26 ?        00:00:00 ora_mrp0_dba01

 

 

If for any reason some further recovery is required, we can manually copy all the archive log files from the production machine to the standby machine standby_archive_dest and do the recovery MANUALLY via the following command:

 

SQL> RECOVER STANDBY DATABASE  ( apply the required archive log file when prompted)

 

This example will put the standby database in MAXIMUM PERFORMANCE mode. In case we wish to configure the standby database for MAXIMUM AVAILABILITY or MAXIMUM PROTECTION we need to create additional standby redo log files ( at least one more than the number of the current online redo log files and make sure they are the same size as the online redo log files).

 

We also will need to use the LGWR keyword in the parameter log_archive_dest_2 and issue the command on the primary database while it is mount state.

DBA Priorizando actividades—diarias, semanales, mensuales, trimestrales o anuales

Priorizando actividades—diarias, semanales, mensuales, trimestrales o anuales

Veamos cuales son las actividades prioritarias que se necesitan cubrir. La calendarización dependerá del lugar de trabajo, necesidades de la aplicación y el peso que tengan las actividades en general.

Diarias

• Respaldos — usualmente son incrementales o acumulativas, uno completo por semana y los logs son almacenados y enviados por correo al DBA en caso de fallas.
• Alert Log de la base de datos — tales como errores ORA, notificaciones automáticas por correo, mensajes a localizadores.
• ADRCI — Automatic Repository Utility and Log Rotation.
• Espacio en el filesystem, CPU y estadísticas de I/O — requiere de apoyo del administrador del SO.
• Instrucciones SQL — Sentencias que están entre el top 5 y 10.
• Corrupción — logs de RMAN, logs de exports y/o datapump, dbverify, v$database_block_corruption.
• Crecimiento de tablespaces — crecerlos, administración de particiones, tablespaces temporales, undo.
• Data Guard — revisar en los logs que la aplicación/transporte esté en sincronía.
• Logs del listener SQL*NET — detección de intrusos.
• Auditoría de logs y evidencias — detección de intrusos, quitar cuentas sin uso.
• Dumps del core y usuario — espacio que ocupan, bugs de Oracle.
• Creación de nuevas cuentas — debería ser al menos automatizado parcialmente.
• Actualizar al usuario sobre la seguridad — al menos con 24 horas de anticipación.
• Migrar esquemas y cambios al código o actualizaciones específicas de SQL.
• Crecimiento de las tablas grandes, crecimiento uniforme del tablespace.
• Llevar un registro de los cambios diarios a la base de datos — publicarlos para ciertas personas del staff de IT.

Semanales

• Respaldos — usualmente de toda la base de datos.
• Clonaciones para bases de datos no productivas — automatizado o con scripts.
• Crecimiento de tablespaces — el diario acumulado en uno semanal.
• Mejora de versión de Oracle o proyectos de migración para aplicar parches — Actualizaciones significativas.
• Pruebas en sitio de Data Guard.
• Revisar actualizaciones de My Oracle Support(MOS) — nuevos parches, actualizaciones o nuevas versiones.
• Actualizaciones en la intranet local sobre procedimientos operacionales.

Mensuales

• Clonaciones para bases de datos no productivas — automatizado o con scripts.
• Monitoreo del crecimiento de tablespaces — el semanal acumulado en uno mensual.
• Tendencias y previsiones — consumo de CPU, estadísticas de I/O, accesos
• Cambio de passwords en producción — sys, system, wallet, schema, grid control, OAS.
• Licensiamiento de uso de Oracle y cuanto abarca.
• Poner en práctica escenarios de recuperación.

Trimestrales

• Aplicación de CPUs(Critical Patch Upate) y PSUs(Patch Set Updates) en producción con planeación de la suspensión del servicio. Aplicación de CPUs, PSUs una sola vez en instancias no productivas.
• Monitoreo del crecimiento de tablespaces — el mensual acumulado en un anual
• Cursos de actualización de Oracle — de Oracle University(en línea o presenciales), libros, encuentros informales.
• Acumulación de tendencias y previsiones.

Anuales

• Crecimiento de tablespaces — reporte anual.
• Suma de tendencias y previsiones.
• Ir a conferencias sobre Oracle — grupos de usuarios locales o nacionales.
• Actualizaciones de Oracle con suspensión de servicio planeado — versión + parches + PSUs + aplicación una sola vez.
• Licenciamiento de software y renovación de servicios.
• Evaluación y actualización de hardware.
• Renovación de certificados SSL, Oracle Wallets.

Crecimiento x Tablespace

Rem
Rem  Crecimiento_x_Tablespace
Rem
Rem  NOMBRE
Rem  Crecimiento_x_Tablespace.sql
Rem
Rem  DESCRIPCION
Rem       Reporte para mostrar como fue creciendo un tablespace por hora, dia,
Rem       semana y mes. Tambien realiza proyecciones de crecimiento por semana -
Rem       mes y muestra STATUS (Aplica para 10+)   
Rem
Rem
Rem
set line 150
col "%Used" format a10
col "%Proy_1s" format a10
col "%Proy_1m" format a10
col tsname format a20
select tsname,
round(tablespace_size*t2.block_size/
1024/1024,2) TSize,
round(tablespace_usedsize*t2.block_size/1024/1024,2) TUsed,
round((tablespace_size-tablespace_usedsize)*t2.block_size/1024/1024,2) TFree,
round(val1*t2.block_size/1024/1024,2) "Dif_1h",
round(val2*t2.block_size/1024/1024,2) "Dif_1d",
round(val3*t2.block_size/1024/1024,2) "Dif_1s",
round(val4*t2.block_size/1024/1024,2) "Dif_1m",
round((tablespace_usedsize/tablespace_size)*100)||'%' "%Used",
round(((tablespace_usedsize+val3)/tablespace_size)*100)||'%' "%Proy_1s",
round(((tablespace_usedsize+val4)/tablespace_size)*100)||'%' "%Proy_1m",
case when ((((tablespace_usedsize+val3)/tablespace_size)*100 < 80) and
          (((tablespace_usedsize+val4)/tablespace_size)*100 < 80)) then 'NORMAL'
     when ((((tablespace_usedsize+val3)/tablespace_size)*100 between 80 and 90)
             or
          (((tablespace_usedsize+val4)/tablespace_size)*100 between 80 and 90))
    then 'WARNING'
else 'CRITICAL' end STATUS
from
(select distinct tsname,
rtime,
tablespace_size,
tablespace_usedsize,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime rows 1 preceding) val1,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime rows 24 preceding) val2,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime rows 168 preceding) val3,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime rows 720 preceding) val4
from (select t1.tablespace_size, t1.snap_id, t1.rtime,t1.tablespace_id,
             t1.tablespace_usedsize-nvl(t3.space,0) tablespace_usedsize
     from dba_hist_tbspc_space_usage t1,
          dba_hist_tablespace_stat t2,
          (select ts_name,sum(space) space
           from recyclebin group by ts_name) t3
     where t1.tablespace_id = t2.ts#
      and  t1.snap_id = t2.snap_id
      and  t2.tsname = t3.ts_name (+)) t1,
dba_hist_tablespace_stat t2
where t1.tablespace_id = t2.ts#
and t1.snap_id = t2.snap_id) t1,
dba_tablespaces t2
where t1.tsname = t2.tablespace_name
and rtime = (select max(rtime) from dba_hist_tbspc_space_usage)
and t2.contents = 'PERMANENT'
order by "Dif_1h" desc,"Dif_1d" desc,"Dif_1s" desc, "Dif_1m" desc

crontab para ejecucion ultimo sabado del mes

#!/bin/bash
LAST_SAT=`(cal | awk '$7!=""{t=$7} END {print t}')`
TODAY=`(date +%d)`

echo $LAST_SAT
echo $TODAY
if [[ "$LAST_SAT" = "$TODAY" ]];then
# Reemplazar la linea echo por la llamada al programa
 echo "si es"
fi
 

Como Renombrar un Tablespace

Como Renombrar un Tablespace

Introducción
A partir de Oracle 10g podemos renombrar un Tablespace con una sola sentencia:
ALTER TABLESPACE tablespace_name RENAME TO tablespace_new_name;
Se pueden renombrar todos los tablespaces permanentes o temporales excepto SYSTEM y SYSAUX.
Requisitos :

• El parámetro COMPATIBLE debe estar seteado en 10.0.0 o superior.
• El tablespace a renombrar y todos sus datafiles deben estar online. 

Casos de estudio

1. Renombramos el Tablespace Users como Users_Data
   • Verificamos que existe el tablespace USERS

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TBSJMW
INVENTORY

8 rows selected.



• Vemos que varios usuarios tienen el tablespace USERS como Default Tablespace

SQL> select username, default_tablespace from  dba_users where default_tablespace='USERS';

USERNAME                        DEFAULT_TABLESPACE
------------------------------  ------------------------------
PM                              USERS
BI                              USERS
.............................................................

16 rows selected.


-> Renombramos Users como Users_Data


SQL> alter tablespace users rename to users_data;

Tablespace altered.



• Verificamos que ya no existe USERS en cambio existe USERS_DATA

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS_DATA
EXAMPLE
TBSJMW
INVENTORY

8 rows selected.



• Verificamos que no quedaron usuarios con default tablespace USERS

SQL> select username, default_tablespace from  dba_users where default_tablespace='USERS';
no rows  selected 



• Verificamos que automáticamente actualizó el Default Tablespace de los usuarios que tenían USERS a USERS_DATA

SQL> select username, default_tablespace from  dba_users where default_tablespace='USERS_DATA';

USERNAME                        DEFAULT_TABLESPACE
------------------------------  ------------------------------
PM                              USERS_DATA
BI                              USERS_DATA

............................................................

16 rows selected.


Conclusión : Excepto SYSTEM y SYSAUX que no se puede renombrar, al renombrar cualquier tablespace que los usuarios tienen definido como Default Tablespace, automáticamente queda actualizado el Default Tablespace de dichos usuarios.


2. Renombramos el UNDO Tablespace, UNDOTBS1 por UNDOTBS2
   • Verificamos que usamos SPFILE

SQL> show parameters pfile

NAME          TYPE        VALUE
--------      ----------  -----------------------------
spfile        string      /u01/app/oracle/product/11.2.0
                          /dbhome_1/dbs/spfileorcl.ora



• Verificamos que esta definido UNDOTBS1

SQL> show parameters UNDO

NAME                                 TYPE         VALUE
-----------------------------------  -----------  ---------------------
undo_management                      string        AUTO
undo_retention                       integer       172800
undo_tablespace                      string        UNDOTBS1



• Renombramos UNDOTBS1 a UNDOTBS2

SQL> alter tablespace undotbs1 rename to undotbs2;
Tablespace altered.



• Comprobamos que NO quedo actualizado 

SQL> show parameters UNDO

NAME                                  TYPE         VALUE
------------------------------------  ----------   --------------------
undo_management                       string       AUTO
undo_retention                        integer      172800
undo_tablespace                       string       UNDOTBS1



• Verificamos que lo cambio en el spfile

SQL> select value from v$spparameter where  name='undo_tablespace';

VALUE
---------------------------------------------------------------
UNDOTBS2



• Reiniciamos la base

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                   1336932 bytes
Variable Size              297798044 bytes
Database Buffers           113246208 bytes
Redo Buffers                 6103040 bytes
Database mounted.
Database opened.



• Verificamos el cambio

SQL> show parameter UNDO

NAME                                  TYPE         VALUE
------------------------------------ -----------  ---------------------
undo_management                       string      AUTO
undo_retention                        integer     172800
undo_tablespace                       string      UNDOTBS2


Conclusión : Renombrar el Undo tablespace solo toma efecto después de reiniciar la base. Si no usamos spfile, antes de levantar la base tenemos que cambiar manualmente el parámetro en el pfile.

3. Renombramos el Tablespace Temporal
   • Para ver el Default Temporary Tablespace consultamos la tabla DATABASE_PROPERTIES

SQL> column property_name format a25
SQL> column property_value format a15
SQL> select property_name, property_value from  DATABASE_PROPERTIES 
where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME              PROPERTY_VALUE
------------------------   ----------------------
DEFAULT_TEMP_TABLESPACE    TEMP 



• Consultamos los usuarios que usan TEMP de Tablespace Temporal

SQL> select username, temporary_tablespace from  dba_users where temporary_tablespace='TEMP';

USERNAME                        TEMPORARY_TABLESPACE
------------------------------  ------------------------------
DBSNMP                          TEMP
DBA1                            TEMP
BI                              TEMP
...........................................................

40 rows selected.



• Renombramos TEMP como TEMP1 

SQL> alter tablespace TEMP rename to TEMP1;
Tablespace altered.



• Verificamos que no quedaron usuarios con temporary tablespace TEMP

SQL> select username, temporary_tablespace from  dba_users 
where temporary_tablespace='TEMP';
no rows  selected 



• Verificamos que automáticamente actualizó el Temporary Tablespace de los usuarios que tenían TEMP a TEMP1

SQL> select username, temporary_tablespace from  dba_users 
where temporary_tablespace='TEMP1';

USERNAME                        TEMPORARY_TABLESPACE
------------------------------  ------------------------------
DBSNMP                          TEMP1
DBA1                            TEMP1
BI                              TEMP1
.............................................................

40 rows selected.



• Verificamos que automáticamente actualizó tabla DATABASE_PROPERTIES

SQL> select property_name, property_value from  DATABASE_PROPERTIES 
where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME              PROPERTY_VALUE
-------------------------  -----------------------
DEFAULT_TEMP_TABLESPACE    TEMP1 


Conclusión : Al renombrar un Tablespace Temporal que los usuarios lo tienen definido como Temporary Tablespace, automáticamente queda actualizado el Temporary Tablespace de dichos usuarios. Si este Tablespace Temporal es el Default Temporary Tablespace también lo actualiza en la tabla Database_Properties.


4. Renombramos un Tablespace Read Only
   • Verificamos que el tablespace READONLY_TBS esta definido como READ ONLY

SQL> select  tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                 STATUS
------------------------------  ------------
SYSTEM                          ONLINE
SYSAUX                          ONLINE
UNDOTBS1                        ONLINE
TEMP                            ONLINE
USERS                           ONLINE
EXAMPLE                         ONLINE
TBSJMW                          ONLINE
INVENTORY                       ONLINE
READONLY_TBS                    READ ONLY 

9 rows selected.



• Renombramos READONLY_TBS como APP_READONLY

SQL> alter tablespace readonly_tbs  rename to app_readonly;



• Verificamos que ya no existe READONLY_TBS, en cambio existe APP_READONLY

SQL> select  tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                 STATUS
------------------------------  ---------
SYSTEM                          ONLINE
SYSAUX                          ONLINE
UNDOTBS1                        ONLINE
TEMP                            ONLINE
USERS                           ONLINE
EXAMPLE                         ONLINE
TBSJMW                          ONLINE
INVENTORY                       ONLINE
APP_READONLY                    READ ONLY

9 rows selected.



• Revisamos el alert.log

[oracle@host01]$ tail  alert_orcl.log
Completed: create tablespace  ReadOnly_TBS datafile '+DATA' size 100M
Sun Jan 26 09:30:34 2014
alter tablespace  ReadOnly_TBS read only
Converting  block 0 to version 10 format
Completed: alter tablespace ReadOnly_TBS read only
Sun Jan 26 09:35:51 2014
alter tablespace readonly_tbs  rename to app_readonly
Tablespace  'READONLY_TBS' is renamed to 'APP_READONLY'.
Tablespace  name change is not propagated to file headersbecause the tablespace is read  only. 
Completed: alter tablespace readonly_tbs rename to  app_readonly


Conclusión : Si el tablespace es Read Only, los headers de los datafiles no son actualizados. Esto no significa que quede corrupto. En el alert.log va a escribir un mensaje que los headers de los datafiles no fueron renombrados pero en el diccionario de datos y en los control file quedó actualizado.