Hello,
Porto Alegre and region, stay sharp: Free event promoted by GUORS/GUOB, Oracle and Aggrandize, next 21th and 22th at PUCRS!
First day is addressed to Academic and Second one for Companies/Market.
Our Speakers:
See you there!
Category: Cloud Services | Exadata | Linux
OS, Linux, Oracle Engineered Systems, Cloud Services.
Roundtable: How are Databases Adapting to New Cloud Architectures?
Hi all!
Stay sharp, it’s confirmed event for next 27th evening Brazilian Time. If you around Três de Maio, hope you can make it in person!
Otherwise, we are looking forward to record and share session, still evaluating streaming options and requirements.
Note Grepora is sponsoring this roundtable, so count with you all!
Oh, it will be fully in portuguese. Hope you don’t mind. 🙂
Event: Round Table: “How are Databases Adapting to New Cloud Architectures”
Date: 27/6/2019 19:30 BRT (18:30 EST) to 21:00 (20:00 EST)
Estimated Duration: 1:30 hours
We count with great names, see below:
See you there, cheers!
Workshop: Best Practices for Oracle DB on Exadata: RUN, It’s Free!
Hey, you in Porto Alegre/Brazil!
Don’t miss this opportunity to have an official Oracle Workshop about Exadata Best Practices for Oracle Database!
This is being being promoted by GUORS in partnership with Oracle Brasil.
When? June 26th from 2PM to 6PM BRT.
Where? TecnoPUC-RS, room 206 – building 99A
Instructor: Valter Rodrigues da Oracle Brasil.
LIMITED POSITIONS: 30!
Requirements:
1. Like GUORS Page on Facebook AND LinkedIn
2. Like Oracle University on LinkedIn
3. Like GUOB on LinkedIn
4. Bring your notebook.
5. Basic knowledge on RAC
Subscribe ASAP!
OEM Real-Time Data not Displayed
Hi all,
So I was facing the follwoing situation in a client:
– SYS as SYSDBA passoword working to connect on database.
– Connecting to targets with this credentials working fine.
– However when loading real-time graphs it was simply not working.
– Tried to create manually the named credentials, still not working now with the error:
Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of the service requested in connect descriptor.
See what I was getting from database home:
Weird, but hey, the solution was already posted in Grepora!
Ok, I discovered anyway from MOS BUG 28513706 – 13c2EM: DB home page shows Connection Cache with this Cache Name does not exist. it miht be related to connection pools.
Even thought this was closed as not a bug, a learned a new workaround for it:
1. [OMS_HOME]/bin/emctl set property -name use_pooled_target_connections -value false 2. Restart oms [OMS_HOME]/bin/emctl stop oms -all [OMS_HOME]/bin/emctl start oms
Hope it helps!
Cheers!
Oracle New Visual Online Tools
Hi all,
So, after a long time of same old documentation style, Oracle started sharing some nice and visual pages for some specifics. Here are 2 examples:
1. Oracle Move to Cloud (http://www.oracle.com/goto/move)
This one was shown to me by Ricardo Gonzalez, and it’s a GREAT tool if you are planning migrations to Oracle Cloud in general.
The part I like most is where you select the source and destination formats/versions you want and all recommended migration methods are suggested. Of course additional validation on database are required for each method, but it can potentially remind you of things you may be forgetting.
Something else I like very much as well is the section with executive summaries of each migration tool. It’s basically ready for a print screen to put in the slides you are going to present to your boss… 🙂
Also, you have handy some links to papers for the most different migration scenarios, as per:
2. Oracle Database Features: (https://apex.oracle.com/database-features/
And by selecting any of the features you can see more info in a very simple way, as per below. By the way, with one click you can be directed to the documentation (the old fashioned one):
Hope you enjoy those as much as I did and hope Oracle continue providing those sort of interfaces to us!
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 database space usage something like a summary table for executive view, once the last report gives it by the database, right…
Here it goes:
– I hid 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 example of the table and the code:
And let me ask you: Would you like to have this view from the ASM Perspective? Like this?
Great, right?
The codes for this:
NOTE: We got comments on this regarding the sysdate implicit conversion to timestamp. It worked fine to by, but in case you have any issue, you might want to check over this. Thank you for pointing out, Günter Nowak!
#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!
OEM: HttpConnectionException:Response code:404
Hello all,
So, a client’s OEM Information Report was running and email being sent, but all queries (Report sections) resulting on error:
Unexpected error getting the HTTP response stream while generating report: oracle.sysman.eml.ip.publishJob.HttpConnectionException:Response code:404
Weird?
I had a silar issue, as per documented on this post: OEM Information Reports: ORA-00600 [kpndbcon-svchpnotNULL]
This is happening due TCP timeout while executing Report after 5 minutes. By default, Idle Timeout set for Profile parameter (TCP) for the virtual server in F5 is 300 seconds, for example.
I simulated manually execution of each SQL on report and noticed report takes around 8 minutes to run.
What I did? I took some of the slow queries and transformed to a Materialized View with daily refresh.
This was valid in my case because the report runs only once a day. Remember to always check on your own situation. Perhaps an hourly refresh or so is better for you.
Another possibility is to engage the LB admin to modify this parameter by setting a new protocol profile for the virtual server or modifying the existing one.
NOTE: After modifying SLB Values, you may need to restart OMS :
emctl stop oms -all emctl start oms
Reference?
MOS oracle.sysman.eml.ip.publishJob.HttpConnectionException:Response code:404″ Errors for reports run from EM (Doc ID 2061361.1)
Getting started with Oracle Cloud – OCI
Hi all, this one is a quick one 🙂
I’m starting the Oracle Cloud journey, a bit late but still have time right?
The Oracle cloud has being improving and adding new features lately.
To get started on it, you can, as I am :-), watching these youtube series
OCI Level 100 on YouTube
OCI Level 200 on YouTube
Hope it helps on the your Oracle cloud journey!
Elisson Almeida
OEM Report: Last 6 month Database Space Usage and Growth
Hello All!
So I had worked in some very useful reports to have in OEM. In next weeks I’ll share some code you may like… 🙂
To create it? Enterprise -> Reports -> Information Reports. There are several nice default reports there you me like.
Now, let’s go to the first Report, as per title:
1. First you Select the Database:
2. Then you see the report:
That’s nice, right?
Here is a report for another database with actual 640GB average growth per month (its expected, once DBSize is over 60TB):
So, Mat, can you share the queries? Of course:
More“OEM Report: Last 6 month Database Space Usage and Growth”