Using Oracle Easy Connect Naming Method while connecting to Oracle and ORA-12504

(http://uraloracleblog.blogspot.com- blog_post_201610121441)

Below are examples connecting to a remote database by using Oracle Easy Connect Naming Method. This way you do not need to define a new tnsnames alias in tnsnames.ora file to connect to a remote database.
You can also use this format while creating db links.

[oracle@server01] /home/oracle > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Oct 12 14:11:41 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SYS@DB01 > conn sys@'exaserver-scan:1521/db02' as sysdba
Enter password:
Connected.

SYS@exaserver-scan:1521/db02 > select name from v$database;

NAME
---------
DB02

SYS@exaserver01-scan:1521/db02 > exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

[oracle@server01] /home/oracle >

--If you omit the port number it is assumed as 1521 by default.

[oracle@server01] /home/oracle > sqlplus sys@exaserver-scan/db02 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Oct 12 14:16:18 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Enter user-name: ^C
[oracle@server01] /home/oracle > sqlplus sys@'exaserver-scan/db02' as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Oct 12 14:16:31 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Enter user-name:
[oracle@server01] /home/oracle > sqlplus sys@'exaserver-scan:1521/db02' as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Oct 12 14:16:50 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Enter user-name: ^C

[oracle@server01] /home/oracle > tnsping exaserver-scan/db021

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 12-OCT-2016 14:22:08

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=db021))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.38.23)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.38.22)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.38.24)(PORT=1521)))
OK (10 msec)
[oracle@server01] /home/oracle > tnsping exaserver-scan/db02

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 12-OCT-2016 14:22:11

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=db02))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.38.22)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.38.24)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.38.23)(PORT=1521)))
OK (10 msec)

[oracle@server01] /home/oracle > sqlplus sys@"exaserver-scan/db02" as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Oct 12 14:26:23 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Enter user-name: ^C
[oracle@server01] /home/oracle > sqlplus sys@"exaserver-scan:1521/db02" as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Oct 12 14:26:45 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Enter user-name: ^C
[oracle@server01] /home/oracle > sqlplus sys@"exaserver-scan:1521/db021" as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Oct 12 14:26:52 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Enter user-name: ^C

--When we escaped the double quotes then it worked

[oracle@server01] /home/oracle > sqlplus sys@\"exaserver-scan:1521/db02\" as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Oct 12 14:32:15 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SYS@exaserver-scan:1521/db02 > exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@server01] /home/oracle >

SYS@DB01 >
SYS@DB01 > conn sys@"exaserver-scan:1521/db02" as sysdba
Enter password:
Connected.
SYS@exaserver-scan:1521/db02 >

--You can use the same syntax while creating a db link without defining a new tns alias in tnsnames.ora for the remote database.

create database link "db02_exaserver"  
connect to user01                   
identified by "password01"           
using 'exaserver-scan:1521/db02';

Alter session set current_schema

