SCP
scp hola.txt oracle@10.1.1.200:/u02/backup/.
scp -r Downloads/ oracle@10.1.1.200:/u02/backup/.
sudo yum install "kernel-devel-uname-r == $(uname -r)"
VERSION 7
yum list *release-el7VERSION 8
dnf list *release-el8ASM
https://www.oracle.com/linux/downloads/linux-asmlib-v8-downloads.html
EDITAR EL GRUP
/boot/grub2
ls -ltr grub.cfg
Para instalar VMware Tools en un sistema operativo invitado Linux mediante el compilador:
Examples
Dropping a Tablespace: Example The following statement drops the tbs_01 tablespace and drops all referential integrity constraints that refer to primary and unique keys inside tbs_01:
DROP TABLESPACE tbs_01
INCLUDING CONTENTS CASCADE CONSTRAINTS;
Deleting Operating System Files: Example
The following example drops the tbs_02 tablespace and deletes all associated operating system data files:
DROP TABLESPACE tbs_02 INCLUDING CONTENTS AND DATAFILES;
VALIDAT TODOS LOS PRIVILEGIOS DE SISTEMA.
SQL> select * from system_privilege_map
Privilegios Otorgados
La información de los privilegios otorgados se almacena en el diccionario de datos. Estos datos son accesibles a través de las siguientes vistas del diccionario de datos:
| Vista | Contenidos |
| DBA_ROLES | Nombres de los roles y su estado del password. |
| DBA_ROLES_PRIVS | Usuarios a los que han sido otorgados roles. |
| DBA_SYS_PRIVS | Usuarios a los que han sido otorgados privilegios del sistema. |
| DBA_TAB_PRIVS | Usuarios a los que han sido otorgados privilegios sobre objetos. |
| DBA_COL_PRIVS | Usuarios a los que han sido otorgados privilegios sobre columnas de tablas. |
| ROLE_ROLE_PRIVS | Roles que han sido otorgados a otros roles. |
| ROLE_SYS_PRIVS | Privilegios de sistema que han sido otorgados a roles. |
| ROLE_TAB_PRIVS | Privilegios de tabla que han sido otorgados a roles. |
The number of dispatchers started at instance startup is controlled by the DISPATCHERS attribute.
Note: Unlike the shared servers, the number of dispatchers does not change dynamically. The number of dispatchers must be explicitly changed with the ALTER SYSTEM command. You can change the number of dispatchers in this manner up to a maximum limit specified by the MTS_MAX_DISPATCHERS parameter. See the Oracle8i Designing and Tuning for Performance for further information about this parameter. |
The appropriate number of dispatchers for each instance depends upon the performance you want from your database, the host operating system's limit on the number of connections per process, (which is operating system dependent), and the number of connections required per network protocol.
Once you know the number of possible connections per process for your operating system, calculate the initial number of dispatchers to create during instance startup, per network protocol, using the following formula.
number maximum number of concurrent sessions of = CEIL (--------------------------------------------------------------------------) dispatchers connections per dispatcher
CEIL represents the number roundest to the next highest number.
As an example, assume that a system that has:
In this case, the DISPATCHERS attribute for TCP/IP should be set to a minimum of four dispatchers and SPX should be set to a minimum of three dispatchers:
mts_dispatchers="(protocol=tcp)(dispatchers=4)(connections=1000)" mts_dispatchers="(protocol=spx)(dispatchers=3)(connections=1000)"
Depending on performance, you may need to adjust the number of dispatchers.
To force the IP address used for the dispatchers, set the following:
mts_dispatchers="(address=(protocol=tcp)(host=144.25.16.201)) (dispatchers=2)"
This starts two dispatchers that listen on HOST=144.25.16.201. Note that Net8 dynamically selects the TCP/IP port for the dispatcher.
To force the exact location of the dispatchers, add the PORT as follows:
mts_dispatchers="(address=(protocol=tcp) (host=144.25.16.201)(port=5000))(dispatchers=1)" mts_dispatchers="(address=(protocol=tcp) (host=144.25.16.201)(port=5001))(dispatchers=1)"
Connection pooling is a resource utilization feature that enables you to reduce the number of physical network connections to a dispatcher. This is achieved by sharing or pooling a set of connections among the client processes.
To configure connection pooling, set the MTS_DISPATCHERS parameter in the initialization parameter file with the POOL attribute and the following optional attributes:
Returning to the example in "Calculating the Initial Number of Dispatchers", connection pooling can be used to allow each dispatcher 1,000 connections and 4,000 sessions for TCP/IP and 3,000 sessions for SPX. This reduces the configuration to one dispatcher for each protocol, as shown in the following:
mts_dispatchers="(protocol=tcp)(dispatchers=1)(pool=on)(tick=1) (connections=1000)(sessions=4000)"
mts_dispatchers="(protocol=spx)(dispatchers=1)(pool=on)(tick=1)
(connections=2000)"
An Oracle8i database can be represented by multiple service names. Because of this, a pool of MTS dispatchers can be allocated exclusively for clients requesting a particular service. This way, the mission critical requests may be given more resources and, thus, in effect increase their priority.
For example, the following initialization parameter file sample shows two dispatchers. The first dispatcher services requests for clients requesting sales.us.acme.com. The other dispatcher services requests for only clients requesting adminsales.us.acme.com.
service_names=sales.us.acme.com instance_name=sales mts_dispatchers="(protocol=tcp)" mts_dispatchers="(protocol=tcp)(service=adminsales.us.acme.com)"
If MTS is configured and a client connection request comes when no dispatchers are registered, the requests can be handled by a dedicated server (configured in the listener.ora file). If you want a particular client to always use a dispatcher, configure (SERVER=SHARED) in the connect data portion of the connect descriptor. For example:
sales=
(description=
(address=(protocol=tcp)(host=sales-server)(port= 1521))
(connect_data=
(service_name=sales.us.acme.com)
(server=shared)))
Configuration Best Practices
Target FRA = Current FRA + DB_FLASHBACK_RETENTION_TARGET x 60 x Peak Redo Rate (MB/sec)
Example:
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.
Example:
SQL> alter system set "_db_flashback_log_min_size"=4g;
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
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
Create Guaranteed Restore Point (GRP)
Execute test
loop
Flashback database to GRP
Open resetlogs
Create new GRP
Drop old GRP
Execute test
End loop
Performance tuning for specific application use cases
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:
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
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
[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]$
| Subprogram | Description |
|---|---|
| READ_CLIENT_INFO Procedure | Reads the value of the client_info field of the current session |
| READ_MODULE Procedure | Reads the values of the module and action fields of the current session |
| SET_ACTION Procedure | Sets the name of the current action within the current module |
| SET_CLIENT_INFO Procedure | Sets the client_info field of the session |
| SET_MODULE Procedure | Sets the name of the module that is currently running to a new module |
| SET_SESSION_LONGOPS Procedure | Sets a row in the V$SESSION_LONGOPS table |
client_info field of the current session.V$SQLAREA or by calling the READ_MODULE procedure. Client information can be retrieved by querying the V$SESSION view, or by calling the READ_CLIENT_INFO Procedure.MODULE and ACTION column of the V$SQLAREA.SELECT sql_text, disk_reads, module, action FROM v$sqlarea WHERE module = 'add_employee'; SQL_TEXT DISK_READS MODULE ACTION ------------------- ---------- ------------------ ---------------- INSERT INTO emp 1 add_employee insert into emp (ename, empno, sal, mgr, job, hiredate, comm, deptno) VALUES (name, next.emp_seq, manager, title, SYSDATE, commission, department) 1 row selected.
| Parameter | Description |
|---|---|
action_name | The name of the current action within the current module. When the current action terminates, call this procedure with the name of the next action if there is one, or NULL if there is not. Names longer than 32 bytes are truncated. |
NULL after the transaction completes, so that subsequent transactions are logged correctly. If you do not set the transaction name to NULL, subsequent transactions may be logged with the previous transaction's name.CREATE OR REPLACE PROCEDURE bal_tran (amt IN NUMBER(7,2)) AS
BEGIN
-- balance transfer transaction
DBMS_APPLICATION_INFO.SET_ACTION(
action_name => 'transfer from chk to sav');
UPDATE chk SET bal = bal + :amt
WHERE acct# = :acct;
UPDATE sav SET bal = bal - :amt
WHERE acct# = :acct;
COMMIT;
DBMS_APPLICATION_INFO.SET_ACTION(null);
END;
| Parameter | Description |
|---|---|
client_info | Supplies any additional information about the client application. This information is stored in the V$SESSION view. Information exceeding 64 bytes is truncated. |
CLIENT_INFO is readable and writable by any user. For storing secured application attributes, you can use the application context feature.| Parameter | Description |
|---|---|
module_name | Name of module that is currently running. When the current module terminates, call this procedure with the name of the new module if there is one, or NULL if there is not. Names longer than 48 bytes are truncated. |
action_name | Name of current action within the current module. If you do not want to specify an action, this value should be NULL. Names longer than 32 bytes are truncated. |
CREATE or replace PROCEDURE add_employee(
name VARCHAR2,
salary NUMBER,
manager NUMBER,
title VARCHAR2,
commission NUMBER,
department NUMBER) AS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'add_employee',
action_name => 'insert into emp');
INSERT INTO emp
(ename, empno, sal, mgr, job, hiredate, comm, deptno)
VALUES (name, emp_seq.nextval, salary, manager, title, SYSDATE,
commission, department);
DBMS_APPLICATION_INFO.SET_MODULE(null,null);
END;
V$SESSION_LONGOPS view. This is a view that is used to indicate the on-going progress of a long running operation. Some Oracle functions, such as parallel execution and Server Managed Recovery, use rows in this view to indicate the status of, for example, a database backup.SET_SESSION_LONGOPS procedure to advertise information on the progress of application specific long running tasks so that the progress can be monitored by way of the V$SESSION_LONGOPS view.DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS ( rindex IN OUT BINARY_INTEGER, slno IN OUT BINARY_INTEGER, op_name IN VARCHAR2 DEFAULT NULL, target IN BINARY_INTEGER DEFAULT 0, context IN BINARY_INTEGER DEFAULT 0, sofar IN NUMBER DEFAULT 0, totalwork IN NUMBER DEFAULT 0, target_desc IN VARCHAR2 DEFAULT 'unknown target', units IN VARCHAR2 DEFAULT NULL) set_session_longops_nohint constant BINARY_INTEGER := -1;
| Parameter | Description |
|---|---|
rindex | A token which represents the v$session_longops row to update. Set this to set_session_longops_nohint to start a new row. Use the returned value from the prior call to reuse a row. |
slno | Saves information across calls to set_session_longops: It is for internal use and should not be modified by the caller. |
op_name | Specifies the name of the long running task. It appears as the OPNAME column of v$session_longops. The maximum length is 64 bytes. |
target | Specifies the object that is being worked on during the long running operation. For example, it could be a table ID that is being sorted. It appears as the TARGET column of v$session_longops. |
context | Any number the client wants to store. It appears in the CONTEXT column of v$session_longops. |
sofar | Any number the client wants to store. It appears in the SOFAR column of v$session_longops. This is typically the amount of work which has been done so far. |
totalwork | Any number the client wants to store. It appears in the TOTALWORK column of v$session_longops. This is typically an estimate of the total amount of work needed to be done in this long running operation. |
target_desc | Specifies the description of the object being manipulated in this long operation. This provides a caption for the target parameter. This value appears in the TARGET_DESC field of v$session_longops. The maximum length is 32 bytes. |
units | Specifies the units in which sofar and totalwork are being represented. It appears as the UNITS field of v$session_longops. The maximum length is 32 bytes. |
V$SESSION_LONGOPS on the procedure's progress.DECLARE
rindex BINARY_INTEGER;
slno BINARY_INTEGER;
totalwork number;
sofar number;
obj BINARY_INTEGER;
BEGIN
rindex := dbms_application_info.set_session_longops_nohint;
sofar := 0;
totalwork := 10;
WHILE sofar < 10 LOOP
-- update obj based on sofar
-- perform task on object target
sofar := sofar + 1;
dbms_application_info.set_session_longops(rindex, slno,
"Operation X", obj, 0, sofar, totalwork, "table", "tables");
END LOOP;
END;
SENTENCIAS SQL PARA VALIDAR LOS PROCESOS EN EJECUCION
SELECT SQL_TEXT,MODULE,ACTION
FROM V$SQLAREA WHERE SQL_TEXT = 'SELECT OWNER,COUNT(*) FROM ALL_OBJECTS GROUP BY OWNER ORDER BY OWNER' SELECT sql_text, disk_reads, module, action FROM v$sqlarea WHERE module like 'DBA%'
set define on
prompt 'Digite el nombre de usuario:' "&&USERNAME"
CREATE USER "&USERNAME" PROFILE "DBA" IDENTIFIED BY 0r1cl310g PASSWORD EXPIRE DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT ADMINISTER ANY SQL TUNING SET TO "&USERNAME" ;
GRANT ADMINISTER DATABASE TRIGGER TO "&USERNAME" ;
GRANT DBA TO "&USERNAME" ;
GRANT ADMINISTER SQL TUNING SET TO "&USERNAME" ;
GRANT ADVISOR TO "&USERNAME" ;
GRANT ALTER ANY INDEX TO "&USERNAME" ;
GRANT ALTER ANY INDEXTYPE TO "&USERNAME" ;
GRANT ALTER ANY MATERIALIZED VIEW TO "&USERNAME" ;
GRANT ALTER ANY PROCEDURE TO "&USERNAME" ;
GRANT ALTER ANY SEQUENCE TO "&USERNAME" ;
GRANT ALTER ANY TABLE TO "&USERNAME" ;
GRANT ALTER ANY TRIGGER TO "&USERNAME" ;
GRANT ALTER DATABASE TO "&USERNAME" ;
GRANT ALTER SESSION TO "&USERNAME" ;
GRANT ALTER SYSTEM TO "&USERNAME" ;
GRANT ALTER TABLESPACE TO "&USERNAME" ;
GRANT ALTER USER TO "&USERNAME" ;
GRANT ANALYZE ANY TO "&USERNAME" ;
GRANT ANALYZE ANY DICTIONARY TO "&USERNAME" ;
GRANT BACKUP ANY TABLE TO "&USERNAME" ;
GRANT BECOME USER TO "&USERNAME" ;
GRANT CREATE ANY DIRECTORY TO "&USERNAME" ;
GRANT CREATE DATABASE LINK TO "&USERNAME" ;
GRANT CREATE JOB TO "&USERNAME" ;
GRANT CREATE PROFILE TO "&USERNAME" ;
GRANT CREATE ROLE TO "&USERNAME" ;
GRANT CREATE PUBLIC DATABASE LINK TO "&USERNAME" ;
GRANT CREATE PUBLIC SYNONYM TO "&USERNAME" ;
GRANT CREATE SESSION TO "&USERNAME" ;
GRANT EXECUTE ANY PROCEDURE TO "&USERNAME" ;
GRANT EXPORT FULL DATABASE TO "&USERNAME" ;
GRANT FLASHBACK ANY TABLE TO "&USERNAME" ;
GRANT GRANT ANY OBJECT PRIVILEGE TO "&USERNAME" ;
GRANT GRANT ANY PRIVILEGE TO "&USERNAME" ;
GRANT GRANT ANY ROLE TO "&USERNAME" ;
GRANT IMPORT FULL DATABASE TO "&USERNAME" ;
GRANT MANAGE TABLESPACE TO "&USERNAME" ;
GRANT RESTRICTED SESSION TO "&USERNAME" ;
GRANT SELECT ANY DICTIONARY TO "&USERNAME" ;
GRANT SELECT ANY SEQUENCE TO "&USERNAME" ;
GRANT SELECT ANY TABLE TO "&USERNAME" ;
GRANT "DBA" TO "&USERNAME" ;
GRANT "EXECUTE_CATALOG_ROLE" TO "&USERNAME" ;
GRANT "EXP_FULL_DATABASE" TO "&USERNAME" ;
GRANT "GATHER_SYSTEM_STATISTICS" TO "&USERNAME" ;
GRANT "IMP_FULL_DATABASE" TO "&USERNAME" ;
GRANT "MGMT_USER" TO "&USERNAME" ;
GRANT "OEM_ADVISOR" TO "&USERNAME" ;
GRANT "OEM_MONITOR" TO "&USERNAME" ;
GRANT "SELECT_CATALOG_ROLE" TO "&USERNAME" ;
