learn-oracle-RAC

Saturday, March 19, 2016

ENABLE REAL TIME APPLY ON STANDBY DATABASE

*********************************************************************
    REAL TIME APPLY OF ARCHIVE LOG FILES IN READ ONLY MODE:
*********************************************************************

SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/app/oracle/oraarch/
Oldest online log sequence     250
Next log sequence to archive   0
Current log sequence           252

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> alter database open read only;

Database altered.

***********************************************************************************
    NOW ON PRIMARY DATABASE I CREATE TABLE IN ONE OF USER ADD INSERT SOME DATA
***********************************************************************************

[oracle@Linux01 Desktop]$ sqlplus /"As sysdba"

SQL> select name,open_mode,db_unique_name,PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

NAME        OPEN_MODE                   DB_UNIQUE_NAME   PROTECTION_MODE        PROTECTION_LEVEL
--------     --------------------    ---------         --------------------         ---------
ORCL        READ ONLY WITH APPLY     ORCLSTB1        MAXIMUM PERFORMANCE     MAXIMUM PERFORMAN

SQL> conn atoorpu/XXXXX;
Connected.
SQL> create table abcd (name varchar2(20));

Table created.

SQL> insert into abcd values ('arvind');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into abcd values ('reddy');

1 row created.

SQL> commit;

Commit complete.

SQL> create table abcds as select * from abcd;

Table created.

***********************************************************************************
    NOW ON STANDBY DATABASE LETS QUERY SAME DATA SOME DATA
***********************************************************************************

[oracle@Linux02 Desktop]$ sqlplus /"As sysdba"

SQL> select name,open_mode,db_unique_name,PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

NAME        OPEN_MODE                   DB_UNIQUE_NAME   PROTECTION_MODE        PROTECTION_LEVEL
--------     --------------------    ---------         --------------------         ---------
ORCL        READ ONLY WITH APPLY     ORCLSTB1        MAXIMUM PERFORMANCE     MAXIMUM PERFORMAN

SQL> conn atoorpu/XXXXX;
Connected.
SQL> select * from abcd;

NAME
--------------------
arvind

SQL> select * from abcd;

NAME
--------------------
arvind
reddy

OPEN STANDBY DATABASE IN READ ONLY

SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

SQL> select name,db_unique_name,open_mode,DATABASE_ROLE from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE

--------- ------------------------------ -------------------- ----------------

ORCL ORCLSTB1 MOUNTED PHYSICAL STANDBY

SQL> alter database open read only;

Database altered.

SQL> select name,db_unique_name,open_mode,DATABASE_ROLE from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE

--------- ------------------------------ -------------------- ----------------

ORCL ORCLSTB1 READ ONLY PHYSICAL STANDBY

SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE OPEN_MODE

---------------- --------------------

PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH    CLOSING
ARCH    CLOSING
ARCH    CONNECTED
ARCH    CLOSING
ARCH    CLOSING
RFS    IDLE
RFS    IDLE
RFS    IDLE
RFS    IDLE
MRP0    APPLYING_LOG

Similar Posts :

Friday, March 18, 2016

no emocmrsp oracle respose file found in OPTACH directory

grid@Linux211:[/u01/app/11.2/grid_home/OPatch/ocm/bin] $ cd /u01/app/11.2/grid_home/OPatch/ocm/bin/emocmrsp

*************************** ****************************
Missing emosmrsp
Opatch requires ocm.rsp file. Note 966023.1 describes How To Create An OCM Response File For Opatch Silent Installation.

**************************** ****************************

Note :

The OPatch (Patch p6880880) brings the $GRID_HOME/Opatch/ocm/bin/emocmrsp.
You have to use the most recent OPatch anyway, it's a prerequisite for every PSU and patch to apply.

-bash: cd: /u01/app/11.2/grid_home/OPatch/ocm/bin/emocmrsp: No such file or directory

