Crear una Standby usando RMAN Duplicate from Active Database

IMPLEMENTANDO PHYSICAL STANDBY USANDO RMAN DUPLICATE FROM ACTIVE DATABASE

A continuación detallaré el paso a paso para la implementación de un Physical Standby Database, usando RMAN DUPLICATE DATABASE FROM ACTIVE DATABASE. Este proceso  clonará a través de la red, un ambiente productivo y en base a este generará un physical standby, sin necesidad de tener un backup RMAN previamente generado.

En el presente ejemplo, el origen se encuentra en filesystem y el destino será un Physical Standby sobreASM.

Información de la BD Primaria(origen):
Host: SERVER-PROD
DB_NAME = IRIS
DB_UNIQUE_NAME=IRIS

Primary Database
OS versión-> redhat 6.7
Servidor -> SERVER-PROD
Instancia -> IRIS
ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
Version 12.1.0.2.0
Storage system: Filesystem

Información de la BD Standby(destino):
Host: SERVER-CONT
DB_NAME = IRIS
DB_UNIQUE_NAME = IRISSBY

Standby Database
OS versión-> redhat 7.2
Servidor-> server-conting
Instancia->IRISSBY
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
Version 12.1.0.2.0
Storage system : ASM (+DG_DATA12C_02)

PRIMARY: Todos los siguientes pasos se ejecutarán en el entorno primario

1)Verificar la BD se encuentre en modo archivelog

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivedata/archivelogs/
Oldest online log sequence 5231
Next log sequence to archive 5234
Current log sequence

2) verificar la BD origen se encuentre en modo force_logging

SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES

En caso de ser requerido activar de la siguiente manera :

SQL> alter database force logging;
Database altered.

3.-Crear standby redo logfiles

alter database add standby logfile '/oradata1/IRIS/onlinelog/slog1.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog2.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog3.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog4.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog5.rdo' size 500M;

SQL> select group#,thread#,bytes from v$standby_log;

GROUP# THREAD# BYTES
———- ———- ———-
5 1 524288000
6 1 524288000
7 1 524288000
8 1 524288000
9 1 524288000

SQL> select group#, thread#, bytes/1024/1024 from v$log;

GROUP# THREAD# BYTES/1024/1024
———-      ———-      —————
1                  1                   500
2                  1                  500
3                  1                  500
4                  1                  500

SQL> select group#,thread#,bytes from v$standby_log;

GROUP# THREAD# BYTES
———- ———- ———-
5 1 524288000
6 1 524288000
7 1 524288000
8 1 524288000
9 1 524288000

Quedando de la siguiente manera:

select GROUP#,member from v$logfile
 GROUP#       MEMBER
-------- ------------------------------------------------
 4        /oradata1/IRIS/onlinelog/o1_mf_4_cxjbnd5q_.log
 4        /oradata2/IRIS/onlinelog/o1_mf_4_cxjbndhw_.log
 3        /oradata1/IRIS/onlinelog/o1_mf_3_cxjbnbom_.log
 3        /oradata3/IRIS/onlinelog/o1_mf_3_cxjbncbd_.log
 2        /oradata2/IRIS/onlinelog/o1_mf_2_cxjbn9g5_.log
 2        /oradata3/IRIS/onlinelog/o1_mf_2_cxjbn9r6_.log
 1        /oradata1/IRIS/onlinelog/o1_mf_1_cxjbn7lk_.log
 1        /oradata2/IRIS/onlinelog/o1_mf_1_cxjbn7wl_.log
 5        /oradata1/IRIS/onlinelog/slog1.rdo
 6        /oradata1/IRIS/onlinelog/slog2.rdo
 7        /oradata1/IRIS/onlinelog/slog3.rdo
 8        /oradata1/IRIS/onlinelog/slog4.rdo
 9        /oradata1/IRIS/onlinelog/slog5.rdo

4.-Verificar parametros necesarios para armar el dataguard:

DB_NAME:

SQL> show parameter db_name
 NAME             TYPE               VALUE
 ------------ ----------------- ---------------
 db_name          string              IRIS

DB_UNIQUE_NAME:

SQL> show parameter db_unique_name
  NAME               TYPE         VALUE
 ---------------- ------------- ------------
  db_unique_name     string        IRIS

