ORACLE LINUX REPOSITORIO

3.6 Installing Oracle Linux with Oracle Linux Yum Server Support Use the following procedure to install Oracle Linux and configure your Linux installation for security errata or bug fix updates using the Oracle Linux yum server: Obtain Oracle Linux DVDs from Oracle Store, or download Oracle Linux from the Oracle Software Delivery Cloud: Oracle Store: https://shop.oracle.com Oracle Software Delivery Cloud website: https://edelivery.oracle.com/linux Install Oracle Linux from the ISO or DVD image. Log in as root Download the yum repository file for your Linux distribution from http://yum.oracle.com/, using the instructions you can find on the Oracle Linux yum server website. For example: # cd /etc/yum.repos.d/ # wget http://yum.oracle.com/public-yum-ol6.repo Ensure that the olrelease_latest file (for example, ol6_latest for Oracle Linux 6) is enabled, as this is the repository that contains the Oracle Preinstallation RPM. (Optional) Edit the repo file to enable other repositories. For example, enable the repository ol6_UEK_latest by setting enabled=1 in the file with a text editor. Run the command yum repolist to verify the registered channels. Start a terminal session and enter the following command as root, depending on your platform. For example: Oracle Linux 6 and Oracle Linux 7: # yum install oracle-rdbms-server-12cR1-preinstall # yum install -y oracleasm # yum install -y oracleasm-support # yum install oracleasmlib /usr/sbin/oracleasm configure -i /usr/sbin/oracleasm init # yum install oracle-validated You should see output indicating that you have subscribed to the Oracle Linux channel, and that packages are being installed. For example: el5_u6_i386_base el5_u6_x86_64_patch Oracle Linux automatically creates a standard (not role-allocated) Oracle installation owner and groups, and sets up other kernel configuration settings as required for Oracle installations. After installation, run the command yum update as needed to obtain the most current security errata and bug fixes for your Oracle Linux installation.

VISTA ARCHIVOS DE TRACE V$DIAG_INFO

SQL> select name,value from v$diag_info; Diag Enabled TRUE ADR Base /u01/app/oracle ADR Home /u01/app/oracle/diag/rdbms/acme/acme Diag Trace /u01/app/oracle/diag/rdbms/acme/acme/trace Diag Alert /u01/app/oracle/diag/rdbms/acme/acme/alert Diag Incident /u01/app/oracle/diag/rdbms/acme/acme/incident Diag Cdump /u01/app/oracle/diag/rdbms/acme/acme/cdump Health Monitor /u01/app/oracle/diag/rdbms/acme/acme/hm Default Trace File /u01/app/oracle/diag/rdbms/acme/acme/trace/acme_ora_10592.trc Active Problem Count 0 Active Incident Count 0

VISTA PROCESOS V$BGPROCESS