grid@Linux211:[~] $ cd $ORACLE_HOME
grid@Linux211:[/u01/app/11.2/grid_home] $ cd OPatch/
grid@Linux211:[/u01/app/11.2/grid_home/OPatch] $ ls
crs   emdpatch.pl ocm     opatch.bat opatchdiag.bat opatch.pl      oplan
docs jlib         opatch opatchdiag opatch.ini      opatchprereqs

grid@Linux211:[/u01/app/11.2/grid_home/OPatch] $ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.

Oracle Home       : /u01/app/11.2/grid_home
Central Inventory : /u01/app/grid/oraInventory
   from           : /u01/app/11.2/grid_home/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /u01/app/11.2/grid_home/cfgtoollogs/opatch/opatch2016-03-16_09-34-04AM_1.log

Lsinventory Output file location : /u01/app/11.2/grid_home/cfgtoollogs/opatch/lsinv/lsinventory2016-03-16_09-34-04AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Grid Infrastructure 11g                                       11.2.0.4.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

Rac system comprising of multiple nodes
Local node = Linux211
Remote node = Linux212

--------------------------------------------------------------------------------

OPatch succeeded.
grid@Linux211:[/u01/app/11.2/grid_home/OPatch] $ opatch version
OPatch Version: 11.2.0.3.4

OPatch succeeded.
grid@Linux211:[/u01/app/11.2/grid_home/OPatch] $

*******************************************************************************
UPGRADING OPATCH TO OPatch version    : 11.2.0.3.12
*******************************************************************************

Asumming that you have downloaded and copied patch (6880880) to ORACLE_HOME

grid@Linux211:[/u01/app/11.2/grid_home] $ unzip p6880880_112000_Linux-x86-64.zip
Archive: p6880880_112000_Linux-x86-64.zip
   creating: OPatch/
inflating: OPatch/opatchdiag
inflating: OPatch/opatch.pl
   creating: OPatch/jlib/
inflating: OPatch/jlib/oracle.opatchcore.classpath.jar
inflating: OPatch/jlib/opatch.jar
inflating: OPatch/jlib/oracle.opatch.classpath.jar
inflating: OPatch/jlib/oracle.opatch.classpath.unix.jar
inflating: OPatch/jlib/oracle.opatch.classpath.windows.jar
inflating: OPatch/jlib/opatchsdk.jar
inflating: OPatch/jlib/oracle.opatchcore.classpath.unix.jar
inflating: OPatch/jlib/oracle.opatchcore.classpath.windows.jar
   creating: OPatch/crs/
inflating: OPatch/crs/patch11203.pl
inflating: OPatch/crs/installPatch.excl
inflating: OPatch/crs/patch11202.pl
inflating: OPatch/crs/auto_patch.pl
   creating: OPatch/crs/log/
inflating: OPatch/crs/patch112.pl
inflating: OPatch/crs/patchDB.pl
inflating: OPatch/crs/opatchauto
inflating: OPatch/crs/CRSProductDriver.jar
inflating: OPatch/crs/driver.jar
   creating: OPatch/ocm/
   creating: OPatch/ocm/doc/
extracting: OPatch/ocm/ocm.zip
inflating: OPatch/ocm/ocm_platforms.txt
   creating: OPatch/ocm/lib/
inflating: OPatch/ocm/lib/emocmclnt.jar
inflating: OPatch/ocm/lib/xmlparserv2.jar
inflating: OPatch/ocm/lib/http_client.jar
inflating: OPatch/ocm/lib/emocmclnt-14.jar
inflating: OPatch/ocm/lib/regexp.jar
inflating: OPatch/ocm/lib/jnet.jar
inflating: OPatch/ocm/lib/jsse.jar
inflating: OPatch/ocm/lib/osdt_core3.jar
inflating: OPatch/ocm/lib/osdt_jce.jar
inflating: OPatch/ocm/lib/emocmcommon.jar
inflating: OPatch/ocm/lib/log4j-core.jar
inflating: OPatch/ocm/lib/jcert.jar
   creating: OPatch/ocm/bin/
