SSL Configuration On WebLogic Adminserver & BI Managed Server

Command 1 :
To create 1024 bit certificate use this:
keytool -genkey -alias certkey -keyalg RSA -keysize 1024 -validity 365 -keypass Welcome1 -keystore testcert.jks -storepass Welcome1

OR:

To create a 2048 bit SHA2/SHA256 certificate use the following command :

>> keytool -genkey -alias mykey -keyalg RSA -keysize 2048 -sigalg SHA256withRSA -validity 1095 -keypass Welcome1 -keystore identity.jks -storepass Welcome1

Command 2 :

>> keytool  -export -alias mykey -file root.cer -keystore identity.jks -storepass Welcome1

Command 3 :

>> keytool -import -alias mykey -file root.cer -keystore trust.jks -storepass Welcome1

------------------------------------------------------------------
To see the contents of the keystore use the following command :

Command :
>> keytool -list -v -keystore identity.jks -storepass Welcome1

To see the contents of an individual certificate ( like root.cer in our case ).

Command :
>> keytool -printcert -file root.cer

--------------------------------------------------------------------
Changes Required From The Console:
--------------------------------------------------------------------
Step 1 :

>> Login to Weblogic Admin console --> Environment --> Servers -->
                                                   ---> AdminServer/bi_server1
                                                     --> Configuration ->
                                                       --> General
                                                         --> SSL Listen Port Enabled ( Check )

Note : The default SSL Listen Port would be 7002, change it if required.


Step 2 :

>> Click on Keystores tab under " Configuration " tab :


Step 2a :

>> Click on the drop down menu next to Keystores and select " Custom Identity and Custom Trust "


Step 2b :

>> Now fill in the following information :

---Identity---

Custom Identity Keystore: <location_with_file_name_of_identity_keystore_that_you_have_created

NOTE : By default WLS will look for this keystore file in domain_home location.

 Custom Identity Keystore Type : jks

 Custom Identity Keystore Passphrase: < This_would_be_your_storepass >

 ---Trust---

 Custom Trust Keystore : < location_of_trust_keystore_that_you_have_created>

NOTE : By default WLS will look for this keystore file in domain_home location.

 Custom Trust Keystore Type : jks

 Custom Trust Keystore Passphrase: < This_would_be_your_storepass >



Step 2c :

Now save the changes and click on " SSL " tab :

Private Key Alias: < This_would_be_your_certificate_alias >

Private Key Passphrase: < This_would_be_your_keypass >


Step 3 :

Save the changes and click on the " >Advanced " field under the " SSL " tab :

Set the " Hostname Verification: " to None ( from the drop down menu ).

Note : We need to select the hostname verification as none if the CN of the certificate is not the same as the hostname of the machine where WLS is installed.


Step 4: 

Now access your Weblogic Admin console over https URL :

https://Adminserver/bi_server_ip:7002/console

Mamun's DBA Blog: Why and How to Open Database in Restrict Mode?

As a DBA sometime you have to take database in restrict mode. Today's article will discuss the details about Database Restrict Mode. Stay Tune!!!

1. What Is Restricted Mode

When a database is placed in restricted mode, only users that have RESTRICTED SESSION privilege granted to them can connect to the database. Users that lack this privilege will get an error.
Users that are already connected to the database, prior to placing the database in restricted session, will not get disconnected. These sessions need to be manually killed after.

Why would you put a database in restricted session?
During certain maintenance task, you might want to restrict access to the database.
You want to be able to connect to the database to perform your tasks, but you do not want any other users mocking around, changing the data. Such maintenance tasks could be upgrading a database with an export/import process, fixing replicated data, deleting specific data, reorganizing tables, taking a consistent export. In these cases the restricted session is not a must, you could still proceed with the database open read write, but you might encounter some problems down the road.

2. Starting Up Database In Restricted Mode

You can start up a database in restricted mode.
Prior to starting up the database in restricted mode, you need to shutdown the running database first.

SQL> shutdown immediate;
SQL> startup restrict;
SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

The good thing about this method is, that all users connected to the database will get disconnected when you shutdown the database. These users will not be able to connect unless they have the RESTRICTED SESSION privilege.

3. Placing Open Database In Restricted Mode

If you do not want to shutdown the database, there is an ALTER SYSTEM command you can use to place the database in restricted mode.

SQL> alter system enable restricted session;

