Tag: Oracle

WAIT_FOR_GAP. How to restore missing archivelogs from backup?

In a Dataguard configuration, Oracle’s RFS (Remote File Server) writes redo data to the standby. When for any reason it can’t write this data, MRP (Managed Recovery Process) will wait for the archivelog to be applied and have the status “WAIT_FOR_LOG”. This will lead the standby to be out-of-sync with the primary database.
Sometimes some archivelogs can’t be transferred from primary database to the standby leaving a gap in the archivelog sequence. The MRP process will have the status “WAIT_FOR_GAP”.  
In order to fix the archivelog gap we have to manually transfer the archivelogs missing.
To find the gap you can query v$archive_gap (gv$archive_gap for RAC).
SELECT INST_ID, THREAD#, HIGH_SEQUENCE#, LOW_SEQUENCE# FROM GV$ARCHIVE_GAP;
INST_ID       THREAD#       HIGH_SEQUENCE#       LOW_SEQUENCE#
————- ————— ———————— ————————
2             2             823                  811
You can see that we are missing archivelogs from sequence 811 to 823 for thread 2. If these archivelogs are not available in the primary we have to restore them from backup.
RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 811 UNTIL SEQUENCE 823 THREAD=2;
Keep in mind that parameter THREAD defaults to 1, so you must specify the thread number when you are trying to restore from a different thread.
After restoring these archivelogs the RFS process should transfer them automatically to the standby. Verify if the gap is fixed.
Thanks,

Alfredo

OEM Agent Plug-in Testing

Today I want to write about Oracle Enterprise Manager (OEM) 12c plug-ins and how to test them. 

OEM is divided into two logical parts, the EM Platform and the EM Plug-ins. The EM Platform basically consists of the UI console, job system, metrics, EM agent, etc. The EM Plug-ins are modules that you can attach to you EM Platform to extend the monitoring functionality. As a result, we have plug-ins for Oracle Databases, Fusion Middleware, even hardware or 3rd-party vendors like MS SQL Server.

The plug-ins have a set of Perl scripts that the agent uses to compute status and metrics for the targets. You can find these scripts under “/plugins//scripts”.
Let us see an example:

Fusion Middleware plug-in version 12.1.0.7:
$ cd  /plugins/oracle.sysman.emas.agent.plugin_12.1.0.7.0/scripts

In there you can see tons of Perl scripts. These scripts are being used by the agent to compute response and metrics for the targets registered in the agent.

Now let’s say you have a Web Cache target that shows an incorrect status in OEM and you want to know how the agent computes the response status of this target.

 $ ls webcache*.pl
webcacheesm.pl  webcacheIsStandalone.pl  webcacheresource.pl  webcacheresponse.pl

You can see that there’s a response script available. 
Now the question is, how to test it?

Based on the MOS id 1534087.1, you can create an env.sh script to load the required variables to your shell. I did create the env.sh file and source it:

$ . env.sh

Now I’m able to test the webcacheresponse.pl:

$ perl webcacheresponse.pl
em_result=1

Looks like having 1 as the result means that the Web Cache is up per the script’s logic. This result is passed to the agent and finally to the OMS.

Hope this is helpful for you while trying to investigate status related issues with your targets.
Thanks,

Alfredo

How to upgrade the RMAN recovery catalog to 12c?

Some days back I faced an issue while trying to upgrade the RMAN recovery catalog to support 12c databases.


RMAN> upgrade catalog;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-07539: insufficient privileges to create or upgrade the catalog schema

The problem is that for 12c the catalog owner requires additional privileges. The solution is to run the dbmsrmansys.sql script that comes with the 12c binaries.

You have to copy this file from your 12c home “$ORACLE_HOME/rdbms/admin” directory to where your RMAN recovery catalog database is and execute it.

SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql
The script is going to complain for the lack of 2 other scripts, but the upgrade runs just fine.
$ rman CATALOG rman@catalog
recovery catalog database Password:
RMAN> UPGRADE CATALOG;
RMAN> UPGRADE CATALOG;
RMAN> EXIT;
Thanks,

Alfredo

ALTER SYSTEM in a RAC environment

