MERCADOS FINANCIEROS

miércoles, 6 de junio de 2012

Raid 1 en Linux

shian:~# fdisk /dev/hdb
Command (m for help): p

Disk /dev/hdb: 1073 MB, 1073741824 bytes
16 heads, 63 sectors/track, 2080 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes

Device Boot Start End Blocks Id System

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-2080, default 1):INTRO
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-2080, default 2080):INTRO
Using default value 2080

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): fd
Changed system type of partition 1 to fd (Linux raid autodetect)

Command (m for help): p

Disk /dev/hdb: 1073 MB, 1073741824 bytes
16 heads, 63 sectors/track, 2080 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes

Device Boot Start End Blocks Id System
/dev/hdb1 1 2080 1048288+ fd Linux raid autodetect

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
• Repetimos los pasos para /dev/hdc.
• Creamos el raid 1 con las dos particiones que acabamos de crear. En las opciones indicamos que el tipo de raid será 1 (mirror), que deseamos utilizar dos discos y que el nuevo dispositivo raid será /dev/md0.
shian:~# mdadm --create /dev/md0 --verbose --level=1 --raid-devices=2 /dev/hdb1 /dev/hdc1
mdadm: size set to 1048192K
mdadm: array /dev/md0 started.

NOTA: Si ya hemos usado el disco anteriormente para otro raid, es necesario reiniciar el superbloque para que se borre la información existente, puesto que sino, la creación puede fallar:
shian:~# mdadm --zero-superblock /dev/sdXX
• El raid 1 se está creando en segundo plano. En función del tamaño de los discos tardará más o menos. Se puede ver el estado en el archivo /proc/mdstat:
shian:~# cat /proc/mdstat
Personalities : [raid1]
read_ahead 1024 sectors
md0 : active raid1 ide/host0/bus1/target0/lun0/part1[1] ide/host0/bus0/target1/lun0/part1[0]
1048192 blocks [2/2] [UU]
[===========>.........] resync = 58.2% (611196/1048192) finish=0.0min speed=101866K/sec
unused devices:
• El porcentaje va subiendo hasta que finalmente el dispositivo está listo:
shian:~# cat /proc/mdstat
Personalities : [raid1]
read_ahead 1024 sectors
md0 : active raid1 ide/host0/bus1/target0/lun0/part1[1] ide/host0/bus0/target1/lun0/part1[0]
1048192 blocks [2/2] [UU]

unused devices:
• A partir de este momento para cualquier manipulación que deseemos hacer del raid debemos utilizar /dev/md0 y no /dev/hdb1 ni /dev/hdc1.
• Formateamos el raid
shian:~# mkfs.ext3 /dev/md0
mke2fs 1.37 (21-Mar-2005)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
131072 inodes, 262048 blocks
13102 blocks (5.00%) reserved for the super user
First data block=0
8 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376

