12.2 Scheduler Job Disappeared After Creation

Hi all,
Just sharing an experience here. Recently in a migrated 12.2 environment I created a Scheduler Job and it simply disappeared. How come?
Well, seems since 12.2 the AUTO_DROP attribute is set to true by default. I absolutely don’t know why. So I had to recreate the job and set auto_drop to false, simple like this.

I couldn’t find any related bug on MOS, but I’d relate this as a bug, once the default behavior have changed with no prior information.
So, now on, I strongly suggest you to check on your job AUTO_DROP attribute after job creation.

Here is a test case that reproduces the problem:

In a 12.2 database:

begin 
  DBMS_SCHEDULER.create_job (
    job_name        => 'TEST_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN null; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'SYSTIMESTAMP + INTERVAL ''10'' SECOND',
    end_date        => NULL,
    enabled         => false,
    comments        => 'Test job');
end;
/
select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB';
-- Job should appear here

begin 
  dbms_scheduler.enable('TEST_JOB'); 
  dbms_scheduler.run_job('TEST_JOB',false); 
end;
/
 
select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB';

-- There is no job now in 12.2.

You can take the AUTO_DROP easily from the query above, or using getting the job_ddl and you’ll see the AUTO_DROP set to true, as in the bottom of this page by Tim Hall.

Hope it helps!
Cheers!

ORA-00600 [ipc_recreate_que_2]

Hi all,
So this week I started receiving this error in a client environment. This was happening due Bug 26803191 – Getting ORA-00600 [ipc_recreate_que_2] instead of ORA-27515 – superseded (Doc ID 26803191.8)
In summary, failures due to low memlock limit are leading ORA-00600 [ipc_recreate_que_2] instead of ORA-27515, patching ensure that ORA-27515 is raised on failures due to low memlock limit instead of this ORA-600.

As workaround, the memlock limited can be changed, as also described in MOS ORA-00600: Internal Error Code, Arguments: [ipc_recreate_que_2] When Running Exachk (Doc ID 2480088.1).

Before:

[boesing@exa1dbadm01 trace]$ grep memlock /etc/security/limits.conf |grep oracle
# - memlock - max locked-in-memory address space (KB)
oracle soft memlock 237809520
oracle hard memlock 237809520

After:

[boesing@exa1dbadm01 ~]$ grep memlock /etc/security/limits.conf
# - memlock - max locked-in-memory address space (KB)
oracle soft memlock unlimited
oracle hard memlock unlimited
* soft memlock 32768
* hard memlock 32768

Hope it helps,
Cheers!

ORA-00600: internal error code, arguments: [13011], [7907] on SYS.DBMS_AQ_INV

Hi all,

So a couple weeks ago I was facing the following on a client environment. It was appearing every 10 mins on alert.log:

ORA-00600: internal error code, arguments: [13011], [7907], [12679954], [1], [13461738], [0], [], [], [], [], [], []
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQ_INV", line 1248

I could find several references to ORA-600 [13011], always related to some internal corruption. As this table is related to Advanced Queueing, decided to check on Scheduler Job table structures:

SQL> analyze table SYS.SCHEDULER$_EVENT_LOG validate structure cascade; 

Table analyzed. 

SQL> analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure cascade; 
analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure cascade 
* 
ERROR at line 1: 
ORA-01499: table/index cross reference failure - see trace file

SQL> analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure;

Table analyzed.

Ahá!
Also on the generated trace file:

2019-03-01 22:26:37.736 
SESSION ID:(39.32751) 2019-03-01 22:26:37.736 
CLIENT ID) 2019-03-01 22:26:37.736 
SERVICE NAME:(SYS$USERS) 2019-03-01 22:26:37.736 
MODULE NAME:(sqlplus.exe) 2019-03-01 22:26:37.736 
CLIENT DRIVER:(SQL*PLUS) 2019-03-01 22:26:37.736 
ACTION NAME) 2019-03-01 22:26:37.736 
CONTAINER ID:(1) 2019-03-01 22:26:37.736 
Table/Index row count mismatch 
table 273184 : index 275017, 1832 
Index root = tsn: 1 rdba: 0x00c0128a

So ANALYZE on table SCHEDULER$_JOB_RUN_DETAILS fails with CASCADE but succeeds without CASCADE. This means that there is a problem with one of the index of this table. It has gone out of sync with the table.

SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where table_name='SCHEDULER$_JOB_RUN_DETAILS';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index SYS.I_SCHEDULER_JOB_RUN_DETAILS rebuild;

SQL> alter index SYS.I_SCHEDULER_JOB_RUN_DETAILS rebuild online;

Index altered.

After this, error solved and no more recurrence of that ORA-600.

Also note I couldn’t find any document about this on MOS, so this is kind of exclusive by now. 🙂

Cheers!

Restore table statistics using dbms_stats

Hi all, another quick as reference. Table statistics as everyone knows are  very important when the CBO is creating the execution plan. When you have new statistics Oracle will invalid the current execution plans affected by them and create new execution plans based on the new statistics. Most of the time it gets right  if that causes your execution plan to change for worse?

In this situations dba_tab_stats_history   view and dbms_stats.restore_table_stats procedures are your friends.

To validate set and check and set the stats history:

select dbms_stats.get_stats_history_retention from dual; 
exec dbms_stats.alter_stats_history_retention(30);

On dba_tab_stats_history you can view the last statistics available:

select table_name, stats_update_time, from dba_tab_stats_history where table_name='TABLE1' and owner='USER' order by 2 desc;

Use the dbms_stats.restore_table_stats to restore the statistics:

exec dbms_stats.restore_table_stats(ownname=>'USER',tabname=>'TABLE1',AS_OF_TIMESTAMP=>'DATE from column STATS_UPDATE_TIME on dba_tab_stats_history');

Hope it helps!

Elisson Almeida

Oracle 11g Semantic Hints

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

Here is a quick summary I did for a client regarding new Semantic Hints on 11g..

11g New Semantic Hints

In Database version 11.2.0.1, Oracle introduced three new hints: IGNORE_ROW_ON_DUPKEY_INDEX, CHANGE_DUPKEY_ERROR_INDEX and RETRY_ON_ROW_CHANGE. They have different working ends but they all tries to avoid ORA-0001 in some specific situations. An important note is that, unlike almost all other hints, this hint has a semantic effect: it changes the actual behavior – not just the optimization – of the SQL. Oracle Docs says about this Hint:

“The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in “Hints” does not apply for these three hints.”

Some specifics about each one:

  • IGNORE_ROW_ON_DUPKEY_INDEX: “When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row.”
  • CHANGE_DUPKEY_ERROR_INDEX: “The CHANGE_DUPKEY_ERROR_INDEX hint provides a mechanism to unambiguously identify a unique key violation for a specified set of columns or for a specified index. When a unique key violation occurs for the specified index, an ORA-38911 error is reported instead of an ORA-001.”
  • RETRY_ON_ROW_CHANGE: “When you specify this hint, the operation is retried when the ORA_ROWSCN for one or more rows in the set has changed from the time the set of rows to be modified is determined to the time the block is actually modified.”

The use of those hints, however, is only recommended for pretty specific ends and should be avoided if possible. Not only for having some related bugs, but to add some complexity to known operations like bulk inserts and similar.

Other important hint is the Result Cache Hint, mentioned some months ago, under Result Cache Post.

Cheers!

12c Datapatch Failing on ORA-65108: Applying Datapatch Manually

Hi all,

So, in a new 12c environment datapatch failed on the following:

ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-65108: invalid use of a cursor belonging to another container
kpdbaKillPdbSessions: Starting kill.
2019-01-29 07:09:21.486584 :kjcipctxinit(): (pid|psn)=(25|2): initialised and linked pctx 0x00007FFB1A7B49C8 into process list
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-65108: invalid use of a cursor belonging to another container

Point is that was failing for the PDB$SEED. When upgrading, there are phases where you need the seed opened read-write. But in general you don’t to that yourself.
The scripts to run in each container are called through catcon.pl which, by default, opens the seed read-write and ensures that the initial open mode is restored at the end even in case of error.

So how to manually proceed with this?

1. Apply the datapatch first to other PDBs using the -pds clause:

./datapatch -verbose -pdbs PDB1,PDB2

2. Open PDB$SEED for Oracle Scripts:

SYS@CDB$ROOT SQL> alter session set "_oracle_script"=true;
 Session altered. 
 SYS@CDB$ROOT SQL> alter pluggable database pdb$seed open read write force;
 Pluggable database PDB$SEED altered.