The downside to this method is that users already connected to the database will not get disconnected. You need to manually kill these sessions.

4. Script To Kill Existing Sessions

In order to help you kill the sessions that are sticking around after the database has been placed in restricted mode you can use the following script.
This script can be used any other time to generate the kill commands.

--check what sessions there are in the database

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.sql_id
FROM   gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
AND    s.username not in ('SYS','SYSTEM')
ORDER BY s.inst_id,s.sid,s.serial#;


INST_ID   SID  SERIAL# SPID    USERNAME  PROGRAM            SQL_ID
------- ----- -------- ------- --------- ------------------ -------------
      1     4    33271 31370   HRAPP     JDBC Thin Client 
      1     5      369 25228   HRAPP     JDBC Thin Client   318fujwu02mz1
      1    43       59 25232   SYSADM    JDBC Thin Client   ar3mfzt410h9y
      1    46      415 25513   HRQUERY   JDBC Thin Client 

-- generate the kill command.
-- if you decide to kill the sessions, then run the generated commands.

select 'alter system kill session ' || '''' || s.sid|| ',' || s.serial# || ',@' || s.inst_id || ''';' as "Kill_Session_Command"
FROM   gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
AND    s.username not in ('SYS','SYSTEM')
ORDER BY s.inst_id,s.sid,s.serial#;

Kill_Session_Command
----------------------------------------
alter system kill session '4,33271,@1';
alter system kill session '5,369,@1';
alter system kill session '43,59,@1';
alter system kill session '46,415,@1';

Mamun's DBA Blog: Reset weblogic server admin user password

Reset WebLogic User Password: Action Plan-1
-------------------------------------------------------
1. Make sure all the services are down

2. change directory to /u01/app/middleware/user_projects/domains/bifoundation_domain/bin

cd /u01/app/middleware/user_projects/domains/bifoundation_domain/bin

3. Source the setDomainEnv.sh (Linux)/setDomainEnv.cmd(Windows)  script so we can get all the WebLogic variables set correctly

In Linux:  . /setDomainEnv.sh

In windows: setDomainEnv.cmd

4. Now change the directory to security directory in your instance.

cd /u01/app/middleware/user_projects/domains/bifoundation_domain/security

5. Move the DefaultAuthenticatorInit.ldift to a backup file

In Linux:

mv DefaultAuthenticatorInit.ldift DefaultAuthenticatorInit_old.ldift

In Windows:

mv DefaultAuthenticatorInit.ldift DefaultAuthenticatorInit_old.ldift

6. Run the “java weblogic.security.utils.AdminAccount  newuser  newpassword .” to generate a new DefaultAuthenticatorInit.ldift with a new user with the assigned password within the security directory

eg:
java weblogic.security.utils.AdminAccount weblogic weblogic123 .

Note: Replace newuser and newpassword with a user and password of your choosing. Ensure the password is a minimum of 8 characters and contains at least one numeric character.
Also, don’t forget the period “.” at the end of the above command, it is required.

7. Change directory to /u01/app/middleware/user_projects/domains/bifoundation_domain/servers/AdminServer/security

cd /u01/app/middleware/user_projects/domains/bifoundation_domain/servers/AdminServer/security/

8. Edit the boot.properties file and replace newpassword and newusername with the user and password you defined in step 6.
Do this for all the servers in the domain.

vi boot.properties

username=weblogic
password=newpassword

The boot.properties will get encrypted once the admin server starts up

9. Rename or remove directory /u01/app/middleware/user_projects/domains/bifoundation_domain/servers/AdminServer/data/ldap
as it will get recreated once the WebLogic Admin server starts.

e.g:
cd /u01/app/middleware/user_projects/domains/bifoundation_domain/servers/AdminServer/data/

mv ldap ldap_date

10. Change directory to /u01/app/middleware/user_projects/domains/bifoundation_domain/bin and start the Weblogic Admin Server

cd  /u01/app/middleware/user_projects/domains/bifoundation_domain/bin

In Linux:  ./startWebLogic.sh

In Windows: startWebLogic.cmd

11. Use new credentials to startup in startup command.Once the weblogic server is up, you’ll be able to login to Console with the new weblogic Admin user and password.

===================================================================
Reset WebLogic User Password: Action Plan-2
-------------------------------------------------------
Please try the below actionplan to change the weblogic admin password:

--> Firstly Shut down the WebLogic domain--> the managed servers, Admin Server and Node Manager.