Writing inode tables: done
Creating journal (4096 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 22 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
• Creamos el punto de montaje, añadimos la entrada correspondiente para que el raid se monte cuando se arranca la máquina y lo montamos:
shian:~# mkdir /mnt/raid

shian:~# echo "/dev/md0 /mnt/raid ext3 defaults 0 1" >> /etc/fstab

shian:~# mount /mnt/raid

shian:~# df -h /dev/md0
Filesystem Size Used Avail Use% Mounted on
/dev/md0 1008M 17M 941M 2% /mnt/raid
• Aquí ya habríamos terminado la instalación del raid y podríamos utilizarlo, aunque tal y como está configurado y montado sólo tendría permisos el usuario root.
A partir de aquí, lo que yo hice inicialmente fue simular que un disco duro se estropeaba y al arrancar la máquina quería montar el raid sólo con el otro disco y utilizarlo normalmente. Además, después de simular con la máquina virtual que añadía un nuevo disco duro, quería añadirlo al raid para volver a tener de nuevo la redundancia. Después de leer muchos tutoriales y foros no había manera de que funcionase. Si reiniciaba la máquina sin un disco del raid, éste no se montaba y no podía acceder a los datos. Además, el dispositivo /dev/md0 no era reconocido, por lo que era como si el raid no existiese!. Finalmente, encontré en un pequeño tutorial la solución a mis problemas.
• Es necesario indicarle al sistema operativo cómo acceder a ese dispositivo raid para que sea capaz de utilizarlo. Esto que puede parecer tan obvio no venía en ningún tutorial ni en ninguna ayuda de las que consulté.
shian:/# cd /etc/mdadm
shian:/etc/mdadm# cp mdadm.conf mdadm.conf.`date +%y%m%d`
shian:/etc/mdadm# echo "DEVICE partitions" > mdadm.conf
shian:/etc/mdadm# mdadm --detail --scan >> mdadm.conf
shian:/etc/mdadm#
shian:/etc/mdadm# cat mdadm.conf
DEVICE partitions
ARRAY /dev/md0 level=raid1 num-devices=2 UUID=a48e6816:ea6e7f37:6cc50cdb:6fead399
devices=/dev/hdb1,/dev/hdc1
• Ahora ya podemos reiniciar la máquina y el raid arrancará y se montará automáticamente en el arranque.
• Podemos probar a parar el dispositivo y a levantarlo de nuevo:
shian:/etc/mdadm# umount /mnt/raid
shian:/etc/mdadm# mdadm --stop /dev/md0
shian:/etc/mdadm# cat /proc/mdstat
Personalities : [raid1]
read_ahead 1024 sectors
unused devices:

shian:/etc/mdadm# mdadm --assemble /dev/md0 /dev/hdb1 /dev/hdc1
mdadm: /dev/md0 has been started with 2 drives.
shian:/etc/mdadm# cat /proc/mdstat
Personalities : [raid1]
read_ahead 1024 sectors
md0 : active raid1 ide/host0/bus0/target1/lun0/part1[0] ide/host0/bus1/target0/lun0/part1[1]
1048192 blocks [2/2] [UU]

unused devices:

Ahora sí, vamos a probar si realmente el podemos recuperar la información y el sistema funciona correctamente en caso de caída de un dispositivo. Además, veremos cómo reemplazar el disco defectuoso y recuperar de nuevo el raid 1 con los dos discos.
• Creamos un archivo aleatorio de 25MB en el raid montando previamente de nuevo el raid:
shian:/# mount /dev/md0 /mnt/raid
shian:/# dd if=/dev/urandom of=/mnt/raid/random1 count=51200
51200+0 records in
51200+0 records out
26214400 bytes transferred in 7.829523 seconds (3348148 bytes/sec)
• Calculamos su CRC y lo apuntamos. Posteriormente nos servirá para comprobar que todo es correcto:
shian:/# cksum /mnt/raid/random1
1652310020 26214400 /mnt/raid/random1
• Vamos a simular un fallo en uno de los dispositivos. Para ello apagamos el sistema, desconectamos uno de los discos duros (en este caso /dev/hdb) y arrancamos de nuevo.
• Una vez arrancado de nuevo el sistema, si examinamos con detalle los mensajes de arranque encontraremos algo como lo siguiente. Como se puede ver el sistema ha detectado un disco falla y al no haber un disco de repuesto (spare) levanta el raid en modo degradado con un sólo disco. Podremos seguir utilizando el raid con total normalidad pero si este disco también fallase, perderíamos irremediablemente todos los datos.
md: bind
md: ide/host0/bus1/target0/lun0/part1's event counter: 00000006
md0: former device hdb1 is unavailable, removing from array!
md: raid1 personality registered as nr 3
md0: max total readahead window set to 124k
md0: 1 data-disks, max readahead per data-disk: 124k
raid1: device ide/host0/bus1/target0/lun0/part1 operational as mirror 1
raid1: md0, not all disks are operational -- trying to recover array
raid1: raid set md0 active with 1 out of 2 mirrors
md: updating md0 RAID superblock on device
md: ide/host0/bus1/target0/lun0/part1 [events: 00000007]<6>(write) ide/host0/bus1/target0/lun0/part1's sb offset: 1048192
md: recovery thread got woken up ...
md0: no spare disk to reconstruct array! -- continuing in degraded mode
md: recovery thread finished ...
• En el archivo /proc/mdstat podemos ver el estado del raid. Ahora mismo se encuentra funcionando sólo con un dispositivo de dos posibles y nos indica que el que ha fallado es el primero de ellos:
shian:~# cat /proc/mdstat
Personalities : [raid1]
read_ahead 1024 sectors
md0 : active raid1 ide/host0/bus1/target0/lun0/part1[1]
1048192 blocks [2/1] [_U]

unused devices:
• No obstante el raid está montado y el filesystem es accesible:
shian:~# df -h /dev/md0
Filesystem Size Used Avail Use% Mounted on
/dev/md0 1008M 42M 916M 5% /mnt/raid
• Ahora marcamos el disco /dev/hdb1 como fallo para proceder a cambiarlo:
shian:~# mdadm --manage /dev/md0 --fail /dev/hdb1
mdadm: set /dev/hdb1 faulty in /dev/md0

Apagamos la máquina y cambiamos el disco duro defectuoso por uno nuevo. En el caso de VMware basta con crear un nuevo dispositivo de tipo disco duro. Además, este disco duro nuevo que añadimos va a ser de mayor tamaño que el anterior. Idealmente en un raid 1 los dos discos duros deben tener el mismo tamaño, pero linux nos proporciona la suficiente flexibilidad para que esto no sea así.
• En el arranque de la máquina vemos que el raid sigue arrancando pero en modo degradado. Lo que vamos a hacer es crear la tabla de particiones del nuevo disco duro exáctamente igual que la del disco duro que aún funciona y que forma parte del raid:
shian:~# sfdisk -d /dev/hdc | sfdisk /dev/hdb
Checking that no-one is using this disk right now ...
OK

Disk /dev/hdb: 4161 cylinders, 16 heads, 63 sectors/track

sfdisk: ERROR: sector 0 does not have an msdos signature
/dev/hdb: unrecognized partition table type
Old situation:
No partitions found
New situation:
Units = sectors of 512 bytes, counting from 0

Device Boot Start End #sectors Id System
/dev/hdb1 63 2096639 2096577 fd Linux raid autodetect
/dev/hdb2 0 - 0 0 Empty
/dev/hdb3 0 - 0 0 Empty
/dev/hdb4 0 - 0 0 Empty
Warning: no primary partition is marked bootable (active)
This does not matter for LILO, but the DOS MBR will not boot this disk.
Successfully wrote the new partition table

Re-reading the partition table ...

If you created or changed a DOS partition, /dev/foo7, say, then use dd(1)
to zero the first 512 bytes: dd if=/dev/zero of=/dev/foo7 bs=512 count=1
(See fdisk(8).)
• Como este nuevo disco duro es mayor que el anterior, podemos crear una partición /dev/hdb2 y formatearla para utilizarla sin problemas.
shian:~# fdisk /dev/hdb

The number of cylinders for this disk is set to 4161.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (2081-4161, default 2081):INTRO
Using default value 2081
Last cylinder or +size or +sizeM or +sizeK (2081-4161, default 4161):INTRO
Using default value 4161

Command (m for help): p

Disk /dev/hdb: 2147 MB, 2147483648 bytes
16 heads, 63 sectors/track, 4161 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes

Device Boot Start End Blocks Id System
/dev/hdb1 1 2080 1048288+ fd Linux raid autodetect
/dev/hdb2 2081 4161 1048824 83 Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

shian:~# mkfs.ext3 /dev/hdb2
mke2fs 1.37 (21-Mar-2005)
warning: 62 blocks unused.

Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
131328 inodes, 262144 blocks
13110 blocks (5.00%) reserved for the super user
First data block=0
8 block groups
32768 blocks per group, 32768 fragments per group
16416 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 30 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

shian:~# mkdir /mnt/tmp

shian:~# mount /dev/hdb2 /mnt/tmp
• Ahora vamos a reconstruir el raid:
shian:~# mdadm --manage /dev/md0 --add /dev/hdb1
mdadm: hot added /dev/hdb1
• En este instante el raid 1 se está reconstruyendo. Toda la información del disco existente (/dev/hdc1) se está escribiendo en el nuevo disco (/dev/hdb1) para reconstruir el mirror y tener de nuevo la redundancia. Podemos comprobar el estado en el archivo /proc/mdstat:
shian:~# cat /proc/mdstat
Personalities : [raid1]
read_ahead 1024 sectors
md0 : active raid1 ide/host0/bus0/target1/lun0/part1[2] ide/host0/bus1/target0/lun0/part1[1]
1048192 blocks [2/1] [_U]
[=======>.............] recovery = 39.5% (415488/1048192) finish=0.1min speed=69248K/sec
unused devices:
• Finalmente, después de un tiempo tenemos el raid recuperado:
shian:~# cat /proc/mdstat
Personalities : [raid1]
read_ahead 1024 sectors
md0 : active raid1 ide/host0/bus0/target1/lun0/part1[0] ide/host0/bus1/target0/lun0/part1[1]
1048192 blocks [2/2] [UU]

unused devices:
• Si no nos fiamos de que todo esté correcto (yo tengo que verlo para creerlo), podemos hacer lo siguiente para comprobar que la recuperación se ha realizado satisfactoriamente. Lo que vamos a hacer es desmontar el raid, montar únicamente el nuevo dispositivo /dev/hdb1 y comprobar el CRC del archivo que generamos anteriormente para comprobar que la recuperación ha sido correcta.
shian:~# umount /mnt/raid
shian:~# mount /dev/hdb1 /mnt/raid
shian:~# cksum /mnt/raid/random1
1652310020 26214400 /mnt/raid/random1
• Y listo!. El raid se ha recuperado correctamente y toda nuestra información está a salvo. Es importante dejarlo todo como estaba antes de utilizarlo puesto que ahora mismo en /mnt/raid se encuentra montado de manera temporal sólo un dispositivo del raid y no éste completo. Si ahora hiciéramos algún cambio, creásemos archivos,... perderíamos todos esos datos en cuanto montásemos de nuevo el raid. Mejor lo dejamos todo como estaba:
shian:~# umount /mnt/raid/
shian:~# mount /mnt/raid/
shian:~# df -h /mnt/raid/
Filesystem Size Used Avail Use% Mounted on
/dev/md0 1008M 42M 916M 5% /mnt/raid
• Si ahora reiniciamos la máquina vemos que el raid arranca correctamente con los dos discos de nuevo:
md: ide/host0/bus0/target1/lun0/part1's event counter: 0000000c
md: ide/host0/bus1/target0/lun0/part1's event counter: 0000000c
md: raid1 personality registered as nr 3
md0: max total readahead window set to 124k
md0: 1 data-disks, max readahead per data-disk: 124k
raid1: device ide/host0/bus0/target1/lun0/part1 operational as mirror 0
raid1: device ide/host0/bus1/target0/lun0/part1 operational as mirror 1
raid1: raid set md0 active with 2 out of 2 mirrors
md: updating md0 RAID superblock on device
md: ide/host0/bus0/target1/lun0/part1 [events: 0000000d]<6>(write) ide/host0/bus0/target1/lun0/part1's sb offset: 1048192
md: ide/host0/bus1/target0/lun0/part1 [events: 0000000d]<6>(write) ide/host0/bus1/target0/lun0/part1's sb offset: 1048192

lunes, 4 de junio de 2012

DGMGRL

[oracle@11gPrimary dbs]$ dgmgrl

DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
1.) Creating Data Guard Broker configuration for the primary database. Next, I’ll use the CREATE CONFIGURATION command to initialize a Data Guard Broker configuration named MAA_orcl, and confirm its creation with the SHOW CONFIGURATION command:

DGMGRL> CREATE CONFIGURATION 'MAA_orcl' AS
> PRIMARY DATABASE IS primary_db
> CONNECT IDENTIFIER IS orcl;

Configuration "MAA_orcl" created with primary database "primary_db"

DGMGRL> show configuration

Configuration
Name: MAA_orcl
Enabled: NO
Protection Mode: MaxPerformance
Databases:
primary_db - Primary database

Fast-Start Failover: DISABLED

Current status for "MAA_orcl":
DISABLED
2.) Adding a standby database to the Data Guard Broker configuration. To add a standby database to the current configuration, I’ll use the ADD DATABASE command. Note that I could add up to eight additional standby databases to the configuration using DGMGRL:

DGMGRL> ADD DATABASE 'stdby_db' AS
> CONNECT IDENTIFIER IS stdby;

Database "stdby_db" added
I’ll then use DGMGRL’s SHOW CONFIGURATION and SHOW DATABASE commands to confirm the configuration before I enable it:

DGMGRL> show configuration verbose;

Configuration
Name: MAA_orcl
Enabled: NO
Protection Mode: MaxPerformance
Databases:
primary_db - Primary database
stdby_db - Physical standby database

Fast-Start Failover: DISABLED

Current status for "MAA_orcl":
DISABLED

DGMGRL> show database stdby_db

Database
Name: stdby_db
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: OFFLINE
Instance(s):
orcl

Current status for "stdby_db":
DISABLED
4.) Enabling the Data Guard Broker configuration. Lastly, I’ll issue the ENABLE CONFIGURATION command to activate the configuration and then confirm the successful activation of the primary and standby databases via the SHOW DATABASE command:

DGMGRL> enable configuration;

Enabled.

DGMGRL> show database primary_db;

Database
Name: primary_db
Role: PRIMARY
Enabled: YES
Intended State: TRANSPORT-ON
Instance(s):
orcl

Current status for "primary_db":
SUCCESS

DGMGRL> show database stdby_db;

Database
Name: stdby_db
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-ON
Instance(s):
orcl

Current status for "stdby_db":
SUCCESS
One of the beautiful things about Data Guard Broker is that it automatically creates and issues all necessary ALTER SYSTEM commands to enable the required Data Guard configurations on each database in the configuration. I’ve showed the results of enabling the Data Guard Broker configuration from the primary and standby databases’ alert logs in Listing 2.3.

Performing a Switchover between Primary and Standby Databases
If you’ve ever performed a switchover operation between a primary and a standby database without Data Guard Broker, I’m sure you’ll agree that it’s a tedious, manually-intensive process that involves several distinct steps which must be performed carefully and in precise order to insure a successful role transition. Here’s a brief summary of the steps required:

1.) Check the status of the primary database. Column V$DATABASE.SWITCHOVER_STATUS on the primary database must reflect the proper state of the primary database. Either a value of TO_STANDBY or SESSIONS_ACTIVE indicates that a switchover is possible; otherwise, redo transport services haven’t been configured properly.

2.) Start the switchover process on the primary database. Next, the standby database has to be prepared to handle switchover by issuing the appropriate switchover command from SQL*Plus. During this process, the primary database is prepared to assume the standby role, and its control file is backed up to make sure it could be reconstructed in case a failure occurs.

-- If no sessions are active ...
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
-- ... but if sessions are active:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
3.) Shut down, then mount, the original primary database. Once the role transition is complete on the original primary (and soon to become the standby) database, it’s time to shut it down and mount it.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
4.) Check that the target database’s status is ready for switchover. The target standby database must be ready to accept its new primary role. Column V$DATABASE.SWITCHOVER_STATUS on this database must contain a value of either TO­_PRIMARY or SESSIONS_ACTIVE to indicate that a switchover is possible; otherwise, redo transport services haven’t been configured properly.

5.) Switch the target database to its new primary role. Depending on the result from the previous step, the appropriate command needs to be issued on the (soon to be) primary database from SQL*Plus:

-- If no sessions are active ...
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
-- ... but if sessions are active:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
6.) Open the new primary database. It’s time to activate the new primary database from SQL*Plus:

SQL> ALTER DATABASE OPEN;
7.) Make the original primary database into the standby database. To complete the role transition, the original primary database must be enabled to accept its new standby role.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE
DISCONNECT FROM SESSION;
In this relatively simple example of a role transition, notice that I haven’t discussed what to do if there’s a failure at any point during this process. Frankly, I’d much rather have a lot more insurance should the process fail at any point … and that’s where Data Guard Broker comes in.

Example: Using Data Guard Broker to Perform a Switchover
Data Guard Broker makes extremely short work of role transitions like a switchover. Once I’ve connected to DGMGRL as the SYS user on the primary database server, all I need to do is issue one simple command, SWITCHOVER TO :

[oracle@11gPrimary dbs]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.

DGMGRL> SWITCHOVER TO stdby_db;

Performing switchover NOW, please wait...
New primary database "stdby_db" is opening...
Operation requires shutdown of instance "orcl" on database "primary_db"
Shutting down instance "orcl"...
ORA-1109: database not open
Database dismounted.
ORACLE instance shut down.

Operation requires startup of instance "orcl" on database "stdby_db"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "stdby_db"
I’ve showed the results of this switchover operation from the perspective of the primary and standby databases’ alert logs in Listing 2.4.

Example: Using Data Guard Broker to Perform a Switchback
Data Guard Broker also makes it simple to revert the original primary and standby database back to their original roles. Once again, I’ll connect to DGMGRL as the SYS user on the original primary database server and just issue the SWITCHOVER TO command to revert to the original Data Guard configuration:

[oracle@11gPrimary dbs]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.

DGMGRL> SWITCHOVER TO primary_db;

Performing switchover NOW, please wait...
New primary database "stdby_db" is opening...
Operation requires shutdown of instance "orcl" on database "stdby_db"
Shutting down instance "orcl"...
ORA-1109: database not open
Database dismounted.
ORACLE instance shut down.

Operation requires startup of instance "orcl" on database "primary_db"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "primary_db"

Oracle Data Guard Broker configuration example

Oracle Data Guard Broker configuration example

The official Oracle Data Guard Broker docs docs are, of course, the best source for accurate and complete information about how to set up Oracle Data Guard with Oracle Data Guard Broker. They are, however, not always the most useful source, since they generally sacrifice readability for completeness.

This doc should be useful to people setting up a Data Guard Broker with DGMGRL. Managing Data Guard through the Broker is much simpler than managing it manually. DGMGRL is allegedly not as simple as managing Data Guard through Orcle Enterprise Manager Grid Control; however, Grid Control is an extra-cost option that not all of us have.

This procedure makes a number of assumptions that you have made life easy on yourself: you’re using Oracle 11g; you’ve configured a db_recovery_file_dest; your primary and standby databases will be on separate machines with identical directory structures; etc.

Special thanks go out to Chris Ruel of Perpetual Technologies in Indianapolis for his “Oracle 11g Data Guard” presentation, which provided the starting material for this. I have pared away some parts and fleshed out others, but I never would have figured the core of the material out without his help.