Few weeks ago I had to modify an instance parameter to a different value. In this case, I just wanted to modify it for only one instance and leave the other instance as it was.
I thought that if I didn’t specify the instance name using the SID clause, the default is going to be just that instance. I was really wrong about that and the command ended modifying both instances.
After looking at Oracle’s documentation here’s what I’ve found.
SID Clause
·         Specify SID = ‘*’ if you want Oracle Database to change the value of the parameter for all instances.
·         Specify SID = ‘sid’ if you want Oracle Database to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID = ‘*’.

If you do not specify this clause:

·         If the instance was started up with a pfile (client-side initialization parameter file), then Oracle Database assumes the SID of the current instance.
·         If the instance was started up with an spfile (server parameter file), then Oracle Database assumes SID = ‘*’.

If you specify an instance other than the current instance, then Oracle Database sends a message to that instance to change the parameter value in the memory of that instance.

So now I know that in future I need to specify the SID in a RAC instance using spfile.

https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_2013.htm

Thanks,

Alfredo

Why You Should Attend GLOC 2016?

This year’s Great Lakes Oracle Conference (GLOC) is going to be huge! I had the privilege to attend the GLOC 2014 and present in the GLOC 2015. The conference continues to bring most of the best Oracle experts to the Midwest, helping professionals and organizations to keep up to-date on products plus a great networking opportunity.
Keynote presentations are in charge of Maria Colgan and Bryn Llewellyn, both from Oracle. If you are a performance enthusiast, you should know or use Snapper, well, Tanel Poder is in da’ house with “Connecting Hadoop and Oracle” and showing us “Modern Linux Tools for Oracle Performance Diagnosis”. Want more? Tim Gorman is going to present on “Troubleshooting Using ASH” and Kellyn Pot’Vin-Gorman with “OEM 13c – Empowering The DBA With Advanced Features”.
If you like to know why your execution plan changed or your SQL performance is not optimal, we have Carlos Sierra explaining how-to “Find An Execution Plan The CBO hides” and Mauro Pagano will show how-to use “SQLd360, SQL Tuning Diagnosis Made Easy”.
If what you like is Oracle development, Jeff Smith from Oracle is going to present “More Than Just Tips & Tricks: SQL Developer & How-To Turbo Charge Your Oracle Experience”. GLOC 2016 features more than 50 sessions for DBA’s, developers and application administrators plus the pre-conference workshops, including “RAC Attack”!
Don’t miss the opportunity and be part of the biggest conference in the Great Lakes Region!
Uhhh, I almost forgot! The early registration ends April 11, so hurry up!
https://www.neooug.org/gloc/
Thanks,

Alfredo

Compression Advisor killed my database!

Over the weekend one of the databases hung due to the flash recovery area was 100% full. I noticed one J001 process consuming significant CPU and I/O resources. Turns out this process was the automatic segment advisor job that runs on the weekend maintenance window.
The SQL executed was something like:

CREATE TABLE .dbms_tabcomp_temp_uncmp
TABLESPACE NOLOGGING
AS
SELECT /*+ FULL(.

) */
*
FROM .

…
After reading Oracle note Id 13463481.8 and confirming this with an SR, this is related to a bug for 11.2.0.3 version and fixed in 11.2.0.4. This bug generates excessive amount of redo when running the compression advisor on a table with a LOB column in a database running in ARCHIVELOG mode.

As we can’t just apply the required patch to the ORACLE_HOME right away, we decided to perform the workaround of disabling the automatic segment advisor task. The compression advisor is part of the segment advisor and is not possible just to disable one or the other.

To disable the segment advisor:

SQL> BEGIN
dbms_auto_task_admin.disable(
client_name => ‘auto space advisor’,
operation => NULL,
window_name => NULL);
END;
/  2    3    4    5    6    7
PL/SQL procedure successfully completed.
After executing the procedure, verify that the “auto space advisor” is disabled.

SQL> SELECT client_name, status FROM dba_autotask_client;
CLIENT_NAME                                                      STATUS
—————————————————————- ——–
auto optimizer stats collection                                  ENABLED
auto space advisor                                               DISABLED
sql tuning advisor                                               ENABLED
Although the advisor will not automatically run, you can always run it manually on the segments or indexes you want to be analyzed.
Thanks,

