Monitor Tablespace Script Oracle 12c 11g 10g

The Below Script will generate output if percentage of tablespace space left free is less tan 20%. You can easily put in a shell script and schedule it on crontab to send alerts on a daily basis.

 

select df.tablespace_name tspace,
round(sum(fs.bytes)/(df.bytes) * 100) “%_free”,
round(sum(fs.bytes)/(1024*1024)) free_ts_size,
df.bytes/(1024*1024) tot_ts_size
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
having round(sum(fs.bytes)/(df.bytes) * 100) < 20;

Active – Active (Bi-Directional) Replication using Oracle Golden Gate 12c

Since it was announced in Early 2010 that Oracle GoldenGate will be the RoadMap for Replication for Oracle Database, The adoption of GoldenGate over Streams has been quite Rapid. The complexity of Streams has intimidated many adopters, specially for Multi-Master or Peer-to-Peer Replication. GoldenGate not only makes MultiMaster Replication easy but is so full of options and topologies that anybody who has used Golden Gate would most likely never go back to Oracle Streams.

In the Example below we will create a Bi-Directional Model where both the Tables have same name in different databases and can be used for INSERT’ing transactions. The CR( Conflict Resolution) is handled by GG itself.

You can download the latest version of GoldenGate from OTN >> http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

We will use the Sample Table EMP in Sample Schema SCOTT . Before we begin, the assumption is that you have installed and created Oracle Database 12c in both Site A and Site B for creating the replication, (This setup was done on Windows platform, there isn’t much difference between the Linux and Windows setup)

Site A : GGPRIM

————————

Host OS: Windows 2008R2 X64

DB Version: Oracle 12c 12.1.0.2

GoldenGate Version : 12.1.2

Site A : GGSTANDBY

————————-

Host OS: Windows 2008R2 X64

DB Version: Oracle 12c 12.1.0.2

GoldenGate Version : 12.1.2

With 12c the GoldenGate installation is GUI based. GG12c will need to be installed on both Sites and ensure that both databases are in archive log mode.

1. Enable Supplemental Logging
** Do Below Steps in Both Databases **

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;
Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

SQL> create user ggate identified by ggate;

SQL> grant dba to ggate;
SQL> conn scott/scott

2. At SITE A, Create the extract (EXT1) and data pump (DPUMP1) —

Site A Home Directory of GG : “D:\app\sql_admin\product\12.1.2\oggcore_1”

add extract ext1 tranlog begin now

add exttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa extract ext1

add extract dpump1 exttrailsource D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa

— The below Directory is remote directory on other Database Server —

ADD RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab, EXTRACT DPUMP1

edit params ext1

EXTRACT ext1
USERID ggate, PASSWORD ggate
EXTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;

edit params dpump1

EXTRACT dpump1
USERID ggate, PASSWORD ggate
RMTHOST 172.21.104.49, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab
PASSTHRU
TABLE scott.emp;

3. On SITE B Add Replicat (REP2) —

Site A Home Directory of GG : “F:\app\sql_admin\product\12.1.2\oggcore_1”

ggsci> dblogin userid ggate
password : ******

ADD CHECKPOINTTABLE ggate.ggschkpt

exit

ggsci>

add replicat rep2 exttrail F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab checkpointtable ggate.ggschkpt
REPLICAT added
.

edit params rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE F:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;

Create the extract (EXT2) and data pump (DPUMP2) on Site B

add extract ext2 tranlog begin now
EXTRACT added.

add exttrail F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac extract ext2
EXTTRAIL added.

add extract dpump2 exttrailsource F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
EXTRACT added.

— Below Directory is Remote Directory of Other Database Server —

add rmttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad, extract dpump2
RMTTRAIL added.

edit params ext2

EXTRACT ext2
USERID ggate, PASSWORD ggate
EXTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;

edit params dpump2

EXTRACT dpump2
USERID ggate, PASSWORD ggate
RMTHOST 172.20.4.13, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad
PASSTHRU
TABLE scott.emp;

4. On SITE A, Add replicat (REP1) —

ggsci> dblogin userid ggate
password : ******

ADD CHECKPOINTTABLE ggate.ggschkpt

exit

ggsci>

add replicat rep1 exttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad checkpointtable ggate.ggschkpt
REPLICAT added.