I am a beginner at this, and this procedure almost certainly contains steps that are not optimal, and perhaps completely unnecessary. All I can say for certain is that this is one path that got me going successfully.

To begin, assume that you have a primary database server, prodserv.myco.com, hosting a production database, PROD. You have a standby server, stbyserv.myco.com, and intend to put a standby Oracle database on it, STBY. Oracle 11g + is installed on both machines.
1.
On stbyserv, create $ORACLE_HOME/dbs/initSTBY.ora with just one parameter:


db_name = PROD

(On Windows, all references to $ORACLE_HOME/dbs should be understood as %ORACLE_HOME%\database.)

2.
On prodserv:

SQL> CREATE PFILE FROM SPFILE;
Add these parameters to $ORACLE_HOME/dbs/initPROD.ora:

*.db_unique_name='PROD'
*.dg_broker_start=TRUE
*.fal_client='PROD'
*.fal_server='STBY'
*.log_archive_config='dg_config=(STBY,PROD)'
*.log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_2='service="STBY", db_unique_name="STBY", valid_for=(online_logfile,primary_role)'
*.standby_file_management='AUTO'
3.
These new parameters must be incorporated into PROD‘s SPFILE, and the instance must be restarted from the SPFILE:

SQL> shutdown immediate;
SQL> !rm $ORACLE_HOME/dbs/spfilePROD.ora
SQL> startup;
SQL> create spfile from pfile;
SQL> shutdown immediate;
SQL> startup;
4.
Edit $ORACLE_HOME/network/admin files.

On prodserv, add to listener.ora:

(SID_DESC =
(GLOBAL_DBNAME = stby_DGMGRL)
(ORACLE_HOME = {oracle home directory here})
(SID_NAME = STBY)
(SERVICE_NAME = STBY)
)
On stbyserv, add to listener.ora:

(SID_DESC =
(GLOBAL_DBNAME = stby.myco.com)
(ORACLE_HOME = {oracle home directory here})
(SID_NAME = STBY)
)
(SID_DESC =
(GLOBAL_DBNAME = prod_DGMGRL)
(ORACLE_HOME = {oracle home directory here})
(SID_NAME = PROD)
(SERVICE_NAME = PROD)
)
(Note that each machine contains a _DGMGRL entry for the database hosted on the other machine)

tnsnames.ora should have these entries on both machines:

PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodserv.myco.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod.myco.com)
)
)
STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stbyserv.myco.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stby.myco.com)
)
)
Run lsnrctl reload on both machines. Verify that you can tnsping prod and tnsping stby from both machines.

5.
Under *nix, copy $ORACLE_HOME/dbs/orapwPROD from PROD to $ORACLE_HOME/dbs/orapwSTBY on stbyserv.

Under Windows, on stbyserv:

oradim -new -sid stby -intpwd mypassword
6.
SQL> connect sys@stby as sysdba
SQL> startup nomount;
7.
PROD should have standby logfiles - one more than its number of online redo logfiles, I believe.

SQL> connect sys@prod as sysdba
SQL> select type, count(*) from v$logfile group by type;
SQL> alter database add standby logfile;
SQL> alter database add standby logfile;
SQL> alter database add standby logfile;
SQL> alter database add standby logfile;
8.
RMAN> connect target sys@prod
RMAN> backup database plus archivelog;
9.
Copy the {flash recovery area}/PROD/BACKUPSET/{today} directory from prodserv to stbyserv. Copy it to exactly the same place in the directory structure. Do not change prod to stby in the path.

On stbyserv:

mkdir {oradata directory}/PROD
Again, do not rename PROD to STBY in the directory structure.

10.
RMAN> connect target sys@prod auxiliary sys@stby
RMAN> run {
allocate channel prmy1 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database
For standby
from active database spfile
set db_unique_name = 'STBY'
set fal_client = 'STBY'
set fal_server = 'PROD'
set standby_file_management = 'AUTO'
set log_archive_config = 'dg_config=(PROD,STBY)'
set log_archive_dest_1 = 'service=PROD ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=PROD'
dorecover nofilenamecheck;
}
11.
SQL> connect sys@stby as sysdba;
SQL> recover managed standby database disconnect;
SQL> recover managed standby database cancel;
SQL> alter database open read only;
SQL> recover managed standby database using current logfile disconnect;
12.
DGMGRL> connect sys@prod
DGMGRL> create configuration 'standby_config' as
> primary database is 'PROD'
> connect identifier is 'PROD';

DGMGRL> show configuration;
DGMGRL> add database 'STBY' as
> connect identifier is stby;
DGMGRL> enable configuration;
DGMGRL> enable database stby;
13.
Finally ready!

DGMGRL> connect sys@prod
DGMGRL> switchover to 'STBY';

CONFIGURACION DATAGUARD BROKER

Configurando Dataguard Broker


July 28, 20091 Comment

Oracle nos ofrece esta herramienta para poder hacer pasaje automatico en caso de tener un switch o un fail, y su uso una vez configurado es tan sencillo como conectarse a la consola del administrador con el comando dgmgrl o desde el Entrerprise Manager con solo haciendo un click.

Yo en este caso voy a disponibilizar el modo de configuración desde la linea de comandos ya que me parece super práctico y nos sirve para conocer un poco más el modo en que trabaja.

Es importante saber que tanto desde la consola del GRID CONTROL o desde comandos hay que hacer una configuración previa que incluye:
■Configuración de Listener en primaria y secundaria.
■Configuración de Parametros de la base.
■Creación de los archivos de configuración del broker. (Debemos ser sysdba)
■En el caso de trabajar con una base que esta en RAC, hay que hacer la configuración con srvctl. (En este caso yo voy a presentarlo en una instancia en RAC y serán informados de ello para que sepan que el modo es el mismo para la single instance, menos este paso.)

CONFIGURANDO LISTENERS

Comenzaremos configurando los listeners, tanto en nuestra base primaria como en nuestra standby.
Es muy importante este paso ya que si el broker no ve el servicio no podrá hacer ninguna operación de switcheo.
# listener.ora.saturno01 Network Configuration File: /u01/app/oracle/product/10.2.0/db_mbpro/network/admin/listener.ora.saturno01
# Generated by Oracle configuration tools.

MBPRO_SATURNO01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = saturno01-vip.danahomelinux.com)(PORT = 1526)
(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = saturno01.danahomelinux.com)(PORT = 1526)
(IP = FIRST))
)
)

SID_LIST_MBPRO_SATURNO01 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_mbpro)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = MBPRO_DGMGRL.danahomelinux.com)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_mbpro)
(SID_NAME = MBPRO1)
)
)
Una vez realizada la tarea, procedemos a relodear el listener, y luego con un status service vemos si los cambios fueron impactados.
lsnrctl status MBPRO_SATURNO01
lsnrctl service MBPRO_SATURNO01
Ahora procedemos a realizar la misma tarea en nuestra base secundaria.

CONFIGURANDO LA BASE

Ahora que configuramos los listeners (y los parametros con el comando srvctl en el caso de estar trabajando con una base en RAC) procedemos a crear los archivos fisicos de la configuración del broker y activar el feature en la base misma poniendo el parametro dg_broker_start=start, donde si tenemos una terminal abierta podremos ver en el alert.log que se activa un proceso nuevo que en DMON.
Primero creamos en un path en el SO que sea seguro y luego en la base pasamos esos mismos párametros.
SQL> sho parameter broker

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string
dg_broker_config_file2 string
dg_broker_start boolean FALSE