3. Apply the datapatch to the PDB$SEED

$ORACLE_HOME/OPatch/datapatch -verbose -pdbs ‘PDB$SEED’

4. Close PDB$SEED and Mount it back

 SYS@CDB$ROOT SQL> alter session set container=PDB$SEED
 Session altered. 
 SYS@CDB$ROOT SQL> shutdown immediate
 Pluggable Database closed
++ From Root
 SYS@CDB$ROOT SQL> alter pluggable database PDB$SEED open read only;

Hope it helps, cheers!

OEM Report: Last 6 month Database Space Usage and Growth [2]

Hey,
So I see you liked the last OEM Report post. But some have asked me for a table with all databases space usage something like a summary table for executive view, once the last report give it by database, right…

Here it goes:
– I hided the target names for safety, but check on the last column “AVG 6-m Growth”. This is the one you want.
– This obviously needs to be in another Report, once no DB filter is present. So, here is an exame of table and the code:

ExecutiveReport.png

And let me ask you: Would you like to have this view from the ASM Perspective? Like this?

ASM-Growth

Great, right?
The codes for this:

#Database Space Usage information in the last 6 months

select CURRENT_ALLOCATEDSPACE.TARGET_NAME,               
round(CURRENT_ALLOCATEDSPACE.SIZE_GB,2) as "Current allocated(GB)",
round(CURRENT_USEDSIZE.SIZE_GB,2) as "Current Used (GB)",
round(CURRENT_ALLOCATEDSPACE.SIZE_GB- CURRENT_USEDSIZE.SIZE_GB,2) as "Current Free (GB)",
round(CURRENT_USEDSIZE.SIZE_GB-USED_LAST_MONTH.SIZE_GB,2) as "1-m Growth (GB)",
round(MAX_USED.SIZE_GB,2) as "Max Used 6-m (GB)",
round((CURRENT_USEDSIZE.SIZE_GB- FIRST_USEDSPACE.SIZE_GB)/6,2) as "AVG 6-m Growth " 
from 
		(select T.TARGET_NAME,  --FIRST USED SPACE
		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP,
		sum(M.AVERAGE/1024)as SIZE_GB
		from   MGMT$METRIC_DAILY M,
		MGMT$TARGET_TYPE T,
			(select T.TARGET_NAME, MIN(ROLLUP_TIMESTAMP) MIN_TIME 
			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	  
			where (T.TARGET_TYPE='rac_database' OR 
			(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and      
			M.TARGET_GUID=T.TARGET_GUID and
			M.METRIC_GUID=T.METRIC_GUID and
			T.METRIC_NAME='tbspAllocation' and
			T.METRIC_COLUMN='spaceUsed' and
			M.METRIC_NAME=T.METRIC_NAME and
			M.METRIC_COLUMN=T.METRIC_COLUMN and
			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME, 			M.METRIC_COLUMN) MIN_DATE 		where (T.TARGET_TYPE='rac_database' OR  		(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='tbspAllocation' and 		T.METRIC_COLUMN='spaceUsed' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,m.rollup_timestamp) FIRST_USEDSPACE,	--FIRST USED SPACE 		(select T.TARGET_NAME,    --CURRENT ALLOCATED SPACE 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, max(ROLLUP_TIMESTAMP) MAX_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where (T.TARGET_TYPE='rac_database' OR  			(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='tbspAllocation' and 			T.METRIC_COLUMN='spaceAllocated' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME, 			M.METRIC_COLUMN) MAX_DATE 		where (T.TARGET_TYPE='rac_database' OR  		(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='tbspAllocation' and 		T.METRIC_COLUMN='spaceAllocated' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME 		group by t.target_name,m.rollup_timestamp) CURRENT_ALLOCATEDSPACE,--CURRENT ALLOCATED SPACE 		(select T.TARGET_NAME,  --USED SPACE IN LAST MONTH 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, MIN(ROLLUP_TIMESTAMP) MIN_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where (T.TARGET_TYPE='rac_database' OR  			(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='tbspAllocation' and 			T.METRIC_COLUMN='spaceUsed' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-30),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME, 			M.METRIC_COLUMN) MIN_DATE 		where (T.TARGET_TYPE='rac_database' OR  		(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='tbspAllocation' and 		T.METRIC_COLUMN='spaceUsed' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,m.rollup_timestamp) USED_LAST_MONTH, --USED SPACE IN LAST MONTH 		(select A_SIZE.TARGET_NAME, --MAX USED SPACE IN 6 MONTHS 		round(max(A_SIZE.SIZE_GB),2) as SIZE_GB 		from (select T.TARGET_NAME,   			M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 			sum(M.AVERAGE/1024) as SIZE_GB 			from MGMT$METRIC_DAILY M, 			MGMT$TARGET_TYPE T 			where (T.TARGET_TYPE='rac_database' OR  			(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='tbspAllocation' and 			T.METRIC_COLUMN='spaceUsed' and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME, 			M.METRIC_COLUMN, 			M.ROLLUP_TIMESTAMP) A_SIZE 		group by A_SIZE.TARGET_NAME) MAX_USED,	--MAX USED SPACE IN 6 MONTHS				   		(select T.TARGET_NAME,   --CURRENT USED SPACE 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, max(ROLLUP_TIMESTAMP) MAX_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where (T.TARGET_TYPE='rac_database' OR  			(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and      	 			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='tbspAllocation' and 			T.METRIC_COLUMN='spaceUsed' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate)
			group by T.TARGET_NAME,
			M.METRIC_COLUMN) MAX_DATE
		where (T.TARGET_TYPE='rac_database' OR 
		(T.TARGET_TYPE='oracle_database' and T.TYPE_QUALIFIER3 != 'RACINST')) and      
		M.TARGET_GUID=T.TARGET_GUID and
		M.METRIC_GUID=T.METRIC_GUID and
		T.METRIC_NAME='tbspAllocation' and
		T.METRIC_COLUMN='spaceUsed' and
		M.METRIC_NAME=T.METRIC_NAME and
		M.METRIC_COLUMN=T.METRIC_COLUMN and
		T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME
		group by t.target_name,m.rollup_timestamp) CURRENT_USEDSIZE				 					     
WHERE 
CURRENT_ALLOCATEDSPACE.TARGET_NAME=CURRENT_USEDSIZE.TARGET_NAME and
CURRENT_ALLOCATEDSPACE.TARGET_NAME=USED_LAST_MONTH.TARGET_NAME and 
CURRENT_ALLOCATEDSPACE.TARGET_NAME=FIRST_USEDSPACE.TARGET_NAME and 
CURRENT_ALLOCATEDSPACE.TARGET_NAME=MAX_USED.TARGET_NAME 
order by 3 desc

# ASM Diskgroup Usage information in the last 6 months

select CURRENT_ALLOCATEDSPACE.TARGET_NAME "DB Name" , 
CURRENT_ALLOCATEDSPACE.KEY_VALUE  "Diskgroup",
round(CURRENT_ALLOCATEDSPACE.SIZE_GB,2) as "Current Allocated (GB)",
round(CURRENT_ALLOCATEDSPACE.SIZE_GB-CURRENT_FREESIZE.SIZE_GB,2) as "Current Used (GB)",
round(CURRENT_FREESIZE.SIZE_GB,2) as "Current Free (GB)",
round((CURRENT_ALLOCATEDSPACE.SIZE_GB-CURRENT_FREESIZE.SIZE_GB)-(TOTAL_SPACE_LAST_MONTH.SIZE_GB-FREE_LAST_MONTH.SIZE_GB),2) as "1-m Growth (GB)",
round(USED_SPACE_INFORMATION.USED_SPACE,2) as "Max Used 6-m (GB)",
round(((CURRENT_ALLOCATEDSPACE.SIZE_GB-CURRENT_FREESIZE.SIZE_GB)- (FIRST_TOTAL_SPACE.SIZE_GB-FIRST_FREESPACE.SIZE_GB))/6,2) as "AVG 6-m Growth" 
from 
		(select T.TARGET_NAME,  --FIRST FREE SPACE
		M.KEY_VALUE,
		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP,
		sum(M.AVERAGE/1024)as SIZE_GB
		from   MGMT$METRIC_DAILY M,
		MGMT$TARGET_TYPE T,
			(select T.TARGET_NAME, M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME 
			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	  
			where T.TARGET_TYPE='osm_cluster' and      
			M.TARGET_GUID=T.TARGET_GUID and
			M.METRIC_GUID=T.METRIC_GUID and
			T.METRIC_NAME='DiskGroup_Usage' and
			T.METRIC_COLUMN='free_mb' and
			M.METRIC_NAME=T.METRIC_NAME and
			M.METRIC_COLUMN=T.METRIC_COLUMN and
			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME,M.key_value,M.METRIC_COLUMN) MIN_DATE 		where T.TARGET_TYPE='osm_cluster'  and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='DiskGroup_Usage' and 		T.METRIC_COLUMN='free_mb' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.KEY_VALUE=MIN_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,M.KEY_VALUE, m.rollup_timestamp) FIRST_FREESPACE,	--FIRST FREE SPACE 		(select T.TARGET_NAME,  --FIRST TOTAL  SPACE 		M.KEY_VALUE, 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where T.TARGET_TYPE='osm_cluster' and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='DiskGroup_Usage' and 			T.METRIC_COLUMN='total_mb' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME,M.key_value,M.METRIC_COLUMN) MIN_DATE 		where T.TARGET_TYPE='osm_cluster'  and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='DiskGroup_Usage' and 		T.METRIC_COLUMN='total_mb' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND M.KEY_VALUE=MIN_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,M.KEY_VALUE, m.rollup_timestamp) FIRST_TOTAL_SPACE,	--FIRST TOTAL  SPACE		 		(select T.TARGET_NAME,    --CURRENT TOTAL SPACE 		M.KEY_VALUE, 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, M.KEY_VALUE, max(ROLLUP_TIMESTAMP) MAX_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where T.TARGET_TYPE='osm_cluster'  and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='DiskGroup_Usage' and 			T.METRIC_COLUMN='total_mb' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME, m.key_value, 			M.METRIC_COLUMN) MAX_DATE 		where T.TARGET_TYPE='osm_cluster' and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='DiskGroup_Usage' and 		T.METRIC_COLUMN='total_mb' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.KEY_VALUE=MAX_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME 		group by t.target_name,m.key_value,m.rollup_timestamp order by 1,3) CURRENT_ALLOCATEDSPACE,--CURRENT TOTAL SPACE 		(select T.TARGET_NAME,  --FREE SPACE IN LAST MONTH 		M.KEY_VALUE, 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME,M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where T.TARGET_TYPE='osm_cluster' and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='DiskGroup_Usage' and 			T.METRIC_COLUMN='free_mb' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-30),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME,M.KEY_VALUE) MIN_DATE 		where T.TARGET_TYPE='osm_cluster' and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='DiskGroup_Usage' and 		T.METRIC_COLUMN='free_mb' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND  M.KEY_VALUE=MIN_DATE.KEY_VALUE and  M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,m.key_value, m.rollup_timestamp) FREE_LAST_MONTH, --FREE SPACE IN LAST MONTH 		(select T.TARGET_NAME,  --TOTAL SPACE IN LAST MONTH		 		M.KEY_VALUE, 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME,M.KEY_VALUE, MIN(ROLLUP_TIMESTAMP) MIN_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where T.TARGET_TYPE='osm_cluster' and       			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='DiskGroup_Usage' and 			T.METRIC_COLUMN='total_mb' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and 			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-30),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 			group by T.TARGET_NAME,M.KEY_VALUE) MIN_DATE 		where T.TARGET_TYPE='osm_cluster' and       		M.TARGET_GUID=T.TARGET_GUID and 		M.METRIC_GUID=T.METRIC_GUID and 		T.METRIC_NAME='DiskGroup_Usage' and 		T.METRIC_COLUMN='total_mb' and 		M.METRIC_NAME=T.METRIC_NAME and 		M.METRIC_COLUMN=T.METRIC_COLUMN and 		T.TARGET_NAME=MIN_DATE.TARGET_NAME AND  M.KEY_VALUE=MIN_DATE.KEY_VALUE and  M.ROLLUP_TIMESTAMP=MIN_DATE.MIN_TIME 		group by t.target_name,m.key_value, m.rollup_timestamp) TOTAL_SPACE_LAST_MONTH, --TOTAL SPACE IN LAST MONTH	 		(SELECT SPACE_ALLOCATED.TARGET_NAME, -- USED_SPACE_INFORMATION 		SPACE_ALLOCATED.KEY_VALUE,  		max(SPACE_ALLOCATED.SIZE_GB- FREE_SPACE.SIZE_GB) USED_SPACE 		FROM( 				select T.TARGET_NAME,  				M.KEY_VALUE,  				M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 				sum(M.AVERAGE/1024) as SIZE_GB 				from MGMT$METRIC_DAILY M, 				MGMT$TARGET_TYPE T 				where T.TARGET_TYPE='osm_cluster' and       				M.TARGET_GUID=T.TARGET_GUID and 				M.METRIC_GUID=T.METRIC_GUID and 				T.METRIC_NAME='DiskGroup_Usage' and 				T.METRIC_COLUMN='total_mb' and 				M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
				M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 				group by T.TARGET_NAME,	M.KEY_VALUE, 				M.ROLLUP_TIMESTAMP) SPACE_ALLOCATED, 				(select T.TARGET_NAME,  				M.KEY_VALUE,  				M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 				sum(M.AVERAGE/1024) as SIZE_GB 				from MGMT$METRIC_DAILY M, 				MGMT$TARGET_TYPE T 				where T.TARGET_TYPE='osm_cluster' and       				M.TARGET_GUID=T.TARGET_GUID and 				M.METRIC_GUID=T.METRIC_GUID and 				T.METRIC_NAME='DiskGroup_Usage' and 				T.METRIC_COLUMN='free_mb' and 				M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
				M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate) 				group by T.TARGET_NAME,	M.KEY_VALUE, 				M.ROLLUP_TIMESTAMP) FREE_SPACE 		WHERE SPACE_ALLOCATED.TARGET_NAME=FREE_SPACE.TARGET_NAME AND 		SPACE_ALLOCATED.KEY_VALUE=FREE_SPACE.KEY_VALUE AND 		SPACE_ALLOCATED.MONTH_TIMESTAMP=FREE_SPACE.MONTH_TIMESTAMP  		group by  SPACE_ALLOCATED.TARGET_NAME, SPACE_ALLOCATED.KEY_VALUE) USED_SPACE_INFORMATION, -- USED_SPACE_INFORMATION				 		(select T.TARGET_NAME,   --CURRENT FREE SPACE 		M.KEY_VALUE, 		M.ROLLUP_TIMESTAMP as MONTH_TIMESTAMP, 		sum(M.AVERAGE/1024)as SIZE_GB 		from   MGMT$METRIC_DAILY M, 		MGMT$TARGET_TYPE T, 			(select T.TARGET_NAME, M.KEY_VALUE, max(ROLLUP_TIMESTAMP) MAX_TIME  			from  MGMT$METRIC_DAILY M, MGMT$TARGET_TYPE T	   			where T.TARGET_TYPE='osm_cluster' and      	 			M.TARGET_GUID=T.TARGET_GUID and 			M.METRIC_GUID=T.METRIC_GUID and 			T.METRIC_NAME='DiskGroup_Usage' and 			T.METRIC_COLUMN='free_mb' and 			M.METRIC_NAME=T.METRIC_NAME and 			M.METRIC_COLUMN=T.METRIC_COLUMN and  			M.ROLLUP_TIMESTAMP >= to_timestamp(trunc((sysdate-180),'MM')) and
			M.ROLLUP_TIMESTAMP <= to_timestamp(sysdate)
			group by T.TARGET_NAME,M.KEY_VALUE ,M.METRIC_COLUMN) MAX_DATE
		where T.TARGET_TYPE='osm_cluster' and      
		M.TARGET_GUID=T.TARGET_GUID and
		M.METRIC_GUID=T.METRIC_GUID and
		T.METRIC_NAME='DiskGroup_Usage' and
		T.METRIC_COLUMN='free_mb' and
		M.METRIC_NAME=T.METRIC_NAME and
		M.METRIC_COLUMN=T.METRIC_COLUMN and
		T.TARGET_NAME=MAX_DATE.TARGET_NAME AND M.KEY_VALUE=MAX_DATE.KEY_VALUE AND M.ROLLUP_TIMESTAMP=MAX_DATE.MAX_TIME
		group by t.target_name,m.key_value,m.rollup_timestamp) CURRENT_FREESIZE	  --CURRENT FREE SPACE			
WHERE 
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=CURRENT_FREESIZE.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=CURRENT_FREESIZE.KEY_VALUE) and
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=FREE_LAST_MONTH.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=FREE_LAST_MONTH.KEY_VALUE) and 
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=FIRST_FREESPACE.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=FIRST_FREESPACE.KEY_VALUE) and 
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=TOTAL_SPACE_LAST_MONTH.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=TOTAL_SPACE_LAST_MONTH.KEY_VALUE) and
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=FIRST_TOTAL_SPACE.TARGET_NAME and CURRENT_ALLOCATEDSPACE.KEY_VALUE=FIRST_TOTAL_SPACE.KEY_VALUE) and
(CURRENT_ALLOCATEDSPACE.TARGET_NAME=USED_SPACE_INFORMATION.TARGET_NAME AND CURRENT_ALLOCATEDSPACE.KEY_VALUE=USED_SPACE_INFORMATION.KEY_VALUE )
order by 4 desc