inflating: OPatch/ocm/bin/emocmrsp
inflating: OPatch/opatch
   creating: OPatch/opatchauto-dir/
   creating: OPatch/opatchauto-dir/opatchautocore/
inflating: OPatch/opatchauto-dir/opatchautocore/oplan
inflating: OPatch/opatchauto-dir/opatchautocore/opatchautobinary
   creating: OPatch/opatchauto-dir/opatchautocore/jlib/
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/ValidationRules.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/patchsdk.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/ProductDriver.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/osysmodel-utils.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/OsysModel.jar
   creating: OPatch/opatchauto-dir/opatchautocore/jlib/apache-commons/
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/apache-commons/commons-compress-1.4.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/apache-commons/commons-cli-1.0.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/oplan_core.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/oracle.oplan.classpath.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/bundle.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/oplan_sample.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/Validation.jar
   creating: OPatch/opatchauto-dir/opatchautocore/jlib/jaxb/
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/jaxb/activation.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/jaxb/jsr173_1.0_api.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/jaxb/jaxb-api.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/jaxb/jaxb-impl.jar
inflating: OPatch/opatchauto-dir/opatchautocore/jlib/automation.jar
inflating: OPatch/opatchauto-dir/opatchautocore/oplan.bat
inflating: OPatch/opatchauto-dir/opatchautocore/README.txt
inflating: OPatch/opatchauto-dir/opatchautocore/README.html
   creating: OPatch/opatchauto-dir/opatchautodb/
   creating: OPatch/opatchauto-dir/opatchautodb/jlib/
inflating: OPatch/opatchauto-dir/opatchautodb/jlib/oracle.oplan.db.classpath.jar
inflating: OPatch/opatchauto-dir/opatchautodb/jlib/oplan_db.jar
inflating: OPatch/emdpatch.pl
   creating: OPatch/scripts/
inflating: OPatch/scripts/opatch_wls.bat
inflating: OPatch/scripts/opatch_jvm_discovery.bat
inflating: OPatch/scripts/opatch_wls
inflating: OPatch/scripts/opatch_jvm_discovery
inflating: OPatch/operr
inflating: OPatch/operr_readme.txt
inflating: OPatch/operr.bat
   creating: OPatch/opatchprereqs/
inflating: OPatch/opatchprereqs/prerequisite.properties
   creating: OPatch/opatchprereqs/oui/
inflating: OPatch/opatchprereqs/oui/knowledgesrc.xml
   creating: OPatch/opatchprereqs/opatch/
inflating: OPatch/opatchprereqs/opatch/runtime_prereq.xml
inflating: OPatch/opatchprereqs/opatch/rulemap.xml
inflating: OPatch/opatchprereqs/opatch/opatch_prereq.xml
inflating: OPatch/opatch.bat
   creating: OPatch/oplan/
   creating: OPatch/oplan/jlib/
   creating: OPatch/oplan/jlib/apache-commons/
inflating: OPatch/oplan/jlib/apache-commons/commons-cli-1.0.jar
   creating: OPatch/oplan/jlib/jaxb/
inflating: OPatch/oplan/jlib/jaxb/jsr173_1.0_api.jar
inflating: OPatch/oplan/jlib/jaxb/jaxb-impl.jar
inflating: OPatch/oplan/jlib/jaxb/activation.jar
inflating: OPatch/oplan/jlib/jaxb/jaxb-api.jar
inflating: OPatch/oplan/jlib/Validation.jar
inflating: OPatch/oplan/jlib/oracle.oplan.classpath.jar
inflating: OPatch/oplan/jlib/OuiDriver.jar
inflating: OPatch/oplan/jlib/EMrepoDrivers.jar
inflating: OPatch/oplan/jlib/JMXDrivers.jar
inflating: OPatch/oplan/jlib/bundle.jar
inflating: OPatch/oplan/jlib/CRSProductDriver.jar
inflating: OPatch/oplan/jlib/oplan.jar
inflating: OPatch/oplan/jlib/ValidationRules.jar
inflating: OPatch/oplan/jlib/osysmodel-utils.jar
inflating: OPatch/oplan/jlib/automation.jar
inflating: OPatch/oplan/README.html
inflating: OPatch/oplan/README.txt
inflating: OPatch/oplan/oplan.bat
inflating: OPatch/oplan/oplan
inflating: OPatch/opatchdiag.bat
inflating: OPatch/README.txt
extracting: OPatch/version.txt
   creating: OPatch/docs/