SQL> alter system set dg_broker_config_file1='/u06/dgbroker/dr1_MBPRO.dat'
scope=both sid='*';

System altered.

SQL> alter system set dg_broker_config_file2='/u07/dgbroker/dr2_MBPRO.dat'
scope=both sid='*';

System altered.

SQL> sho parameter broker

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u06/dgbroker/dr1_MBPRO.dat
dg_broker_config_file2 string /u07/dgbroker/dr2_MBPRO.dat
dg_broker_start boolean FALSE

SQL> alter system set dg_broker_start=true scope=both sid='*';
System altered.
Ingresamos a la consola del DGBROKER y luego nos conectamos con el usuario administrador.
Desde aqui vamos a decirle a broker como se llamará la primaria, la secundaria, y que roles tendra cada una. Esto nos servirá en el futuro para switchear, pasar por fail, o mantenimiento mismo.
[oracle@saturno bin]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/PASSXXXX
Connected.
Creamos la configuración
DGMGRL> CREATE CONFIGURATION 'DR_MBPRO' AS
> PRIMARY DATABASE IS 'MBPRO'
> CONNECT IDENTIFIER IS MBPRO;
Configuration "DR_MBPRO" created with primary database "MBPRO"
DGMGRL>
DGMGRL> ADD DATABASE 'MBPRODG' AS
> CONNECT IDENTIFIER IS MBPRODG
> MAINTAINED AS PHYSICAL;
Database "MBPRODG" added
DGMGRL>

Ahora podemos ver configuracion que armamos y nos va a decir que esta en modo deshabilitado.
DGMGRL> show configuration;

Configuration
Name: DR_MBPRO
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
MBPRO - Primary database
MBPRODG - Physical standby database

Current status for "DR_MBPRO":
DISABLED
Habilitamos esa configuracion para que pueda ser leida por la instancia primaria y secundaria.
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration
Name: DR_MBPRO
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
MBPRO - Primary database
MBPRODG - Physical standby database

Current status for "DR_MBPRO":
SUCCESS
Vemos la configuracion de la primaria.
DGMGRL> show database verbose "MBPRO";

Database
Name: MBPRO
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
MBPRO

Properties:
InitialConnectIdentifier = 'MBPRO'
ObserverConnectIdentifier = ''
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+PRUEBAS_DG1/MBPRO, +PRUEBAS_DG1/MBPRO'
LogFileNameConvert = '+PRUEBAS_DG1/MBPRODG, +PRUEBAS_DG1/MBPRO'
FastStartFailoverTarget = '+PRUEBAS_DG1/MBPRODG, +PRUEBAS_DG1/MBPRO'
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'saturno.danahomelinux.com'
SidName = 'MBPRO'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=saturno.danahomelinux.com)(PORT=1529))'
StandbyArchiveLocation = '?/dbs/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "MBPRO":
SUCCESS
Ahora la de la secundaria.
DGMGRL> show database verbose "MBPRODG";

Database
Name: MBPRODG
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
MBPRODG

Properties:
InitialConnectIdentifier = 'MBPROdg'
ObserverConnectIdentifier = ''
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+PRUEBAS_DG1/MBPRO, +PRUEBAS_DG1/MBPRODG'
LogFileNameConvert = '+PRUEBAS_DG1/MBPRO, +PRUEBAS_DG1/MBPRODG'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'saturnoco.danahomelinux.com'
SidName = 'MBPRODG'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=saturnoco.danahomelinux.com)(PORT=1529))'
StandbyArchiveLocation = 'dgsby_MBPRODG'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "MBPRODG":
SUCCESS

DGMGRL>
Ya estamos listos para hacer nuestro switch over.

Ahora que tenemos configurados los listener, servicios de RAC y parametros de la base, podriamos realizar el primer pasaje de base, un cambio de roles.
Si somos los sufientemente curiosos, para este primer paso, podemos abrir los alert y podemos ir mirando que es lo que trancurre y no encontraremos con que:
■El broker hace los mismos pasos que nosotro hariámos a mano.
■El broker pone los párametros que tiene en el listener y los coloca, asi que cualquier modificiación que hicieramos sobre estos siempre se van a impactar en la base.
■Podemos hacer una consulta en la v$archive_dest antes de arracar, para verificar que hay conectividad y no tenemos problemas de red.
■En el caso que la base no tuviese la suficiente memoria por algún proceso enorme o si se encontrara con problemas, el broker comienza el proceso, y si es preciso lo aborta dejando la base intacta al momento de arrancar, sobre su rol antes de comenzar el pasaje. Además de ello no arroja los errores con el log de por qué la base no pudo pasar.
■En el caso de que en un pasaje perdemos el sitio primario, y previamente hubiesemos arrancado el switch, no podrá retornar, pero como buenos DBA’s al mirar el log sabriamos como seguir el switch a mano, ya que en el alert muestra los comandos ejecutados, y con nuestro procedimiento de switch over manual, podriámos verificar que paso faltó y continuar. La experiencia sobre el caso, me dice que esto es cierto y no una mera suposición.
[oracle@saturno01 dbs]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/xxxxxxx
Connected.
DGMGRL> switchover to "MBPRODG";
Performing switchover NOW, please wait...
Operation requires shutdown of instance "MBPRO" on database "MBPROD"
Shutting down instance "MBPRO"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "MBPRODG" on database "MBPRODG"
Shutting down instance "MBPRODG1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "MBPRO" on database "MBPRO"
Starting instance "MBPRO"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "MBPRODG" on database "MBPRODG"
Starting instance "MBPRODG1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "MBPRODG"

DGMGRL>

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

martes, 24 de abril de 2012

SQLNET

sqlnet.inbound_connect_timeout = 600

10 MINUTOS

#Possible values: 0-any valid positive integer (in seconds)
#Default: 0
#Recommended value: 3 seconds (note: this is highly application dependent)
#
#Purpose: Indicates the time interval within which database authentication
# for a client must be completed. If the client fails to complete
# authentication within the given time period, then the database
# server will drop the client connection. This can be used to
# counter Denial of Service attacks in which malicious clients may
# cause numerous servers to be spawn without fully establishing DB
# sessions.
#
# A value of 0 turns off the timeout feature. If a spurious
# timeout error occurs (e.g. due to a slow network/system),
# reconfigure this parameter to a larger value.
#
#Supported since: v9.2
#



miércoles, 7 de marzo de 2012

DATAGUARD

Link Inmportantes

http://basesdedatosues.blogspot.com/2010/07/creando-manualmente-un-data-guard-en.html

CONFIGURACION DATAGUARD PARA DOS INSTANCIAS STANDBY 11G WINDOWS

http://www.youtube.com/watch?v=5keahpkzLtM

CONFIGURACION DATAGUARD SNAPSHOT

http://www.youtube.com/watch?v=Ns2TabgoZew


ACTIVE DATAGUARD 11G

http://www.youtube.com/watch?v=zULeOXDOobM