edit params rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE D:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;

5. On both SITE A AND SITE B, add trandata, this steps will be done on both sites —

dblogin userid ggate password ggate
Successfully logged into database.

add trandata scott.emp

info trandata scott.emp

exit

6. At SITE A, Start the Extract and Data Pump process —

start mgr

start extract ext1
start extract dpump1
info extract ext1
info all

7. SITE B, Start the Extract and Data Pump process on Site B —

start extract ext2
start extract dpump2
info all

— SITE A —

start replicat rep1

status replicat rep1

— SITE B —

start replicat rep2

status replicat rep2

8. — ERROR in ggserr when starting extract —

If you get below error in starting extract on both sites “Operation not supported because enable_goldengate_replication is not set to true.” then set the parameter enable_goldengate_replication=true

— On Both Sites A and B —

sqlplus "/as sysdba"

alter system set enable_goldengate_replication=true scope=spfile;

Restart both the databases since it is a static parameter(this parameter is only in 11.2.0.4 onwards and in 12c)
Now restart the extract process on both Sites

start extract ext1

start extract ext2

— If you get Directory Level Errors, check on Both sides the RMTTRAIL is pointing to Remote Directory of other server respectively and Remove any Firewall Service blocking the ports servers on both —

-- In Site A --
ADD RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab, EXTRACT DPUMP1

-- In Site B --
ADD RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad, EXTRACT DPUMP2

Do this and Start the extract dpump1 and dpump2, replicat rep1 and rep2 in Both sites A and B

9. — Testing The Active Active Replication —

on Site A

Insert into SCOTT.EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(1111, 'SHADAB', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 20);

commit;

Check Record in Site b

On Site B

Insert into SCOTT.EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(1112, 'Yusuf', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 20);

commit;

Check Record in Site A

10.
————————————–
|SUMMARY OF DEFINITIONS ON BOTH SITES |
————————————–

There is a slight modification in definitions below, I created another table called tb_table_1 (code below) on both sites to demonstrate you can add as many tables as you want or even the full schema using * wildcard.

create table Tb_table_1(
user_id number primary key,
field_1 varchar2(30)
);

—- SITE A DEFINITIONS —

edit param mgr

PORT 7809

edit param ext1

EXTRACT ext1
USERID ggate, PASSWORD ggate
EXTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param dpump1

EXTRACT dpump1
USERID ggate, PASSWORD ggate
RMTHOST 172.21.104.49, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab
PASSTHRU
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE D:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;
MAP scott.tb_table_1, TARGET scott.tb_table_1;
TABLE scott.tb_table_1;

—- SITE B DEFINITIONS —

edit param mgr

PORT 7809

edit param ext2

EXTRACT ext2
USERID ggate, PASSWORD ggate
EXTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param dpump2

EXTRACT dpump2
USERID ggate, PASSWORD ggate
RMTHOST 172.20.4.13, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad
PASSTHRU
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE F:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;
MAP scott.tb_table_1, TARGET scott.tb_table_1;

ksvcreate: Process(m000) creation failed. Error in Alert log due to DNS ip change.

Today on one of our 10g (10.2.0.1, don’t ask !) production databases  running on AIX 5.3 we saw the following message in the alert log.

ksvcreate: Process(m000) creation failed

The database slowed down to a crawl. We couldn't login even SQLPLUS, even sqlplus -prelim couldn't bring up the instance. On starting the instance we got the below error.

ORA-00445: background process "PMON" did not start after 120 seconds

After googling around, we figured out it could be related to a DNS change. Our old DNS was commissioned and the new ones were not added to the resolv.conf file. After adding the new name servers. The instance started immediately without any issues. I'm still wondering what is the relation between the Oracle instance startup and the DNS ip's. Could this be a bug specific to 10.2.0.1 or is it something more generic.

Running SQL Script for Multiple Databases SQL*PLUS | Mutiple Servers SQL*PLUS in Shell Script | BASH Script Looping SQL*PLUS commands

For running a single SQL Command via sqlplus on multiple servers, we have to first do a few pre-requisites

1. Oracle client should be installed on the Unix/Linux Server

2. Create Local TNS entry in the tnsnames.ora file for the databases where you will run the commands

