DBMS_SQLTUNE – Profile

References:

https://docs.oracle.com/database/121/TGSQL/tgsql_profiles.htm#TGSQL612

1.) Identify the SQL_IF of your command, you can use the V$SQL for example:
SQL_ID: 0th9yu2b4ad7k

2.) Create a Tuning Task: DBMS_SQLTUNE.CREATE_TUNING_TASK
3.) Set some specific Parameters , if needed, there are some cases where 
the command can take too much time, so you need to increase the time for analysis.
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => stmt_task, 
parameter => 'TIME_LIMIT', value => 15000);

4.) Execute the Tuning Task:
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => stmt_task);

The anonymous block below will execute all steps:
SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '0th9yu2b4ad7k');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => stmt_task, parameter => 'TIME_LIMIT', value => 15000);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => stmt_task);
end;
/

This command will return the NAME of the task: TASK_9931
5.) After the execution, you need to check the outcome of the analysis:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_9931') AS recommendations FROM dual;

6.) If you agree with the suggestion, you can apply the PROFILE:
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_9931', 
task_owner => 'SYS', replace => TRUE);

7.) If you want to disable the PROFILE:
You can find the name on this view DBA_SQLTUNE_PLANS
begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('<NAME>','STATUS','DISABLED');
end;
/

8.) If you want to DROP the PROFILE:
BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE (  name => '<NAME>' );
END;
/

 

Advertisements

Check PSU Applied using dbms_qopatch

New in Oracle 12c

Reference:

https://www.pythian.com/blog/oracle-database-12c-patching-dbms_qopatch-opatch_xml_inv-and-datapatch/
https://blogs.oracle.com/UPGRADE/entry/dbms_qopatch_datapatch_and_other
http://docs.oracle.com/database/121/ARPLS/d_qopatch.htm#ARPLS74834

-- Checking PSU Applied using dbms_qopatch

exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 23054246
        Action : APPLY
        Action Time : 17-NOV-2016 16:57:34
        Description : Database Patch Set Update : 12.1.0.2.160719 (23054246)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/23054246/20464632/23054246_apply_DB01_2016Nov17_16_57_29.log
        Status : SUCCESS

PL/SQL procedure successfully completed.


--- Home and Inventory

set pagesize 0
set long 1000000