Alfredo

How do I open the pluggable database (PDB) when I start the container database (CDB)?

If you’re running pluggable databases (multitenant) in Oracle 12c, you may wonder; how do I open the pluggable database (PDB) when I start the container database (CDB)?
Well, really it depends on the 12c version you are running on. I will show you the options you have depending the version you are running.
If you are running 12.1.0.1, then you have these options:

a)    Create a trigger that opens all the PDBs in the CDB.
CREATE OR REPLACE TRIGGER
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
END;
/

b)    Create a trigger that opens only one PDB in the container.
CREATE OR REPLACE TRIGGER
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE OPEN READ WRITE’;
END;
/

c)    If you are running RAC, then you can use the CRS to open the PDB.

srvctl add service -db -service -pdb -preferred “Container_Instance1,Container_Instance2”
Now, if you are running 12.1.0.2 version, there’s a neat new feature that saves the current state of the PDBs when the CDB restarts. In other words, if you save the state of the “pdb1” when is open, next time you start the container the “pdb1” will open automatically.

In order to do this, you can execute:

ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;
If you want to save the state of all the PDBs in the container.

ALTER PLUGGABLE DATABASE ALL SAVE STATE;
To discard the state of the PDBs.

ALTER PLUGGABLE DATABASE salespdb DISCARD STATE;
Want more?
http://docs.oracle.com/database/121/ADMIN/cdb_admin.htm#ADMIN14251
Thanks,


Alfredo

Using OMS DEBUG mode to troubleshoot OEM 12c problems

This time, I want to show you how to troubleshoot OEM problems by enabling DEBUG mode in the OMS. The virtual machine (VM) running my sandbox installation of OEM 12c 12.1.0.4 crashed during the night. After restarting the VM and all the OEM components, I wasn’t able to login using the SYSMAN account. The error from the console was not very explicit, just, “Authentication failed. If problem persists, contact your system administrator.”

In order to get more details about the error, I decided to enable DEBUG mode for the OMS and reproduce the error. This is what I did to enable DEBUG mode.

$ cd /u01/app/oracle/oms/oms/bin
$ ./emctl set property -name log4j.rootCategory -value “DEBUG, emlogAppender, emtrcAppender” -module logging
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
SYSMAN password:
Property log4j.rootCategory has been set to value DEBUG, emlogAppender, emtrcAppender for all Management Servers
OMS restart is not required to reflect the new property value
After enabling DEBUG mode, I reproduced the error several times using the console. I also wrote down the approximate time of the error, just to easy the search in the log file. Searching in the emoms.trc file located under /em/EMGC_OMS1/sysman/log/, found an ORA-14400 error. The MOS note 1493151.1, explains how to fix the issue by adding a new audit partition.

$ cd /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log/
$ view emoms.trc
java.sql.SQLException: ORA-14400: inserted partition key does not map to any partition
The final step is to disable the DEBUG mode for your OMS, otherwise the log files can grow real big and the performance could be affected.

$ ./emctl set property -name log4j.rootCategory -module LOGGING -value “WARN, emlogAppender, emtrcAppender”
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
SYSMAN password:
Property log4j.rootCategory has been set to value WARN, emlogAppender, emtrcAppender for all Management Servers
OMS restart is not required to reflect the new property value
I hope this information is useful to you next time you are troubleshooting an OEM 12c issue.
Thanks,

Alfredo

OEM 12c very slow after upgrade to 12.1.0.4

I noticed that OEM 12c console was very slow a few hours after the upgrade to 12.1.0.4 version. 

Looking at the repository DB, found several OMS sessions consuming significant CPU resources.
Bug 19199023 explains that some SQL queries executed against the repository consume high CPU on the servers. This bug affects the DB plug-in 12.1.0.6 and the patch 19176910 should be applied to the plug-ins.

More information available on MOS note, 12.1.0.4 OEM: High CPU utilization on Repository Database due to SYSMAN query WITH TARGETGUID AS (SELECT target_guid, host_name FROM mgmt_targets (Doc ID 1912172.1)
Thanks,

Alfredo