VISTA PROCESOS V$BGPROCESS POR SISTEMA OPERATIVO CONSULTANDO LOS PROCESOS BACKGROUND [oracle@localhost ~]$ ps -fea | grep pmon oracle 3478 1 0 Oct19 ? 00:03:56 asm_pmon_+ASM oracle 3658 1 0 Oct19 ? 00:04:31 ora_pmon_acme oracle 8828 1 0 06:35 ? 00:00:00 ora_pmon_orcl oracle 9427 8795 1 07:01 pts/2 00:00:00 grep pmon [oracle@localhost ~]$ [oracle@localhost ~]$ [oracle@localhost ~]$ ps -fea | grep smon oracle 3502 1 0 Oct19 ? 00:00:40 asm_smon_+ASM oracle 3684 1 0 Oct19 ? 00:01:14 ora_smon_acme oracle 8854 1 0 06:35 ? 00:00:00 ora_smon_orcl oracle 9429 8795 0 07:01 pts/2 00:00:00 grep smon [oracle@localhost ~]$ [oracle@localhost ~]$ ps -fea | grep lgwr oracle 3498 1 0 Oct19 ? 00:00:51 asm_lgwr_+ASM oracle 3680 1 0 Oct19 ? 00:03:18 ora_lgwr_acme oracle 8850 1 0 06:35 ? 00:00:01 ora_lgwr_orcl oracle 9435 8795 0 07:01 pts/2 00:00:00 grep lgwr [oracle@localhost ~]$ [oracle@localhost ~]$ ps -fea | grep dbw0 oracle 3496 1 0 Oct19 ? 00:00:49 asm_dbw0_+ASM oracle 3678 1 0 Oct19 ? 00:02:35 ora_dbw0_acme oracle 8848 1 0 06:35 ? 00:00:00 ora_dbw0_orcl oracle 9444 8795 0 07:02 pts/2 00:00:00 grep dbw0 [oracle@localhost ~]$ ps -fea | grep reco root 3132 2931 0 Oct19 ? 00:00:00 /usr/sbin/restorecond -u oracle 3686 1 0 Oct19 ? 00:00:22 ora_reco_acme oracle 8856 1 0 06:35 ? 00:00:00 ora_reco_orcl oracle 9603 8795 0 07:10 pts/2 00:00:00 grep reco [oracle@localhost ~]$ ps -fea | grep lreg oracle 3504 1 0 Oct19 ? 00:00:53 asm_lreg_+ASM oracle 3689 1 0 Oct19 ? 00:00:55 ora_lreg_acme oracle 8858 1 0 06:35 ? 00:00:00 ora_lreg_orcl oracle 9637 8795 0 07:13 pts/2 00:00:00 grep lreg [oracle@localhost ~]$ [oracle@localhost ~]$ ps -fea | grep arc oracle 3734 1 0 Oct19 ? 00:00:32 ora_arc0_acme oracle 3736 1 0 Oct19 ? 00:00:32 ora_arc1_acme oracle 3738 1 0 Oct19 ? 00:01:20 ora_arc2_acme oracle 3740 1 0 Oct19 ? 00:00:32 ora_arc3_acme oracle 8880 1 0 06:36 ? 00:00:00 ora_arc0_orcl oracle 8882 1 0 06:36 ? 00:00:00 ora_arc1_orcl oracle 8884 1 0 06:36 ? 00:00:00 ora_arc2_orcl oracle 8888 1 0 06:36 ? 00:00:01 ora_arc3_orcl oracle 9775 8557 0 07:20 pts/1 00:00:00 grep arc [oracle@localhost ~]$

VISTAS ARCHIVOS ORACLE DBA_DATA_FILES - DBA_TEMP_FILES

SQL> desc v$controlfile; Name Null? Type ----------------------------------------- -------- ---------------------------- STATUS VARCHAR2(7) NAME VARCHAR2(513) IS_RECOVERY_DEST_FILE VARCHAR2(3) BLOCK_SIZE NUMBER FILE_SIZE_BLKS NUMBER CON_ID NUMBER SQL> desc v$log; Name Null? Type ----------------------------------------- -------- ---------------------------- GROUP# NUMBER THREAD# NUMBER SEQUENCE# NUMBER BYTES NUMBER BLOCKSIZE NUMBER MEMBERS NUMBER ARCHIVED VARCHAR2(3) STATUS VARCHAR2(16) FIRST_CHANGE# NUMBER FIRST_TIME DATE NEXT_CHANGE# NUMBER NEXT_TIME DATE CON_ID NUMBER SQL> desc v$logfile; Name Null? Type ----------------------------------------- -------- ---------------------------- GROUP# NUMBER STATUS VARCHAR2(7) TYPE VARCHAR2(7) MEMBER VARCHAR2(513) IS_RECOVERY_DEST_FILE VARCHAR2(3) CON_ID NUMBER SQL> SQL> desc dba_data_files; Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7) SQL> desc dba_temp_files; Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME NOT NULL VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(7) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER

SERVETOUTPUT ON - DBMS_OUTPUT

 SET SERVEROUTPUT ON

 
BEGIN
 Dbms_Output.Put_Line(Systimestamp);
END;
/

DB_FLASHBACK_RETENTION_TARGET

 Configuration Best Practices