AWS EC2: Custom Functions to Verify Passwords

Hello all!
Looking forward to create password functions on EC2? Easy, we just need to use AWS internal functions for it. Check below an example:

# To create it:

begin
    rdsadmin.rdsadmin_password_verify.create_verify_function(
        p_verify_function_name => 'CUSTOM_PASSWORD_FUNCTION', 
        p_min_length           => 12, 
        p_min_uppercase        => 2, 
        p_min_digits           => 1, 
        p_min_special          => 1,
        p_disallow_at_sign     => true);
end;
/

In case you want to double check the code created, here goes a quick trick: Check on DBA_SOURCE:

col text format a150
  select TEXT  from DBA_SOURCE 
   where OWNER = 'SYS' and NAME = 'CUSTOM_PASSWORD_FUNCTION' order by LINE;

Hope it helps. Cheers!

Oracle Read Only Tables: Do you use it?

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Read Only Tables

In previous Oracle releases, tables could be made to appear read-only to other users by only granting the SELECT object privilege to them, but the tables remained read-write for the owner. Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command. Examples as per below:

ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;

In case of any other DML or DDL operation try to be ran, the following error will be prompted:

ORA-12081: update operation not allowed on table "OWNER"."TABLE_NAME"

This can be a very useful feature for specific situations where we need to guarantee that no changes be performed on table by applications, like a forced revoke on this specific table, even system grants be in place like “update any table”.