5.-Configurar ARCHIVE_LOG_CONFIG para enviar redo logs de origen a destino usando DB_UNIQUE_NAME.

Antes:

SQL> show parameter LOG_ARCHIVE_CONFIG

NAME                    TYPE       VALUE
 ------------------- ----------- ------------
 log_archive_config    string

Cambio:

SQL> alter system set log_archive_config='DG_CONFIG=(IRIS,IRISSBY)';
 System altered.

Después:

SQL> show parameter log_archive_config

NAME                   TYPE               VALUE
 ------------------- ------------   -------------------------
 log_archive_config    string        DG_CONFIG=(IRIS,IRISSBY)

6.-Habilitar LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2

Antes:

SQL> show parameter log_archive_dest_1
NAME                           TYPE         VALUE
--------------------------- ----------- -----------------------------------
log_archive_dest_1            string     LOCATION=/archivedata/archivelogs/
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string

Cambio:

SQL> alter system set log_archive_dest_1='LOCATION=/archivedata/archivelogs/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=IRIS';

System altered.

despues:

SQL> show parameter log_archive_dest_1

NAME                    TYPE                VALUE
-------------------- ----------   ----------------------------------------
log_archive_dest_1     string     LOCATION=/archivedata/archive-
                                  logs/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
                                  DB_UNIQUE_NAME=IRIS
                                                                          
log_archive_dest_10     string
log_archive_dest_11     string
log_archive_dest_12     string
log_archive_dest_13     string
log_archive_dest_14     string
log_archive_dest_15     string
log_archive_dest_16     string
log_archive_dest_17     string
log_archive_dest_18     string
log_archive_dest_19     string

–Seteamos el transporte de redos en el log_archive_dest_2
Antes:

SQL> show parameter log_archive_dest_2
NAME                          TYPE           VALUE
 ----------------------  -------------- ----------------
 log_archive_dest_2           string
 log_archive_dest_20          string
 log_archive_dest_21          string
 log_archive_dest_22          string
 log_archive_dest_23          string
 log_archive_dest_24          string
 log_archive_dest_25          string
 log_archive_dest_26          string
 log_archive_dest_27          string
 log_archive_dest_28          string
 log_archive_dest_29          string

Cambio:

SQL> alter system set log_archive_dest_2='SERVICE=IRISSBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=IRISSBY';
 System altered.

Después:

SQL> show parameter log_archive_dest_2
NAME                   TYPE            VALUE
------------------- --------- ----------------------------------
log_archive_dest_2   string   SERVICE=IRISSBY LGWR ASYNC VALID_FOR                                                  
                              =(ONLINE_LOGFILES,PRIMARY_ROLE) 
                              DB_UNIQUE_NAME=IRISSBY
 log_archive_dest_20 string
 log_archive_dest_21 string

7.-Configurar FAL_SERVER y FAL_CLIENT

FAL_SERVER=Fetch archivelog al destino – Primary
FAL_CLIENT=Cliente que recibe los archivelogs – Standby

FAL_SERVER:

SQL> alter system set fal_server=IRIS;
System altered.

SQL> show parameter fal_server
NAME                TYPE                 VALUE
 -------------- ------------------ ------------------
 fal_server           string             IRIS

FAL_CLIENT:

SQL> alter system set fal_client=IRISSBY;
System altered.

SQL> show parameter fal_client
 NAME                   TYPE             VALUE
 -------------- ----------------  ---------------------
 fal_client           string          IRISSBY

8.-Habilitar replicación de SO.

Para que el standby tambien replique operaciones del SO como adición o borrado de archivos(por ejemplo cuando agregas o borras datafiles en PROD,estas operaciones sean replicadas al standby), el parametro standby_file_management debe ser seteado a AUTO.

alter system set standby_file_management=auto;

SQL> show parameter standby_file_management
NAME                         TYPE             VALUE
 -----------------------  --------------- ------------------
 standby_file_management     string            AUTO

9.-Habilitar el login remoto

alter system set remote_loging_passwordfile=exclusive;

SQL> show parameter password
NAME                            TYPE            VALUE
-------------------------- ---------------- ----------------
remote_login_passwordfile     string            EXCLUSIVE

