Oracle DBA – A lifelong learning experience

Creating standby database inc DG Broker and 12c changes

Posted by John Hallas on July 29, 2015

I thought I would refresh my knowledge of creating a standby database and at the same time include some DataGuard Broker configuration which also throws in some changes that came along with 12c

Overview

Database Name QUICKIE host server 1 ASM disk

Database Name STAN host server 2 ASM disk

Create a standby database STAN using ACTIVE DUPLICATE from the source database QUICKIE

 

QUICKIE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = QUICKIE)
)
)

STAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STAN)
)

server2 – listener.ora – note I have selected 1524 as that port is not currently in use and I do not want to interfere with any existing databases

 

LISTENERCLONE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524))
)
)

(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STAN)
(ORACLE_HOME = /app/oracle/product/12.1.0.2/dbhome_1)
(SID_NAME = STAN)
)
)

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

server2 – tnsnames.ora

STAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STAN)
)
)

LISTENERCLONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524))
)
(CONNECT_DATA =
(SERVICE_NAME = STAN)
)

 

  1. Start clone listener on server2

Read the rest of this entry »

Posted in 12c new features, Oracle | Tagged: , , , | 1 Comment »

Startup PDB databases automatically when a container is started – good idea?

Posted by John Hallas on July 27, 2015

I posed a note on the Oracle-L Mailing list around pluggable database and why they were not opened automatically by default when the container database was opened. The post is below

I am trying to get my head around the thing about how pluggable databases react after the container database is restarted.

Pre 12.1.0.2 it was necessary to put a startup trigger in to run a ‘alter pluggable database all open;’ command to move them from mounted to open.

Now 12.1.0.2 allows you to save a state in advance using ‘alter pluggable database xxx save state’ which does seem a step forward

However why would the default not be to start all the pluggable databases (or services as they are seen) not leave them in a mounted state. Obviously Oracle have thought about this and changed the trigger method, maybe due to customer feedback but I wonder why they have not gone the whole hog and started the services automatically.

I would much prefer to have the default to be up and running rather than relying on the fact that I have saved the state previously

I did get some interesting and very helpful responses. Jared Still made a couple of good points. The first being that the opening time for all the pluggable databases might be very long if you had 300 of them. That blew my mind a little and I must admit that I had considered scenarios where you might have half a dozen maximum, not into the hundreds.

I did a little test on a virtual 2 CPU, 16Gb server, already loaded with 6 running non container databases. I created 11 pluggables (I have created a new word there) from an existing one – each one took less than 2 minutes Read the rest of this entry »

Posted in 12c new features | Tagged: , , , | 1 Comment »

Issues around recreating a standby database in 12c

Posted by John Hallas on July 22, 2015

When you create a database in 12C it now creates a resource in HAS/CRS , which isn’t a problem

However, when you come to recreate a standby database, probably because it has got such a big lag that it is quicker to recreate than recover the log files, then you will see the following error message :-

 

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/22/2015 15:45:57
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 07/22/2015 15:45:57
RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_unique_name =  'STAN' comment= '' scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

It did stump me for a while and I thought it was around having files in the ASM DATA group from the previous incarnation but removing them did not solve it.

The word ‘resource’ gave me a clue and looking at the resources using srvctl I could see that the database STAN already existed

srvctl status database -d STAN

Database is not running.

So the fix was obvious – and indeed the error message was accurate.

srvctl remove database -d STAN

Remove the database STAN? (y/[n]) Y

Posted in 12c new features, Oracle | Tagged: , , , , , | Leave a Comment »

Large audit trail table causes high db activity – especially when using OEM

Posted by John Hallas on June 24, 2015

On various databases, apparently unrelated we have noticed high activity that seems to be associated with the query below. The quieter the database the more the query stands out.

 

SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count, TO_CHAR(MIN(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS first_occur_time, TO_CHAR(MAX(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS last_occur_time
FROM sys.dba_audit_session
WHERE returncode != 0 AND timestamp >= current_timestamp - TO_DSINTERVAL('0 0:30:00')

Read the rest of this entry »

Posted in Oracle | Tagged: , , , , , | Leave a Comment »

Cloud agent set to DEBUG causing out of memory errors

Posted by John Hallas on June 23, 2015

The following technical detail was put together by a colleague John Evans and have taken it , with his permission, and wrapped some more detail around it as it seemed to be of real value to anybody who might have upgraded an agent to 12.1.0.4

Following an upgrade of the EM agent from 12.1.0.2 (or 12.1.0.3) to 12.1.0.4 after about 90 days of usage we saw a number of agents failing with out of memory errors.

We traced this down to a line in the properties file where the trace level of parameter Logger.sdklog.level=DEBUG  rather than INFO Read the rest of this entry »

Posted in Grid control and agents, Oracle | Leave a Comment »

One million blog views reached

Posted by John Hallas on June 22, 2015

This morning I will pass the 1 million mark for hits on this blog. My first post was written in 2008 and I remember being quite pleased with myself when I reached 5000 hits, I never dreamt of getting 1 million.

The post with the most number of hits is https://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/

One that I get still comments on now saying how well it explains what the SQL92_SECURITY parameter actually does

https://jhdba.wordpress.com/2009/11/23/what-does-the-sql92_security-parameter-actually-do/

I think my current favourite is a piece that I wrote with no preparation in an hour about what I think a good DBA Manager should be doing. It does not mean I am such a person but it does demonstrate what I think is important about that role

https://jhdba.wordpress.com/2013/10/10/the-10-commandments-of-being-a-good-dba-manager/

I still use many of the posts myself because the main reason for starting the blog was to capture issues and problems I had come across. One script I find very useful is

https://jhdba.wordpress.com/2011/02/28/scripts-to-resize-standby-redolog-files/

and https://jhdba.wordpress.com/2012/03/06/the-mother-of-all-asm-scripts/ is a standard script we use a lot at my site and is invaluable

Thanks to everyone for reading and being followers. I will continue to write new posts just as long as I keep on doing technical hands-on work although I am spending more time managing than doing these days. I still really enjoy being an Oracle DBA and I have never regretted moving back into that area after spending several years as a technical project manager – which I enjoyed a lot and I still do some PM’ing  now. I was the saddo who was spotted on a beach in Majorca on a family holiday reading Jonathan Lewis’s 8i book

Posted in Oracle | 1 Comment »

Discrepancies in v$parameter default values in 12c

Posted by John Hallas on April 10, 2015

In my last blog about security parameters I mentioned I had found some oddities in the default values for parameters in 12.1.0.2, this is a more in-depth analysis of my findings.

Taking the parameter SEC_RETURN_SERVER_RELEASE_BANNER as an example.

Prior to 12c the default value for this parameter was ‘FALSE’, whereas the documentation for 12c (https://docs.oracle.com/database/121/REFRN/refrn10275.htm) states that the default is ‘TRUE’.

To confirm this, I made a connection to a 12c (12.1.0.2) database and ran the following query:

select name, value,  default_value,  isdefault

from v$parameter

where name = 'sec_return_server_release_banner';
NAME                                     VALUE               DEFAULT_VALUE       ISDEFAULT
---------------------------------------- -------------------- -------------------- ---------
sec_return_server_release_banner         FALSE               TRUE                 TRUE

After confirming that the parameter had not been explicitly set in the parameter file, or as part of an alter system/session command, we could see that the actual value, held in ‘VALUE’, given to the parameter does not match the value provided by ‘DEFAULT_VALUE’ nor did it match the value it should have been assigned according to the documentation. Read the rest of this entry »

Posted in 11g new features, 12c new features, Oracle | Tagged: , , , , , , , | 2 Comments »

Getting sql statements out of a trace file

Posted by John Hallas on March 15, 2015

The focus on this post started off in one direction and ended up in another. Originally I had been running a drop user script which had hung and even when I killed the process I could not drop the users as it gave a “ORA-01940: cannot drop a user that is currently connected” – despite the users having left the company months ago and there being no chance of them actually having connected sessions. My suspicions were that the drop user command actually took a lock on the users or connected as them whilst dropping them.  I was also intrigued by the length of time it took to drop users who had no objects.  Therefore I created a user, dropped it and traced the session to see what was happening. I was amazed by the size of the output file and that is where the direction changed. I wanted to find an easy way to get all the lines of code out of  trace file so that I could review them quickly.

SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 14 06:55:08 2015
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

create user test identified by test;
User created.
set timing on
ALTER SESSION SET sql_trace=TRUE;
Session altered.
Elapsed: 00:00:00.00
drop user test;
User dropped.
Elapsed: 00:00:00.26
ALTER SESSION SET sql_trace=FALSE;
Session altered.
Elapsed: 00:00:00.00

I now had a trace file and I used the insert=filename parameter of tkprof to produce a script containing all the sql_statements in the trace file, in the same order.

tkprof xe_ora_5796.trc xe_ora_5796.tkp insert=xe_ora_5796.sql

Edit the sql file that is produced (xe_ora_57967.sql in my example) , changing the field SQL_STATEMENT from LONG to a varchar2(4000) Read the rest of this entry »

Posted in Oracle | Tagged: , , , , , , , , , | 2 Comments »

Running two oracle installations from the same terminal

Posted by John Hallas on February 23, 2015

Two posts from me on the same day. The other one about Datapatch is about a brand new utility in 12c and is probably new to most people. This post caused mixed reactions when I mentioned it at work last week. Some people laughed at my naivety in not knowing about it, others took the same view as me and were interested to hear about it as it may prove useful one day.

A colleague had a double installation of new Oracle binaries on primary and standby servers and I suggested he get someone else to run one in parallel as he could not run two installers at the same time. He came back later on to show how it could be done and prove me wrong.

When starting up Xming using the Launch script the first window that comes up has a Display number of 0 at the bottom

xming_1

 

That maps to the :0 in the DISPLAY command you export

export DISPLAY=10.3.127.4:0.0

 So if you want a second installer to run you change the display number to 1 and use

export DISPLAY=10.3.127.4:1.0

And voila you can have 2 xming installer sessions running in parallel

The last 0 (.0) is used to have multiple screens within the same display – something that is not used very frequently these days.

So if you think I have wasted your time feel free to ignore this post and if you have found it useful I would happy to get comments saying so.

 

Posted in Oracle | Tagged: , , , , | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 256 other followers