3. All databases should have one common user with a common password (this method is not the safest method
since the password will be in plain text in your Shell script)
Now let us proceed to first create a text file called dbnodes.txt which will have the TNSNAMES for the
database we will connect to..

boston
chicago
newyork

Above is the entries in the dbnodes.txt file, these are the databases where we will loop the sql commands

Create a sql script with the commands you have to run on all the databases, lets call the file script.sql

set echo on
set linesize 200
set pages 0
select sysdate from dual;
select user from dual;
select instance_name,host_name from gv$instance;
alter system set cpu_count=8 scope=both sid='*';
show parameter cpu_count;
alter session set nls_date_format='DD/MM/YYYY';
exit;

I do this to dynamically to cap the cpu_count on some of our databases to contain any resouce hogging.

Now the final shell script which will call the dbnodes.txt and script.sql to loop the above sql commands
through all databases. The shell script is called dbloop.sh

#!/bin/bash
cat dbnodes.txt | while read line
do
sqlplus -s user/user123@$line @/u03/scripts/script.sql
done

Copy all 3 files dbnodes.txt, script.sql and dbloop.sh to one directiry and run it like below; to collect the log of the SQL commands.

$--> dbloop.sh > dbrun.log

This will spool the output to a logfile.

So there you see it, one of the easiest ,method to run a set ofcommon sql commands on hundreds of server. I use this script to do basics like checking a certain parameters on all our production databases, to check dataguard status for multiple production DB’s etc. The use cases of this script is unlimited. Hope you enjoyed it 🙂 Keep it Easy and Keep It Oracle !

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
TNS-12541: TNS:no listener
TNS-00511: No listener
ERROR:
ORA-12560: TNS:protocol adapter error

So you tried connecting to an Oracle Database and got the dreadful error. TNS listener not available, this is one of the most common errors connecting to an Oracle database and one which could have a wide variety of reasons. Though it would not be possible to touch the length and breadth of why this error cancome. I will elaborate using a small test scenario.

The first thing you would like to look at when you get this error is if you have defined the SERVICE_NAME correctly in the tnsnames.ora file

After which login to the Oracle DB Server and checl with “lsnrctl status”
$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 27-OCT-2014 07:35:22

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 26-OCT-2014 20:51:02
Uptime 0 days 10 hr. 44 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/testdb01/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Services Summary…
If you do no see the service name defined in your TNSNAMES.ora file in the listener services, then there is a good possibility that the PMON process (the process responsible for registering the instance with listener) has not done its job. There are 2 days to register the instance with the listener : Dynamic Registration and Static Registration.

Static Registration : It is the process by which an explicit name is defined in the listener.ora file to register the instance with the listener.

Below is an example of a listener.ora file where instances are statically registered.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test01)
(ORACLE_HOME = /u02/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = test01)
)
(SID_DESC =
(GLOBAL_DBNAME = test02)
(ORACLE_HOME = /u02/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = test02)
)
(SID_DESC =
(GLOBAL_DBNAME = test03)
(ORACLE_HOME = /u02/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = test03)
)
)
This will reguster the test01, test02, test03 services with the listener. Please note that this does not guarantee a connection, since the appropriate instance should be running in the background.
Dynamic Registration: PMON is the process associated with registering the instance with the listener. Generally there are few parameters associated with dynamic registration viz. LOCAL_LISTENER, DB_NAME,DB_DOMAIN, SERVICE_NAMES etc.

To add address of a local listener and register it, below are the steps you have to do

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))’ scope=both;

System altered.

SQL> alter system register;

System altered.
SQL> show parameter local

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=1
27.0.0.1)(PORT=1521))
This will register the service on listener as DB_NAME (if DB_DOMAIN is defined DB_NAME.DB_DOMAIN is the format used)

SQL> show parameter db_domain

NAME TYPE VALUE
———————————— ———– ——————————
db_domain string easyoradba.com
SQL> show parameter db_name

NAME TYPE VALUE
———————————— ———– ——————————
db_name string test
SQL> show parameter service_name

NAME TYPE VALUE
———————————— ———– ——————————
service_names string test.easyoradba.com
So when you check the listener you will see a service called ‘test.easyoradba.com’ is registerd with instanced called ‘test’

The tnsnames.ora for this setup will ideally look like

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test.easyoradba.com)
)
)