(http://uraloracleblog.blogspot.com- blog_post_201610121358)

While you are connected to an Oracle database as SYS user, you can run sql statements as if you are connected as different database user as below.
Table01 is a table in user01 schema.

connect sys as sysdba

alter session set current_schema=user01;

alter table table01 drop constraint table01_ck

/

Oracle RAC scan listener

(http://uraloracleblog.blogspot.com- blog_post_201610120921)

You can check the status of the scan VIPs and scan listener with the below commands.

[grid@exeserver01] /home/grid > srvctl config scan
SCAN name: exa01-scan, Network: 1
Subnet IPv4: 10.10.38.0/255.255.255.0/bondeth0, static
Subnet IPv6:
SCAN 0 IPv4 VIP: 10.10.38.23
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 1 IPv4 VIP: 10.10.38.22
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 2 IPv4 VIP: 10.10.38.24
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:

[grid@exeserver01] /home/grid > srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node exaserver01
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node exaserver02
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node exaserver02


[grid@exeserver01] /home/grid > srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
[grid@exeserver01] /home/grid >


[grid@exeserver01] /home/grid > srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node exeserver01
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node exeserver02
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node exeserver02
[grid@exeserver01] /home/grid >

--To stop the scan listener
[grid@exeserver01] /home/grid > srvctl stop scan_listener

--To stop the scan VIPs
[grid@exeserver01] /home/grid > srvctl stop scan

--Check that DNS can resolve the scan Ips
[grid@exeserver01] /home/grid > nslookup exa01-scan
Server:         10.10.10.35
Address:        10.10.10.35#53

Name:   exa01-scan.anadoluhayat.com.tr
Address: 10.10.38.23
Name:   exa01-scan.anadoluhayat.com.tr
Address: 10.10.38.24
Name:   exa01-scan.anadoluhayat.com.tr
Address: 10.10.38.22

--To start the scan listener (This will also start the scan VIPs)

[grid@exeserver01] /home/grid > srvctl start scan_listener

Oracle stored function returning columns of a table as one line seperated by commas

(http://uraloracleblog.blogspot.com- blog_post_201610111902)

Whenever you need a function that returns columns of a table as comma delimited line.
You can use it in an automation script.

CREATE OR REPLACE function user01.f_cols
(
 owner      varchar2,
 table_name varchar2
)
return varchar2
is
  i varchar2(32000);
  column_name varchar2(32000);
  stmt varchar2(32000);
  type cur_typ is ref cursor;
  c cur_typ;
begin
  stmt := 'select column_name from dba_tab_cols where owner='''
||owner||''' and table_name ='''||table_name ||''' and virtual_column = ''NO'' order by column_id';
  open c for stmt;
    loop
      fetch c into column_name;
      exit when c%notfound;
      i:= i||',"'||column_name||'"';
    end loop;
  close c;
  i:= substr(i,2);

  return(i);
End f_cols;

/

ORA-00245: control file backup failed; target is likely on a local file system

(http://uraloracleblog.blogspot.com- blog_post_201610111852)

RMAN gives the below error for an Oracle RAC database while taking backup.

ORA-00245: control file backup failed; target is likely on a local file system

Related RAC database snapshot controlfile should be configured to be created on a clustered filesystem for a Oracle RAC database not to get this error.

You can also check the below Oracle Support note related to this error.
ORA-245: In RAC environment from 11.2 onwards Backup Or Snapshot controlfile needs to be in shared location (Doc ID 1472171.1)


Below are what I did to solve this error.

[root@server01 ~]# su - oracle
[oracle@server01 ~]$ export ORACLE_SID=DB011
[oracle@server01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 17 09:07:51 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB01 (DBID=2013035595)

RMAN> show snapshot controlfile name;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DB01 are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_DB011.f'; # default

--I created a new folder in the cluster shared filesystem
[root@server01 cloudfs]# mkdir snapshot_controlfile
[root@server01 cloudfs]# cd snapshot_controlfile
[root@server01 snapshot_controlfile]# pwd
/cloudfs/snapshot_controlfile
[root@server01 ~]# chown oracle:oinstall /cloudfs/snapshot_controlfile


RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/cloudfs/snapshot_controlfile/snapcf_DB011.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/cloudfs/snapshot_controlfile/snapcf_DB011.f';
new RMAN configuration parameters are successfully stored


RMAN> 

Oracle Linux Crontab job to delete the automatically generated Oracle database audit files

(http://uraloracleblog.blogspot.com- blog_post_201610111839)

This is a scheduled job to remove Oracle database generated audit files older than 2 weeks that will run everyday at 18:45.

[oracle@server01 ~]$ crontab -e
45 18 * * * /usr/bin/find /u01/app/12.1.0/grid/rdbms/audit -type f -mtime +14 -exec rm {} \;

[oracle@server01 ~]$ crontab -l

45 18 * * * /usr/bin/find /u01/app/12.1.0/grid/rdbms/audit -type f -mtime +14 -exec rm {} \;

What is the SID of my current session in Oracle database

(http://uraloracleblog.blogspot.com- blog_post_201610111831)

In a single instance database use the below sql.
 
select min(sid) from v$mystat;
/*
MIN(SID)
4209
*/

In a Oracle RAC database you can use the following sql.

select inst_id, min(sid) from gv$mystat group by inst_id order by inst_id;
/*
INST_ID,MIN(SID)
1,1291
2,302

*/

Using Oracle Easy Connect Naming Method while connecting to Oracle and ORA-12504

( http://uraloracleblog.blogspot.com - blog_post_201610121441) Below are examples connecting to a remote database by using Oracle Easy Conne...