select xmltransform(dbms_qopatch.get_opatch_install_info, 
dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;

Oracle Home       : /u01/oracle/products/12.1.0/dbhome_1
Inventory         : /u01/oraInventory


1 row selected.
--- Specific Patch

select xmltransform(dbms_qopatch.is_patch_installed('21359755'), 
dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;

Patch Information:
         21359755:   applied on 2016-10-26T11:18:57+02:00


1 row selected.

--- All Details:

select xmltransform(dbms_qopatch.get_opatch_lsinventory, 
dbms_qopatch.get_opatch_xslt) from dual;

Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------
Oracle Home       : /u01/oracle/products/12.1.0/dbhome_1
Inventory         : /u01/oraInventory
--------------------------------------------------------------------------------
Installed Top-level Products (1):
                                    12.1.0.2.0
Installed Products ( 135)

                                                            12.1.0.2.0
                                                            1.6.0.75.0
                                                            12.1.0.2.0
                                                            12.1.0.2.0
                                                            12.1.0.1.2
                                                            12.1.0.2.0
...

Oracle Official Script to check Dataguard

Reference: Script to Collect Data Guard Physical and Active Standby 
Diagnostic Information for Version 10g and above (Including RAC) 
(Doc ID 1577406.1)
------------------------------------------------------------------------------
-- NAME: new_dg_psby_diag.sql
--
-- Copyright 2002, Oracle Corporation
--       
-- LAST UPDATED: 02-Sep-2015
--
-- Usage: @new_dg_psby_diag
-- 
-- (Run from sqlplus on PHYSICAL STANDBY, ACTIVE STANDBY as SYS)
-- 
-- PURPOSE:
--  
--  This script is to be used to assist in the collection of information to help
--  troubleshoot Data Guard issues involving a Physical or Active Standby.
--  
-- DISCLAIMER:
--  
--  This script is provided for educational purposes only. It is NOT   
--  supported by Oracle World Wide Technical Support.  
--  The script has been tested and appears to work as intended.  
--  You should always run new scripts on a test instance initially.  
--
--
-- Script output is as follows:
 
set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT TO_CHAR(sysdate,'yyyymmdd_hh24mi') timecol, '.html' spool_extension FROM dual;
column output new_value dbname
SELECT value || '_' output FROM v$parameter WHERE name = 'db_unique_name';
spool new_dg_psby_diag_&&dbname&&timestamp&&suffix
set linesize 2000
set pagesize 50000
set numformat 999999999999999
set trim on
set trims on
set markup html on
set markup html entmap off
set feedback on

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
SELECT TO_CHAR(sysdate) time FROM dual;

set echo on


-- The following select will give us the generic information about how this standby is setup.
-- The DATABASE_ROLE should be STANDBY as that is what this script is intended to be run on.
-- PLATFORM_ID should match the PLATFORM_ID of the primary or conform to the supported options in
-- Note: 413484.1 Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration.
-- FLASHBACK can be YES (recommended) or NO.
-- If PROTECTION_LEVEL is different from PROTECTION_MODE then for some reason the mode listed in PROTECTION_MODE experienced a need to downgrade.
-- Once the error condition has been corrected the PROTECTION_LEVEL should match the PROTECTION_MODE after the next log switch.

SELECT database_role role, name,dbid, db_unique_name, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;

-- FORCE_LOGGING is not mandatory but is recommended.
-- REMOTE_ARCHIVE should be ENABLE.
-- SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI must be enabled if this standby is associated with a primary that has a logical standby.
-- During normal operations it is acceptable for SWITCHOVER_STATUS to be NOT ALLOWED.
-- DATAGUARD_BROKER can be ENABLED (recommended) or DISABLED.
 
column force_logging format a13 tru
column supplemental_log_data_pk format a24 tru
column supplemental_log_data_ui format a24 tru

SELECT force_logging, remote_archive, supplemental_log_data_pk, supplemental_log_data_ui, switchover_status, dataguard_broker  FROM v$database;  

-- Check how many threads are enabled and started for this database. If the number of instances below does not match then not all instances are up.

SELECT thread#, instance, status FROM v$thread;

-- The number of instances returned below is the number currently running.  If it does not match the number returned in Threads above then not all instances are up.
-- VERSION should match the version from the primary database.
-- ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the archiver failed to archive a log last time, but will try again within 5 minutes.
-- LOG_SWITCH_WAIT the ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for.
-- Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online redo log, then the value is NULL.

column host_name format a32 wrap

SELECT thread#, instance_name, host_name, version, archiver, log_switch_wait FROM gv$instance ORDER BY thread#;
 
-- Check the number and size of online redo logs on each thread.

SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;

-- The following query is run to see if standby redo logs have been created.
-- The standby redo logs should be the same size as the online redo logs.
-- There should be (( # of online logs per thread + 1) * # of threads) standby redo logs.
-- A value of 0 for the thread# means the log has never been allocated.

SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;

-- This query produces a list of defined archive destinations.
-- It shows if they are enabled, what process is servicing that destination, if the destination is local or remote, and if remote what the current mount ID is.
-- For a physical standby we should have at least one remote destination that points the primary set.
 
column destination format a35 wrap
column process format a7
column ID format 99
column mid format 99
 
SELECT thread#, dest_id, destination, gvad.status, target, schedule, process, mountid mid FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;
 
-- If the protection mode of the standby is set to anything higher than max performance then we need to make sure the remote destination that points to the primary is set with the correct options else we will have issues during switchover.
 
set numwidth 8
column archiver format a8
column ID format 99
column error format a55 wrap

SELECT thread#, dest_id, gvad.archiver, transmit_mode, affirm, async_blocks, net_timeout, delay_mins, reopen_secs reopen, register, binding FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;
 
-- The following select will show any errors that occured the last time an attempt to archive to the destination was attempted.
-- If ERROR is blank and status is VALID then the archive completed correctly.

SELECT thread#, dest_id, gvad.status, error FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;
 
-- The query below will determine if any error conditions have been reached by querying the v$dataguard_status view (view only available in 9.2.0 and above).
 
column message format a80

SELECT timestamp, gvi.thread#, message FROM gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal') ORDER BY timestamp, thread#;
 
-- Query gv$managed_standby to see the status of processes involved in the shipping redo on this system.
-- Does not include processes needed to apply redo.
 
SELECT thread#, process, pid, status, client_process, client_pid, sequence#, block#, active_agents, known_agents FROM gv$managed_standby ORDER BY thread#, process;

-- Verify the last sequence# received and the last sequence# applied to standby database.

SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;

-- Check the transport lag and apply lag from the V$DATAGUARD_STATS view.  This is only relevant when LGWR log transport and real time apply are in use.

SELECT * FROM v$dataguard_stats WHERE name LIKE '%lag%';

-- Check how often and how far the apply lags.

SELECT name, time, unit, count, TO_DATE(last_time_updated, 'MM/DD/YYYY HH24:MI:SS') FROM v$standby_event_histogram ORDER BY unit DESC, time;

-- The V$ARCHIVE_GAP fixed view on a physical standby database only returns the next gap that is currently blocking redo apply from continuing.
-- After resolving the identified gap and starting redo apply, query the V$ARCHIVE_GAP fixed view again on the physical standby database to determine the next gap sequence, if there is one.

SELECT * FROM v$archive_gap;

-- Non-default init parameters.
-- For a RAC DB Thread# = * means the value is the same for all threads (SID=*)
-- Threads with different values are shown with their individual thread# and values.

column num noprint

SELECT num, '*' "THREAD#", name, value FROM v$PARAMETER WHERE NUM IN (SELECT num FROM v$parameter WHERE (isdefault = 'FALSE' OR ismodified <> 'FALSE') AND name NOT LIKE 'nls%'
MINUS
SELECT num FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND  gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvpa.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id  AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%')
UNION
SELECT num, TO_CHAR(thread#) "THREAD#", name, value FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id  AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%' ORDER BY 1, 2;

spool off
set markup html off entmap on
set echo on

Oracle Direct NFS – Database 12c

On this post i will show you how to enable the database to use Direct NFS 
over Infiniband.
References:
https://docs.oracle.com/cd/E51475_01/html/E52872/integration__ssc__configure_appliance__configuring_oracle_direct_nfs_dnfs.html
Oracle ZFS Storage Appliance: How to Enable Oracle Intelligent Storage Protocol (OISP) (Doc ID 1943618.1)
Direct NFS monitoring and v$views (Doc ID 1495739.1)

**** BEFORE START:
----> SHUTDOWN THE INSTANCE(S)/LISTENERS UNDER THE ORACLE_HOME <-----


root@sun-prod02:~# ping -a pathA-ib ---> (This is the HOSTNAME or IP Address for the InfiniBand PATH)
pathA-ib (192.168.255.10) is alive

 
1.) Re-Link the binary to use DNFS
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk dnfs_on
 
2.) Configure the file: oranfstab
Note: The export parameters should reflect the mount points similar to the entries 
created in /etc/vfstab. The entries should look similar to the following

cd $ORACLE_HOME/dbs
 
-bash-4.1$ cat oranfstab

server:pathA-ib
local:192.168.255.27
path:192.168.255.10
nfs_version:nfsv4
export:/export/sun-prod02/oracle mount:/oracle
export:/export/sun-prod02/export mount:/export
export:/export/sun-prod02/fast_recovery_area mount:/fast_recovery_area
export:/export/sun-prod02/oradata mount:/oradata
export:/export/sun-prod02/oralog mount:/oralog
management:192.168.255.10
community:comm0

3.) Restart the database
srvctl start database -d <DB Name>

4.) Check the Alert Log:

grep -i '"Sun ZFS"|Direct' alert_*.log
grep -i "Direct NFS:" alert_sun2.log

Thu Jan 12 14:15:44 2017
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0

Direct NFS: channel id [0] path [192.168.255.10] to filer [pathA-ib] via local 
[192.168.255.27] is UP
Thu Jan 12 14:16:45 2017
Direct NFS: channel id [0] path [192.168.255.10] to filer [pathA-ib] via local 
[192.168.255.27] is UP
Thu Jan 12 14:16:45 2017
Direct NFS: channel id [0] path [192.168.255.10] to filer [pathA-ib] via local 
[192.168.255.27] is UP

5.) You can check inside of DB also using the views below:

Monitoring:

v$dnfs_servers - Displays information about the NFS servers accessed by Direct NFS.
v$dnfs_files - Displays information about the Oracle files opened using Direct NFS.
v$dnfs_channels - Displays information about the network connections (channels) 
open to the NFS servers.
v$dnfs_stats - Displays statistics of the different NFS operations issued by 
Direct NFS (dNFS does not expose statistics per mount point, but rather per 
process).

RMAN Restore Database

-- Before start
get the info on the ALERT.LOG regarding last TIMESTAMP valid, 
the last time before the crash;

Example:

Wed Jan 04 15:28:43 2017
Thread 1 advanced to log sequence 8725 (LGWR switch)
  Current log# 3 seq# 8725 mem# 0: /u01/oradata/dbtst/redo03.log
Wed Jan 04 15:28:43 2017 <------------------------------------------------------------ * THIS ON THIS CASE
Archived Log entry 13322 added for thread 1 sequence 8724 ID 0xee867f48 dest 1:
Wed Jan 04 15:43:40 2017
Errors in file /u01/oracle/diag/rdbms/dbtst/dbtst/trace/dbtst_m002_10209.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/dbtst/redo01.log'
ORA-27037: unable to obtain file status
/15:43:40 2017 


1.)
rman target /