inflating: OPatch/docs/cversion.txt
inflating: OPatch/docs/Prereq_Users_Guide.txt
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/Users_Guide.txt

grid@Linux211:[/u01/app/11.2/grid_home] $ ls -ll p6880880_112000_Linux-x86-64.zip
total 51796

-rw-r--r--. 1 grid oinstall 52733900 Mar 16 09:58 p6880880_112000_Linux-x86-64.zip

grid@Linux211:[/u01/app/11.2/grid_home] $ rm p6880880_112000_Linux-x86-64.zip
grid@Linux211:[/u01/app/11.2/grid_home] $ cd OPatch

grid@Linux211:[/u01/app/11.2/grid_home/OPatch] $ ls
crs          jlib    opatchauto-dir opatchdiag.bat operr             oplan       version.txt
docs         ocm     opatch.bat      opatch.pl       operr.bat         README.txt
emdpatch.pl opatch opatchdiag      opatchprereqs   operr_readme.txt scripts

grid@Linux211:[/u01/app/11.2/grid_home/OPatch] $ PATH=$PATH:/u01/app/11.2/grid_home/OPatch
grid@Linux211:[/u01/app/11.2/grid_home/OPatch] $ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation. All rights reserved.

Oracle Home       : /u01/app/11.2/grid_home
Central Inventory : /u01/app/grid/oraInventory
   from           : /u01/app/11.2/grid_home/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/11.2/grid_home/cfgtoollogs/opatch/opatch2016-03-16_09-59-17AM_1.log

Lsinventory Output file location : /u01/app/11.2/grid_home/cfgtoollogs/opatch/lsinv/lsinventory2016-03-16_09-59-17AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: Linux211.devquikpayasp.com
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Grid Infrastructure 11g                                       11.2.0.4.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

Rac system comprising of multiple nodes
Local node = Linux211
Remote node = Linux212

--------------------------------------------------------------------------------

OPatch succeeded.
grid@Linux211:[/u01/app/11.2/grid_home/OPatch] $ cd bin
-bash: cd: bin: No such file or directory

grid@Linux211:[/u01/app/11.2/grid_home/OPatch] $ ls
crs          jlib    opatchauto-dir opatchdiag.bat operr             oplan       version.txt
docs         ocm     opatch.bat      opatch.pl       operr.bat         README.txt
emdpatch.pl opatch opatchdiag      opatchprereqs   operr_readme.txt scripts

grid@Linux211:[/u01/app/11.2/grid_home/OPatch] $ cd ocm/bin/

*******************************************************************************
Now we can see the oracle response file. This file is a part of OPTACH :
*******************************************************************************

grid@Linux211:[/u01/app/11.2/grid_home/OPatch/ocm/bin] $ ls
emocmrsp
grid@Linux211:[/u01/app/11.2/grid_home/OPatch/ocm/bin] $

Thursday, March 17, 2016

Drop all tables in a schema

**********************************************************************
You can use this simple plsql block to drop all objects in your current schema.
**********************************************************************

declare
VSQL varchar2(4000);
OBJ_NAME varchar2(100);
OBJ_TYPE varchar2(100);
OBJ_OWNER varchar2(100);
cursor c1 is select object_type,object_name from user_objects where object_type in ('TABLE','VIEW');

begin