10.-Configurar conectividad de red:

La Instancia primaria debe estar registrada estaticamente con su propio listener

--Listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = IRIS)
 (ORACLE_HOME = /oracle/app/oracle/product/12.1.0/dbhome_1)
 (SID_NAME = IRIS)
 )
 )

Registrar en el tnsnames.ora las cadenas de conexion de IRIS y IRISSBY en ambos servidores Primario y Standby.

-bash-4.2$ cat tnsnames.ora
IRIS =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.44.155)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRIS)
 )
 )

IRISSBY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = fcrac06)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRISSBY)
 ) (UR=A)
 )

STANDBY: Todos los siguientes pasos se ejecutarán en el entorno standby.

11.-Configuraciones de red en standby

Registrar en el tnsnames.ora las cadenas de conexion de IRIS y IRISSBY en servidor standby.

IRIS =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.44.155)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRIS)
 )
 )

IRISSBY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = fcrac06)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRISSBY)
 ) (UR=A)
 )

–Registrar la BD standby staticamente en el listener del servidor standby

[grid@fcrac06 admin]$ cat listener.ora
LISTENER_EXT=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_EXT)))) # line added by Agent
MGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR)))) # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=IRISSBY)
(SID_NAME=IRISSBY)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
)

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET # line added by Agent
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=() # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_EXT=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_EXT=SUBNET # line added by Agent

validar:

 [oracle@fcrac06 admin]$ lsnrctl status | grep IRIS
 Service "IRISSBY" has 1 instance(s).
 Instance "IRISSBY", status UNKNOWN, has 1 handler(s) for this service...

12.-Crear las rutas a nivel de SO que alojaran los archivos de BD.
/u01/app/oracle/admin/IRISSBY/

[oracle@ocm2 ~]$ mkdir oradata
[oracle@ocm2 ~]$ mkdir oradata/pritst
[oracle@ocm2 ~]$ mkdir oradata/pritst/arch
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/adump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/bdump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/dpdump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/pfile

13.- Registrar en el oratab del servidor standby

 vi /etc/oratab
 IRIS:/u01/app/oracle/product/12.1.0/dbhome_1:N # line added by jeanpierre to standbyduplicate

[oracle@fcrac06 admin]$ cat /etc/oratab | grep IRIS
IRIS:/u01/app/oracle/product/12.1.0/dbhome_1:N # line added by jeanpierre to standbyduplicate
[oracle@fcrac06 admin]$

14.-Crear pfile basico en $ORACLE_HOME/dbs del standby

[oracle@fcrac06 dbs]$ cat initIRISSBY.ora
DB_NAME=IRIS
DB_UNIQUE_NAME=IRISSBY
[oracle@fcrac06 dbs]$

15.-copiar el passwordfile de origen a standby

scp oracle@ocm1:$ORACLE_HOME/dbs/orapwIRIS $ORACLE_HOME/dbs

[oracle@fcrac06 dbs]$ ls -ltr
total 10220
-rw-r–r– 1 oracle oinstall 2992 Jun 26 2017 init.ora
-rw-r—– 1 oracle oinstall 7680 Apr 8 01:47 orapwIRIS —
-rw-r—– 1 oracle oinstall 7680 Apr 8 08:40 orapwIRISSBY
-rw-r—– 1 oracle asmadmin 10403840 Apr 8 10:38 cntrlIRISSBY.dbf
-rw-rw—- 1 oracle asmadmin 1544 Apr 8 10:46 hc_IRISSBY.dat
-rw-r–r– 1 oracle oinstall 1238 Apr 8 10:53 initIRIS.ora.old
-rw-r–r– 1 oracle oinstall 36 Apr 8 13:16 initIRISSBY.ora

16.-Usando el pfile simple subir el standby hasta nomount.