1. Set DB_FLASHBACK_RETENTION_TARGET correctly.  Set DB_FLASHBACK_RETENTION_TARGET initialization parameter to the largest value prescribed by any of the following conditions that apply:
• To leverage flashback database to reinstate your failed primary database after Data Guard failover, for most cases set DB_FLASHBACK_RETENTION_TARGET to a minimum of 60 (minutes) to enable reinstatement of a failed primary. 
• Consider cases where there are multiple outages (e.g. first a network outage, followed later by a primary database outage) that may result in a transport lag between primary and standby database at failover time.  For such cases set DB_FLASHBACK_RETENTION_TARGET to a value equal to the sum of 60 (mins) plus the maximum transport lag that you wish to accommodate.  This will insure that the failed primary database can be flashed back to an SCN that precedes the SCN at which the standby became primary - a requirement for primary reinstatement.
• If using Flashback Database for fast point in time recovery from user error or logical corruptions, set DB_FLASHBACK_RETENTION_TARGET to a value equal to the farthest time in the past that you wish to be able to recover to.
• Set Primary and Standby  DB_FLASHBACK_RETENTION_TARGET to be the same.
2. Size Fast Recovery Area (FRA).  Ensure the fast recovery area has allocated sufficient space to accommodate flashback database flashback logs for the target retention size and for peak batch rates.  Sizing the fast recovery area is described in detail in the 10g Database Backup and Recovery Basics guide  and the 11g Database Backup and Recovery User's Guide /  12c Database Backup and Recovery User's Guide  but the general rule of thumb is the volume of flashback log generation is approximately the same order of magnitude as redo log generation.  Use the following conservative formula and approach

Target FRA = Current FRA + DB_FLASHBACK_RETENTION_TARGET x 60 x Peak Redo Rate (MB/sec)

      Example:

• Current FRA or DB_RECOVERY_FILE_DEST_SIZE=1000G
• Target DB_FLASHBACK_RETENTION_TARGET=360 (360 minutes)
• From AWR:  1) Peak redo rate for OLTP workload is 3 MB/sec for database.  2)  Peak redo rate for batch workload is 30 MB/sec for database and longest duration is 4 hours.   3) worst-case redo generation size for 6 hour window is       ( 240 minutes x 30 MB/sec x 60 secs/min) +  (120 minutes x 3 MB/sec x 60 secs/min ) = 453,600 MB or approx 443 GB
• Proposed FRA or DB_RECOVERY_FILE_DEST_SIZE= 443 GB +1000 GB = 1443 GB.

An additional method to determine fast recovery area sizing is to enable flashback database and allow the database applications to run for a short period (2-3 hours) and query V$FLASHBACK_DATABASE_STAT.ESTIMATED_FLASHBACK_SIZE.

Note that the DB_FLASHBACK_RETENTION_TARGET is a target and there is no guarantee that you can flashback the database that far.  In some cases if there is space pressure in the flash recovery area where the flashback logs are stored then the oldest flashback logs may be deleted.  For a detailed explanation of the flash recovery area deletion rules see the Database Backup and Recovery User's Guide, Maintaining the Fast Recovery Area section.  To guarantee a flashback point-in-time you must use guaranteed restore points (GRP).  With GRP, the required flashback logs will never be recycled or purged until GRP is dropped.     You can hang the database if you have a GRP and there’s insufficient space; so you need allocate more space in the FRA depending on the intended duration of the GRP.

3. Configure sufficient I/O bandwidth for Fast Recovery Area.  Insufficient I/O bandwidth with flashback database on is usually indicated by a high occurrence of the "FLASHBACK BUF FREE BY RVWR" wait event in an Automatic Workload Repository (AWR) report for OLTP workloads and “FLASHBACK LOG FILE WRITE” latency > 30 ms for large insert operations.   In general, flashback IOs are 1 MB in size and the overall write throughput will be similar to the redo generation rate if database force logging was enabled or similar to your load rate for direct load operations.   For simplicity, configure one large shared storage GRID and configure DATA on the outer portion of the disks or LUNS and RECO (fast recovery area) on the inner portion of the disks or LUNS.
4. Recommended LOG_BUFFER settings to give flashback database more buffer space in memory.
   
   Recommend to set the LOG_BUFFER to maximum value for the specific database release and platform.   
   The previous 8 MB recommendation does not work well in high throughput applications with flashback database enabled.   
   
   We now recommend  64 MB for 32-bit systems
                and up to  256 MB for 64-bit systems
   
   