open c1;
loop
fetch c1 into OBJ_TYPE,OBJ_NAME;
exit when c1%NOTFOUND;
IF OBJ_TYPE='TABLE'
THEN
VSQL:=' drop '||OBJ_TYPE||' '||OBJ_NAME||' cascade constraints';
DBMS_OUTPUT.PUT_LINE(VSQL);
execute IMMEDIATE VSQL;
ELSE
VSQL:=' drop '||OBJ_TYPE||' '||OBJ_NAME;
DBMS_OUTPUT.PUT_LINE(VSQL);
execute IMMEDIATE VSQL;
END IF;
end loop;
close c1;
end;
/

Note :

Remove "where object_type in ('TABLE','VIEW')" to drop all objects from current user.
Or you can edit to include only type of objects you want to be dropped.

You can Alter OBJECT TYPE any of below:

'TABLE','VIEW','SYNONYM','SEQUENCE','PROCEDURE','TRIGGER'

Wednesday, March 16, 2016

Restricting database access to IP addeess using sqlnet.ora

**************************************************************

FROM MY CLIENT MACHINE:

**************************************************************

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:20

Used parameter files:

C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo

cl1)))

OK (0 msec)

**************************************************************

NOW I want my DB to be only be accessible from this machine and restrict all other. For this I will need to make changed to my DB server sqlnet.ora :

**************************************************************

ON DB SERVER:

oracle@Linux03 admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tcp.validnode_checking = yes ## I have just added this line to test if it works

[oracle@Linux03 admin]$ vi sqlnet.ora

[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:20:03

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

TNS-00584: Valid node checking configuration error

TNS-12560: TNS:protocol adapter error

As expected it doesn't work as listener needs to know where to accept or deny connections from.

Now I tweak the sqlnet.ora file to below

[oracle@Linux03 admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tcp.validnode_checking = yes

tcp.invited_nodes = (Linux03) ## I only want to accept connections from my localhost i.e Linux03

RESTART THE LISTENER NOW.

[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:21:06

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

The command completed successfully

(OR)

[oracle@Linux03 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:36

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

The command completed successfully

[oracle@Linux03 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:41

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production

System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 07-MAR-2016 15:28:41

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

The listener supports no services

The command completed successfully

****************************************************

NOW I TRY TO CONNECT FROM CLIENT AGAIN :

****************************************************

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:57

Used parameter files:

C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo

cl1)))

TNS-12537: TNS:connection closed

C:\Windows\System32>

Nope it doesn't allow me to connect.

*************************************************************

Lets try to remove the tcp.validnode_checking & tcp.invited_nodes and see

**************************************************************

[oracle@Linux03 admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:31:58

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

The command completed successfully

[oracle@Linux03 admin]$ vi sqlnet.ora

[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:32:16

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

The command completed successfully

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:36

Used parameter files:

C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo

cl1)))

OK (0 msec)

**************************************************************************

SUCCESS YOU HAVE SUCCESFULLY IMPLEMENTED ANOTHER LAYER OF SECURITY ON DB SERVER

*************************************************************************

tcp.validnode_checking = yes

tcp.invited_nodes = (Linux03,HOSTNAME1,HOSTNAME2 ...)

YOU CAN ALSO EXPLICITLY EXCLUDE NODE LIKE THIS :

tcp.excluded_nodes = (192.168.100.101,HOSTNAME2,)

But : If you only specify invited nodes with tcp.invited_nodes, all other nodes will be excluded, so there is really no reason to do both. The same is true for excluded nodes. If you put tcp.excluded_nodes = (192.168.100.101,192.168.100.160) then IP containing 192.168.100.101 and 192.168.100.160 will be excluded/denied to connect to database as a database user while allowing others to connect.

TABS

Saturday, March 19, 2016

ENABLE REAL TIME APPLY ON STANDBY DATABASE

OPEN STANDBY DATABASE IN READ ONLY

Friday, March 18, 2016

no emocmrsp oracle respose file found in OPTACH directory

Thursday, March 17, 2016

Drop all tables in a schema

Wednesday, March 16, 2016

Restricting database access to IP addeess using sqlnet.ora