Cheers!

Starting ASM: ORA-29701: unable to connect to Cluster Synchronization Service

Hey all,
So, I bet you have seen this error already, as this is quite common when messing up with Cluster configuration, which DBAs love to do…. no?

Well, here is what you may be facing:

SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
SQL>

The error is kind of clear: Cluster Synchronization Service (CSS) is not available. So, let’s start it from ASM Cluster (or HAS).

$GRID_HOME/bin/crsctl start resource -all

Or, for Standalone:

$GRID_HOME/bin/crsctl start has

To check on status:

$GRID_HOME/bin/crsctl status resource -t

Complete example (attention to CSSD):

[root@greporasrv1 ~]# crsctl start has
CRS-4640: Oracle High Availability Services is already active
CRS-4000: Command Start failed, or completed with errors.
[root@greporasrv1 ~]# crsctl start resource -all
CRS-5702: Resource ‘ora.evmd’ is already running on ‘greporasrv1’
CRS-2501: Resource ‘ora.ons’ is disabled
CRS-2672: Attempting to start ‘ora.cssd’ on ‘greporasrv1’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘greporasrv1’
CRS-2676: Start of ‘ora.diskmon’ on ‘greporasrv1’ succeeded
CRS-2676: Start of ‘ora.cssd’ on ‘greporasrv1’ succeeded
CRS-4000: Command Start failed, or completed with errors.
[root@greporasrv1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ons
OFFLINE OFFLINE greporasrv1 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE greporasrv1 STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE greporasrv1 STABLE
--------------------------------------------------------------------------------
[root@greporasrv1 ~]#

Hope that worked! 😀

Oh, it didn’t? Did you changed hostname name or something? In this case, you may want to deconfig HAS and reconfigure using root.sh (part regular installation):

cd $ORACLE_HOME
./crs/install/roothas.pl -deconfig -force
./crs/install/roothas.pl -delete -force
./root.sh

 

Hey! Be careful with that, it might be unrecoverable. 😉