Mallik034: May 2020

Sunday, May 31, 2020

Create Physical Standby Database using RMAN Backup Restore

In this article, we will see Physical Standby database creation and configuration using RMAN backup and restore.

Step 1: Connect to the Primary database and check if recovery area

show parameter db_recovery

Step 2: Connect to RMAN and take backup

rman target /

backup database plus archivelog;

Step 3: Create standby control file from the primary database and create pfile from spfile.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/DEVDRDB.ctl';

CREATE PFILE FROM SPFILE;

Step 4: Change following parameter in pfile.

CHANGE FOLLOWING PARAMETER IN PFILE

*.db_unique_name='DEVDRDB'

*.fal_server='DEVDB'

*.log_archive_dest_2='SERVICE=DEVDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DEVDB'

Step 5: Connect to Standby database server and create necessary directories.

mkdir -p /u01/app/oracle/oradata/DEVDRDB/datafile

mkdir -p /u01/app/oracle/oradata/DEVDRDB/controlfile

mkdir -p /u01/app/oracle/fast_recovery_area/DEVDRDB/controlfile

mkdir -p /u01/app/oracle/oradata/DEVDRDB/onlinelog

mkdir -p /u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog

Step 6: Transfer standby control file to standby database and rename it as defined in control_files initialization parameter.

Step 7: Transfer backup to Standby database server

Step 8: Transfer pfile to standby database

Step 9: Transfer password file to standby database.

Step 10: Connect to Standby database and create spfile from pfile.

sqlplus / as sysdba

create spfile from pfile;

Step 11: In standby database connect to RMAN and start the database in mount stage.

rman target /

startup mount

Step 12: Restore database using restore database command.

restore database;

Step 13: Connect to SQL prompt of standby database and create redo log files.

alter system set standby_file_management=manual;

alter database add logfile ('/u01/app/oracle/oradata/DEVDRDB/onlinelog/redo01.log') size 512m;

alter database add logfile ('/u01/app/oracle/oradata/DEVDRDB/onlinelog/redo02.log') size 512m;

alter database add logfile ('/u01/app/oracle/oradata/DEVDRDB/onlinelog/redo03.log') size 512m;

alter database add logfile ('/u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog/redo01.log') size 512m;

alter database add logfile ('/u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog/redo02.log') size 512m;

alter database add logfile ('/u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog/redo03.log') size 512m;

alter system set standby_file_management=AUTO;

Check Standby database synchronization with the Primary database

Step 14: Connect to the Primary database and check the role of the primary database.

select name,open_mode,database_role from v$database;

Step 15: Connect to Standby database and check the role of the database.

select name,open_mode,database_role from v$database;

Step 16: Check maximum archive log sequence from the primary.

