Para ver el resultado:
Copiar
lunes, 20 de enero de 2020
lunes, 13 de enero de 2020
Tamaño de tabla en ORACLE
Dejo consulta para averiguar cuanto pesa una tabla en ORACLE
select tablespace_name Tablespace, segment_name Tabla,
trunc(sum(bytes)/(1024*1024*1024)) Gigas
from dba_extents
where segment_type='TABLE'
group by segment_name, tablespace_name
order by 3 desc;
gracias!!
select tablespace_name Tablespace, segment_name Tabla,
trunc(sum(bytes)/(1024*1024*1024)) Gigas
from dba_extents
where segment_type='TABLE'
group by segment_name, tablespace_name
order by 3 desc;
gracias!!
viernes, 10 de enero de 2020
Ubicacion de la WALLET en TDE !!
Ademas de fijarse en el archivo sqlnet.ora par confirmar la ubicacion de la wallet de TDE podemos hacer este query:
select * from V$ENCRYPTION_WALLET;
select * from V$ENCRYPTION_WALLET;
martes, 7 de enero de 2020
Espera por sesion. ORACLE
Script:
prompt
spool waits.rpt
col p1text for a30
col p2text for a30
col p3text for a30
col program for a40
col sid for 9999
col event for a57
col wait_class for a15
col "OS User" for a9
col "User" for a20
col "Status" for a10
col "Program" for a40
col "Module" for a38
col "Terminal" for a15
col "Client Info" for a15
col machine for a25
col "SID/Serial" for a15
set feed off
accept x_user prompt 'User : '
accept x_sid prompt 'SID : '
accept x_NoSqlNet prompt 'Ignore SQL*Net client waits? [Y] ' default 'Y'
prompt
prompt Current sessions by username
prompt
select i.instance_name "instance",
s.osuser "OS User",
s.sid || ',' || s.serial# "SID/Serial",
r.spid "OS PID",
s.username "User",
s.status "Status",
s.logon_time "Logon",
s.machine "Machine",
s.program "Program",
s.module "Module",
s.client_info "Client Info"
from gv$session s,
gv$process r,
gv$instance i
where s.username like upper( '%' || '&&x_user' || '%' )
and s.sid = nvl( '&x_sid', s.sid )
and i.inst_id = s.inst_id
and r.inst_id = s.inst_id
and r.addr (+)= s.paddr
order by s.username, s.terminal, s.program, s.logon_time;
prompt
prompt By event, username, inst_id, wait_class, sid
prompt
select b.event,
a.username, a.inst_id,
c.wait_class, b.sid, c.total_waits, c.time_waited,
b.seconds_in_wait,
decode( b.wait_time,
0, 'Waiting',
-1, 'Last Wait minimal',
-2, 'Waited Unknown',
decode( sign( b.wait_time ),
-1, 'Wrapped',
1, 'Last Wait',
'Unknown'
)
) "Wait status",
a.osuser, a.program,
b.p1text, b.p1, b.p1raw, b.p2text, b.p2, b.p2raw, b.p3text, b.p3, b.p3raw,
b.seq#
from gv$session a,
gv$session_wait b,
gv$session_wait_class c
where a.username like upper( '%&x_user%' )
and b.sid = decode( '&&x_sid', null, b.sid, 0, b.sid, '&&x_sid' )
and b.wait_time > -2
and a.sid = b.sid
and ( upper( nvl( '&x_NoSqlNet', 'N' ) ) <> 'Y'
or instr( b.event, 'SQL*Net message from client' ) = 0
)
and b.inst_id = a.inst_id
and c.inst_id = a.inst_id
and c.sid = a.sid
and c.serial# = a.serial#
order by b.event, username, inst_id, wait_class, sid;
prompt
prompt By seconds_in_wait, total_waits
prompt
select b.seconds_in_wait, c.total_waits,
a.inst_id, a.username, b.sid,
c.wait_class, c.time_waited,
decode( b.wait_time,
0, 'Waiting',
-1, 'Last Wait minimal',
-2, 'Waited Unknown',
decode( sign( b.wait_time ),
-1, 'Wrapped',
1, 'Last Wait',
'Unknown'
)
) "Wait status",
b.event,
a.osuser, a.program,
b.p1text, b.p1, b.p1raw, b.p2text, b.p2, b.p2raw, b.p3text, b.p3, b.p3raw,
b.seq#
from gv$session a,
gv$session_wait b,
gv$session_wait_class c
where a.username like upper( '%&x_user%' )
and b.sid = decode( '&&x_sid', null, b.sid, 0, b.sid, '&&x_sid' )
and b.wait_time > -2
and a.sid = b.sid
and ( upper( nvl( '&x_NoSqlNet', 'N' ) ) <> 'Y'
or instr( b.event, 'SQL*Net message from client' ) = 0
)
and b.inst_id = a.inst_id
and c.inst_id = a.inst_id
and c.sid = a.sid
and c.serial# = a.serial#
order by b.seconds_in_wait, c.total_waits;
select event, inst_id, count(*)
from gv$session_wait
group by event, inst_id
order by event, inst_id;
prompt
prompt ITL waits
prompt
col statistic_name for a15
select a1.statistic_name,
a1.owner,
a1.object_type,
a1.object_name,
a1.value,
a1.subobject_name,
a1.inst_id,
a1.tablespace_name,
a1.ts#,
a1.obj#,
a1.dataobj#,
a1.statistic#
from gv$segment_statistics a1
where statistic_name = 'ITL waits'
and owner = nvl( upper( '&&x_user' ), owner )
and value > 0
order by value desc;
prompt
prompt ITL waits: summary
prompt
select a1.statistic_name,
a1.owner,
a1.object_type,
a1.object_name,
sum( a1.value ) "Combined Value",
a1.subobject_name,
a1.tablespace_name,
a1.ts#,
a1.obj#,
a1.dataobj#,
a1.statistic#
from gv$segment_statistics a1
where statistic_name = 'ITL waits'
and owner = nvl( upper( '&&x_user' ), owner )
and value > 0
group by a1.statistic_name,
a1.owner,
a1.object_type,
a1.object_name,
a1.subobject_name,
a1.tablespace_name,
a1.ts#,
a1.obj#,
a1.dataobj#,
a1.statistic#
order by "Combined Value";
spool off
_______________________________________________________________
EJEMPLO DE SALIDA.
User :
SID :
Ignore SQL*Net client waits? [Y]
Current sessions by username
instance OS User SID/Serial OS PID User Status Logon Machine Program Module Client Info
---------------- --------- --------------- ------------------------ -------------------- ---------- ------------------- ------------------------- ---------------------------------------- -------------------------------------- ---------------
FERD oracle 753,12935 16291 APEX_PUBLIC_USER INACTIVE 30/09/2015 14:32:45 axdevap05 httpd.worker@xxdevjj05 (TNS V1-V3)
FERD oracle 502,1 24866 DBSNMP INACTIVE 25/09/2015 19:48:40 blx24ax01 JDBC Thin Client JDBC Thin Client
FERD oracle 352,2133 32351 DBSNMP INACTIVE 30/09/2015 11:38:45 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database
FERD oracle 552,1905 18784 DBSNMP INACTIVE 30/09/2015 14:18:44 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database
FERD oracle 6,883 17550 DBSNMP INACTIVE 30/09/2015 14:48:44 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database
FERD oracle 302,3427 17532 DBSNMP INACTIVE 30/09/2015 14:48:44 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database
FERD peterm 702,18145 24137 UTILITIES_DEV01 ACTIVE 30/09/2015 13:52:28 M10000_HG98A sqlplusw.exe SQL*Plus
By event, username, inst_id, wait_class, sid
EVENT USERNAME INST_ID WAIT_CLASS SID TOTAL_WAITS TIME_WAITED SECONDS_IN_WAIT Wait status OSUSER PROGRAM P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW SEQ#
--------------------------------------------------------- ------------------------------ ---------- --------------- ----- ----------- ----------- --------------- ----------------- ---------- ---------------------------------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ----------
SQL*Net message to client UTILITIES_DEV01 1 Idle 702 51 351732 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142
SQL*Net message to client UTILITIES_DEV01 1 Network 702 52 0 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142
SQL*Net message to client UTILITIES_DEV01 1 System I/O 702 14 0 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142
SQL*Net message to client UTILITIES_DEV01 1 User I/O 702 25 8 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142
By seconds_in_wait, total_waits
SECONDS_IN_WAIT TOTAL_WAITS INST_ID USERNAME SID WAIT_CLASS TIME_WAITED Wait status EVENT OSUSER PROGRAM P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW SEQ#
--------------- ----------- ---------- ------------------------------ ----- --------------- ----------- ----------------- --------------------------------------------------------- ---------- ---------------------------------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ----------
0 14 1 UTILITIES_DEV01 702 System I/O 0 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148
0 25 1 UTILITIES_DEV01 702 User I/O 8 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148
0 54 1 UTILITIES_DEV01 702 Idle 351737 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148
0 55 1 UTILITIES_DEV01 702 Network 0 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148
EVENT INST_ID COUNT(*)
--------------------------------------------------------- ---------- ----------
DIAG idle wait 1 2
EMON slave idle wait 1 5
SQL*Net message from client 1 6
SQL*Net message to client 1 1
Space Manager: slave idle wait 1 1
Streams AQ: emn coordinator idle wait 1 1
Streams AQ: qmn coordinator idle wait 1 1
Streams AQ: qmn slave idle wait 1 1
Streams AQ: waiting for time management or cleanup tasks 1 1
VKTM Logical Idle Wait 1 1
pmon timer 1 1
rdbms ipc message 1 13
smon timer 1 1
ITL waits
STATISTIC_NAME OWNER OBJECT_TYPE OBJECT_NAME VALUE SUBOBJECT_NAME INST_ID TABLESPACE_NAME TS# OBJ# DATAOBJ# STATISTIC#
--------------- -------------------- ------------------ ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ---------- ---------- ----------
ITL waits SYS INDEX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST ########## 1 SYSAUX 1 500 500 15
ITL waits: summary
STATISTIC_NAME OWNER OBJECT_TYPE OBJECT_NAME Combined Value SUBOBJECT_NAME TABLESPACE_NAME TS# OBJ# DATAOBJ# STATISTIC#
--------------- -------------------- ------------------ ------------------------------ -------------- ------------------------------ ------------------------------ ---------- ---------- ---------- ----------
ITL waits SYS INDEX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 1 SYSAUX 1 500 500 15
Backup historico de RMAN
prompt
accept x_days prompt "Days [7]: " default 7
prompt
col "Time" for a10
col "In" for a20
col "Out" for a20
col "Day" for a4
col status for a12
col command_id for a20
select command_id, input_type, to_char( start_time, 'Dy' ) "Day", start_time, end_time, status, time_taken_display "Time",
input_bytes_display "In", output_bytes_display "Out", output_device_type
from v$rman_backup_job_details
where start_time > sysdate - &x_days
order by start_time;
prompt
prompt Non Full:
select command_id, input_type, to_char( start_time, 'Dy' ) "Day", start_time, end_time, status, time_taken_display "Time",
input_bytes_display "In", output_bytes_display "Out", output_device_type
from v$rman_backup_job_details
where start_time > sysdate - &x_days
and input_type <> 'DB FULL'
order by start_time;
prompt
prompt Full:
select command_id, input_type, to_char( start_time, 'Dy' ) "Day", start_time, end_time, status, time_taken_display "Time",
input_bytes_display "In", output_bytes_display "Out", output_device_type
from v$rman_backup_job_details
where start_time > sysdate - &x_days
and input_type = 'DB FULL'
order by start_time;
Example output:
SQL> @rmans
Days [7]:
COMMAND_ID INPUT_TYPE Day START_TIME END_TIME STATUS Time In Out OUTPUT_DEVICE_TYP
-------------------- ------------- ---- ------------------- ------------------- ------------ ---------- -------------------- -------------------- -----------------
DEV2R2.online DB FULL Mon 28/09/2015 20:25:19 28/09/2015 20:28:32 COMPLETED 00:03:13 16.79G 8.92G SBT_TAPE
DEV2R1.archlogs ARCHIVELOG Mon 28/09/2015 22:41:43 28/09/2015 22:43:06 COMPLETED 00:01:23 443.93M 444.25M SBT_TAPE
DEV2R2.archlogs ARCHIVELOG Wed 30/09/2015 02:08:10 30/09/2015 02:11:14 COMPLETED 00:03:04 594.39M 594.75M SBT_TAPE
DEV2R2.archlogs ARCHIVELOG Wed 30/09/2015 23:53:33 01/10/2015 00:00:58 COMPLETED 00:07:25 316.18M 316.50M SBT_TAPE
DEV2R1.online DB FULL Thu 01/10/2015 20:25:58 01/10/2015 20:30:20 COMPLETED 00:04:22 16.79G 8.93G SBT_TAPE
DEV2R2.archlogs ARCHIVELOG Thu 01/10/2015 22:42:19 01/10/2015 22:43:33 COMPLETED 00:01:14 432.98M 433.50M SBT_TAPE
DEV2R1.archlogs ARCHIVELOG Fri 02/10/2015 22:41:43 02/10/2015 22:44:17 COMPLETED 00:02:34 438.50M 438.75M SBT_TAPE
DEV2R2.archlogs ARCHIVELOG Sat 03/10/2015 22:43:07 03/10/2015 22:44:42 COMPLETED 00:01:35 450.88M 451.50M SBT_TAPE
DEV2R1.online DB FULL Sun 04/10/2015 20:32:45 04/10/2015 20:43:00 COMPLETED 00:10:15 16.79G 8.93G SBT_TAPE
DEV2R1.archlogs ARCHIVELOG Sun 04/10/2015 22:41:50 04/10/2015 22:43:25 COMPLETED 00:01:35 465.95M 466.25M SBT_TAPE
10 rows selected.
Non Full:
COMMAND_ID INPUT_TYPE Day START_TIME END_TIME STATUS Time In Out OUTPUT_DEVICE_TYP
-------------------- ------------- ---- ------------------- ------------------- ------------ ---------- -------------------- -------------------- -----------------
DEV2R1.archlogs ARCHIVELOG Mon 28/09/2015 22:41:43 28/09/2015 22:43:06 COMPLETED 00:01:23 443.93M 444.25M SBT_TAPE
DEV2R2.archlogs ARCHIVELOG Wed 30/09/2015 02:08:10 30/09/2015 02:11:14 COMPLETED 00:03:04 594.39M 594.75M SBT_TAPE
DEV2R2.archlogs ARCHIVELOG Wed 30/09/2015 23:53:33 01/10/2015 00:00:58 COMPLETED 00:07:25 316.18M 316.50M SBT_TAPE
DEV2R2.archlogs ARCHIVELOG Thu 01/10/2015 22:42:19 01/10/2015 22:43:33 COMPLETED 00:01:14 432.98M 433.50M SBT_TAPE
DEV2R1.archlogs ARCHIVELOG Fri 02/10/2015 22:41:43 02/10/2015 22:44:17 COMPLETED 00:02:34 438.50M 438.75M SBT_TAPE
DEV2R2.archlogs ARCHIVELOG Sat 03/10/2015 22:43:07 03/10/2015 22:44:42 COMPLETED 00:01:35 450.88M 451.50M SBT_TAPE
DEV2R1.archlogs ARCHIVELOG Sun 04/10/2015 22:41:50 04/10/2015 22:43:25 COMPLETED 00:01:35 465.95M 466.25M SBT_TAPE
7 rows selected.
Full:
COMMAND_ID INPUT_TYPE Day START_TIME END_TIME STATUS Time In Out OUTPUT_DEVICE_TYP
-------------------- ------------- ---- ------------------- ------------------- ------------ ---------- -------------------- -------------------- -----------------
DEV2R2.online DB FULL Mon 28/09/2015 20:25:19 28/09/2015 20:28:32 COMPLETED 00:03:13 16.79G 8.92G SBT_TAPE
DEV2R1.online DB FULL Thu 01/10/2015 20:25:58 01/10/2015 20:30:20 COMPLETED 00:04:22 16.79G 8.93G SBT_TAPE
DEV2R1.online DB FULL Sun 04/10/2015 20:32:45 04/10/2015 20:43:00 COMPLETED 00:10:15 16.79G 8.93G SBT_TAPE
3 rows selected.
Ver las auditorias actuales de la base ORACLE
-- Name : audits.sql
prompt
prompt Purpose : auditorias actuales de la base ORACLE
prompt
--
-- Oracle Version: 11g
-- Other Oracle versions: should work on 9i and later
----------------------------------------------------------------------------------------
spool audits.rpt
prompt
col value for a20
col display_value for a50
col update_comment for a50
col policy_text for a30
prompt Instances
prompt ----------
select * from gv$instance order by inst_id;
prompt Audit parameters
prompt -----------------
col name for a20
col value for a40
break on inst_id skip 1
select *
from gv$parameter
where upper( name ) like '%AUDIT%'
order by inst_id;
clear breaks
prompt
prompt DBA_STMT_AUDIT_OPTS
prompt --------------------
select * from sys.dba_stmt_audit_opts order by nvl( user_name, '0' ), audit_option;
prompt
prompt DBA_PRIV_AUDIT_OPTS
prompt --------------------
select * from sys.dba_priv_audit_opts order by privilege;
prompt
prompt DBA_OBJ_AUDIT_OPTS
prompt -------------------
prompt S=Session, A=Access
prompt S/- = audit whenever successful
prompt -/S = audit whenever unsuccessful
col owner for a30
select *
from sys.dba_obj_audit_opts
where ALT <> '-/-'
or AUD <> '-/-'
or COM <> '-/-'
or DEL <> '-/-'
or GRA <> '-/-'
or IND <> '-/-'
or INS <> '-/-'
or LOC <> '-/-'
or REN <> '-/-'
or SEL <> '-/-'
or UPD <> '-/-'
or REF <> '-/-'
or EXE <> '-/-'
or CRE <> '-/-'
or REA <> '-/-'
or WRI <> '-/-'
order by owner, object_name;
-- default settings
prompt
prompt ALL_DEF_AUDIT_OPTS
prompt -------------------
select * from all_def_audit_opts;
prompt
prompt DBA_AUDIT_TRAIL (breakdown)
prompt ----------------------------
select action_name, returncode, count(*) from dba_audit_trail group by action_name, returncode order by action_name, returncode;
prompt
prompt DBA_AUDIT_POLICIES
prompt -------------------
select * from dba_audit_policies;
prompt
prompt SYS.FGA_LOG$
prompt
declare
cursor csr_main_cursor is select * from SYS.DBA_AUDIT_POLICIES order by policy_name;
begin
dbms_output.put_line( 'Details from SYS.DBA_AUDIT_POLICIES' );
dbms_output.put_line( '-----------------------------------' );
for i in csr_main_cursor loop
dbms_output.put_line( 'Object Schema : ' || i.object_schema );
dbms_output.put_line( 'Object Name : ' || i.object_name );
dbms_output.put_line( 'Policy Name : ' || i.policy_name );
dbms_output.put_line( 'Policy Text : ' || i.policy_text );
dbms_output.put_line( 'Policy Column : ' || i.policy_column );
dbms_output.put_line( 'Pf Schema : ' || i.pf_schema );
dbms_output.put_line( 'Pf Package : ' || i.pf_package );
dbms_output.put_line( 'Pf Function : ' || i.pf_function );
dbms_output.put_line( 'Enabled : ' || i.enabled );
dbms_output.put_line( 'Sel : ' || i.sel );
dbms_output.put_line( 'Ins : ' || i.ins );
dbms_output.put_line( 'Upd : ' || i.upd );
dbms_output.put_line( 'Del : ' || i.del );
dbms_output.put_line( 'Audit Trail : ' || i.audit_trail );
dbms_output.put_line( 'Policy Column Options : ' || i.policy_column_options );
dbms_output.put_line( chr(10) );
dbms_output.put_line( rpad( '-', 80, '-' ) );
end loop;
end;
/
col obj$name for a30
prompt
prompt SYS.FGA_LOG$ summary
prompt ---------------------
select policyname, obj$schema, obj$name, stmt_type, count(*)
from sys.fga_log$
group by policyname, obj$schema, obj$name, stmt_type
order by policyname, obj$schema, obj$name, stmt_type;
prompt
prompt SYS.GV_$XML_AUDIT_TRAIL summary
prompt -------------------------------
col db_user for a20
col os_user for a20
col "Audit Type" for a25
col "Action (Code)" for a20
select a.db_user,
a.os_user,
b.name || ' (' || a.action || ')' "Action (Code)",
a.returncode,
a.audit_type ||
decode( a.audit_type,
1, ' Standard XML Audit',
2, ' Fine Grained XML Audit',
4, ' SYS XML Audit',
8, ' Mandatory XML Audit',
a.audit_type || ' ????' ) "Audit Type",
count(*)
from SYS.GV_$XML_AUDIT_TRAIL a,
audit_actions b
where b.action (+)= a.action
group by a.db_user,
a.os_user,
b.name || ' (' || a.action || ')',
a.returncode,
a.audit_type ||
decode( a.audit_type,
1, ' Standard XML Audit',
2, ' Fine Grained XML Audit',
4, ' SYS XML Audit',
8, ' Mandatory XML Audit',
a.audit_type || ' ????' )
order by 1, 2;
select sql_text, count(*) from gv$xml_audit_trail group by sql_text order by upper( sql_text );
spool off
___________________________________________________________
SALIDA
Instances
---------
INST_ID INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --------------- ---------------- -------------------- ----------------- ------------------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- ---
1 1 DEV1 linux01a 11.2.0.3.0 25/09/2015 19:41:47 OPEN YES 1 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
2 2 DEV2 linux01b 11.2.0.3.0 25/09/2015 20:01:08 OPEN YES 2 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
2 rows selected.
Audit parameters
----------------
INST_ID NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS DESCRIPTION UPDATE_COMMENT HASH
---------- ---------- -------------------- ---------- ---------------------------------------- -------------------------------------------------- --------- ----- --------- ----- ---------- ----- ----- ----- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ----------
1 1677 audit_sys_operations 1 FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable sys auditing 2991425711
1840 audit_file_dest 2 /opt/oracle/admin/admin/DEV0A/adump /opt/oracle/admin/admin/DEV0A/adump FALSE FALSE DEFERRED TRUE FALSE FALSE FALSE FALSE Directory in which auditing files are to reside 2188534560
1841 audit_syslog_level 2 TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Syslog facility and level 1189300432
1869 audit_trail 2 DB DB FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable system auditing 4289193100
2 1677 audit_sys_operations 1 FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable sys auditing 2991425711
1840 audit_file_dest 2 /opt/oracle/admin/admin/DEV0A/adump /opt/oracle/admin/admin/DEV0A/adump FALSE FALSE DEFERRED TRUE FALSE FALSE FALSE FALSE Directory in which auditing files are to reside 2188534560
1841 audit_syslog_level 2 TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Syslog facility and level 1189300432
1869 audit_trail 2 DB DB FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable system auditing 4289193100
8 rows selected.
DBA_STMT_AUDIT_OPTS
-------------------
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
DATABASE LINK BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
PROFILE BY ACCESS BY ACCESS
PUBLIC SYNONYM BY ACCESS BY ACCESS
ROLE BY ACCESS BY ACCESS
SYSTEM AUDIT BY ACCESS BY ACCESS
SYSTEM GRANT BY ACCESS BY ACCESS
28 rows selected.
DBA_PRIV_AUDIT_OPTS
-------------------
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
23 rows selected.
DBA_OBJ_AUDIT_OPTS
------------------
S=Session, A=Access
S/- = audit whenever successful
-/S = audit whenever unsuccessful
OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
------------------------------ ------------------------------ ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
DEV_0001 COUNTRIES TABLE -/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
CODE_002 PROC_UPDATE_TEAMS PROCEDURE -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/-
2 rows selected.
ALL_DEF_AUDIT_OPTS
------------------
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
1 row selected.
DBA_AUDIT_TRAIL (breakdown)
---------------------------
ACTION_NAME RETURNCODE COUNT(*)
---------------------------- ---------- ----------
DELETE 0 1045
LOGOFF 0 697
LOGOFF BY CLEANUP 0 475
LOGON 0 1188
LOGON 28000 1
LOGON 28001 2
6 rows selected.
DBA_AUDIT_POLICIES
------------------
no rows selected
SYS.FGA_LOG$
Details from SYS.DBA_AUDIT_POLICIES
-----------------------------------
PL/SQL procedure successfully completed.
SYS.FGA_LOG$ summary
--------------------
no rows selected
SYS.GV_$XML_AUDIT_TRAIL summary
------------------------------
no rows selected
no rows selected
resultado del ADDM
Description:
Show the results of the latest ADDM run(s)
Script:
prompt
prompt ADDM (Automatic Database Diagnostic Monitor) recommendations and suggestions for possible problems
prompt
declare
-- find latest ADDM finding that actually produced recommendations
-- can be more than 1
cursor c_latest_recommends is
select *
from dba_advisor_tasks
where execution_end = ( select max( execution_end )
from dba_advisor_tasks dat,
dba_advisor_recommendations dar
where dat.task_name = dar.task_name
);
cursor c_problems ( i_task in varchar2 ) is
select *
from dba_advisor_findings
where task_name = i_task
and type = 'PROBLEM';
cursor c_symptoms ( i_task in varchar2, i_parent_id in integer) is
select *
from dba_advisor_findings
where task_name = i_task
and type = 'SYMPTOM'
and parent = i_parent_id;
cursor c_recommendations ( i_task in varchar2, i_problem_id in integer) is
select *
from dba_advisor_recommendations
where task_name = i_task
and finding_id = i_problem_id
order by rec_id;
cursor c_actions ( i_task in varchar2, i_rec_id in integer) is
select *
from dba_advisor_actions
where task_name = i_task
and rec_id = i_rec_id;
cursor c_objects ( i_task in varchar2, i_obj_id in integer) is
select *
from dba_advisor_objects
where task_name = i_task
and object_id = i_obj_id;
begin
dbms_output.put_line( 'Latest Sets of Recommendations' );
dbms_output.put_line( '------------------------------' || chr(10) );
for i in c_latest_recommends loop
dbms_output.put_line( '' );
dbms_output.put_line( 'Task Name: ' || i.task_name );
dbms_output.put_line( rpad( '-', length( 'Task Name: ' || i.task_name ), '-' ) );
dbms_output.put_line( 'Task Started: ' || to_char( i.execution_start, 'DD-MON-YYYY HH24:MI:SS') );
dbms_output.put_line( '' );
dbms_output.put_line( 'For full details connect as ' || i.owner || ' and run:' );
dbms_output.put_line( '' );
dbms_output.put_line( 'select dbms_advisor.get_task_report(''' || i.task_name || ''', ''TEXT'', ''TYPICAL'' ) from dual;');
dbms_output.put_line( '' );
for j in c_problems( i.task_name ) loop
dbms_output.put_line( ' ' || j.type || ': ' || j.message );
dbms_output.put_line( ' ' || rpad( '-', length( j.type || ': ' || j.message ), '-' ) );
-- Symptoms
dbms_output.put_line( '' );
dbms_output.put_line( ' Symptoms:' );
dbms_output.put_line( ' ---------' );
for k in c_symptoms( i.task_name, j.finding_id) loop
dbms_output.put_line( ' ' || k.message );
end loop;
-- Recommendations
for l in c_recommendations( i.task_name, j.finding_id ) loop
dbms_output.put_line( '' );
dbms_output.put_line( ' Recommendation:' );
dbms_output.put_line( ' ---------------' );
dbms_output.put_line( ' ' || l.type );
-- Actions for recommendation
dbms_output.put_line( '' );
dbms_output.put_line( ' Recommended action:' );
dbms_output.put_line( ' -------------------' );
for m in c_actions( i.task_name, l.rec_id ) loop
dbms_output.put_line( ' ' || m.message );
-- Object attributes
dbms_output.put_line( '' );
dbms_output.put_line( ' Attributes:' );
dbms_output.put_line( ' -------------------' );
for n in c_objects( i.task_name, m.object_id ) loop
dbms_output.put_line( ' ' || n.attr3 || ' ' || n.attr4 || ' ' || n.attr5);
end loop;
end loop;
end loop;
dbms_output.put_line( chr(10) );
end loop;
dbms_output.put_line( chr(10) );
end loop;
end;
/
Example output:
SQL> @addm
ADDM (Automatic Database Diagnostic Monitor) recommendations and suggestions for possible problems
Latest Sets of Recommendations
------------------------------
Task Name: ADDM:2594484505_29821
--------------------------------
Task Started: 01-OCT-2015 23:00:30
For full details connect as SYS and run:
select dbms_advisor.get_task_report('ADDM:2594484505_29821', 'TEXT', 'TYPICAL' ) from dual;
PROBLEM: Individual database segments responsible for significant "User I/O" and "Cluster" waits were found.
------------------------------------------------------------------------------------------------------------
Symptoms:
---------
Wait class "User I/O" was consuming significant database time.
Recommendation:
---------------
Segment Tuning
Recommended action:
-------------------
Run "Segment Advisor" on INDEX "OCEAN.PK_LOCATION_LINKS" with object ID 116737.
Attributes:
-------------------
Investigate application logic involving I/O on INDEX "DEPUTY.ALIAS_INDEX_01" with object ID 116737.
Attributes:
-------------------
Recommendation:
---------------
Segment Tuning
Recommended action:
-------------------
Investigate application logic involving I/O on TABLE "DEPUTY.ALIAS" with object ID 114499.
Attributes:
-------------------
Look at the "Top SQL Statements" finding for SQL statements consuming significant I/O on this segment. For example, the INSERT statement with SQL_ID "8szmfgm7fysa3" is responsible for 100% of "User I/O" and "Cluster" waits for this segment.
Attributes:
-------------------
PROBLEM: PL/SQL execution consumed significant database time.
-------------------------------------------------------------
Symptoms:
---------
Recommendation:
---------------
SQL Tuning
Recommended action:
-------------------
Tune the entry point PL/SQL "SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC" of type "PACKAGE" and ID 7945. Refer to the PL/SQL documentation for addition information.
Attributes:
-------------------
PROBLEM: SQL statements consuming significant database time were found. These statements offer a good opportunity for performance improvement.
----------------------------------------------------------------------------------------------------------------------------------------------
Symptoms:
---------
Recommendation:
---------------
SQL Tuning
Recommended action:
-------------------
Run SQL Tuning Advisor on the INSERT statement with SQL_ID "8szmwam7fysa3".
Attributes:
-------------------
insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'))
Recommendation:
---------------
SQL Tuning
Recommended action:
-------------------
Run SQL Tuning Advisor on the SELECT statement with SQL_ID "7wgks43wrjtrz".
Attributes:
-------------------
SELECT U.SPACE_USED, U.SPACE_ALLOCATED FROM TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF( :B1 , :B2 , :B3 , 0, :B4 , 'TRUE', :B5 )) U
lunes, 6 de enero de 2020
Consultas SQL útiles para obtener información sobre Oracle
- Vista que muestra el estado de la base de datos:
select * from v$instance
- Consulta que muestra si la base de datos está abierta:
select status from v$instance
- Vista que muestra los parámetros generales de Oracle:
select * from v$system_parameter
- Versión de Oracle:
select value from v$system_parameter where name = 'compatible'
- Ubicación y nombre del fichero spfile:
select value from v$system_parameter where name = 'spfile'
- Ubicación y número de ficheros de control:
select value from v$system_parameter where name = 'control_files'
- Nombre de la base de datos
select value from v$system_parameter where name = 'db_name'
- Vista que muestra las conexiones actuales a Oracle:
select osuser, username, machine, program from v$session order by osuser
- Vista que muestra el número de conexiones actuales a Oracle agrupado por aplicación que realiza la conexión
select program Aplicacion, count(program) Numero_Sesiones from v$session group by program order by Numero_Sesiones desc
- Vista que muestra los usuarios de Oracle conectados y el número de sesiones por usuario
select username Usuario_Oracle, count(username) Numero_Sesiones from v$session group by username order by Numero_Sesiones desc
- Propietarios de objetos y número de objetos por propietario
select owner, count(owner) Numero from dba_objects group by owner order by Numero desc
- Diccionario de datos (incluye todas las vistas y tablas de la Base de Datos):
select * from dictionary select table_name from dictionary
- Muestra los datos de una tabla especificada (en este caso todas las tablas que lleven la cadena "EMPLO"):
select * from ALL_ALL_TABLES where upper(table_name) like '%EMPLO%'
- Muestra los disparadores (triggers) de la base de datos Oracle Database:
select * from ALL_TRIGGERS
- Tablas propiedad del usuario actual:
select * from user_tables
- Todos los objetos propiedad del usuario conectado a Oracle:
select * from user_catalog
- Consulta SQL para el DBA de Oracle que muestra los tablespaces, el espacio utilizado, el espacio libre y los ficheros de datos de los mismos:
Select t.tablespace_name "Tablespace", t.status "Estado",
ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",
ROUND((MAX(d.bytes)/1024/1024) -
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",
t.pct_increase "% incremento",
SUBSTR(d.file_name,1,80) "Fichero de datos"
FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC
- Productos Oracle instalados y la versión:
select * from product_component_version
- Roles y privilegios por roles:
select * from role_sys_privs
- Reglas de integridad y columna a la que afectan:
select constraint_name, column_name from sys.all_cons_columns
- Tablas de las que es propietario un usuario, en este caso "HR":
SELECT table_owner, table_name from sys.all_synonyms where table_owner like 'HR'
- Otra forma más efectiva (tablas de las que es propietario un usuario):
SELECT DISTINCT TABLE_NAME FROM ALL_ALL_TABLES WHERE OWNER LIKE 'HR'
- Parámetros de Oracle, valor actual y su descripción:
SELECT v.name, v.value value, decode(ISSYS_MODIFIABLE, 'DEFERRED',
'TRUE', 'FALSE') ISSYS_MODIFIABLE, decode(v.isDefault, 'TRUE', 'YES',
'FALSE', 'NO') "DEFAULT", DECODE(ISSES_MODIFIABLE, 'IMMEDIATE',
'YES','FALSE', 'NO', 'DEFERRED', 'NO', 'YES') SES_MODIFIABLE,
DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES', 'FALSE', 'NO',
'DEFERRED', 'YES','YES') SYS_MODIFIABLE , v.description
FROM V$PARAMETER v
WHERE name not like 'nls%' ORDER BY 1
- Usuarios de Oracle y todos sus datos (fecha de creación, estado, id, nombre, tablespace temporal,...):
Select * FROM dba_users
- Tablespaces y propietarios de los mismos:
select owner, decode(partition_name, null, segment_name, segment_name || ':' || partition_name) name, segment_type, tablespace_name,bytes,initial_extent, next_extent, PCT_INCREASE, extents, max_extents from dba_segments Where 1=1 And extents > 1 order by 9 desc, 3
- Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó:
select distinct vs.sql_text, vs.sharable_mem, vs.persistent_mem, vs.runtime_mem, vs.sorts, vs.executions, vs.parse_calls, vs.module, vs.buffer_gets, vs.disk_reads, vs.version_count, vs.users_opening, vs.loads, to_char(to_date(vs.first_load_time, 'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time, rawtohex(vs.address) address, vs.hash_value hash_value , rows_processed , vs.command_type, vs.parsing_user_id , OPTIMIZER_MODE , au.USERNAME parseuser from v$sqlarea vs , all_users au where (parsing_user_id != 0) AND (au.user_id(+)=vs.parsing_user_id) and (executions >= 1) order by buffer_gets/executions desc
- Todos los ficheros de datos y su ubicación:
select * from V$DATAFILE
- Ficheros temporales:
select * from V$TEMPFILE
- Tablespaces:
select * from V$TABLESPACE
- Otras vistas muy interesantes:
select * from V$BACKUP select * from V$ARCHIVE select * from V$LOG select * from V$LOGFILE select * from V$LOGHIST select * from V$ARCHIVED_LOG select * from V$DATABASE
- Memoria Share_Pool libre y usada:
select name,to_number(value) bytes from v$parameter where name ='shared_pool_size' union all select name,bytes from v$sgastat where pool = 'shared pool' and name = 'free memory'
- Cursores abiertos por usuario:
select b.sid, a.username, b.value Cursores_Abiertos
from v$session a,
v$sesstat b,
v$statname c
where c.name in ('opened cursors current')
and b.statistic# = c.statistic#
and a.sid = b.sid
and a.username is not null
and b.value >0
order by 3
- Aciertos de la caché (no debe superar el 1 por ciento):
select sum(pins) Ejecuciones, sum(reloads) Fallos_cache,
trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos
from v$librarycache
where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');
- Sentencias SQL completas ejecutadas con un texto determinado en el SQL:
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text FROM v$session c, v$sqltext d WHERE c.sql_hash_value = d.hash_value and upper(d.sql_text) like '%WHERE CAMPO LIKE%' ORDER BY c.sid, d.piece
- Una sentencia SQL concreta (filtrado por sid):
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text FROM v$session c, v$sqltext d WHERE c.sql_hash_value = d.hash_value and sid = 105 ORDER BY c.sid, d.piece
- Tamaño ocupado por la base de datos
select sum(BYTES)/1024/1024 MB from DBA_EXTENTS
- Tamaño de los ficheros de datos de la base de datos:
select sum(bytes)/1024/1024 MB from dba_data_files
- Tamaño ocupado por una tabla concreta sin incluir los índices de la misma
select sum(bytes)/1024/1024 MB from user_segments where segment_type='TABLE' and segment_name='NOMBRETABLA'
- Tamaño ocupado por una tabla concreta incluyendo los índices de la misma
select sum(bytes)/1024/1024 Table_Allocation_MB
from user_segments
where segment_type in ('TABLE','INDEX') and
(segment_name='NOMBRETABLA' or segment_name in
(select index_name
from user_indexes
where table_name='NOMBRETABLA'))
- Tamaño ocupado por una columna de una tabla:
select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB
from NOMBRETABLA
- Espacio ocupado por usuario:
SELECT owner, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB GROUP BY owner
- Espacio ocupado por los diferentes segmentos (tablas, índices, undo, rollback, cluster, ...):
SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB GROUP BY SEGMENT_TYPE
- Espacio ocupado por todos los objetos de la base de datos, muestra los objetos que más ocupan primero:
SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB GROUP BY SEGMENT_NAME ORDER BY 2 DESC
- Obtener todas las funciones de Oracle: NVL, ABS, LTRIM, ...:
SELECT distinct object_name FROM all_arguments WHERE package_name = 'STANDARD' order by object_name
- Obtener los roles existentes en Oracle Database:
select * from DBA_ROLES
- Obtener los privilegios otorgados a un rol de Oracle:
select privilege from dba_sys_privs where grantee = 'NOMBRE_ROL'
- Obtener la IP del servidor de la base de datos Oracle Database:
select utl_inaddr.get_host_address IP from dual
- Mostrar datos de auditoría de la base de datos Oracle (inicio y desconexión de sesiones):
select username, action_name, priv_used, returncode from dba_audit_trail
- Comprobar si la auditoría de la base de datos Oracle está activada:
select name, value from v$parameter where name like 'audit_trail'
Aplicar PARCHE en EXADATA post UPDATE.
Cuando administramos un Oracle Exadata Machine es importante encontrarnos con el roadmap actualizado de la fixes de seguridad, patchsets, etc.
Como parte de estas tareas que nos previenen de bugs y otras incidencias, como así también poder migrar los motores de bases de datos, es que decidimos hacer un upgrade poder llevar a la ultima release de la versión de GridInfra Structure, realizando el upgrade a 12.1.0.2 y decidimos hacer este trabajo en modo rolling :
Verificamos la release actual:
[oragrid@exa2adbadm01 ~]$ crsctl query crs activeversion Oracle Clusterware active version on the cluster is [12.1.0.0.0]
Stopeamos los servicios del nodo del Cluster:
Run the pre root script. As the root user execute: # /crs/install/rootcrs.pl -unlock
Empezamos con el parche:
/u01/app/grid/12.1.0.2/OPatch/opatch napply -oh /u01/app/grid/12.1.0.2 -local -silent /u01/patches/BP/26610322/26609798 /u01/app/grid/12.1.0.2/OPatch/opatch napply -oh /u01/app/grid/12.1.0.2 -local -silent /u01/patches/BP/26610322/26609945 /u01/app/grid/12.1.0.2/OPatch/opatch napply -oh /u01/app/grid/12.1.0.2 -local -silent /u01/patches/BP/26610322/25869830 /u01/app/grid/12.1.0.2/OPatch/opatch napply -oh /u01/app/grid/12.1.0.2 -local -silent /u01/patches/BP/26610322/21436941 /u01/app/grid/12.1.0.2/OPatch/opatch napply -oh /u01/app/grid/12.1.0.2 -local -silent /u01/patches/ONEOFF/23727148 exit
Ahora subimos el cluster en modo patch
As the root user execute: # /rdbms/install/rootadd_rdbms.sh # /crs/install/rootcrs.pl -patch
Al utilizar el wildcard -patch, lo que ocurre de forma automatica los siguientes pasos:
- Aplica , relinkea el uso de las librerias nuevas aplicadas con el patch set.
- Sube el cluster en modo patch para buscar/verificar inconsistencias.
- Baja el Cluster.
- Sube el Cluster en modo normal con el parche aplicado.
Verificamos la versión y podemos continuar con la mismo proceso en el segundo nodo.
Ahora estamos actualizados en la release correspondiente:
[oragrid@exa2adbadm01 ~]$ crsctl query crs activeversion Oracle Clusterware active version on the cluster is [12.1.0.2.0]
Revisamos que los servicios de Cluster esten up and running:
[oragrid@exa2adbadm01 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA_EXA2A.dg
ONLINE ONLINE exa2adbadm01 STABLE
ONLINE ONLINE exa2adbadm02 STABLE
ora.DBFS_DG.dg
ONLINE ONLINE exa2adbadm01 STABLE
ONLINE ONLINE exa2adbadm02 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE exa2adbadm01 STABLE
ONLINE ONLINE exa2adbadm02 STABLE
ora.RECO_EXA2A.dg
ONLINE ONLINE exa2adbadm01 STABLE
ONLINE ONLINE exa2adbadm02 STABLE
ora.asm
ONLINE ONLINE exa2adbadm01 Started,STABLE
ONLINE ONLINE exa2adbadm02 Started,STABLE
ora.net1.network
ONLINE ONLINE exa2adbadm01 STABLE
ONLINE ONLINE exa2adbadm02 STABLE
ora.ons
ONLINE ONLINE exa2adbadm01 STABLE
ONLINE ONLINE exa2adbadm02 STABLE
Muchas Gracias
CONSULTA LA VERSIÓN DE PARCHES APLICADOS (PSU)
Se han preguntado si su base de datos tiene algún parche aplicado y desean saber en que versión esta, pues bien aqui les dejo el Query que les permitira saber cuando y que parche fue aplicado en su Base de Datos Oracle.
Patch Set Updates (PSU) son los mismos parches acumulativos que incluyen tanto los parches de seguridad y correcciones prioritarias. La clave con la PSU es que son actualizaciones menores de versión (por ejemplo, 11.2.0.1.1 a 11.2.0.1.2). Una vez que se aplica una fuente de alimentación, sólo las UPM se puede aplicar en los próximos trimestres hasta que la base de datos se actualiza a una nueva versión de la base.
La consulta para obtener la información de los parches aplicados seria:
La consulta para obtener la información de los parches aplicados seria:
SQL> set lines 255
SQL> col action_time for a40
SQL> col action for a8
SQL> col namespace for a7
SQL> col version for a11
SQL> col comments for a40
SQL> select substr(ACTION_TIME,1,40) action_time,substr(ACTION,1,8) action,substr(NAMESPACE,1,7) namespace,
substr(VERSION,1,11) version,ID,substr(COMMENTS,1,40) comments,BUNDLE_SERIES from registry$history;
SQL> col action_time for a40
SQL> col action for a8
SQL> col namespace for a7
SQL> col version for a11
SQL> col comments for a40
SQL> select substr(ACTION_TIME,1,40) action_time,substr(ACTION,1,8) action,substr(NAMESPACE,1,7) namespace,
substr(VERSION,1,11) version,ID,substr(COMMENTS,1,40) comments,BUNDLE_SERIES from registry$history;
Espero les sirva.
Suscribirse a:
Entradas (Atom)