--> Rename the data folder under the AdminServer of the domain to old_data, this is the path: %DOMAIN_HOME%/servers/AdminServer/data

--> In the same command prompt that you opened earlier, set the environment variable of the domain by calling: %DOMAIN_HOME%/bin/ --> ./setDomainEnv.sh

--> In the same command prompt, change directory to security folder: cd %DOMAIN_HOME%/security

-->Inside the %DOMAIN_HOME%/security directory, move the file "DefaultAuthenticatorInit.ldift" to "%DOMAIN_HOME%/security/old/"

--> In the next step, the command that we are going to run will create a new file “DefaultAuthenticatorInit.ldift” in the security folder

-->Run the following command to reset or change admin password:
java weblogic.security.utils.AdminAccount weblogic password .

NOTE: The DOT at the end of the above command represents the current directory. A new "DefaultAuthenticatorInit.ldift" file will be created in the current directory
when you execute the command.

--> Update the "boot.properties" file under the directory %DOMAIN_HOME%/servers/AdminServer/security/ with your username and the new password.

--> Now start the admin server using the new credentials from %DOMAIN_HOME%/bin/ --> ./startWeblogic.sh

Once the AdminServer started, login into the Admin console using the username and the new password.

Mamun's DBA Blog: How to Install & Run ORAchk

To run orachk follow the steps below...

Step 1. Download the ORAchk zip from MOS DOC ID 1268927.2 if not have downloaded.

Note: Download the 2^nd one.

Step 2. Check if the directory “/opt/oracle.SupportTools/orachk” exists

     a. If the directory does not exist, create it and unzip the zip file to it.

     b. If the directory exists but ORAchk isn't installed, unzip the zip file.

Step 3. After you’ve completed the installation, you’re ready to run ORAchk from the OS command line as root

    ./orachk –a –o verbose

Step 4. Then get the report as zip by following the directed location at the end.

Mamun's DBA Blog: How To Check Undo Tablespace Size and Free Size in Oracle

First Connect through SQLPlus then,

SQL> select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
     from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name from dba_data_files a,
     dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.contents = 'UNDO'
     group by b.tablespace_name) a, (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
     from DBA_UNDO_EXTENTS c where status <> 'EXPIRED'group by c.tablespace_name) b
     where a.tablespace_name = b.tablespace_name;

TABLESPACE_NAME     SIZEMB    USAGEMB        FREEMB
--------------- ---------- ---------- -------------------------------------------------
UNDOTBS1                        15360         50.6875           15309.3125
UNDOTBS2                        15360         40.5625           15319.4375

Mamun's DBA Blog: How To Check Redolog File Size in Oracle

Here we go for fetch the size of all redo logs...
SQL> set linesize 300
SQL> column REDOLOG_FILE_NAME format a45
SQL> SELECT
        a.GROUP#,
        a.THREAD#,
        a.SEQUENCE#,
        a.ARCHIVED,
        a.STATUS,
        b.MEMBER    AS REDOLOG_FILE_NAME,
        (a.BYTES/1024/1024) AS SIZE_MB
    FROM v$log a
    JOIN v$logfile b ON a.Group#=b.Group#
    ORDER BY a.GROUP# ASC;

    GROUP#    THREAD#  SEQUENCE#  ARC           STATUS                    REDOLOG_FILE_NAME                    SIZE_MB
---------- ---------- ---------- --- --------- --------------------------------------------- ---------------------------------------------------------------------
         1               1               17309           NO             CURRENT   +RECO/ieclive/onlinelog/group_1.257.860629021        200
         1               1               17309           NO              CURRENT   +DATA/ieclive/onlinelog/group_1.266.860629021       200
         2               1               17306          YES             INACTIVE  +RECO/ieclive/onlinelog/group_2.259.860629023        200
         2               1               17306          YES             INACTIVE  +DATA/ieclive/onlinelog/group_2.268.860629023        200
         3               2               30917          NO              CURRENT   +DATA/ieclive/onlinelog/group_3.261.860629087        200
         3               2               30917          NO              CURRENT   +RECO/ieclive/onlinelog/group_3.261.860629087        200
         4               2               30914         YES              INACTIVE  +DATA/ieclive/onlinelog/group_4.270.860629089         200
         4               2               30914         YES              INACTIVE  +RECO/ieclive/onlinelog/group_4.262.860629089         200
         5               1               17307         YES              INACTIVE  +RECO/ieclive/onlinelog/group_5.258.860629023         200
         5               1               17307         YES              INACTIVE  +DATA/ieclive/onlinelog/group_5.272.860629023         200
  