select max(sequence#) from v$thread;

Step 17: Check maximum archive log sequence from standby database.

select max(sequence#) from v$thread;

Step 18: Start the MRP process at standby side.

alter database recover managed standby database disconnect from session;

alter database recover managed standby database cancel;

Step 19: Switch logfile at primary database

alter system switch logfile;

Step 20: Check again max archive log sequence at the standby database.

select max(sequence#) from v$thread;

Regards,
Mallik

Saturday, May 30, 2020

Drop Database Delete Database manual method or GUI method

Drop Database:

1. GUI Method:

--- Launch dbca and select drop option to drop the database

--- OEM or Grid Control (Only Instance delete)

2. Manual Method:

--- connect to database in mount restrict mode and drop the database

--- shutdown the database and manually delete all the datafiles and configuration file (Not Recommended)

Drop Database possibilities:

open mode - drop database is not possible

mount mode - drop database is not possible

mount exclusive - drop database is not possible

mount restricted - drop database is possible

Please refer the more details in my YouTube channel https://youtu.be/3EWLBLie8-w

Regards,

Mallik

Friday, May 29, 2020

How to identify any parameter as static or dynamic?

Answer: Check for v$parameter we can find one column ie. ISSYS_MODIFIABLE

set pages 1000

set lines 200

col name for a35

col value for a25

select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter;

1. Parameters basics:

In general, parameter is a placeholder for a variable that contains some value of some type.

We can call it as key-value pair

db_block_siz(key)=8K(value)

db_name(key)=DEVDB(value)

2. How to Identify the parameter as stic or dynamic

SQL> select ISSYS_MODIFIABLE from v$parameter where name like '%utl_file_dir%';

ISSYS_MOD

---------

FALSE=======================================>static

Other examples are:

select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='processes';

select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sessions';

alter system set processes=200 scope=spfile;

alter system set sessions=500 scope=spfile;

3. Check the current value for a parameter processes & sessions

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='processes';

NAME VALUE ISSES ISSYS_MOD

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

processes 150 FALSE FALSE

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sessions';

NAME VALUE ISSES ISSYS_MOD

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

sessions 248 FALSE FALSE

4. Try to change staic parameter with scope=both it will fail

SQL> alter system set processes=200 scope=both;

alter system set processes=200 scope=both

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sessions=500 scope=both;

alter system set sessions=500 scope=both

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

5. Try to change static parameter with scope=memory it will fail

SQL> alter system set processes=200 scope=memory;

alter system set processes=200 scope=memory

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sessions=500 scope=memory;

alter system set sessions=500 scope=memory

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

6. Try to change static parameter with scope=spfile it will be successful

SQL> alter system set processes=200 scope=spfile;

System altered.

SQL> alter system set sessions=500 scope=spfile;

System altered.

7. After changing the parameter re verify the values still pointing to old values since these static parameter takes effect only after database bounce

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='processes';

NAME VALUE ISSES ISSYS_MOD

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

processes 150 FALSE FALSE

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sessions';

NAME VALUE ISSES ISSYS_MOD

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

sessions 248 FALSE FALSE

10. Shutdown the database

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

11. Start the database

SQL> startup;

ORACLE instance started.

Total System Global Area 1570009088 bytes

Fixed Size 2253584 bytes

Variable Size 1023413488 bytes

Database Buffers 536870912 bytes

Redo Buffers 7471104 bytes

Database mounted.

Database opened.

12. Verify the static parameter and now poiting to new values

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='processes';

NAME VALUE ISSES ISSYS_MOD

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

processes 200 FALSE FALSE

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sessions';

NAME VALUE ISSES ISSYS_MOD

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

sessions 500 FALSE FALSE

SQL>

13. How to Identify the parameter as static or dynamic

SQL> select ISSYS_MODIFIABLE from v$parameter where name like '%log_archive_dest_2';

ISSYS_MOD

---------

IMMEDIATE====================================>dynamic

Other examples are:

select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='log_archive_dest_2';

select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='open_cursors';

select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='awr_snapshot_time_offset';

alter system set open_cursors=500 scope=both;

alter system set awr_snapshot_time_offset=2 scope=both;

14. Check the current value for a parameter open_cursors & awr_snapshot_time_offset

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='open_cursors';

NAME VALUE ISSES ISSYS_MOD

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

open_cursors 300 FALSE IMMEDIATE

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='awr_snapshot_time_offset';

NAME VALUE ISSES ISSYS_MOD

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

awr_snapshot_time_offset 0 FALSE IMMEDIATE

SQL>

15. Try to change these dynamic parameter with scope=both it will be successful

SQL> alter system set open_cursors=500 scope=both;

System altered.

SQL> alter system set awr_snapshot_time_offset=2 scope=both;

System altered.

12. Verify the these dynamic parameter and now pointing to new values immediately without bouncing the database.

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='open_cursors';

NAME VALUE ISSES ISSYS_MOD

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

open_cursors 500 FALSE IMMEDIATE

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='awr_snapshot_time_offset';

NAME VALUE ISSES ISSYS_MOD

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

awr_snapshot_time_offset 2 FALSE IMMEDIATE

Please refer the YoutTube video for more information

https://youtu.be/hSntn8Gfedo

Regards,
Mallik

orapwd utility or Password File managmenet in Oracle

1. The default location for the password file is:

$ORACLE_HOME/dbs/orapw$ORACLE_SID ---------------- Unix

%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora ------- Windows

2. Database parameter which play major role in password file managmenet is

REMOTE_LOGIN_PASSWORDFILE.

REMOTE_LOGIN_PASSWORDFILE= NONE/EXCLUSIVE/SHARED

NONE - Oracle ignores the password file if it exists i.e. no privileged connections are allowed over non secure connections. If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

EXCLUSIVE - Password file is exclusively used by only one (instance of the) database. Any user can be added to the password file. Only an EXCLUSIVE file can be modified. EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

SHARED - The password file is shared among databases. A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. However, the only user that can be added/authenticated is SYS.

3. orapwd:

You can create a password file using orapwd utility.

Users are added to the password file when they are granted the SYSDBA, SYSOPER, SYSASM, SYSKM, SYSDG or SYSBACKUP privilege.

The Oracle orapwd utility assists the DBA while granting SYSDBA, SYSOPER, SYSASM, SYSKM, SYSDG or SYSBACKUP privileges to other users. By default, SYS is the only user that has SYSDBA and SYSOPER privileges.

Creating a password file, via orapwd, enables remote users to connect with administrative privileges.

Oracle 11g:

$ orapwd file=password_file_name [password=the_password] [entries=n] [force=Y|N] [ignorecase=Y|N] [nosysdba=Y|N]

Oracle 12c release 2:

orapwd FILE = filename

[FORCE = {y|n}]

[ASM = {y|n}]

[DBUNIQUENAME = dbname]

[FORMAT = {12.2|12}]

[SYS = {y|n|password|external('sys-external-name')|global('sys-directory-DN')}]

[SYSBACKUP = {y|n|password|external('sysbackup-external-name')|global('sysbackup-directory-DN')}]

[SYSDG = {y|n|password|external('sysdg-external-name')|global('sysdg-directory-DN')}]

[SYSKM = {y|n|password|external('syskm-external-name')|global('syskm-directory-DN')}]

[DELETE = {y|n}]

[INPUT_FILE = input-fname]

4. Examples

$ orapwd file=orapwSID password=sys_password force=y nosysdba=y

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret

5. login to database and verify the pwfile_users before creating password file:

pwfile_users is the dynamic view used at database to verify the admin previlaged users.

SQL> select * from v$pwfile_users;

no rows selected

SQL>

6. Create password file

$ orapwd file=orapwDEVDB entries=2 force=y

--- Maximum entries for the admin userd to login to database remotely.

[oracle@rac1 dbs]$ orapwd file=orapwDEVDB entries=2 force=y

Enter password for SYS:

[oracle@rac1 dbs]$ ll orapwDEVDB

-rw-r----- 1 oracle oinstall 1.5K May 29 17:33 orapwDEVDB

[oracle@rac1 dbs]$

7. Verify the pwfile_users file at database, dafult SYS user is added as soon as you creat a password file.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS

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

SYS TRUE TRUE FALSE

8. Grant sysdba to 3 or 4 users and verify pwfile_users file at database

SQL> grant sysdba to user1;

Grant succeeded.

SQL> grant sysdba to user2;

Grant succeeded.

SQL> grant sysdba to user3;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS

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

SYS TRUE TRUE FALSE

USER1 TRUE FALSE FALSE

USER2 TRUE FALSE FALSE

USER3 TRUE FALSE FALSE

SQL> exit

9. Connect to remote machine and login to database as sysdba with user1/user2/user3

[oracle@rac2 ~]$ sqlplus user1/user1@DEVDB as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 29 17:37:33 2020

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

[oracle@rac2 ~]$ sqlplus user2/user2@DEVDB as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 29 17:37:52 2020

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

10. Remove the password file at database server and try to login to database from remote maching as sysdba you will get error message.

[oracle@rac1 dbs]$ mv orapwDEVDB orapwDEVDB_backup

[oracle@rac2 ~]$ sqlplus user1/user1@DEVDB as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 29 17:38:28 2020

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name: ^C

[oracle@rac2 ~]$

11. Try to login to database without sysdba you will be able to login since you are login as normal user not sysdba administrator user

[oracle@rac2 ~]$ sqlplus user1/user1@DEVDB

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 29 17:38:57 2020

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL>

12. Verify the what all users are available in password file using OS strings command

Restore password file back.

[oracle@rac1 dbs]$ mv orapwDEVDB_backup orapwDEVDB

[oracle@rac1 dbs]$ strings orapwDEVDB

]\[Z

ORACLE Remote Password file

INTERNAL

71B9E71760673BBA

3EF87B7945B97475

USER1

BBE7786A584F9103

USER2

1718E5DBB8F89784

USER3

94152F9F5B35B103

[oracle@rac1 dbs]$

Conclusion:

1. Password file will be used for remote database login as admin user for administration activities

2. Entries what you mention while creating the password file is the maximum number of admin users are allowed to login remotely as the same time.

3. You can grant the sysdba privilege to N-number of users irrespective of maximum entries mentioned in password file.

Please refer the YoutTube video for more information

https://youtu.be/hdhoL2j2ePQ

Regards,

Mallik

Archivelog Mode and noarchivelog mode in Oracle database and How to convert from noarchivelog mode to archivelog mode

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time.

NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time.

Converting database from noarchivelog mode to archivelog mode:

---> Please note that converition of noarchivelog to archivelog mode is only posible in database mount state:

1. Login to database and verify the database state and log mode:

sqlplus / as sysdba

SQL> select name, open_mode, database_role, log_mode from v$database;

NAME OPEN_MODE DATABASE_ROLE LOG_MODE

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

DEVDB READ WRITE PRIMARY NOARCHIVELOG

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Current log sequence 5

SQL>

2. Shutdown the database

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

3. Start the database in mount mode.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1570009088 bytes

Fixed Size 2253584 bytes

Variable Size 973081840 bytes

Database Buffers 587202560 bytes

Redo Buffers 7471104 bytes

Database mounted.

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Current log sequence 5

SQL>

4. Convert the database from noarchivelog to archivelog mode

SQL> alter database archivelog;

Database altered.

5. Open the database

SQL> alter database open;

Database altered.

6. Verify the database state and log mode:

SQL> select name, open_mode, database_role, log_mode from v$database;

NAME OPEN_MODE DATABASE_ROLE LOG_MODE

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

DEVDB READ WRITE PRIMARY ARCHIVELOG

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Next log sequence to archive 5

Current log sequence 5

SQL>

Converting database from archivelog mode to noarchivelog mode:

---> Please note that converition of noarchivelog to archivelog mode is only posible in database mount state:

1. Login to database and verify the database state and log mode:

SQL> select name, open_mode, database_role, log_mode from v$database;

NAME OPEN_MODE DATABASE_ROLE LOG_MODE

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

DEVDB READ WRITE PRIMARY ARCHIVELOG

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Next log sequence to archive 5

Current log sequence 5

SQL>

2. Shutdown the database

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

3. Start the database in mount mode.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1570009088 bytes

Fixed Size 2253584 bytes

Variable Size 973081840 bytes

Database Buffers 587202560 bytes

Redo Buffers 7471104 bytes

Database mounted.

4. Convert the database from archivelog to noarchivelog mode

SQL> alter database noarchivelog;

Database altered.

5. Open the database

SQL> alter database open;

Database altered.

6. Verify the database state and log mode:

SQL> select name, open_mode, database_role, log_mode from v$database;

NAME OPEN_MODE DATABASE_ROLE LOG_MODE

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

DEVDB READ WRITE PRIMARY NOARCHIVELOG

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Current log sequence 5

SQL>

Please follow more on my YouTube

https://youtu.be/PUYuv0aU5S0

Regards,
Mallik

listener.ora and tnsnames.ora configuration using netca & netmgr

listener.ora and tnsnames.ora configuration using netca & netmgr

1. check listneter is running or not using lsnrctl utility

[oracle@rac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-MAY-2020 23:00:21

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

Linux Error: 111: Connection refused

[oracle@rac1 ~]$

2. Check listener os process is running or not

[oracle@rac1 ~]$ ps -ef|grep tns

root 21 2 0 22:45 ? 00:00:00 [netns]

oracle 5440 4838 0 23:00 pts/1 00:00:00 grep tns

[oracle@rac1 ~]$

3. Check listener.or and tnsnames.ora files are available on TNS_ADMIN locastion or not

[oracle@rac1 ~]$ cd $TNS_ADMIN

[oracle@rac1 admin]$ pwd

/u01/app/oracle/product/11204/dbhome_1/network/admin

[oracle@rac1 admin]$ ls -ltrh

total 16K

-rw-r--r-- 1 oracle oinstall 381 Dec 17 2012 shrept.lst

drwxr-xr-x 2 oracle oinstall 4.0K May 27 20:41 samples

[oracle@rac1 admin]$

[oracle@rac1 admin]$ In this location there should be tnsnames.ora, listenr.ora and sqlnet.ora file

4. Verify at the database level whether listner is registared or not (remote_listener & local_listener parameter)

[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 28 23:16:11 2020

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> show parameter remote

NAME TYPE VALUE

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

remote_dependencies_mode string TIMESTAMP

remote_listener string

remote_login_passwordfile string EXCLUSIVE

remote_os_authent boolean FALSE

remote_os_roles boolean FALSE

result_cache_remote_expiration integer 0

SQL> show parameter local

NAME TYPE VALUE

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

local_listener string

log_archive_local_first boolean TRUE

parallel_force_local boolean FALSE

SQL> clear

SQL> exit

[oracle@rac1 admin]$

5. run the netca GUI tool to configure the listner

[oracle@rac1 admin]$ cd $ORACLE_HOME/bin

[oracle@rac1 bin]$ pwd

/u01/app/oracle/product/11204/dbhome_1/bin

[oracle@rac1 bin]$ ll netca

-rwxr-xr-x 1 oracle oinstall 6685 May 27 20:45 netca

[oracle@rac1 bin]$

[oracle@rac1 bin]$ ./netca

Oracle Net Services Configuration:

Configuring Listener:LISTENER

Listener configuration complete.

Oracle Net Listener Startup:

Running Listener Control:

/u01/app/oracle/product/11204/dbhome_1/bin/lsnrctl start LISTENER

Listener Control complete.

Listener started successfully.

Oracle Net Services configuration successful. The exit code is 0

6. Verify the listener up and running after configuration

[oracle@rac1 bin]$ ps -ef|grep tns

root 21 2 0 22:45 ? 00:00:00 [netns]

oracle 5740 1 0 23:20 ? 00:00:00 /u01/app/oracle/product/11204/dbhome_1/bin/tnslsnr LISTENER -inherit

oracle 5790 5548 0 23:24 pts/1 00:00:00 grep tns

7. Verify the listner status using lsnrctl utility

[oracle@rac1 bin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-MAY-2020 23:24:50

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 28-MAY-2020 23:20:23

Uptime 0 days 0 hr. 4 min. 27 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11204/dbhome_1/network/admin/listener.ora

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

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.localdomain)(PORT=1521)))

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

Services Summary...

Service "DEVDB" has 1 instance(s).

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

Service "DEVDBXDB" has 1 instance(s).

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

The command completed successfully

8. Verify the listener.ora file create under the TNS_ADMIN location or not

[oracle@rac1 bin]$ cd $TNS_ADMIN

[oracle@rac1 admin]$ ls -ltrh

total 24K

-rw-r--r-- 1 oracle oinstall 381 Dec 17 2012 shrept.lst

drwxr-xr-x 2 oracle oinstall 4.0K May 27 20:41 samples

-rw-r--r-- 1 oracle oinstall 380 May 28 23:20 listener.ora

[oracle@rac1 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11204/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@rac1 admin]$

9. user netmgr tool to create tnsnames.ora file, You can use this netmger tool to create listner as well.

[oracle@rac1 admin]$ cd /u01/app/oracle/product/11204/dbhome_1/bin/

[oracle@rac1 bin]$ pwd

/u01/app/oracle/product/11204/dbhome_1/bin

[oracle@rac1 bin]$ ll netmgr

-rwxr-xr-x 1 oracle oinstall 6464 May 27 20:45 netmgr

[oracle@rac1 bin]$ ./netmgr

10. Verify the tnsnames.ora file create under the TNS_ADMIN location or not

[oracle@rac1 bin]$ cd $TNS_ADMIN

[oracle@rac1 admin]$ ls -ltrh

total 32K

-rw-r--r-- 1 oracle oinstall 381 Dec 17 2012 shrept.lst

drwxr-xr-x 2 oracle oinstall 4.0K May 27 20:41 samples

-rw-r--r-- 1 oracle oinstall 380 May 28 23:20 listener.ora

-rw-r--r-- 1 oracle oinstall 337 May 28 23:28 tnsnames.ora

[oracle@rac1 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11204/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DEVDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = DEVDB)

)

11. Verify the listner and registred status at the database;

[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 28 23:29:00 2020

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> show parameter local

NAME TYPE VALUE

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

local_listener string

log_archive_local_first boolean TRUE

parallel_force_local boolean FALSE

SQL> alter system register;

System altered.

SQL> show parameter local

NAME TYPE VALUE

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

local_listener string

log_archive_local_first boolean TRUE

parallel_force_local boolean FALSE

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

[oracle@rac1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-MAY-2020 23:30:56

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 28-MAY-2020 23:20:23

Uptime 0 days 0 hr. 10 min. 32 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11204/dbhome_1/network/admin/listener.ora

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

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.localdomain)(PORT=1521)))

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

Services Summary...

Service "DEVDB" has 1 instance(s).

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

Service "DEVDBXDB" has 1 instance(s).

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

The command completed successfully

[oracle@rac1 admin]$

Regards,
Mallik

Mallik034

Sunday, May 31, 2020

Create Physical Standby Database using RMAN Backup Restore

Regards,
Mallik

Saturday, May 30, 2020

Drop Database Delete Database manual method or GUI method

Friday, May 29, 2020

How to identify any parameter as static or dynamic?

orapwd utility or Password File managmenet in Oracle

Archivelog Mode and noarchivelog mode in Oracle database and How to convert from noarchivelog mode to archivelog mode

listener.ora and tnsnames.ora configuration using netca & netmgr

Automation Script | Archivelog Generation Hourly Monitoring

Social Plugin

Blog Archive

Sunday, May 31, 2020

Regards,Mallik

Saturday, May 30, 2020

Friday, May 29, 2020

Regards,
Mallik