[oracle@fcrac06 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 8 13:32:20 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.

SQL> !pwd
/u01/app/oracle/product/12.1.0/dbhome_1/dbs

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initIRISSBY.ora';
 ORACLE instance started.

Total System Global Area 2466250752 bytes
 Fixed Size 2927240 bytes
 Variable Size 2097153400 bytes
 Database Buffers 318767104 bytes
 Redo Buffers 47403008 bytes

17.-Preparar script para duplicate
[oracle@fcrac06 ~]$ cat dupstby.cmd

 run {
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 allocate auxiliary channel aux1 type disk;
 allocate auxiliary channel aux2 type disk;
 duplicate target database
 for standby
 from active database
 dorecover
 spfile
 set db_unique_name='IRISSBY'
 set fal_client='IRIS'
 set fal_server='IRISSBY'
 set log_archive_dest_1='location=+DG_ARCH12C_02 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=IRISSBY'
 set log_archive_dest_2='service=IRIS ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=IRIS'
 set db_create_file_dest='+DG_DATA'
 set db_create_online_log_dest_1='+DG_ARCH12C_02'
 set db_create_online_log_dest_2='+DG_DATA12C_02'
 set db_create_online_log_dest_3='+DG_DATA12C_02'
 set standby_file_management='AUTO'
 set log_archive_config='dg_config=(IRIS,IRISSBY)'
 set audit_file_dest='/u01/app/oracle/admin/IRISSBY/adump'
 set core_dump_dest='/u01/app/oracle/admin/IRISSBY/cdump'
 set diagnostic_dest='/u01/app/oracle'
 nofilenamecheck;
 }
 exit

18.-En el servidor standby conectarse al primario y auxiliay(standby) a traves de RMAN y ejecutar el duplicate database

[oracle@fcrac06 ~]$ rman target sys/oracle@IRIS

Recovery Manager: Release 12.1.0.2.0 – Production on Sun Apr 8 13:52:27 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: IRIS (DBID=1678477687)

RMAN>

RMAN> connect auxiliary sys/oracle@IRISSBY
connected to auxiliary database: IRIS (not mounted)

RMAN>
run {
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 allocate auxiliary channel aux1 type disk;
 allocate auxiliary channel aux2 type disk;
 duplicate target database
 for standby
 from active database
 dorecover
 spfile
 set db_unique_name='IRISSBY'
 set fal_client='IRIS'
 set fal_server='IRISSBY'
 set log_archive_dest_1='location=+DG_ARCH12C_02 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=IRISSBY'
 set log_archive_dest_2='service=IRIS ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=IRIS'
 set db_create_file_dest='+DG_DATA12C_02'
 set db_create_online_log_dest_1='+DG_ARCH12C_02'
 set db_create_online_log_dest_2='+DG_DATA12C_02'
 set db_create_online_log_dest_3='+DG_DATA12C_02'
 set standby_file_management='AUTO'
 set log_archive_config='dg_config=(IRIS,IRISSBY)'
 set audit_file_dest='/u01/app/oracle/admin/IRISSBY/adump'
 set core_dump_dest='/u01/app/oracle/admin/IRISSBY/cdump'
 set diagnostic_dest='/u01/app/oracle'
 nofilenamecheck;
 }
 exit

19.-Proceso de duplicate inicia:
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 100931731456 bytes

Fixed Size 7656992 bytes
Variable Size 13958646240 bytes
Database Buffers 86704652288 bytes
Redo Buffers 260775936 bytes
allocated channel: aux
channel aux: SID=2401 device type=DISK

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/oradata/IRISSBY/controlfile/control01.ctl’;
}
executing Memory Script

Starting backup at 08-APR-18
channel c1: starting datafile copy
copying standby control file
output file name=/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_IRIS.f tag=TAG20180408T165819
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-APR-18

contents of Memory Script:

20.-Iniciar Active Dataguard

—Activar proceso de Sincronización MRP.

SQL> select status from v$instance;
 STATUS
 ------------
 MOUNTED

SQL> alter database open read only;
Database altered.

SQL> select open_mode from v$database;
OPEN_MODE
 --------------------
 READ ONLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 8 USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.

21.-Validación final.
Finalmente generamos 3 archivelogs en el origen y validamos sean aplicados en standby

alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
 SELECT sequence#, first_time, next_time, applied, thread# FROM v$archived_log ORDER BY 1,2;
 SEQUENCE#   FIRST_TIME           NEXT_TIME           APPLIED  THREAD#
 ---------- -------------------- -------------------- -------  -------
 5262       08/04/2018 17:18:36  08/04/2018 17:20:20  YES        1
 5263       08/04/2018 17:20:20  08/04/2018 17:21:14  YES        1
 5264       08/04/2018 17:21:14  08/04/2018 17:22:56  YES        1
 5265       08/04/2018 17:22:56  08/04/2018 17:23:15  YES        1
 5266       08/04/2018 17:23:15  08/04/2018 17:23:18  IN-MEMORY  1

como se puede apreciar nuestro nuevo physical standby se encuentra terminado y totalmente funcional!!!.

Cantidad de procesadores en LINUX

El método más sencillo para saber el número de procesadores presentes en una máquina Linux es el siguiente:

~$ nproc --all
8

Otra forma de obtener el mismo resultado, el cual también nos permite obtener información adicional sobre nuestro procesador es el comando lscpu:

~$ lscpu | grep 'CPU(s)'
CPU(s):                8
On-line CPU(s) list:   0-7
NUMA node0 CPU(s):     0-7

O también podemos examinar el fichero cpuinfo del sistema de ficheros /proc:

~$ grep processor /proc/cpuinfo | wc -l
8

Podemos encontrar numerosos detalles adicionales sobre nuestros procesadores en /proc/cpuinfo, entre ellos el modelo de CPU y el número de núcleos o cores que tiene:

~$ cat /proc/cpuinfo | grep "model name"
model name : Intel(R) Xeon(R) CPU           E5520  @ 2.27GHz
model name : Intel(R) Xeon(R) CPU           E5520  @ 2.27GHz
~$ cat /proc/cpuinfo | grep "cpu cores"
cpu cores : 4
cpu cores : 4

Como puedes ver, la máquina del ejemplo tiene 2 procesadores con 4 cores cada uno.

Cómo auditar una traza/trace en una sesión de Oracle

Primero deberás ubicar la sesión que quieras auditar:

Ojo: La puedes buscar por hostname de la PC que se está conectando, por usuario a nivel de sistema operativo, etc

1. select sid, serial#, USERNAME from v$session where terminal='PC001';

