The below MS Word Document contains the steps to add disk groups to ASM via ASMCA.
Adding disk groups to ASM
Category: Oracle
Extract the DDL for a profile
I needed to extract the DDL for a few profiles. This DDL was a quick and easy way to do it:
select dbms_metadata.get_ddl('PROFILE',a.profile) from dba_profiles a where a.profile = '' and rownum = 1;
Missing an hourly snapshot while running AWR?
Have you ever tried to run an AWR report only to find that your hourly snapshots did not get created? A colleague of mine ran into this and worked with Oracle to identify a solution:
select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > sysdate - 1 and instance_number = 1 order by 1; SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME ---------- ------------------------------ ------------------------------ 239690 2017-06-28-01.17.23.246000 2017-06-28-02.00.34.302000 239691 2017-06-28-02.00.34.302000 2017-06-28-03.00.28.007000 239692 2017-06-28-03.00.28.007000 2017-06-28-04.00.39.460000 239693 2017-06-28-04.00.39.460000 2017-06-28-05.00.03.694000 239694 2017-06-28-05.00.03.694000 2017-06-28-06.00.13.452000 <--missing 239696 2017-06-28-07.00.10.342000 2017-06-28-08.00.20.270000 239697 2017-06-28-08.00.20.270000 2017-06-28-09.00.09.222000 239698 2017-06-28-09.00.09.222000 2017-06-28-10.00.39.143000 239699 2017-06-28-10.00.39.143000 2017-06-28-11.00.55.633000 239700 2017-06-28-11.00.55.633000 2017-06-28-12.00.02.801000 239701 2017-06-28-12.00.02.801000 2017-06-28-13.00.12.972000 239702 2017-06-28-13.00.12.972000 2017-06-28-19.21.20.515000 <--missing 12 rows selected.
The issue is caused by MMON being suspended – confirmed by this command:
oradebug unit_test kebm_dmp_slv_attrs kewrmafsa_ Status: 3 Flags: 0 Runtime limit: 900 CPU time limit: 300 Violations: 4 Suspended until: 1498817730 <-- non-zero indicated MMON is suspended
To clear the suspension issue this command:
oradebug unit_test kebm_set_slv_attrs kewrmafsa_ retain retain retain retain 0 0 Modified attributes of kewrmafsa_ (slave id 12)
And now it shows zero and snapshots are being generated again:
oradebug unit_test kebm_dmp_slv_attrs kewrmafsa_ Status: 3 Flags: 0 Runtime limit: 900 CPU time limit: 300 Violations: 0 Suspended until: 0 zero indicates MMON is running normally
The reason for the missing snapshots was SYSAUX cleanup work which locked up some of the tables that snapshot generation needs. But it can happen for a variety of reasons.
ORA-04031 in the middle of upgrade to 11.2.0.4
A colleague of mine was performing an upgrade of a four node Oracle RAC from 11.2.0.3 to 11.2.0.4 using DBUA silent mode. About halfway through the upgrade the following error was reported and DBUA failed:
Upgrading Oracle interMedia
UPGRADE_PROGRESS : 46%
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4096 bytes of shared memory ("java
I suspected that the solution was to increase the below parameters and restart the upgrade.
ALTER SYSTEM SET SGA_TARGET=2048M scope=spfile sid='*'; ALTER SYSTEM SET pga_aggregate_target=500M scope=spfile sid='*'; ALTER SYSTEM SET sga_max_size =2048M scope=spfile sid='*';
Also set cluster to false for the upgrade
alter system set cluster_database=true scope=spfile;
However, because this was a production system, I opened a severity one service request with our Oracle to get their opinion before we continued. Oracle agreed with the with the above. The steps we followed were:
01. Set Oracle home to 11.2.0.4
02. Copy initora file from 11.2.0.3 home to 11.2.0.4 home
(file contains 1 line pointing to spfile in asm)
03. Some DB processes were running on node 4. Killed
these via Unix kill commands
04. Startup no mount on node 4 via SQL (not srvctl)
05. Make above alters
06. Shutdown immediate
07. Startup no mount on node 4 via SQL (not srvctl)
08. Confirm above parameter changes were in place
09. Shutdown immediate
10. startup upgrade
11. Exit this session
12. cd to oracle home
13. Start a new session as sqlplus " / as sysdba "
14. spool /tmp/upgrade.log
15. set echo on
16. @$ORACLE_HOME/rdbms/admin/catupgrd.sql;
17. spool off
18. Shutdown immediate (seemed to have been issued
in the catupgrd.sql)
19. Check catupgrd.sql spool file for errors
20. Restart the database in normal mode
21. @$ORACLE_HOME/rdbms/admin/catuppst.sql;
22. @$ORACLE_HOME/rdbms/admin/utlrp.sql;
23. Execute dbupgdiag.sql
After the above is complete, upgrade the Oracle Clusterware Configuration
srvctl upgrade database -d-o
Our time zone was already current so we did not have to upgrade to the latest version.
After all of the above was complete, the database was ready to use. We had to complete some routine tasks such as turning of flash back, confirming that all scripts had been updated to use the new oracle home etc.
Upgrading an Oracle database with Oracle Wallet Manager
Today we upgraded a 4 node RAC from 11.2.0.3 to 11.2.0.4. Unfortunately, we did not know the database was using encryption and the Oracle Wallet Manager. The users began to notice issues accessing the encrypted table. I ran the below SQL and the status indicated there was an issue:
SQL> select wrl_type wallet,status,wrl_parameter wallet_location from v$encryption_wallet; WALLET STATUS WALLET_LOCATION ------ ------------------ ------------------------ file OPEN_NO_MASTER_KEY /opt/oracle/product/cost
The solution for this was to
back up the sqlnet.ora file in the ora_home_11.2.0.4/network/admin directory On each node of the RAC, copy the sqlnet.ora file from the ora_home_11.2.0.3/network/admin directory to the ora_home_11.2.0.4/network/admin directory bounce the RAC with srvctl commands
I am not sure if a rolling bounce would have resolved the issue or not. We were in a hurry to get the application back into service and hence bounced the database.
Oracle – Performing silent patching
As part of the process to automate the creation of an Oracle AMI based on an Amazon Linux AMI, I had to create a yml that would install Oracle and patch it. Since this is being done via Jenkins/Ansible/yml etc., all the patching had to be performed in silent mode. In order to run opatch in silent mode, you need a response file. This is created as follows:
change directory to any directory that you want to create the response file output
cd /tmp
Invoke the command to create the file
oracle_home/OPatch/ocm/bin/emocmrsp
You will be prompted as follow:
OCM Installation Response Generator 10.3.7.0.0 - Production Copyright (c) 2005, 2012, Oracle and/or its affiliates. All rights reserved. Provide your email address to be informed of security issues, install and initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name. Visit http://www.oracle.com/support/policies.html for details. Email address/User Name: You have not provided an email address for notification of security issues. Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y The OCM configuration response file (ocm.rsp) was successfully created.
A file named ocm.rsp will be create in /tmp.
You can use this file to perform silent patching. As an example, I am applying patch 16188701
cd /software/11204/11204_160416_RDBMS_ONEOFF/03_parallel/16188701 opatch apply -local -silent -ocmrf /software/ocm.rsp
Export/Import from a database containing Object Types
If you are performing and Export/Import from a source database that contains types, you may encounter the error:
ORA-39083: Object type TYPE failed to create with error: ORA-02304: invalid object identifier literal Failing sql is: CREATE TYPE ""."object_type_name" OID '8FD43617684FA1F6E033A625D854A1F6' AS OBJECT ( parm_01 varchar2(18), parm_02 varchar2(50) );
Each type has an OID. If you create an object type and do not specify an OID, Oracle generates an OID and assigns it to the type.
To resolve this error, you need to transform the OID by adding the below option to you IMPDP command:
transform=OID:n:type
The type object will now get a new OID in the target database.
Unusual variation in elapsed times on a SQL
Today we had users complaining that a query would run fast the first time and then fail to complete the second time. The SQL used bind parameters and we (DBAs) suspected that the issue was caused by the user changing the bind parameters. However, when we ran the SQL ourselves with hard coded values we also experienced the same behavior. The first execution completed in < 5 seconds and the second execution was killed at > 400 seconds of elapsed time. After some searching on the web I ran across an excellent explanation at the web site:
We added
/*+ OPT_PARAM('_OPTIMIZER_USE_FEEDBACK' 'FALSE') */
and the SQL began to perform consistently every time.
This was experienced on Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production.
Simulating an Oracle error into the alert log
In an Oracle AWS RDS instance we do not have direct access to the server and hence we could not use our regular CRON scripts. I was testing an Oracle Stored procedure to replace this functionality and needed to simulate an error in the alert log. I found the below web site
(nice site with a good explanation)
and used these commands:
alter session set events '942 incident(SIMULATED_ERROR)'; drop table tablethatdoesnotexist; alter session set events '942 trace name context off';
The alert log contained:
ORA-00700: soft internal error, arguments: [EVENT_CREATED_INCIDENT], [942], [SIMULATED_ERROR], [], [], [], [], [], [], [], [], [] ORA-00942: table or view does not exist
This was exactly what I needed for my testing.
Hail mary on db links
We had a situation wherein a colleague was attempting to get DB Links working. This was on a 11.2.0.4 database where password obfuscation is not allowed and the users were unsure of the passwords. After multiple changes to the passwords on the target system and drops and recreates of the links, the users continued to encounter errors while using the db link. We looked up a number of posts by different people on the web and then tried
alter system flush shared pool;
This resolved the issue. This was a non production environment. Please consider all the implications before you issue a flush in a production environment.