procedure to drop empty partitions

set serveroutput on
declare
v_count number(4);
Var_sql varchar2(1000);
Var_sql1 varchar2(1000);
BEGIN
  FOR item IN
  ( SELECT partition_name
from dba_tab_partitions
where partition_name in ('P20061412_7','P20061412_5')
and table_name='SC_RAW'
and table_owner='RRS' )
  LOOP
var_sql:='select count(1)  from rrs.SC_RAW partition(' || item.partition_name || ')';
execute immediate var_sql into v_count;
dbms_output.put_line(var_sql);
dbms_output.put_line(v_count);
        if v_count=0
then
dbms_output.put_line('dropping this partition');
var_sql1:='alter table rrs.sc_raw drop partition '||item.partition_name ;
dbms_output.put_line(var_sql1);
execute immediate var_sql1;
END IF;
   END LOOP;
END;
/

Technical Explanation is included below 

Usually  CELLSRV observes that the service time on the disk increases, its proactively marked as confinedOffline.  This is a first phase of detection of poor performance on the disk. At this stage, we see the alert being generated. After this, the disk is ready for further tests.      Now MS runs the performance tests on the CELLDISK. If it turns out that the disk is performing well, MS will notify CELLSRV that the disk is fine and CELLSRV will notify ASM to put the grid disks ONLINE.   If it comes out that the performance is bad in the tests, the disk status is changed to proactive failure.    Now the GRIDDISK  are dropped from ASM. Then the status of the disk is set to predictive failure.   If MS has indicated that the disk is performing well and hence is set back to normal and a notification for the alert clearance is updated in alert history.   This is all automated operation and no interference is required. 

As the alert is cleared and disk status changed to NORMAL , there is no further action required on this disk.  However in your case as indicated by Travis yesterday this frequently happening, so this need to go to Oracle Support immediately. 

Do you have the full rman log? To restore a cold backup you have to use the noredo.

restore database;
recover database noredo;



1. I need additional information to analyze and troubleshoot your issue.


You can copy and paste into sqlplus the complete script and it will create
results01.txt. I will review it and see what our options are.

Note: Do this as the SYSDBA.

REM # Please run the following SQL statements, as shown below, via SQL*Plus,
REM # and then upload the "results01.txt" for further analysis. You can run
REM # these statements while the database is at least mounted. Cut and paste
REM # the script into sqlplus.
REM ##########################################################################
REM