Mamun's DBA Blog: Gathering Table Stats in Oracle11g

Hello Visitors!!!

• When we gather stats we are updating Optimizer to choose optimal execution plan which enhance the performance of the database.
• We can gather stats at database level,schema level, Table level,Index level.

Table Level Stats :

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
         ownname =>'SCOTT',
         tabname =>'EMPLOYEE',
         degree => 2,
         cascade => TRUE,
         METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
         estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
         END;
         /


Parameters Defination:-

• Owner-name = Schema Name which holds the table.
• Tabname = Table Name
• Degree  = When you set degree to some value oracle will invoke slave process which speedup the performance.
• cascade = The cascade option can be used to analyze al the indexes.
• METHOD_OPT (FOR COLUMNS SIZE AUTO) = Auto is the Oracle default and this option will analyze histograms  on what the optimizer considers to be "important columns". The optimizer does this by examining your current workload when you run the dbms_stats command,interrogating all current SQL in the library cache to see which columns might benefit from a histogram.
•  estimate_percent = This argument is a new way to allow Oracle's dbms_stats to automatically   estimate  the "best" percentage of a segment to sample when gathering statistics.

Mamun's DBA Blog: How to Check Oracle Database Listener Port Status from Sql & OS Level

Hello Everyone!

You can check listener status in windows environment by using Sql & Os using Netstat.


SQL> select * from gv_$listener_network;

  

C:\Users\oracle> netstat -na |find "1521"

  TCP    0.0.0.0:1521                0.0.0.0:0                       LISTENING
  TCP    10.10.20.244:1521      10.10.20.244:49928     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49929     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49933     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49934     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49939     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49947     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:50037     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.246:50605     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:50613     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:55062     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57671     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57675     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57680     ESTABLISHED



C:\Users\oracle> netstat -a -o -n

Active Connections

  Proto  Local Address    Foreign Address       State           PID
  TCP    0.0.0.0:135            0.0.0.0:0              LISTENING       752
  TCP    0.0.0.0:445            0.0.0.0:0              LISTENING       4
  TCP    0.0.0.0:1158           0.0.0.0:0              LISTENING       2828
  TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       1984
  TCP    0.0.0.0:1831           0.0.0.0:0              LISTENING       14504
  TCP    0.0.0.0:3389           0.0.0.0:0              LISTENING       5316

Mamun's DBA Blog: Manage Tablespace in oracle

What is a tablespace and datafile:

Tablespace is the primary logic structure of the oracle database. It consists of one or more physical datafiles. Datafiles physical stores database data in storage.(DISKS) . So when we create a datafile of 30G in database, it will eat 30G of space from defined storage. All the table or index segment is created in tablespace only.

Types of Tablespace

SYSTEM TABLESPACE:

    Each database contains a SYSTEM tablespace.( It is created by default while creating the database itself) .
    It contains the data dictionary tables of the database.
    All the view,procedures, functions, packages and triggers are stored in SYSTEM tablespace
    It is not recommended to user SYSTEM tablespace for creating any other table or index.

SYSAUX TABLESPACE:    

All the database metadata, that doesnt store in SYSTEM tablespace, will be stored here.

    Many database components datas are stored in this tablespace

PERMANENT TABLESPACE:

    When someone says tablespace, Means they are mostly refering to PERMANENT TABLESPACE
    This tablespace are used for storing the actual schema tables or indexes . i.e it Stores user data.
    It doesnt store any of the data dictionary tables.

    There are two types of PERMANENT TABLESPACE.

    SMALL FILE – (DEFAULT) – This is the common size tablespace, It can contain multiple datafiles with each datafile of size 31G;

    BIG FILE – It can contain only one datafile which can grow upto 128 TB.

    UNDO TABLESPACE:

    This tablespace stores Undo data:
    We can’t create any table or index inside undo tablespace.
    Each instance in the database have one defined UNDO tablespace. i.e for standalone database one assigned undo tablespace, and for multi node rac system, each instance on the node will have one assigned undo tablespace.

    TEMPORARY TABLESPACE:

    This tablespace stores the data temporarily for sessions doing sorting and join operations.
    Sort operations are also generated by SELECT * FROM WHERE CLAUSE that join rows from within tables and between tables. The segments generated during this process will be stored in temp tablespace.
    Each database will have one default temporary tablespace

    Create a NEW tablespace:

   Create tablespace DATA datafile '/u01/app/oracle/oradata/data01.dbf' size 5G autoextend on next 500M;
       
