This is a little useful sql I used this morning for stripping out all the additional information that dbms_metadata.get_ddl outputs. This strips the create index statements down to their bare bones.
Category Archive: Uncategorized
Jan 24
wrong number or types of arguments in call to ‘CHECKDATAFILEFORSTANDBY’
I started seeing this today in one of our databases I found metalink article 2360045.1 which states that it is a catalog version mismatch, however running upgrade catalog doesn’t resolve the problem. I remember hitting this error a long time ago, and although I can’f find the reason why, running upgrade catalog two times in …
Sep 13
TDE Wallet on RAC
So I have been having trouble getting the oracle wallet to auto open, and it’s always messed up in gv$encryption_wallet
|
1 2 3 4 5 6 7 8 |
select * from gv$encryption_wallet; INST_ID WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID ---------- ------------ ---------------------- ------------- ---------- ------------ ------ 4 FILE OPEN AUTOLOGIN SINGLE NO 0 1 FILE OPEN AUTOLOGIN SINGLE NO 0 3 FILE NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED 0 2 FILE NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED 0 |
According to Oracle this is is because TNS_ADMIN is not set, so I set it and several other suggested variables.
|
1 2 3 4 5 6 7 8 9 10 11 |
$ srvctl setenv database -d oradb1 -T "ORACLE_UNQNAME=oradb1" $ srvctl setenv database -d oradb1 -T "ORACLE_BASE=/u01/app/oracle" $ srvctl setenv database -d oradb1 -T "ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/" $ srvctl setenv database -d oradb1 -T "TNS_ADMIN=/u01/app/oracle/product/12.2.0.1/db/network/admin/" $ srvctl getenv database -d oradb1 oradb1: ORACLE_UNQNAME=oradb1 ORACLE_BASE=/u01/app/oracle TNS_ADMIN=/u01/app/oracle/product/12.2.0.1/db/network/admin/ ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/ |
After restarting the database, the ACFS volume now shows
|
1 2 3 4 5 6 7 8 |
select * from gv$encryption_wallet; INST_ID WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID ---------- -------------------- -------------------------------- -------- -------------------- --------- --------- ---------- 1 FILE /u01/app/oracle/wallet/oradb1/ OPEN AUTOLOGIN SINGLE NO 0 2 FILE /u01/app/oracle/wallet/oradb1/ OPEN AUTOLOGIN SINGLE NO 0 3 FILE /u01/app/oracle/wallet/oradb1/ OPEN AUTOLOGIN SINGLE NO 0 4 FILE /u01/app/oracle/wallet/oradb1/ OPEN AUTOLOGIN SINGLE NO 0 |
Sep 09
DBSNMP long running query
I noticed a long running query in one of my databases this morning.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SELECT TABLESPACE, ROUND ( ( (max_size_mb - used_space + free_space_mb) / max_size_mb), :"SYS_B_00") * :"SYS_B_01" AS pct_free FROM ( SELECT ts.tablespace_name TABLESPACE, ROUND (SUM (NVL (fs.bytes, :"SYS_B_02"))) / :"SYS_B_03" / :"SYS_B_04" free_space_mb, ROUND (SUM (df.bytes)) / :"SYS_B_05" / :"SYS_B_06" used_space, ROUND ( SUM ( CASE autoextensible WHEN :"SYS_B_07" THEN df.maxbytes ELSE df.bytes END)) / :"SYS_B_08" / :"SYS_B_09" AS max_size_mb, ts.block_size AS block_size FROM dba_data_files df LEFT OUTER JOIN ( SELECT file_id, SUM (bytes) bytes FROM dba_free_space fs GROUP BY file_id) fs ON df.file_id = fs.file_id INNER JOIN dba_tablespaces ts ON df.tablespace_name = ts.tablespace_name WHERE ts.contents = :"SYS_B_10" GROUP BY ts.tablespace_name, ts.block_size) ORDER BY pct_free DESC |
Its computing tablespace size. The average runtime is 272.03 sec, or 4.5 minutes. This isn’t right, its running in a few seconds in other databases. I started looking at the query in OEM, and noticed almost all the execution time was …
Jan 04
Monitoring Online ASM Datafile Moves
I’m moving a ~7TB database online as the development group that uses it doesn’t want to take any down time. Thankfully the database isn’t horribly active right now so moving the data files online isn’t causing a huge problem. The only other times I have moved data files online was in an incredibly busy database …
Mar 28
Now on SSL
I have moved this site over to a secure connection. LetsEncrypt offers free SSL certificates for your website! The setup was a bit of a pain because I have a non-standard configuration for this server, but after a little poking and prodding, we are now running on https.
Jun 08
Rushing = Stupid Mistakes
I am currently being asked to refresh a database. The end users are pissed because they asked for the database to be refreshed at noon yesterday, this was the first they had asked for it, and they were doing “really important things” and by 3pm were pissed that I hadn’t responded to them, let alone …
Nov 14
Why Didn’t Oracle Export That?
Ever wonder where Oracle keeps track of objects that won’t be exported?
|
1 |
select * from sys.ku_noexp_tab; |
This table contains a list of objects that will be ignored during an export.
Recent Comments