list backup of controlfile:

(take the last one)

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8421    Full    14.92M     DISK        00:00:01     04-JAN-17
        BP Key: 8421   Status: AVAILABLE  Compressed: NO  Tag: TAG20170104T051133
        Piece Name: /oracle_backups/ITT/dbtst_ctl_s_DBTST932361094u5rp5cs6_1_1
  Standby Control File Included: Ckp SCN: 60656341385   Ckp time: 04-JAN-17


2.)
Controlfile Location:

sqlplus / as sysdba

show parameter control;

/u01/oradata/dbtst/control01.ctl, /u01/oradata/dbtst/control02.ctl

3.) Check if still have the controlfiles:

[oracle@lnx01/ SID : dbtst]$ ls -ltr
total 45744
-rw-r----- 1 oracle oinstall 15613952 Jan  4 13:48 snapcf_dbtst.ctl
-rw-r----- 1 oracle oinstall 15613952 Jan  4 16:26 control02.ctl
-rw-r----- 1 oracle oinstall 15613952 Jan  4 16:26 control01.ctl

4.) Try first do the restore using the existent controlfile, 
if doesn't have anymore the files you need use the backup on the STEP 1, 
to restore the latest controlfile before start
the restore database operation;

JUST in case of you don't have any more the CONTROLFILES execute the step below:

rman target /

startup nomount;

RESTORE CONTROLFILE FROM "/oracle_backups/ITT/dbtst_ctl_s_DBTST932361094u5rp5cs6_1_1"; 

If the CONTROLFILES are in place, proceed like below:

rman target /
startup mount;

run
{
set until time "to_date('04-JAN-2017:15:28:43', 'DD-MON-YYYY:HH24:MI:SS')" ; 
restore database;
recover database;
}

RMAN> alter database open resetlogs;

Statement processed