Here, 500M of extent will be added to the datafile automatically, when the space is required.

For creating a tablespace on ASM disk group:

– With OMF file system:

Create tablespace DATA datafile '+DATAG' size 5G autoextend on next 500M;

--- Else we can give the complete path also:

Create tablespace DATA datafile '+DATAG/oradata/datafile/data01.dbf' size 5G autoextend on next 500M;

Adding a datafile:

alter tablespace DATA add datafile '/u01/app/oracle/oradata/data02.dbf' size 2G;

Resize a datafile: ( To increase or decrease the datafile)

alter database datafile '/u01/app/oracle/oradata/data02.dbf' resize 3G;

Change default tablespace:

Every database will have one default tablespace. If any user is created without mentioning any tablespace_name, then that schema objects will be created under default tablespace. – Get the current default tablespace:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

ALTER DATABASE DEFAULT TABLESPACE DATATBS;

Rename a tablespace:

set pagesize 200
set lines 200
col file_name for a57

SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;

   FILE_ID FILE_NAME                            TABLESPACE_NAME
---------- -------------------------------------------------------- ------------------------------
 37 /u01/app/oracle/oradata/cdb1/test1.dbf        TESTING


--- Rename the tablespace_name from TESTING to PRODUCING;

SQL❯ alter tablespace TESTING rename to PRODUCING;

Tablespace altered.

SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;

   FILE_ID FILE_NAME                            TABLESPACE_NAME
---------- -------------------------------------------------------- ------------------------------
 37 /u01/app/oracle/oradata/cdb1/test1.dbf        PRODUCING

 NOTE: SYSTEM and SYSAUX tablespace cannot be renamed.

Drop a tablespace:

-- Drop a tablespace without removing the physical database files.

SQL❯ drop tablespace TESTING;

Tablespace dropped.

SQL❯ select file_name from dba_data_files where tablespace_name='TESTING';

no rows selected

 -- Drop tablespace including the physical datafiles.

SQL❯ drop tablespace TESTING including contents and datafiles;

Tablespace dropped.

Make tablespace Read only:

alter tablespace DATA2 READ ONLY;

alter tablespace DATA2 read write;

Take tablespace offline:

DBAs usually make the tablespace offline, while restoring or recovering any underlying corrupt datafile.
-- To make a tablespace offline

ALTER TABLESPACE DATATS OFFLINE;

-- To make it online

ALTER TABLESPACE DATATS ONLINE;

Create a temporary tablespace:

CREATE TEMPORARY TABLESPACE TEMP1 '/u01/app/oracle/tempfile/temp01.dbf' size 1G autoextend on next 200M;

Adding a tempfile:

alter tablespace TEMP1 add tempfile '/u01/app/oracle/tempfile/temp02.dbf' size 1G autoextend on next 200M;

Resize tempfile:

alter database tempfile '/u01/app/oracle/tempfile/temp02.dbf' resize 2G;

Drop tempfile:

It will drop and remove the tempfile from physical location.

ALTER DATABASE TEMPFILE '/u01/app/oracle/tempfile/temp02.dbf' DROP INCLUDING DATAFILES;

 Change default temporary tablespace:

When we create a new user, we assign a default temporary tablespace. If nothing is assigned, then it will use the default temporary tablespace set a database level.
Get the current default temporary tablespace for the database


SQL❯ SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP
 
-- Set New default temp tablespace


ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
 
-- Check the new default temp tablespace
SQL❯ SELECT PROPERTY_VALUE FROM  DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP2

Mamun's DBA Blog: DB Link and GLOBAL_NAMES parameter in Oracle

GLOBAL_NAMES is either set to FALSE OR TRUE.

GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects.

If the value of GLOBAL_NAMES is FALSE, then any name can be given to DB_LINK. If value is TRUE, then database link name should be same as that of the database it is pointing.

Default value is FALSE:

SQL> show parameter global_name

NAME                                 TYPE        VALUE

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

global_names                         boolean     FALSE

1. Lets create a database link with any name:

SQL> create public database link TESTDB  connect to SYSTEM IDENTIFIED BY welcome1 USING 'ORCL';

Database link created.

SQL>  select sysdate from dual@TESTDB;

SYSDATE

---------

01-MAR-19

SQL>drop public database link TESTDB ;

So with GLOBAL_NAME set to FALSE, db_link name is independent of target database name.

2. Update the GLOBAL_NAMES value to TRUE:

SQL> alter system set global_names=TRUE scope=both;

System altered.

SQL> show parameter global_names

NAME                                 TYPE        VALUE

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

global_names                         boolean     TRUE

3. Create database link as before(with different name)

SQL> create public database link TESTDB  connect to SYSTEM IDENTIFIED BY welcome1 USING 'ORCL';

Database link created

SQL> select sysdate from dual@TESTDB;

select sysdate from dual@TESTDB

                         *

ERROR at line 1:

ORA-02085: database link TESTDB connects to ORCL

We can see, when global_name is TRUE, DB_LINKs with different name as that of database name is not working.

4. Lets create a db_link having same name as target db.

SQL> create public database link ORCLconnect to SYSTEM IDENTIFIED BY welcome1 USING 'ORCL';

Database link created.

SQL>  select sysdate from dual@ORCL;

SYSDATE

---------

01-MAR-19

Hope it Works!!!

Mamun's DBA Blog: How to create listener using NETCA utility

This article explains the steps for creating a LISTENER using NETCA GUI utility.

NOTE – > utility need to execute from XMANAGER/XSHELL(From where we can call GUI),Just like DBCA or DBUA utility.

1. Set appropriate ORACLE_HOME as per requirement( EITHER GRID HOME OR ORACLE DB BINARY HOME)
export ORACLE_HOME=/oracle/app/oracle/product/12.2.0/dbhome_3
export PATH=$ORACLE_HOME/bin:$PATH
2. execute NETCA
 


 

 

 

Make sure the port number not used on that server.




The listener got created.

Check the status:

lsnrctl status LISTENER_TEST

LSNRCTL for Solaris: Version 12.2.0.1.0 - Production on 04-MAR-2018 11:03:31

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1)(PORT=1587)))

STATUS of the LISTENER

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

Alias                        LISTENER_TEST

Version                    TNSLSNR for Linux: Version 12.2.0.1.0 - Production

Start Date                04-MAR-2019 11:01:35

Uptime                    0 days 0 hr. 1 min. 56 sec

Trace Level             off

Security                  ON: Local OS Authentication

SNMP                     OFF

Listener Parameter File   /oracle/app/oracle/product/12.2.0/dbhome_3/network/admin/listener.ora

Listener Log File         /oracle/app/oracle/diag/tnslsnr/srv1/listener_test/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv1)(PORT=1587)))

The listener supports no services -- >>>>>>      NO SERVICES AVAILABLE.

It shows listener supports no services, because no database has registered with this listener.

Follow below steps for registering the listener port with the database .

1. Add the entry in tnsnames.ora file of the binary.

LISTENER_TEST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = srv1)(PORT = 1587))

2. Set local_listener parameter and register.

SQL> alter system set local_listener='LISTENER_TEST' scope=both;

System altered.

SQL> alter system register;

System altered.

3. Check the listener status again:

# lsnrctl status LISTENER_TEST

LSNRCTL for Solaris: Version 12.2.0.1.0 - Production on 04-MAR-2018 11:05:15

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1)(PORT=1587)))

STATUS of the LISTENER

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

Alias                        LISTENER_TEST

Version                    TNSLSNR for Linux: Version 12.2.0.1.0 - Production

Start Date                04-MAR-2018 11:01:35

Uptime                    0 days 0 hr. 3 min. 39 sec

Trace Level             off

Security                   ON: Local OS Authentication

SNMP                     OFF

Listener Parameter File   /oracle/app/oracle/product/12.2.0/dbhome_3/network/admin/listener.ora

Listener Log File         /oracle/app/oracle/diag/tnslsnr/srv1/listener_test/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv1)(PORT=1587)))

Services Summary...

Service "TEST" has 1 instance(s).

  Instance "TEST", status READY, has 1 handler(s) for this service...

Service "TESTXDB" has 1 instance(s).

  Instance "TEST", status READY, has 1 handler(s) for this service...

The command completed successfully

We can see that the service is available now.:)