5. Set _DB_FLASHBACK_LOG_MIN_SIZE = <redo log size> for any 11.2.0.2 release.   In previous releases, the initially flashback log allocations are hindered because the initial file sizes are too small which may impact primary load performance.  As of 11.2.0.3, the default min size is the redo log group size which is the prescribed best practice.    This enhancement is not available in Oracle 10g so customers may experience some additional performance overhead until DB_FLASHBACK_RETENTION_TARGET is met 

     Example:

     SQL> alter system set "_db_flashback_log_min_size"=4g;

6. Set _DB_FLASHBACK_LOG_MIN_TOTAL_SPACE =< projected flashback size> temporarily if you want to pre-allocate flashback logs in the FRA but you must add more space to FRA beforehand.  This is normally unnecessary since Oracle will allocate flashback logs as you generate changes.   We do recommend enabling flashback database at a non-peak period especially avoiding periods immediately prior to or during your direct load operations.   You can then monitor by querying V$FLASHBACK_DATABASE_LOG.FLASHBACK_SIZE.   Setting this undocumented parameter may be useful if you want to create a guaranteed restore point or enable flashback database prior to a big load and you want to quickly pre-allocate the necessary flashback logs.    

        Example:

            SQL> alter system set "_db_flashback_log_min_size"=4g;

            SQL> alter system set "_db_flashback_log_min_total_space"=50g;

Wait 5 minutes and query “select flashback_size from V$flashback_database_log;”   Repeat until flashback target minimum size is met.  When completed unset _DB_FLASHBACK_LOG_MIN_TOTAL_SPACE.

Operational Best Practices

1. Gather database statistics using Automatic Workload Repository (AWR), Enterprise Manager before and after enabling flashback database so you can measure the impact of enabling flashback database.
2. Set the Enterprise Manager monitoring metric, "Recovery Area Free Space (%)" for proactive alerts of space issues with the fast recovery area.
3. From 11.2 onward, you can enable flashback database while the database is open.  However this operation may fail and signal an error if it fails to get enough contiguous memory.  To guarantee success, you can enable flashback in mount mode.
4. To monitor the progress of a flashback database operation you can query the V$SESSION_LONGOPS view.  An example query to monitor progress is:

     select * from v$session_longops where opname like 'Flashback%';

If more detail is required on the flashback database operation then set _FLASHBACK_VERBOSE_INFO=TRUE database parameter which will generate a detailed trace of the flashback database operation in the DIAGNOSTIC_DEST trace directory for the database

5. When using flashback database to perform repeated tests on a test database, it is recommended to use Guaranteed Restore Points (GRP) only without explicitly turning on flashback database.  To minimize space usage and flashback performance overhead, follow this recommended approach:

Create Guaranteed Restore Point (GRP)
Execute test
loop

     Flashback database to GRP
     Open resetlogs
     Create new GRP
     Drop old GRP
     Execute test

End loop

6. Follow the Data Guard redo apply best practices described in Best Practices for Data Guard and Active Data Guard Redo Apply Performance.
7. Also review the 10g Database Backup and Recovery Basics guide or the 11g Release 2 Backup and Recovery User's Guide.

Performance tuning for specific application use cases

sqlnet.ora BLOQUEAR ACCESO A ORACLE DESDE UNA IP

 

1. Bloquear el acceso a Oracle desde una IP

En caso de no tener un firewall para bloquear el acceso de ciertas ips a una base de datos lo podemos realizar a través del sqlnet.ora.

El “secreto” para bloquear o restringir el acceso por IP a la base de datos se realiza en el archivo sqlnet.ora. Este archivo lo podemos encontrar en el directorio $ORACLE_HOME/network/admin junto con los archivos tnsnames.ora y listener.ora

Editamos el archivo sqlnet.ora y añadimos las siguientes líneas:

tcp.validnode_checking = yes

Con esto conseguimos chequear los listeners que tengamos activos.

A continuación escribimos lo siguiente:

tcp.invited_nodes = (hostnameA, hostnameB)
tcp.excluded_nodes = (192.168.2.15)

Con tcp.invited_nodes puedo especificar qué máquinas quiero que su conexión sea aceptada por las base de datos.
Con tcp.excluded_nodes excluimos las máquinas que no queremos que se conecten a las bases de datos.

La idea de este mecanismo es realizar una lista de las máquinas que queremos que se conecten o realizar una lista de las máquinas que no queremos que se conecten a nuestra base de datos.

A pesar de este mecanismo de seguridad, no podemos decir que estemos totalmente exentos de recibir ataques.

Algunas reglas a tener en cuenta para generar la lista de IPS / hostnames invitados o excluidos pueden ser la siguiente:

• Poner todos los nodos excluidos en una única línea.
• Poner todos los nodos invitados en una única línea.
• Se debería incluir en el listado de nodos invitados localhost.

Después de introducir estas reglas en nuestro sqlnet.ora debemos de reiniciar los listeners de la máquina.

Con nuestro usuario oracle realizamos lo siguiente:

$ lsnrctl stop nb_listener
$ lsnrctl start nb_listener

TRIGGER AFTER LOGIN RESTRINGIR SQL DEVELOPER

CREATE OR REPLACE TRIGGER LOG_T_LOGON
AFTER LOGON ON DATABASE
DECLARE
    osUser VARCHAR2(30);
    machine VARCHAR2(100);
    prog VARCHAR2(100);
    ip_user VARCHAR2(15);
BEGIN
    SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address
    INTO osUser, machine, prog, ip_user
    FROM v$session
    WHERE SID = SYS_CONTEXT('USERENV', 'SID');
    IF (osUser = 'APuente' AND prog = 'SQL Developer')THEN
        RAISE_APPLICATION_ERROR(-20000,'Denied!  You are not allowed to logon from host '||prog|| ' using '|| osUser);
    END IF;
END;
/

VMWARE HERRAMIENTAS

Cross vCenter Workload Migration Utility

Vmware Cross Vcenter Vmotion Utility -> Migracion entre Maquinas virtuales Online VCENTERS 

Vmware Vcenter Converter

Verificar la version de vmware tools

vmware-toolbox-cmd -v

CREATE SEQUENCES CURRVAL NEXTVAL

select * from USER_sequences

select locations_seq.currval from dual;

SQL> select sysdate from dual;

SQL> select hr.locations_seq.currval from dual;

select hr.locations_seq.currval from dual

                                     *

ERROR at line 1:

ORA-08002: sequence LOCATIONS_SEQ.CURRVAL is not yet defined in this session

SE GENERA ESTE ERROR ES PORQUE LA SECUENCIA NO SE HA UTILIZADO

SQL> select hr.locations_seq.nextval from dual;

   NEXTVAL

----------

      3300

SQL> select hr.locations_seq.currval from dual;

   CURRVAL

----------

      3300

SQL> 

ORATAB CONSULTA BASES DE DATOS EXISTENTES

[oracle@localhost etc]$ pwd

/etc

[oracle@localhost etc]$ cat /etc/oratab

#Backup file is  /u01/app/oracle/product/12.1.0/grid/srvm/admin/oratab.bak.localhost line added by Agent

#

# This file is used by ORACLE utilities.  It is created by root.sh

# and updated by either Database Configuration Assistant while creating

# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates

# the entry.  Lines beginning with a pound sign, '#', are comments.

#

# Entries are of the form:

#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:

#

# The first and second fields are the system identifier and home

# directory of the database respectively.  The third field indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

#

# Multiple entries with the same $ORACLE_SID are not allowed.

#

#

orcl:/u01/app/oracle/product/12.1.0/db_1:N

+ASM:/u01/app/oracle/product/12.1.0/grid:N: # line added by Agent

acme:/u01/app/oracle/product/12.1.0/db_1:N: # line added by Agent

[oracle@localhost etc]$