2. exec dbms_system.set_sql_trace_in_session(SID,SERIAL#,true);
O si quiero auditar mi sesión: ALTER SESSION SET SQL_TRACE = TRUE;

3. Ubicar el destino de los archivos "trace": show parameter user_dump_dest
Recuerda que los traces son a nivel sesión, no por usuario.

Este query te ayudará mucho para ubicar qué archivo trace pertenece a cada sesión y usuario:

column username format a10
column trace_file format a70
select b.username, c.value || '\' || lower(d.value) || '_ora_' ||
       to_char(a.spid, 'fm00000') || '.trc' "TRACE_FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr   = b.paddr
and c.name     = 'user_dump_dest'
and d.name     = 'db_name'
and b.username is not null;
 
USERNAME   TRACE_FILE
---------- --------------------------------------------------------
SYS        C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_03164.trc
Marco      C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_02264.trc
Carlos     C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_03578.trc
 

4. Ahora, supongamos que queremos conocer la actividad del usuario Marco:

cd C:\oracle9i\admin\ORCL92\udump\
tkprof ORCL92_ora_02264.trc interpretación_de_traza.out explain=n table:marco.su_table

Ver un codigo en DBA_SOURCE..

/* ******************************************************************* */
/*  EXTRAER CODIGO FUENTE                                              */
/* ******************************************************************* */ ===================================================================
PROMPT  EXPORTA A UN FICHERO EL CODIGO DE LA BASE DE DATOS
PROMPT  ===================================================================
PROMPT
set pagesize 5000
set linesize 90
set echo off
set verify off
set heading off
spool ./codigo.sql

SELECT TEXT FROM dba_source
  WHERE name=UPPER(‘&objeto’)
    AND owner = UPPER(‘&owner’)
  ORDER BY type,name,line
/

spool off
set verify on
set heading on
set linesize 80
set pagesize 14

Recreacion de la replica de la BD (MySQL)

Descripcion Funcional

• Cambiar clave del usuario de replica: repl

grant replication slave on *.* to repl identified by 'XXXXX'; (cualquiera)

• Poner el master en read only

SET GLOBAL read_only = ON;

FLUSH TABLES WITH READ LOCK;

• Ver en el master la ultima secuencia generada

SHOW MASTER STATUS;

• Exportar las tablas y pasarlas al slave

mysqldump -uroot -pXXXXXX > dump.sql

mysqldump -uroot -p --all-databases > alldata.sql

• Sacar de read only el master

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

• Copiar file al server slave

scp file server_destino:

• Importar las tablas en el slave:

mysql -uroot -p

stop slave;

reset slave;

source alldata.sql;

• Configurar el slave para que replique desde esa secuencia

CHANGE MASTER TO MASTER_HOST='$MASTER_HOST_IP', MASTER_USER='repl', MASTER_PASSWORD='cualquieraXXXX', MASTER_LOG_FILE='$File', MASTER_LOG_POS=$Position;

• Comenzar la replicar

START SLAVE;

• Verificar que el slave continua aplicando

SHOW SLAVE STATUS\G

• Tomar un backup desde el slave

Instalando MySQL Server en Ubuntu

Install MySQL

Install the MySQL server by using the Ubuntu package manager:

sudo apt-get update
sudo apt-get install mysql-server

The installer installs MySQL and all dependencies.

If the secure installation utility does not launch automatically after the installation completes, enter the following command:

sudo mysql_secure_installation utility

This utility prompts you to define the mysql root password and other security-related options, including removing remote access to the root user and setting the root password.

Allow remote access

If you have iptables enabled and want to connect to the MySQL database from another machine, you must open a port in your server’s firewall (the default port is 3306). You don’t need to do this if the application that uses MySQL is running on the same server.

Run the following command to allow remote access to the mysql server:

sudo ufw enable
sudo ufw allow mysql

Start the MySQL service

After the installation is complete, you can start the database service by running the following command. If the service is already started, a message informs you that the service is already running:

sudo systemctl start mysql

Launch at reboot

To ensure that the database server launches after a reboot, run the following command:

sudo systemctl enable mysql

Configure interfaces

MySQL, by default is no longer bound to ( listening on ) any remotely accessible interfaces. Edit the “bind-address” directive in /etc/mysql/mysql.conf.d/mysqld.cnf:

bind-address  = 127.0.0.1 ( The default. )
bind-address  = XXX.XXX.XXX.XXX ( The ip address of your Public Net interface. )
bind-address  = ZZZ.ZZZ.ZZZ.ZZZ ( The ip address of your Service Net interface. )
bind-address  = 0.0.0.0 ( All ip addresses. )

Restart the mysql service.

sudo systemctl restart mysql

Start the mysql shell

There is more than one way to work with a MySQL server, but this article focuses on the most basic and compatible approach, the mysql shell.

1. At the command prompt, run the following command to launch the mysql shell and enter it as the root user:

   /usr/bin/mysql -u root -p
   

2. When you’re prompted for a password, enter the one that you set at installation time, or if you haven’t set one, press Enter to submit no password.
   The following mysql shell prompt should appear:

   mysql>
   

Set the root password

If you logged in by entering a blank password, or if you want to change the root password that you set, you can create or change the password.

1. For versions earlier than MySQL 5.7, enter the following command in the mysql shell, replace password with your new password:

   UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';
   

   For version MySQL 5.7 and later, enter the following command in the mysql shell, replacing password with your new password:

   UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root';
   

2. To make the change take effect, reload the stored user information with the following command:

   FLUSH PRIVILEGES;
   

   Note: We’re using all-caps for SQL commands. If you type those commands in lowercase, they’ll work. By convention, the commands are written in all-caps to make them stand out from field names and other data that’s being manipulated.

If you need to reset the root password later, see Reset a MySQL root password.

View users

MySQL stores the user information in its own database. The name of the database is mysql. Inside that database the user information is in a table, a dataset, named user. If you want to see what users are set up in the MySQL user table, run the following command:

SELECT User, Host, authentication_string FROM mysql.user;

The following list describes the parts of that command:

• SELECT tells MySQL that you are asking for data.
• User, Host, authentication_string tells MySQL what fields you want it to look in. Fields are categories for the data in a table. In this case, you are looking for the username, the host associated with the username, and the encrypted password entry.
• FROM mysql.user “ tells MySQL to get the data from the mysql database and the user table.
• A semicolon (;) ends the command.

Note: All SQL queries end in a semicolon. MySQL does not process a query until you type a semicolon.

User hosts

The following example is the output for the preceding query:

SELECT User, Host, authentication_string FROM mysql.user;
+------------------+-----------+-------------------------------------------+
| User             | Host      | authentication_string                     |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *756FEC25AC0E1823C9838EE1A9A6730A20ACDA21 |
| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| debian-sys-maint | localhost | *27E7CA2445405AB10C656AFD0F86AF76CCC57692 |
+------------------+-----------+-------------------------------------------+

Users are associated with a host, specifically, the host from which they connect. The root user in this example is defined for localhost, for the IP address of localhost, and the hostname of the server. You usually need to set a user for only one host, the one from which you typically connect.

If you’re running your application on the same computer as the MySQL server, the host that it connects to by default is localhost. Any new users that you create must have localhost in their host field.

If your application connects remotely, the host entry that MySQL looks for is the IP address or DNS hostname of the remote computer (the one from which the client is coming).

Anonymous users

In the example output, one entry has a host value but no username or password. That’s an anonymous user. When a client connects with no username specified, it’s trying to connect as an anonymous user.

You usually don’t want any anonymous users, but some MySQL installations include one by default. If you see one, you should either delete the user (refer to the username with empty quotes, like ‘ ‘) or set a password for it.

Create a database

There is a difference between a database server and a database, even though those terms are often used interchangeably. MySQL is a database server, meaning it tracks databases and controls access to them. The database stores the data, and it is the database that applications are trying to access when they interact with MySQL.

Some applications create a database as part of their setup process, but others require you to create a database yourself and tell the application about it.

To create a database, log in to the mysql shell and run the following command, replacing demodb with the name of the database that you want to create:

CREATE DATABASE demodb;

After the database is created, you can verify its creation by running a query to list all databases. The following example shows the query and example output:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demodb             |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

Add a database user

When applications connect to the database using the root user, they usually have more privileges than they need. You can add users that applications can use to connect to the new database. In the following example, a user named demouser is created.

1. To create a new user, run the following command in the mysql shell:

   INSERT INTO mysql.user (User,Host,authentication_string,ssl_cipher,x509_issuer,x509_subject)
   VALUES('demouser','localhost',PASSWORD('demopassword'),'','','');
   

2. When you make changes to the user table in the mysql database, tell MySQL to read the changes by flushing the privileges, as follows:

   FLUSH PRIVILEGES;
   

3. Verify that the user was created by running a SELECT query again:

   SELECT User, Host, authentication_string FROM mysql.user;
   
   +------------------+-----------+-------------------------------------------+
   | User             | Host      | Password                                  |
   +------------------+-----------+-------------------------------------------+
   | root             | localhost | *756FEC25AC0E1823C9838EE1A9A6730A20ACDA21 |
   | mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
   | mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
   | debian-sys-maint | localhost | *27E7CA2445405AB10C656AFD0F86AF76CCC57692 |
   | demouser         | localhost | *0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6 |
   +------------------+-----------+-------------------------------------------+
   

Grant database user permissions

Right after you create a new user, it has no privileges. The user can log in, but can’t be used to make any database changes.

1. Give the user full permissions for your new database by running the following command:

   GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;
   

2. Flush the privileges to make the change official by running the following command:

   FLUSH PRIVILEGES;
   

3. To verify that those privileges are set, run the following command:

   SHOW GRANTS FOR 'demouser'@'localhost';
   2 rows in set (0.00 sec)
   

   MySQL returns the commands needed to reproduce that user’s permissions if you were to rebuild the server. USAGE on \*.\* means the users gets no privileges on anything by default. That command is overridden by the second command, which is the grant you ran for the new database.

   +-----------------------------------------------------------------------------------------------------------------+
   | Grants for demouser@localhost                                                                                   |
   +-----------------------------------------------------------------------------------------------------------------+
   | GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' |
   | GRANT ALL PRIVILEGES ON `demodb`.* TO 'demouser'@'localhost'                                                    |
   +-----------------------------------------------------------------------------------------------------------------+
   2 rows in set (0.00 sec)
   

Espero que les sea Util.

Gracias !!!