martes, 27 de octubre de 2020
ORACLE LINUX REPOSITORIO
lunes, 26 de octubre de 2020
VISTA ARCHIVOS DE TRACE V$DIAG_INFO
VISTA PROCESOS V$BGPROCESS
VISTAS ARCHIVOS ORACLE DBA_DATA_FILES - DBA_TEMP_FILES
viernes, 23 de octubre de 2020
DB_FLASHBACK_RETENTION_TARGET
Configuration Best Practices
- 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.
- 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.
- 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.
- 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 - 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;
- 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
- 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.
- Set the Enterprise Manager monitoring metric, "Recovery Area Free Space (%)" for proactive alerts of space issues with the fast recovery area.
- 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.
- 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
- 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
- Follow the Data Guard redo apply best practices described in Best Practices for Data Guard and Active Data Guard Redo Apply Performance.
- 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
lunes, 19 de octubre de 2020
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
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
miércoles, 14 de octubre de 2020
CREATE SEQUENCES CURRVAL NEXTVAL
lunes, 5 de octubre de 2020
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]$
martes, 8 de septiembre de 2020
DBMS_APPLICATION_INFO
Summary of DBMS_APPLICATION_INFO Subprograms
| 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 |
READ_CLIENT_INFO Procedure
This procedure reads the value of theclient_info field of the current session.READ_MODULE Procedure
This procedure reads the values of the module and action fields 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.
SET_ACTION Procedure
This procedure sets the name of the current action within the current module.| 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. |
Set the transaction name to
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;
SET_CLIENT_INFO Procedure
This procedure supplies additional information about the client application.| 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.SET_MODULE Procedure
This procedure sets the name of the current application or module.| 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;
SET_SESSION_LONGOPS Procedure
This procedure sets a row in theV$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.Applications may use the
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%'
jueves, 3 de septiembre de 2020
CREATE USER CON ROLE SYSDBA DINAMICO
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" ;
martes, 1 de septiembre de 2020
martes, 25 de agosto de 2020
tamano tablespaces
set serveroutput on size 10000
clear breaks
clear computes
clear columns
set pagesize 50
set linesize 300
set heading on
PROMPT TAMAÑO TABLESPACES ...
column tablespace_name heading 'Tablespace' justify left format a20 truncated
column tbsize heading 'Size|(Mb) ' justify left format 9,999,999.99
column tbused heading 'Used|(Mb) ' justify right format 9,999,999.99
column tbfree heading 'Free|(Mb) ' justify right format 9,999,999.99
column tbusedpct heading 'Used |% ' justify left format a8
column tbfreepct heading 'Free |% ' justify left format a8
break on report
compute sum label 'Totals:' of tbsize tbused tbfree on report
select t.tablespace_name, round(a.bytes,2) tbsize,
nvl(round(c.bytes,2),'0') tbfree,
nvl(round(b.bytes,2),'0') tbused,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct
from dba_tablespaces t,
(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_data_files
group by tablespace_name
union
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_temp_files
group by tablespace_name ) a,
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes
from dba_segments e
group by e.tablespace_name
union
select tablespace_name, sum(max_size) bytes
from v$sort_segment
group by tablespace_name) b,
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes
from dba_free_space f
group by f.tablespace_name
union
select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes
from dba_temp_files tmp, v$sort_segment sort
where tmp.tablespace_name = sort.tablespace_name
group by tmp.tablespace_name) c
where
t.tablespace_name = a.tablespace_name (+)
and t.tablespace_name = b.tablespace_name (+)
and t.tablespace_name = c.tablespace_name (+)
order by t.tablespace_name
/
VALIDAR FORMATO ENDIAN
SQL> select * from v$transportable_platform
SQL> select tp.endian_format from v$transportable_platform tp, v$database sp where tp.platform_name = sp.platform_name
sábado, 25 de julio de 2020
PARAMETROS FLASHBACK DATABASE
sábado, 18 de julio de 2020
Backup and Restore of ASM Metadata in Oracle 11gR2 (md_backup and md_restore)
Backup and Restore of ASM Metadata in Oracle 11gR2 (md_backup and md_restore)
ASMCMD utility was introduced in Oracle 10g and offered some basic features for navigation, search, monitoring and management, for full description see the docs here. In Oracle 11gR1 the asmcmd functionality was extended to include ability to backup existing disk groups metadata among other new 11g features, for full information refer to the docs here. In Oracle 11gR2 with the introduction of the ACFS the ASMCMD functionality was further extended see here. In Oracle 11gR2 ASMCMD can be used to perform almost all of the activities that used to be performed from sqlplus prompt previously and both sqlplus and asmcmd can be used interchangeably. In this article I will demo how to backup a disk group using md_backup command and use md_restore to obtain the SQL statements to recreate the disk groups and all the dependencies such as templates, aliases, directories and disk group attributes.
The syntax of md_backup in Oracle 11gR2 is as follow.
ASMCMD> md_backup
usage: md_backup backup_file [-G diskgroup [,diskgroup,…]]
help: help md_backup
ASMCMD>
The syntax of md_restore is as follow.
ASMCMD> md_restore
usage: md_restore backup_file [–silent][–full|–nodg|–newdg -o ‘old_diskgroup:new_diskgroup [,…]’][-S sql_script_file] [-G ‘diskgroup [,diskgroup…]’]
md_backup /tmp/backup_ASM.bcp -G data,dgdup,dgdup1,dgdup2,prim,sec
The backed diskgroup metadata can be directly restored upon failure of the disk or we can have asmcmd generate a script and later use the script to generate the disk groups and all of the dependencies from sqlplus.
md_restore /tmp/backup_ASM.bcp –full -S /tmp/ASM_diskgroup.sql
ASMCMD> md_backup backup.bck -G data,fra