spool results01.txt
set echo on feedback on
set pagesize 50
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select * from v$version;
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as current_date from dual;
column name format a10
select dbid, name,
to_char(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode, log_mode,
to_char(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
to_char(controlfile_change#, '999999999999999') as controlfile_change#,
to_char(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time
from v$database;

select * from v$instance;

SELECT to_char(Current_scn, '9999999999999999') FROM V$DATABASE;

select to_char(checkpoint_change#, '999999999999999') as ck_chg from v$database;

select min(fhscn) from x$kcvfh;


Select min(fhafs) "minvalue" ,max(fhafs) "max value" from x$kcvfh;

select max(to_number(fhafs)) "Minimum PITR SCN", min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

archive log list;


select SEQUENCE#, to_char(FIRST_CHANGE#,'999999999999999') as CHK_CHANGE, group#, archived, status,thread#
from v$log order by first_change#;


column member format a45
select group#, status,type,member from v$logfile;

column name format a79
select name, file#, status, enabled, creation_change#,
to_char(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time, to_char(checkpoint_change#, '999999999999999') as checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, to_char(offline_change#, '999999999999999') as offline_change#, to_char(online_change#, '999999999999999') as online_change#, to_char(online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time, to_char(bytes, '9,999,999,999,990') as bytes
from v$datafile
where status <> 'ONLINE'
or checkpoint_change# <> (select checkpoint_change# from v$database);


select distinct to_char(checkpoint_change#, '9999999999999999') from v$datafile;

select distinct(status)from v$datafile;
select FILE#,TS# , status, NAME from v$datafile
where status not in ('SYSTEM','ONLINE')
order by 1;

select distinct (to_char(checkpoint_change#,'9999999999999999')) from v$datafile_header;

REM #
REM # fuzzy (fhsta=4 for example).
REM # Status to open the database is fhsta=0 (except system datafile(s) may be something like 8192)
REM # status 4 which means "online fuzzy" this datafile should be restored again.
REM # You can only cancel recovery once all datafiles are at status 0 using this query
REM# Absolutely FuZzy in Status 16 / 64 / 80 and the database will not open
REM# Status 16 - Media recovery fuzzy
REM# Status 64 - Absolute Fuzzy If you see status 64 redo the recover and apply
REM# the next log, keep going until there are no status 64.


REM# Status 80 - Media fuzzy & Absolute.
REM #Summarize the fhsta information from above in case query above returns hundreds of rows.

select distinct FHSTA from X$KCVFH;

REM#You may run the following query to determine the sequence# of the archivelogs needed for the initial recovery.

select distinct FHRBA_SEQ Sequence, FHTHR Thread from X$KCVFH
order by FHTHR, FHRBA_SEQ;


select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

REM #
REM#

select to_char(hxfil,'99999999999') as FILE#,fhsta STAT,fhscn SCN,
fhthr thrd, fhrba_Seq SEQ,fhtnm T_SPACE
from x$kcvfh order by 1;


select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity, FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;


select df.file#, to_char(df.checkpoint_change#, '9999999999999999') controlfile_change,
to_char(dh.checkpoint_change# , '9999999999999999') datafile_change, df.status
from v$datafile df, v$datafile_header dh
where df.file# = dh.file#;

select a.recid, a.thread#, a.sequence#, a.name, a.archived, a.deleted,
to_date(a.completion_time, 'DD-MON-YYYY HH24:MI:SS') as completed
from v$archived_log a, v$log l
where a.thread# = l.thread#
and a.sequence# = l.sequence#;

REM # If the query return 1 row with STATUS='ONLINE', FUZZY='NO', and similar CHECKPOINT_TIMEthen you REM # can proceed to open the database.

set echo on feedback on time on timing on pagesize 100 linesize 80 numwidth 13
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select error, fuzzy, status, checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by error, fuzzy, status, checkpoint_change#,checkpoint_time
order by checkpoint_change#, checkpoint_time;


select * from v$recovery_log;

select file#, to_char(change#, '9999999999999999') from v$recover_file;

rem# Use the third value: RECOVER DATABASE UNTIL SCN xxxxxxxxxx;

select min(FHSCN) "LOW FILEHDR SCN", max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN"
from X$KCVFH ;


SELECT f.name, f.file#, b.status, to_char(b.change#, '9999999999999999'), b.time
FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';

SELECT status,
checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MM-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;

SELECT fhthr thread,
fhrba_seq sequence,
fhscn scn,
fhsta status,
count(*)
FROM x$kcvfh
group by fhthr,fhrba_seq,fhscn,fhsta;


spool off
REM
REM ##########################################################################
REM # Note:
REM # 1. The "results01.txt" will help me analyze the latest status of
REM # the datafiles and controlfile, particularly their checkpoint change#.



---- Upload results01.txt

---- Upload the alert log

3. Do the following so we will know your RMAN configuration.

To get the right time of the backup you have to make sure NLS date is set.

If using csh

setenv NLS_DATE_FORMAT 'DD-MON-YYYY HH24:MI:SS'

If using ksh use:

export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

For Windows:

sql 'alter session set NLS_DATE_FORMAT = "Mon dd yyyy hh24:mi:ss"';
set echo on;


crosscheck backup of database;
crosscheck backup of archivelog all;

spool log to rman_report.log;
set echo on;
show all;

list incarnation of database;

list backup summary;

list backup of database archivelog all;

list backup of controlfile;

list archivelog all;

report schema;

list copy of database;

restore preview database;

-- Upload rman_report.log  

Purpose 
-------

To provide Information on time sharing scheduling policy on HP and how Oracle 
makes use of the SCHED_NOAGE scheduling policy.
 
SCOPE & APPLICATION
-------------------

This note is intended for DBAs/ UNIX Admin to improve performance in OLTP.


HP SCHED_NOAGE Scheduling Policy
--------------------------------
On HP, most processes use a time sharing scheduling policy. Time sharing can 
have detrimental effects on Oracle performance by descheduling an Oracle process
during critical operations, for example, holding a latch. HP has a 
modified scheduling policy, referred to as SCHED_NOAGE, that specifically  
addresses this issue. Unlike the normal time sharing policy, a process scheduled
using SCHED_NOAGE does not increase or decrease in priority, nor is it preempted.

This feature is suited to online transaction processing (OLTP) environments 
because OLTP environments can cause competition for critical resources.  
In laboratory tests, the use of the SCHED_NOAGE policy with Oracle9i increased 
performance by up to 10 percent in OLTP environments. The SCHED_NOAGE policy 
creates little or no gains in decision support (DSS) Environments because 
there is little resource competition in these environments. Because each 
application and server environment is different, you should test and verify 
whether your environment benefits from the SCHED_NOAGE policy.


Enabling SCHED_NOAGE for Oracle
-------------------------------


To allow Oracle to use the SCHED_NOAGE scheduling policy, the group that the 
Oracle software owner belongs to (dba), must have the RTSCHED and RTPRIO
privileges to change the scheduling policy and set the priority level for Oracle 
processes. 


To give the dba group these privileges: 

1.As the root user, enter the following command: 

# setprivgrp dba RTSCHED RTPRIO 


2.To retain these privileges after rebooting, create the /etc/privgroup file, if 
it does not exist on your system, and add the following line to it: 

dba RTSCHED RTPRIO

___________________________________________________________________
Note:
Please remember to set the MLOCK privilege as well when using
raw devices.
___________________________________________________________________

3. The Oracle DBA must add the parameter HPUX_SCHED_NOAGE to the INIT.ORA file 
to enable the scheduling policy. HPUX_SCHED_NOAGE takes an integer parameter, 
and for HPUX 11.0 the valid ranges are 154 to 255; for HPUX 11i, the valid 
ranges are 178 to 255. Higher priorities are represented by lower values.

hpux_sched_noage=154 (for HP/UX 11.0)
hpux_sched_noage=178 (for HP/UX 11i)

Overall performance improvements of 5 to 10% especially for OLTP applications
can be expected.

References:
Note:217990.1 Init.ora Parameter  "HPUX_SCHED_NOAGE"  Reference
The HP documentation, rtsched(1) and rtsched(2) man page for more information 
on priority policies and priority ranges.

backup oracle home and orainventory

tar cvf – .|gzip -c > /mars/oracle/MRSP/arch/oracle_home_`hostname`_`date +%Y%m%d`.tar.gz

cd /opt/oracle/oraInventory

tar cvf – .|gzip -c > /mars/oracle/MRSP/arch/oracle_oraInventory_`hostname`_`date +%Y%m%d`.tar.gz

How to Force OMS to use FQDN

Situation : My OMS and the database server which I was trying to add Databases and listener from were in different domain.

When OMS was trying to connect to database it filed with below error message.

Looking at the error message , it looks like OMS is not able connect because it use host name without the domain name.

We tried many ways to force OMS to FQDN , tried adding name in host file, added domain name in resolve.conf etc


Solution: But one method which resolved the issue is adding the fully qualified domain name in host section on listener.ora of host where database is running.  

Oracle Redaction commands

to check the redacted columns
--------------------------------------------------------------
select * from redaction_columns;

to check the redaction policies
--------------------------------------------------------------
select * from redaction_policies;



enable a policy
----------------------------------------------------------------
BEGIN
  SYS.dbms_redact.enable_policy (
   object_schema => 'SCHEMA_NAME',
    object_name   => 'T__MEMBER',
    policy_name   => 'T__MEMBER_REDACT'  
    );
END;
/

disable a policy
----------------------------------------------------------------
BEGIN
  SYS.dbms_redact.disable_policy (
   object_schema => 'SCHEMA_NAME',
    object_name   => 'T__MEMBER',
    policy_name   => 'T__MEMBER_REDACT'  
    );
END;
/


add a column to policy
-----------------------------------------------------------------
BEGIN
  DBMS_REDACT.alter_policy (
   object_schema => 'SCHEMA_NAME',
    object_name   => 'T__ADDRESS',
    policy_name   => 'T__ADDRESS_REDACT',
    action        => DBMS_REDACT.add_column,
    column_name   => 'ZIP',
 function_type => DBMS_REDACT.full
    );
END;
/


drop a column from policy
------------------------------------------------------------------
BEGIN
  DBMS_REDACT.alter_policy (
   object_schema => 'SCHEMA_NAME',
    object_name   => 'T__MEMBER',
    policy_name   => 'T__MEMBER_REDACT',
    action        => DBMS_REDACT.drop_column,
    column_name   => 'LAST_NAME',
 function_type => DBMS_REDACT.full
    );
END;
/

ORA-28046: Password change for SYS disallowed

dbhost:dbname> alter user sys identified by "new_password";
alter user sys identified by "new_password"
*
ERROR at line 1:
ORA-28046: Password change for SYS disallowed



dbhost:dbname> show parameter remote

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string
remote_login_passwordfile            string      SHARED
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
result_cache_remote_expiration       integer     0

dbhost:dbname> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive scope=spfile;

System altered.

dbhost:dbname> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
dbhost:dbname> startup;
ORACLE instance started.

Total System Global Area 2155233280 bytes
Fixed Size                  2161064 bytes
Variable Size            1442842200 bytes
Database Buffers          704643072 bytes
Redo Buffers                5586944 bytes
Database mounted.
Database opened.
dbhost:dbname> show parameter remote

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
result_cache_remote_expiration       integer     0
dbhost:dbname> alter user sys identified by "new_password";

User altered.

How the database Target is named in EM12c

• If using the parameter 'db_unique_name' (for example, if using Data Guard) -  the discovered database name will be: db_unique_name.db_domain (if the parameter db_domain is not set, then just db_unique_name will be used)
• If not using db_unique_name, the format db_name.db_domain will be used (if the parameter db_domain is not set, then just the db_name will be used)
• If the names found in the discovery are not unique, a version number will be appended to the name. eg. orcl_1
• If the spfile<sid>.ora cannot be read (or cannot be converted to a pfile) the database target will assume the name <sid>_<hostname>
• If the database is discovered with the incorrect case, see Note:2024374.1"Enterprise Manager 12c discovers the Database Target name in Upper Case"