(select sequence#,applied from v$archived_log)

select NAME, THREAD#, SEQUENCE#, APPLIED, registrar from v$archived_log where APPLIED='NO' order by sequence#;

Consultando la v$archived_log y el alert de la standby.

Para poner la standby a sincronizar, estando montada corres lo siguiente:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Si resulta ser que ya estaba en modo de recover y no aplicaba los logs, fijate en la vista v$dataguard_status.


Como el EM me avisa que estoy teniendo logs sin aplicar, y que estan faltando otros, me logueo por consola y compruebo lo dicho, estan faltando archives y tenemos un GAP.

SQL> SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
2 1785 1785


Como el EM me avisa que estoy teniendo logs sin aplicar, y que estan faltando otros, me logueo por consola y compruebo lo dicho, estan faltando archives y tenemos un GAP.

SQL> SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
2 1785 1785

Teniendo el numero de la secuencia, en este caso la SEQ 1785 THREAD 2, consulto si existe este archivo en el storage del dataguard.

SQL> set line 150
SQL> col NAME format a80
SQL> select NAME, THREAD#, SEQUENCE#, APPLIED, registrar from v$archived_log where SEQUENCE#='1785' and THREAD#='2' order by sequence#; 2

NAME THREAD# SEQUENCE# APP REGISTR
---------------------------------- ---------- ---------- --- -------
2 1785 NO SRMN

Busco en el sitio primario, y efectivamente la pieza faltante se encuentra donde deberia estar.

SQL> select NAME, THREAD#, SEQUENCE#, APPLIED, registrar from v$archived_log where SEQUENCE#='1785' and THREAD#='2' order by sequence#; 2 3

NAME THR# SEQ# APP REGISTR
---------------------------------------------------- ------- ------- --- -------
+JP_DG2/JP/archivelog/2010_04_08
/thread_2_seq_1785.2588.715777831 2 1785 YES SRMN

Restoreamos el archive que necesitamos.

RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 1785 THREAD 2;

Starting restore at 08-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=307 instance=JPDG1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=287 instance=JPDG1 devtype=DISK

archive log thread 2 sequence 1786 is already on disk as file
+JPDG_DG2/JP/archivelog/2010_04_06/thread_2_seq_1786.3764.715610751
archive log thread 2 sequence 1787 is already on disk as file
+JPDG_DG2/JP/archivelog/2010_04_06/thread_2_seq_1787.2909.715610787
archive log thread 2 sequence 1788 is already on disk as file
+JPDG_DG2/JP/archivelog/2010_04_06/thread_2_seq_1788.2125.715610813
archive log thread 2 sequence 1789 is already on disk as file
+JPDG_DG2/JP/archivelog/2010_04_06/thread_2_seq_1789.2620.715610849
archive log thread 2 sequence 1790 is already on disk as file
+JPDG_DG2/JP/archivelog/2010_04_06/thread_2_seq_1790.636.715643317
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=1785
channel ORA_DISK_1: reading from backup piece
/u03/rman_database_backup/thread_2_seq1785_525_1.logs
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/rman_database_backup/thread_2_seq1785_525_1.logs tag=TAG20100408T105951
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 08-APR-10

RMAN>Debemos recordar que al buscar la pieza dentro del ASM el rman la restorea al dia de la fecha y si no ponemos la sentencia de destino, la incorporara al path del dia de hoy.

Lo vemos con mas detalle ingresando al ASM y verificando que el log exista en el filesystem.

ASMCMD> cd 2010_04_08/
ASMCMD> ls
thread_1_seq_1864.791.715755691
thread_1_seq_1865.3747.715772623
thread_1_seq_1866.3007.715774079
thread_1_seq_1867.3722.715774109
thread_1_seq_1868.2712.715774259
thread_1_seq_1869.3680.715774289
thread_1_seq_1870.645.715774325
thread_1_seq_1871.3511.715774353
thread_1_seq_1872.974.715774407
thread_1_seq_1873.3343.715774433
thread_1_seq_1874.2724.715774565
thread_1_seq_1875.628.715774643
thread_2_seq_1785.2588.715777831
thread_2_seq_1813.2260.715772625Al encontrarse el archive restoreado donde corresponde, procedemos a detener el recovery y a registrar la pieza.

ALTER DATABASE REGISTER LOGFILE 'PATH/FILE.logs';Es importante saber que si tenemos el configurado dataguard broker, este trabajo lo hace de manera automatica, y que al intentar registrar el REDO faltante no arrojara el error ORA-16089:

SQL> ALTER DATABASE REGISTER LOGFILE '+JPDG_DG2/JP/ARCHIVELOG/2010_04_08/thread_2_seq_1785.2588.715777831'; 2ALTER DATABASE REGISTER LOGFILE '+JPDG_DG2/JP/ARCHIVELOG/2010_04_08/thread_2_seq_1785.2588.715777831'
*
ERROR at line 1:
ORA-16089: archive log has already been registeredUna vez que comienza el aplicado de logs podemos verlo el alert.log de la base.

Tiramos la consulta para ver si comenzo el aplicación, esto decrementaria la cantidad de logs encolados.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE


También verificamos que el GAP desaparecio.


SQL> select * from v$archive_gap;no rows selected

Review v$ views for errors

–Log Transport from Primary

v$archive_dest_status–status of each destination
v$archived_log –review which archived logs have not been shipped to the standby destination

–Log Application on Standby

v$managed_standby –status of log transport and log apply
v$archive_dest_status–archived_seq# vsapplied_seq#
v$archived_log –list of arch logs received from primary
v$dataguard_status–Data Gurardrelated alert messages
dba_logstdby_progress–progress of SQL apply
v$archive_gap –any gaps in archive logs


RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT UNTIL TIME 'SYSDATE - 2';


SQL> select * from v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
-------- -------------- --------------
1 24 28

From the output above, the physical standby database is currently missing logs from sequence 24 to sequence 28 for thread 1. Note that this view only returns the next gap that is currently blocking managed recovery from continuing.

After resolving the identified gap and starting managed recovery, the DBA should query the V$ARCHIVE_GAP view again on the physical standby database to determine the next (if any) gap sequence. This process should be repeated until there are no more gaps.

After identifying a gap (as shown above), the DBA will need to query the primary database to locate the archived redo logs on the primary database. The following query assumes the local archive destination on the primary database is LOG_ARCHIVE_DEST_1:


SELECT name
FROM v$archived_log
WHERE thread# = 1
AND dest_id = 1
AND sequence# BETWEEN 24 and 28;

NAME
--------------------------------------
/u02/oraarchive/TESTDB/arch_t1_s24.dbf
/u02/oraarchive/TESTDB/arch_t1_s25.dbf
/u02/oraarchive/TESTDB/arch_t1_s26.dbf
/u02/oraarchive/TESTDB/arch_t1_s27.dbf
/u02/oraarchive/TESTDB/arch_t1_s28.dbf

Next, we can copy the above redo log files to the physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE ... SQL statement on the physical standby database. For example:

SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s24.dbf';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s25.dbf';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s26.dbf';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s27.dbf';
SQL> ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s28.dbf';

After the redo logs have been registered on the physical standby database, the DBA can restart the managed recovery operations. For example, to put the physical standby database into automatic recovery managed mode:

SQL> alter database recover managed standby database disconnect from session;


¿Archive log perdido? No reconstruya su Standby


1.Detener la sincronización de la base de datos standby
STDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2.Obtener el SCN al cual se ha llegado en la base de datos standby
STDB> SELECT CURRENT_SCN FROM V$DATABASE;
3.Obtener un backup incremental de la base de datos primaria con RMAN, a partir del SCN obtenido en el paso previo
RMAN> BACKUP INCREMENTAL FROM SCN
DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
4.Catalogar el backup del paso previo en la base de datos standby
RMAN> CATALOG START WITH '/tmp/ForStandby';
5.Recuperar la base de datos standby con el backup ya catalogado
RMAN> RECOVER DATABASE NOREDO;
6.En la base de datos primaria crear un nuevo standby controlfile
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
7.Detener la base de datos standby y levantarla con nomount
RMAN> SHUTDOWN;
RMAN> STARTUP NOMOUNT;
8.Restaurar el standby controlfile obtenido en el paso (6) en la base de datos standby
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
9.Detener la base de datos standby levantarla con mount
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
10.Limpiar los standby redo logs en la base de datos standby
STDB> ALTER DATABASE CLEAR LOGFILE GROUP 1;
STDB> ALTER DATABASE CLEAR LOGFILE GROUP 2;
STDB> ALTER DATABASE CLEAR LOGFILE GROUP 3;
11.Si estaba activo Flashback Database, reiniciarlo
STDB> ALTER DATABASE FLASHBACK OFF;
STDB> ALTER DATABASE FLASHBACK ON;
12.Reiniciar el recovery de la base de datos standby
STDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;


RECUPERAR ARCHIVELOGS PARA APLICAR LUEGO EN STANDBY

Restore Archive Logs to New Location using RMAN
Oracle up to 10g

When attempting to restore archive logs that are already located on disk using RMAN you’ll receive an error if the archivelogs are stored within ASM because until Oracle 11g shipped you could not use the OS copy or move command to retrieve them.

The solution is to use RMAN to copy them from ASM to an OS filesystem:

RMAN> copy archivelog ‘+psdisk1/ORCLSID/archivelog/2008_10_30/thread_1_seq_2.245.88866622′ to ‘/tmp/oraclearchive’;

You would have to repeat this command for each archive log to be copied or try the following solution:

RMAN> change archivelog from logseq=60 until logseq=70 uncatalog;

Once the archivelos have been successfully uncataloged you are now able to successfully restore the archive logs to a new location.

RMAN> run {
set archivelog destination to ‘/tmp’;
restore archivelog from logseq=60 until logseq=70;
}

Following the successful restore of the archivelogs you can then continue to recatalog the archivelogs back into the ASM diskgroup.

Example:

RMAN> catalog archivelog ‘+psdisk1/ORCLSID/archivelog/2008_10_30/thread_1_seq.60.245.888666222′;


COPIAR ARCHIVELOG ASM A DISCO

RMAN> copy archivelog '+FRA/CCPROD/ARCHIVELOG/2011_01_05/thread_1_seq_127538.455.739629505' to '/orahome10/archive';



-- Back up all archived logs from sequence # 250 to sequence # 301 and deletes the archived redo logs after the backup is complete.

run {
allocate channel ch1 type disk format '/tmp/arc_%U';
backup
archivelog from logseq 250 until logseq 301 thread 1
}

In FORMAT clause, %U is replaced with unique file names when creating archivelog backups.

The above RMAN statements create a file in the following format in the destination directory (/etc).

-rw-r----- 1 oracle dba2 46623232 Sep 28 13:14 arc_03kqcj6g_1_1

Then, one can restore archive logs that are already located on a filesystem using restore command:

run {
set archivelog destination to '/tmp';
restore archivelog all;
}





martes, 7 de febrero de 2012

martes, 29 de noviembre de 2011

TKPROF And Oracle Trace

TKPROF And Oracle Trace

The TKPROF program converts Oracle trace files into a more readable form. If you have a problem query you can user TKPROF to get more information. To get the most out of the utility you must enable timed statistics by setting the init.ora parameter or performing the following command:

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;If a suitable plan table is not present one can be created by doing the fooling as the SYS user:

@ORACLE_HOME\rdbms\admin\utlxplan.sql
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.PLAN_TABLE TO PUBLIC;With this done we can trace a statement:

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT COUNT(*)
FROM dual;

ALTER SESSION SET SQL_TRACE = FALSE;The resulting trace file will be located in the USER_DUMP_DEST directory. This can then be interpreted using TKPROF at the commmand prompt as follows:

TKPROF explain=user/password@service table=sys.plan_tableThe resulting output file contains the following type of information for all SQL statements processed, including the ALTER SESSION commands:

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

SELECT COUNT(*)
FROM dual

call count cpu elapsed disk query current rows
------- ----- ----- ------- ------- ------- ------- -------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 1 4 1
------- ----- ----- ------- ------- ------- ------- -------
total 4 0.02 0.02 0 1 4 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 121

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 TABLE ACCESS FULL DUAL

Statspack

1. Objetivo
El objetivo de este documento es explicar cómo se instala statspack y cómo se puede configurar statspack para obtener instantáneas de la base de datos en un momento determinado.



2. Instalación statspack
Para realizar la instalación de statspack hay que tener previamente creado un tablespace disponible para la instalación.

- En base de datos ORACLE8 y ORACLE9 podemos elegir un tablespace que tenga espacio suficiente para la instalación aunque lo más recomendable es crear uno nuevo, por ejemplo un tablespace llamado PERFSTAT.

- En bases de datos ORACLE 10g la instalación se puede realizar en el tablespace SYSAUX.

Nos conectamos a la base de datos en la que queremos instalar STATSPACK y ejecutamos con el usuario sys el script $ORACLE_HOME/rdbms/admin./spcreate.sql

Ejemplo: Instalación de statspack en una base de datos oracle9. Nos conectamos con el usuario oracle9, fijamos la variable ORACLE_SID y lanzamos el script spcreate.sql

$ su – oracle9$ export ORACLE_SID=orasite$ sqlplus /nologSQL > connect /as sysdbaSQL > @$ORACLE_HOME/rdbms/admin/spcreate.sql
Durante la instalación se van a solicitar los siguientes datos:
- Contraseña para el usuario perfstat: *******

- Tablespace a instalar: Ponemos SYSAUX o PERFSTAT dependiendo de la versión en la que estemos.(tablespace creado en oracle8 y oracle9 o tablespace de oracle10).

3. Desinstalación statspack
En caso de que queramos desinstalar STATSPACK de la misma forma que lo hemos instalado en vez de ejecutar el fichero spcreate ejecutamos el fichero spdrop.sql

SQL> @$ORACLE_HOME/rdbms/admin./spdrop.sql4. Obtener una foto de la base de datos con statspack
Para ejecutar statspack y obtener una instantánea de la base de datos en ese momento lo realizamos de la siguiente forma:

Nos conectamos a la base de datos con el usuario perfstat y ejectuamos statspack.snap

sql > connect perfstat/*****sql > execute statspack.snap(I_SNAP_LEVEL=>10);Si vamos a obtener instantáneas a menudo es conveniente actualizar las estadísticas de perfstat.

sql > connect perfstat/*****sql > execute dbms_stats.gather_schema_stats(ownname=>'perfstat');5. Obtener un informe statspack
Para obtener un Informe statspack los pasos que hay que seguir son los siguientes (Hay que tener en cuenta que para sacar un informe hay que tener más de una foto y que entre éstas la instancia no se haya caído o parado):

Con el usuario propietario de oracle nos conectamos a la base de datos como perfstat.

Ejecutamos el script $ORACLE_HOME/rdbms/admin/spreport.sql . Al ejecutar este script obtenemos todas las fotos que se han realizado y vamos contestando a las peticiones que este te hace:

Foto inicial
Foto final
Lugar para generar el informe

Ejemplo para obtener un informe.

$ su – oracle9$ export ORACLE_SID=orasite$ sqlplus /nologSQL> connect perfstat/******SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql

miércoles, 23 de noviembre de 2011

VIEW WITH CHECK OPTION

SQL*Plus: Release 10.2.0.1.0 - Production on Mié Abr 11 12:08:57 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Introduzca el nombre de usuario: carlos@bd01.xxxxxxxx
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

ID_N C_TXT
---------- -------------------------
30 TREINTA
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
21 VEINTIUNO
22 VEINTIDOS
23 VEINTITRES
24 VEINTICUATRO
25 VEINTICINCO

11 filas seleccionadas.Un método sencillo es crear una vista que filtre los datos según el criterio requerido:

carlos@bd01.xxxxxxxx> CREATE VIEW PRUEBA01_VW AS
2> SELECT ID_N, C_TXT FROM PRUEBA01
3> WHERE ID_N <= 10;

Vista creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCOHasta aquí todo fácil. Podemos insertar filas en esa vista sin ningún problema y los datos se almacenarán en la tabla subyacente:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (6, 'SEIS');

1 fila creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
6 SEIS

6 filas seleccionadas.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

ID_N C_TXT
---------- -------------------------
30 TREINTA
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
21 VEINTIUNO
22 VEINTIDOS
23 VEINTITRES
24 VEINTICUATRO
25 VEINTICINCO
6 SEIS

12 filas seleccionadas.Pero aquí pueden empezar los problemas, porque a través de la vista se podrían hacer modificaciones a la tabla de una manera descontrolada:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (11,'ONCE');

1 fila creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
6 SEIS

6 filas seleccionadas.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

ID_N C_TXT
---------- -------------------------
30 TREINTA
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
21 VEINTIUNO
22 VEINTIDOS
23 VEINTITRES
24 VEINTICUATRO
25 VEINTICINCO
6 SEIS
11 ONCE

13 filas seleccionadas.Aquí hemos visto que se puede hacer un ‘INSERT’ ‘a ciegas’: Hemos insertado en la vista una fila que no podemos ver, pero que sí fue insertada en la tabla base. Esto da pie a que puedan hacerse operaciones que pasen inadvertidas.

Incluso más: se pueden dar errores incomprensibles:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (21,'VEINTIUNO');
INSERT INTO PRUEBA01_VW VALUES (21,'VEINTIUNO')
*
ERROR en línea 1:
ORA-00001: unique constraint (CARLOS.PRUEBA01_PK) violatedEn este caso y en el anterior (la inserción ‘a ciegas’ y el error) puede ocurrir que el nombre de la vista no nos dé pistas de que no se trata de una tabla ‘ordinaria’, haciendo el comportamiento más extraño a ojos de un observador.

Estos problemas se solicionarán fácilmente si utilizamos la opción ‘WITH CHECK OPTION’ en la creación de la vista. Esta opción hace que las operaciones DML sobre dicha vista pasen un control de integridad que corresponda a los criterios de definición de la propia vista:

carlos@bd01.xxxxxxxx> CREATE OR REPLACE VIEW PRUEBA01_VW AS
2> SELECT ID_N, C_TXT FROM PRUEBA01
3> WHERE ID_N <= 10
4> WITH CHECK OPTION;

Vista creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
6 SEIS

6 filas seleccionadas.Si ahora intentamos hacer una inserción (o modificación) que salga de los criterios de definición de la vista tendremos que:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (12,'DOCE');
INSERT INTO PRUEBA01_VW VALUES (12,'DOCE')
*
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

carlos@bd01.xxxxxxxx>Por supuesto, esto también es válido para ‘UPDATE’:

carlos@bd01.xxxxxxxx> UPDATE PRUEBA01_VW SET ID_N=50 WHERE ID_N=1;
UPDATE PRUEBA01_VW SET ID_N=50 WHERE ID_N=1
*
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

carlos@bd01.xxxxxxxx>En resumen, si se utilizan vistas actualizables como mecanismo de ‘encapsulamiento de visibilidad de datos’ se debería siempre considerar la opción ‘WITH CHECK OPTION’ para evitar resultados no deseados de inconsistencia y reforzar además las referidas políticas de visibilidad (y más importante: de actualización) de los datos

JOINS

Example of a Left Outer Join 1

SQL> -- create demo table
SQL> create table Employee(
2 EMPNO NUMBER(3),
3 ENAME VARCHAR2(15 BYTE),
4 HIREDATE DATE,
5 ORIG_SALARY NUMBER(6),
6 CURR_SALARY NUMBER(6),
7 REGION VARCHAR2(1 BYTE)
8 )
9 /

Table created.

SQL>
SQL> create table job (
2 EMPNO NUMBER(3),
3 jobtitle VARCHAR2(20 BYTE)
4 )
5 /

Table created.

SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');

1 row created.

SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, 'Jason', to_date('19960725','YYYYMMDD'), 1234, 8767, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, 'John', to_date('19970715','YYYYMMDD'), 2341, 3456, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, 'Joe', to_date('19860125','YYYYMMDD'), 4321, 5654, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, 'Tom', to_date('20060913','YYYYMMDD'), 2413, 6787, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (5, 'Jane', to_date('20050417','YYYYMMDD'), 7654, 4345, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (6, 'James', to_date('20040718','YYYYMMDD'), 5679, 6546, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (7, 'Jodd', to_date('20030720','YYYYMMDD'), 5438, 7658, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (8, 'Joke', to_date('20020101','YYYYMMDD'), 8765, 4543, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (9, 'Jack', to_date('20010829','YYYYMMDD'), 7896, 1232, 'E')
3 /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /

EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- --------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
5 Jane 17-APR-05 7654 4345 E
6 James 18-JUL-04 5679 6546 W
7 Jodd 20-JUL-03 5438 7658 E
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E

9 rows selected.

SQL> select * from job
2 /

EMPNO JOBTITLE
---------- --------------------
1 Tester
2 Accountant
3 Developer
9 Developer

SQL>
SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno = j.empno (+);

ENAME JOBTITLE
--------------- --------------------
Jason Tester
John Accountant
Joe Developer
Jack Developer
Jane
Joke
James
Jodd
Tom

9 rows selected.

SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /

Table dropped.

SQL> drop table job
2 /

Table dropped.



Example of a Right Outer Join 1

SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 EMPNO NUMBER(3),
3 ENAME VARCHAR2(15 BYTE),
4 HIREDATE DATE,
5 ORIG_SALARY NUMBER(6),
6 CURR_SALARY NUMBER(6),
7 REGION VARCHAR2(1 BYTE)
8 )
9 /

Table created.

SQL>
SQL> create table job (
2 EMPNO NUMBER(3),
3 jobtitle VARCHAR2(20 BYTE)
4 )
5 /

Table created.

SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (4,'COder');

1 row created.

SQL>
SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');

1 row created.

SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, 'Jason', to_date('19960725','YYYYMMDD'), 1234, 8767, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, 'John', to_date('19970715','YYYYMMDD'), 2341, 3456, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, 'Joe', to_date('19860125','YYYYMMDD'), 4321, 5654, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, 'Tom', to_date('20060913','YYYYMMDD'), 2413, 6787, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (5, 'Jane', to_date('20050417','YYYYMMDD'), 7654, 4345, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (6, 'James', to_date('20040718','YYYYMMDD'), 5679, 6546, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (7, 'Jodd', to_date('20030720','YYYYMMDD'), 5438, 7658, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (8, 'Joke', to_date('20020101','YYYYMMDD'), 8765, 4543, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (9, 'Jack', to_date('20010829','YYYYMMDD'), 7896, 1232, 'E')
3 /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /

EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- --------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
5 Jane 17-APR-05 7654 4345 E
6 James 18-JUL-04 5679 6546 W
7 Jodd 20-JUL-03 5438 7658 E
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E

9 rows selected.

SQL> select * from job
2 /

EMPNO JOBTITLE
---------- --------------------
1 Tester
2 Accountant
3 Developer
4 COder
9 Developer

SQL>
SQL>
SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno (+) = j.empno ;

ENAME JOBTITLE
--------------- --------------------
Jason Tester
John Accountant
Joe Developer
Tom COder
Jack Developer

SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /

Table dropped.

SQL> drop table job
2 /

Table dropped.