Karan Rajpoot Oracle DBA Blog's

How to resolve "TNS-12547: TNS:lost contact TNS-12560: TNS:protocol adapter error TNS-00517: Lost contact" | Karan Rajpoot

2019-04-14T22:46:00.000+05:30

TNS-12547: TNS:lost contact TNS-12560: TNS:protocol adapter error TNS-00517: Lost contact

Error: - "TNS-12547: TNS: lost contact TNS-12560: TNS: protocol adapter error TNS-00517: Lost contact"

Cause: - This error occurs when loopback entries have been missed in the /etc/hosts file.

Solution: - To resolve this error you need to put the loopback entries in the /etc/hosts file.

Example: -

[oracle@prod bin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-APR-2019 22:08:16

Starting /u02/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u02/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u02/app/oracle/diag/tnslsnr/prod/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.radical.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod.radical.com)(PORT=1521)))

TNS-12547: TNS: lost contact

TNS-12560: TNS: protocol adapter error

TNS-00517: Lost contact

Linux Error: 104: Connection reset by peer

Note: - To resolve the issue need to put the below loopback entries in the /etc/hosts file.

127.0.0.1 prod.localdomain prod localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

After making the changes, Now you can restart your listener.

[oracle@prod ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-APR-2019 22:11:05

Starting /u02/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u02/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u02/app/oracle/diag/tnslsnr/prod/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.radical.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod.radical.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 14-APR-2019 22:11:05

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u02/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /u02/app/oracle/diag/tnslsnr/prod/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.radical.com)(PORT=1521)))

Services Summary...

Service "dr" has 1 instance(s).

Instance "dr", status UNKNOWN, has 1 handler(s) for this service...

Service "prod" has 1 instance(s).

Instance "prod", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Cheers!!

How to resolve "Destination Host Unreachable" Issue in Linux | Karan Rajpoot

2019-04-14T19:35:00.000+05:30

Destination Host Unreachable in Linux

Error: - icmp_seq=2 Destination Host Unreachable

Cause: - When you are trying to ping Linux machine from one node to another node then you may face issue "destination host unreachable", the reason behind this issue is the selection of network while creation of Linux machine.

Solution: - To resolve this issue you need to select the proper network selection.

Example: - In this case, there are two machine prod and dr, When I was tried to ping dr machine from prod then I faced below issue. I checked so many things like firewall, etc/hosts file entries and so on, but didn't get resolution then, at last, I checked my network configuration and found it was set as NAT. I have changed the Network from NAT to the host-only adapter and then tried again finally, that issue has been resolved.

Before changing the network from NAT to Host-only adapter.

[root@prod Desktop]# ping dr

PING dr.radical.com (192.168.2.21) 56(84) bytes of data.

From prod.radical.com (192.168.2.20) icmp_seq=2 Destination Host Unreachable

From prod.radical.com (192.168.2.20) icmp_seq=3 Destination Host Unreachable

From prod.radical.com (192.168.2.20) icmp_seq=4 Destination Host Unreachable

--- dr.radical.com ping statistics ---

5 packets transmitted, 0 received, +3 errors, 100% packet loss, time 4307ms

pipe 3

Network changes from NAT to Host-only adapter.

After changing the network from NAT to Host-only adapter.

[root@prod Desktop]# ping dr
PING dr.radical.com (192.168.2.21) 56(84) bytes of data.
64 bytes from dr.radical.com (192.168.2.21): icmp_seq=1 ttl=64 time=1004 ms
64 bytes from dr.radical.com (192.168.2.21): icmp_seq=2 ttl=64 time=4.63 ms
64 bytes from dr.radical.com (192.168.2.21): icmp_seq=3 ttl=64 time=0.865 ms
^C
--- dr.radical.com ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2389ms

rtt min/avg/max/mdev = 0.865/336.723/1004.676/472.316 ms, pipe 2

Hope this blog will help you!!

How to Install Linux on Oracle Virtual Box

2019-04-06T22:24:00.000+05:30

Below are the steps to Install Linux on Oracle Virtual Box

Step 1) First you need to download the Oracle Virtual Box from the link.

Step 2) Select New Option to choose the name, type, and version of the Operating system.

Step 3) Select the Memory as per your requirement.

Step 4) Choose the Hard disk options as Create a Virtual Hard disk Now.

Step 5) Choose the Hard disk file type as VDI (Virtual Disk Image).

Step 6) Select Storage on a physical hard disk as Dynamically allocated or Fixed size as per your requirement.

Step 7) Select the File Location and Size as per your requirement.

Step 8) Now go to the Storage tab and select the ISO image file which needs to be installed.

Step 9) Now Click on the Start tab to initialize the installation.

Step 10) Now click on the Install or Upgrade an existing system to proceed further in the installation.

Step 11) Click on the Skip tab to skip the media test and start the installation.

Step 12) Click on the Next tab to proceed further.

Step 13) Choose language as English.

Step 14) Select Basic Storage Devices in Installation.

Step 15) Now need to choose the HostName as well as Configure the Network as well.

Host Name

Network Configuration

Step 16) Now select the Time Zone as per your requirement in my case I am selecting Asia/Kolkata.

Step 17) Now Choose the Root Password as per your requirement.

Step 18) Now Choose the Create Custom Layout and click on next.

Step 19) Now you need to create the mount points, as per your requirement, In my case, I am using below mount points. After creation format all the mount points.

Step 20) After creations of all the mount points you need to choose to Customize Now options for the selections of the packages.

Step 21) Now you need to select all the packages including optional one's also which are required for the installation.

Step 22) After the selection of packages, now just start the installation.

Thank You!!

How To Configure Step By Step Oracle DGMGRL Utility | Karan Rajpoot

2019-03-03T20:15:00.000+05:30

In my previous blog, We saw the configuration steps of the data guard setup. In this blog, we will see the configuration of DGMGRL Oracle utility.

let's start the configuration.

Step 1) First we should check the DB name, DB unique name, open_mode, database_role of both the servers.

Primary Server: -

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

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- ------------------
PROD prod READ WRITE PRIMARY YES

Standby Server: -

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

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- ------------------
PROD dr MOUNTED PHYSICAL STANDBY YES

Step 2) Now test the connectivity of both the servers. Both should be ping to each other.

Primary to Standby: -

[oracle@prod admin]$ ping 192.168.2.21
PING 192.168.2.21 (192.168.2.21) 56(84) bytes of data.
64 bytes from 192.168.2.21: icmp_seq=1 ttl=64 time=1.26 ms
64 bytes from 192.168.2.21: icmp_seq=2 ttl=64 time=2.62 ms
64 bytes from 192.168.2.21: icmp_seq=3 ttl=64 time=0.874 ms
^C
--- 192.168.2.21 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2734ms

rtt min/avg/max/mdev = 0.874/1.588/2.626/0.752 ms

Standby to Primary: -

[oracle@dr admin]$ ping 192.168.2.20
PING 192.168.2.20 (192.168.2.20) 56(84) bytes of data.
64 bytes from 192.168.2.20: icmp_seq=1 ttl=64 time=0.953 ms
64 bytes from 192.168.2.20: icmp_seq=2 ttl=64 time=0.974 ms
64 bytes from 192.168.2.20: icmp_seq=3 ttl=64 time=1.19 ms
^C
--- 192.168.2.20 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2616ms

rtt min/avg/max/mdev = 0.953/1.039/1.191/0.110 ms

Step 3) Now set the dg_broker_start parameter as enable on both the servers.

Primary Server: -

SQL> alter system set dg_broker_start=true scope=both;

System altered.

Standby Server: -

SQL> alter system set dg_broker_start=true scope=both;

System altered.

Step 4) Now configure the listener and tnsnames services for both the servers.

Note: - You can use netca or netmgr GUI tools to create the services.

Primary Side: -

Listener File Output: -

[oracle@prod admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = TCP)(HOST = prod.radical.com)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=prod)

(SID_NAME=prod)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=dr)

(SID_NAME=dr)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

Tnsnames File Output: -

[oracle@prod admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = prod)

)

DR =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dr)

)

Standby Side: -

Listener File Output: -

[oracle@dr admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = TCP)(HOST = dr.radical.com)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=prod)

(SID_NAME=prod)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=dr)

(SID_NAME=dr)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

Tnsnames File Output: -

[oracle@dr admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DR =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dr)

)

PROD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = prod)

)

Step 5) Use the dgmgrl utility to connect to the primary server.

[oracle@prod admin]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/sys@prod

Connected.

Step 6) Now configure the broker service on the primary server.

DGMGRL> create configuration 'broker' as primary database is prod connect identifier is prod;

Configuration "broker" created with primary database "prod"

Step 7) Now add the standby database in the above configuration.

DGMGRL> add database 'dr' as connect identifier is dr maintained as physical;

Database "dr" added

Note: - help add command will show the syntax of how to add the standby database in the above configuration.

DGMGRL> help add

Adds a standby database to the broker configuration

Syntax:

ADD DATABASE <database name>

[AS CONNECT IDENTIFIER IS <connect identifier>]

[MAINTAINED AS {PHYSICAL|LOGICAL}];

Step 8) Now you need to enable the configuration.

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration;

Configuration - broker

Protection Mode: MaxPerformance

Databases:

prod - Primary database

dr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

We can see from the above output our broker configuration has been enabled.

Step 9) Now we need to set the below property

These below two properties are used to sync the log file between primary and standby.

DGMGRL> edit database prod set property logxptmode= 'sync';

Property "logxptmode" updated

DGMGRL> edit database dr set property logxptmode= 'sync';

Property "logxptmode" updated

These below properties are used when there will be failover activity will perform between primary and standby server.

DGMGRL> edit database prod set property faststartfailovertarget= 'dr';

Property "faststartfailovertarget" updated

DGMGRL> edit database dr set property faststartfailovertarget= 'prod';

Property "faststartfailovertarget" updated

DGMGRL> edit configuration set property faststartfailoverthreshold= '10';

Property "faststartfailoverthreshold" updated

Step 10) If you want to check the detailed information then you can use below commands.

1) show instance prod: - It will give the status of the primary Instance.

DGMGRL> show instance prod;

Instance 'prod' of database 'prod'

Instance Status:

SUCCESS

2) show instance dr: - It will give the status of the standby Instance.

DGMGRL> show instance dr;

Instance 'dr' of database 'dr'

Instance Status:

SUCCESS

3) show database verbose prod: - It will give the detailed logs of the primary server.

DGMGRL> show database verbose prod;

Database - prod

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

prod

Properties:

DGConnectIdentifier = 'prod'

ObserverConnectIdentifier = ''

LogXptMode = 'sync'

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = ' '

LogFileNameConvert = ' '

FastStartFailoverTarget = 'dr'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

ApplyLagThreshold = '0'

TransportLagThreshold = '0'

TransportDisconnectedThreshold = '30'

SidName = 'prod'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.radical.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))'

StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

4) show database verbose dr: - It will give the detailed logs of the standby server.

DGMGRL> show database verbose dr;

Database - dr

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds (computed 1 second ago)

Apply Lag: 0 seconds (computed 1 second ago)

Apply Rate: 0 Byte/s

Real Time Query: OFF

Instance(s):

Properties:

DGConnectIdentifier = 'dr'

ObserverConnectIdentifier = ''

LogXptMode = 'sync'

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = 'prod, dr'

LogFileNameConvert = 'prod, dr'

FastStartFailoverTarget = 'prod'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

ApplyLagThreshold = '0'

TransportLagThreshold = '0'

TransportDisconnectedThreshold = '30'

SidName = 'dr'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dr.radical.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dr_DGMGRL)(INSTANCE_NAME=dr)(SERVER=DEDICATED)))'

StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

Thank You!!

Oracle Physical Standby Dataguard in 11g | Karan Rajpoot

2019-03-03T13:15:00.001+05:30

Required Parameters to configure the Dataguard setup:-

DB_NAME= It should be the same on the primary and standby side.

DB_UNIQUE_NAME= Must be different on the primary and dr side.

LOG_ARCHIVE_CONFIG= This parameter includes db_unique_name which is the part of data guard configuration.

LOG_ARCHIVE_DEST_n= It defines the local and remote archive log file location.

LOG_ARCHIVE_DEST_STATE_n= It defines the state of archiving (enable or differ).

REMOTE_LOGIN_PASSWORDFILE= it should be in EXCLUSIVE mode.

FAL_SERVER= Use for archive log gap resolution (required only in physical standby server).

DB_FILE_NAME_CONVERT= It is required when directory structure is needed to be in the different datafile.

LOG_FILE_NAME_CONVERT= It is required when directory structure is needed to be in the different logfile.

STANDBY_FILE_MANAGEMENT= It helps to create the file automatically on the standby server.

In this demonstration, I am using the below names of primary and standby databases.

priamry DB_NAME=prod

primary DB_UNIQUE_NAME=prod

standby DB_NAME=prod

standby DB_UNIQUE_NAME=dr

Now let's start the configuration.

Primary Server Configuration: -

Step 1) On the primary server, check the archive mode of the database, It should be in archive log mode.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 80
Next log sequence to archive 82
Current log sequence 82

Note: - I can see from the above output, my database is in archive log mode. If it is not in archive mode then we need to change manually through the below steps.

Step to change database mode from NoArchive to Archive.

shutdown the database

start the database in mount stage

alter database archive log

alter database open

Step 2) Now check the logging mode of the database, It should be force_logging mode.

SQL> select force_logging from v$database;

FOR

---

YES

Step 3) Now check the db_name and db_unique_name of the primary server.

SQL> select name from v$database;

NAME

---------

PROD

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME

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

prod

Step 4) Now make the db_unique_name to be the part of the data guard configuration using below command.

SQL> alter system set log_archive_config='DG_CONFIG=(prod,dr)' ;

System altered.

Step 5) Now set the archivelog destinations.

SQL> alter system set log_archive_dest_2='service=dr lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=dr';

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

Step 6) Now set remote login password to exclusive.

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

Step 7) Now set falserver and file name convert parameters.

SQL> alter system set fal_server=dr;

System altered.

SQL> alter system set db_file_name_convert='prod','dr' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='prod','dr' scope=spfile;

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

Step 8) Now Configure the listener and tnsnames entries on the primary server.

Listener file Output: -

[oracle@prod admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = TCP)(HOST = prod.radical.com)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=prod)

(SID_NAME=prod)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=dr)

(SID_NAME=dr)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

tnsnames file output: -

[oracle@prod admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = prod)

)

DR =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dr)

)

Step 9) Now take a backup of the primary database using RMAN utility.

Command: - backup database plus archivelog;

Step 10) Now we need to create the standby control file and pfile.

SQL> alter database create standby controlfile as '/tmp/drcontrol.ctl';

Database altered.

SQL> create pfile='/tmp/initdr.ora' from spfile;

File created.

Note: - After creating the pfile, we need to modify the pfile and create the necessary directory as per the requirement on the standby server.

Below are some mandatory changes which required on the standby server.

1) change db_unique_name

2) change fal_server

3) change log_archive_dest_n

4) Make a directory on the standby server like adump, controlfile path directory, flash recovery directory.

Step 11) Now we need to copy backup set, archivelog, pfile, standby controlfile and password file to standby database.

Control File: -

[oracle@prod admin]$ scp /tmp/drcontrol.ctl oracle@192.168.2.21:/u01/app/oracle/oradata/dr/control01.ctl

oracle@192.168.2.21's password:

drcontrol.ctl 100% 9808KB 9.6MB/s 00:00

Now copy the content of control01.ctl control file to other control files to make it sync on the standby server.

[oracle@dr ~]$ cp /u01/app/oracle/oradata/dr/control01.ctl /u01/app/oracle/oradata/dr/control03.ctl

[oracle@dr ~]$ cp /u01/app/oracle/oradata/dr/control01.ctl /u01/app/oracle/oradata/dr/control04.ctl

Backup set and archivelog: -

[oracle@prod admin]$ scp -r /u01/app/oracle/fast_recovery_area/PROD oracle@192.168.2.21:/u01/app/oracle/fast_recovery_area/

oracle@192.168.2.21's password:

o1_mf_nnndf_TAG20190303T113247_g7pvj845_.bkp 100% 1131MB 7.3MB/s 02:34

o1_mf_annnn_TAG20190303T113231_g7pvhrb8_.bkp 100% 152MB 4.8MB/s 00:32

o1_mf_annnn_TAG20190303T113503_g7pvnj52_.bkp 100% 75KB 75.0KB/s 00:00

o1_mf_annnn_TAG20190209T004745_g5vogs7v_.bkp 100% 257KB 257.0KB/s 00:00

o1_mf_nnndf_TAG20190217T001807_g6jpq8g4_.bkp 100% 1107MB 10.1MB/s 01:50

o1_mf_annnn_TAG20190217T002003_g6jptvqd_.bkp 100% 68KB 68.0KB/s 00:00

o1_mf_annnn_TAG20190217T001730_g6jpp3vm_.bkp 100% 436MB 9.9MB/s 00:44

o1_mf_s_1001936105_g7pvnm0t_.bkp 100% 9888KB 9.7MB/s 00:01

o1_mf_s_999737268_g5vogwod_.bkp 100% 9888KB 9.7MB/s 00:01

o1_mf_s_1000426805_g6jptydc_.bkp 100% 9888KB 9.7MB/s 00:00

o1_mf_1_72_g6joh1l9_.arc 100% 5424KB 5.3MB/s 00:00

o1_mf_1_78_g75mzbxx_.arc 100% 25MB 12.3MB/s 00:02

o1_mf_1_79_g75n35b3_.arc 100% 1113KB 1.1MB/s 00:00

o1_mf_1_82_g7pvhog5_.arc 100% 6757KB 6.6MB/s 00:01

o1_mf_1_80_g7ps5p8d_.arc 100% 39MB 19.5MB/s 00:02

o1_mf_1_83_g7pvnhm2_.arc 100% 74KB 73.5KB/s 00:00

o1_mf_1_81_g7psbblo_.arc 100% 44MB 14.5MB/s 00:03

o1_mf_1_73_g6jpcf8l_.arc 100% 2745KB 2.7MB/s 00:01

o1_mf_1_75_g6jpp0tq_.arc 100% 247KB 247.0KB/s 00:00

o1_mf_1_74_g6jpgdgg_.arc 100% 582KB 582.0KB/s 00:00

o1_mf_1_76_g6jptvb3_.arc 100% 67KB 66.5KB/s 00:00

o1_mf_1_77_g6lvmgrz_.arc 100% 28MB 4.7MB/s 00:06

parameter file: -

[oracle@prod admin]$ scp /tmp/initdr.ora oracle@192.168.2.21:/tmp/initdr.ora

oracle@192.168.2.21's password:

initdr.ora 100% 1261 1.2KB/s 00:00

Remote login password file: -

[oracle@prod admin]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwprod oracle@192.168.2.21:/u01/app/oracle/product/11.2.0/db_1/dbs/

oracle@192.168.2.21's password:

orapwprod 100% 1536 1.5KB/s 00:00

Note: - We need to change the password file from orapwprod to orapwdr on the standby server.

mv orapwprod orapwdr

Physical Standby Server Configuration: -

Step 12) Now create the listener and tnsnames entry for standby database as well and update /etc/oratab file.

[oracle@dr admin]$ cat /etc/oratab

# This file is used by ORACLE utilities. It is created by root.sh

# and updated by either the Database Configuration Assistant while creating

# a database or ASM Configuration Assistant while creating an ASM instance.

# A colon, ':', is used as the field terminator. A new line terminates

# the entry. Lines beginning with a pound sign, '#', are comments.

# Entries are of the form:

# $ORACLE_SID:$ORACLE_HOME:<N|Y>:

# The first and second fields are the system identifier and home

# directory of the database respectively. The third filed indicates

# to the dbstart utility that the database should, "Y", or should not,

# "N", be brought up at system boot time.

# Multiple entries with the same $ORACLE_SID are not allowed.

dr:/u01/app/oracle/product/11.2.0/db_1:N

Step 13) Now create spfile from the pfile file which we were transferred from primary to the standby server.

SQL> create spfile from pfile='/tmp/initdr.ora';

File created.

Step 14) Now restore the backup file on the standby server using RMAN utility.

[oracle@dr admin]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Mar 3 12:34:00 2019

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area 839282688 bytes

Fixed Size 2257880 bytes

Variable Size 545262632 bytes

Database Buffers 289406976 bytes

Redo Buffers 2355200 bytes

RMAN> restore database;

Starting restore at 03-MAR-19

Starting implicit crosscheck backup at 03-MAR-19

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=28 device type=DISK

Crosschecked 10 objects

Finished implicit crosscheck backup at 03-MAR-19

Starting implicit crosscheck copy at 03-MAR-19

using channel ORA_DISK_1

Crosschecked 6 objects

Finished implicit crosscheck copy at 03-MAR-19

searching for all files in the recovery area

cataloging files...

no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dr/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/dr/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/dr/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dr/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/dr/goms01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/dr/goms_data01.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/dr/users03.dbf

channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/dr/recovery_catalog.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2019_03_03/o1_mf_nnndf_TAG20190303T113247_g7pvj845_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_03_03/o1_mf_nnndf_TAG20190303T113247_g7pvj845_.bkp tag=TAG20190303T113247

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:20

Finished restore at 03-MAR-19

Step 15) Now we can create standby redo log file on both the side to transfer the archive from primary to standby server using the current log file.

Make sure you will create standby redo log file as same size as online redo log file.

Standby side: -

alter database add standby logfile

('/u01/app/oracle/oradata/dr/standby_redo01.log') size 50M;

alter database add standby logfile

('/u01/app/oracle/oradata/dr/standby_redo02.log') size 50M;

alter database add standby logfile

('/u01/app/oracle/oradata/dr/standby_redo03.log') size 50M;

alter database add standby logfile

('/u01/app/oracle/oradata/dr/standby_redo04.log') size 50M;

Primary Side: -

alter database add standby logfile

('/u01/app/oracle/oradata/prod/standby_redo01.log') size 50M;

alter database add standby logfile

('/u01/app/oracle/oradata/prod/standby_redo02.log') size 50M;

alter database add standby logfile

('/u01/app/oracle/oradata/prod/standby_redo03.log') size 50M;

alter database add standby logfile

('/u01/app/oracle/oradata/prod/standby_redo04.log') size 50M;

Step 16) Now start the redo apply process om standby server using below command.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

[oracle@dr admin]$ ps -ef | grep mrp

oracle 4622 1 4 12:47 ? 00:00:00 ora_mrp0_dr

oracle 4628 3613 0 12:47 pts/0 00:00:00 grep mrp

Step 17) Now on the primary server check the latest archive log and force a log switch.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 85

Next log sequence to archive 87

Current log sequence 87

Now on the physical standby server check the latest archive log has been applied or not.

SQL> select sequence#, first_time,next_time, applied from v$archived_log ORDER BY sequence#;

SEQUENCE# FIRST_TIME NEXT_TIME APPLIED

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

83 03-MAR-2019 11:32:29 03-MAR-2019 11:35:03 YES

84 03-MAR-2019 11:35:03 03-MAR-2019 12:34:21 YES

85 03-MAR-2019 12:34:21 03-MAR-2019 12:50:06 YES

86 03-MAR-2019 12:50:06 03-MAR-2019 12:50:20 YES

As we can see from the above output, the archive has been applied successfully to the standby server.

Step 18) Now we can check the DB name, DB unique name, role, mode of both the servers.

Primary Server Output: -

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

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE

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

PROD prod READ WRITE PRIMARY

Standby Server Output: -

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

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE

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

PROD dr READ ONLY WITH APPLY PHYSICAL STANDBY

Oracle SQL Developer Installation in Linux | Karan Rajpoot

2019-02-26T00:26:00.000+05:30

Steps to Install Oracle SQL Developer

Step 1) You need to download the SQL developer rpm and java development kit rpm of the version as per your requirement.

Links to download rpm's: -

Java development kit: - https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

SQL developer: - https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Step 2) Login to your Linux Machine and install both the rpm's.

[root@dr sf_Oracle_DBA_Softwares]# rpm -ivh sqldeveloper-18.4.0-376.1900.noarch.rpm

Preparing... ########################################### [100%]

1:sqldeveloper ########################################### [100%]

[root@dr sf_Oracle_DBA_Softwares]#

[root@dr sf_Oracle_DBA_Softwares]# rpm -ivh jdk-8u201-linux-x64.rpm

warning: jdk-8u201-linux-x64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

Preparing... ########################################### [100%]

1:jdk1.8 ########################################### [100%]

Unpacking JAR files...

tools.jar...

plugin.jar...

javaws.jar...

deploy.jar...

rt.jar...

jsse.jar...

charsets.jar...

localedata.jar...

[root@dr sf_Oracle_DBA_Softwares]#

Step 3) Now find the java home path through below command.

To check the java home path execute the command: - namei /usr/bin/java

Output: -

[root@dr sf_Oracle_DBA_Softwares]# namei /usr/bin/java

f: /usr/bin/java

d /

d usr

d bin

l java -> /etc/alternatives/java

d /

d etc

d alternatives

l java -> /usr/java/jdk1.8.0_201-amd64/jre/bin/java

d /

d usr

d java

d jdk1.8.0_201-amd64

d jre

d bin

- java

[root@dr sf_Oracle_DBA_Softwares]#

In my case java home is /usr/java/jdk1.8.0_201-amd64

Step 4) Now execute the sqldeveloper command and provide the path of the java home.

Java Home

Oracle SQL Welcome Page

Note: - Sometimes while executing the sqldeveloper command, you will face below issue.

Issue: -

[root@dr sf_Oracle_DBA_Softwares]# sqldeveloper

Exception in thread "main" java.lang.UnsupportedClassVersionError: oracle/dbtools/raptor/startup/HomeSupport : Unsupported major.minor version 52.0 at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:800) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142) at java.net.URLClassLoader.defineClass(URLClassLoader.java:449) at java.net.URLClassLoader.access$100(URLClassLoader.java:71) at java.net.URLClassLoader$1.run(URLClassLoader.java:361) at java.net.URLClassLoader$1.run(URLClassLoader.java:355) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:354) at java.lang.ClassLoader.loadClass(ClassLoader.java:425) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308) at java.lang.ClassLoader.loadClass(ClassLoader.java:358) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Class.java:274) at java.util.ServiceLoader$LazyIterator.next(ServiceLoader.java:363) at java.util.ServiceLoader$1.next(ServiceLoader.java:445) at oracle.ide.osgi.boot.LauncherUtil.processOJStartupHook(LauncherUtil.java:356) at oracle.ide.osgi.boot.LauncherUtil.initialize(LauncherUtil.java:101) at oracle.ide.osgi.boot.OracleIdeLauncher.main(OracleIdeLauncher.java:80)

Cause: - The java development kit which you have installed is not matched with the current SQL developer version.

Solution: - Install the java development kit version as same as your SQL developer version. I have provided the above links to download both the rpm's.

If this blog is helpful please like, share and comment.

Thank you!!

RMAN Backup Based Cloning in Oracle 11g | Karan Rajpoot

2019-02-17T01:53:00.000+05:30

Introduction:

RMAN has the ability to duplicate, or clone, a database from a backup or from an active database. It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure.

In this demonstration, I am using the "prod" as a Source database and "clone" as an Auxiliary database.

Prerequisites:

RMAN must be connected as AUXILIARY to the instance of the duplicate database. The instance of the duplicate database is called the auxiliary instance. The auxiliary instance must be started with the NOMOUNT option.

High-Level steps of RMAN backup based cloning in Oracle 11g

Source Server: -

1) The database should be in Archive log mode.

2) Set the recovery parameters.

3) Set the datafile and logfile convert parameter.

4) Create Parameter file (pfile).

5) Take a fresh backup.

6) Create Password file.

7) Configure the Listener and Tnsnames files.

Target Server: -

1) Add the database entry in /etc/oratab file.

2) Configure the Listener and Tnsnames files.

3) Copy the parameter file, password file and backups from source to the target server.

4) Edit the pfile and make the database in the nomount stage.

5) Make the necessary directories paths.

Let's start the Practical to understand in a better way.

Source Server: -

Step 1) Check the database mode, It should be in Archive log.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 71

Next log sequence to archive 73

Current log sequence 73

Step 2) Set the recovery Parameters. Below db_recovery_file_dest and db_recovery_file_dest_size has been set perfectly.

SQL> show parameter recover

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/oracle/fast_recovery_

area

db_recovery_file_dest_size big integer 4182M

db_unrecoverable_scn_tracking boolean TRUE

recovery_parallelism integer 0

Step 3) Now set the datafile and logfile parameter, but before setting the parameters, check you datafile and logfiles locations.

Datafile location: - /u01/app/oracle/oradata/prod

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/prod/users01.dbf

/u01/app/oracle/oradata/prod/undotbs01.dbf

/u01/app/oracle/oradata/prod/sysaux01.dbf

/u01/app/oracle/oradata/prod/system01.dbf

8 rows selected.

Log file location: - /u01/app/oracle/oradata/prod

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER

3 ONLINE /u01/app/oracle/oradata/prod/redo03.log NO

2 ONLINE /u01/app/oracle/oradata/prod/redo02.log NO

1 ONLINE /u01/app/oracle/oradata/prod/redo01.log

Setting the parameters for both the files: -

SQL> alter system set db_file_name_convert ='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/clone' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert ='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/clone' scope=spfile;

System altered.

Step 4) Create pfile from spfile.

SQL> create pfile from spfile;

File created.

Step 5) Now shut down and start the database again and check the status of datafile and logfile parameters.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2257880 bytes

Variable Size 545262632 bytes

Database Buffers 289406976 bytes

Redo Buffers 2355200 bytes

Database mounted.

Database opened.

SQL> show parameter db_file_name_convert

NAME TYPE VALUE

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

db_file_name_convert string /u01/app/oracle/oradata/prod,

/u01/app/oracle/oradata/clone

SQL> show parameter log_file_name_convert;

NAME TYPE VALUE

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

log_file_name_convert string /u01/app/oracle/oradata/prod,

/u01/app/oracle/oradata/clone

As we can see from the above output, both the parameter are successfully set.

Step 6) Take a fresh backup plus archive log as well.

RMAN> backup database plus archivelog;

Starting backup at 17-FEB-19

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=37 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=28 RECID=26 STAMP=997740572

input archived log thread=1 sequence=29 RECID=27 STAMP=997744658

input archived log thread=1 sequence=30 RECID=28 STAMP=997744941

input archived log thread=1 sequence=31 RECID=29 STAMP=997745067

input archived log thread=1 sequence=32 RECID=33 STAMP=997745614

input archived log thread=1 sequence=33 RECID=36 STAMP=997746012

input archived log thread=1 sequence=34 RECID=38 STAMP=997746396

input archived log thread=1 sequence=35 RECID=40 STAMP=997746399

input archived log thread=1 sequence=36 RECID=42 STAMP=997746404

input archived log thread=1 sequence=37 RECID=44 STAMP=997747735

input archived log thread=1 sequence=38 RECID=45 STAMP=997748661

input archived log thread=1 sequence=39 RECID=47 STAMP=997749207

input archived log thread=1 sequence=40 RECID=48 STAMP=997749208

input archived log thread=1 sequence=41 RECID=49 STAMP=997749254

input archived log thread=1 sequence=42 RECID=50 STAMP=997749259

input archived log thread=1 sequence=43 RECID=51 STAMP=997749465

input archived log thread=1 sequence=44 RECID=52 STAMP=997749662

input archived log thread=1 sequence=45 RECID=54 STAMP=997749668

input archived log thread=1 sequence=46 RECID=57 STAMP=997749698

input archived log thread=1 sequence=47 RECID=59 STAMP=997749857

input archived log thread=1 sequence=48 RECID=60 STAMP=997908946

input archived log thread=1 sequence=49 RECID=61 STAMP=997909993

input archived log thread=1 sequence=50 RECID=62 STAMP=997910037

input archived log thread=1 sequence=51 RECID=63 STAMP=997910209

input archived log thread=1 sequence=52 RECID=64 STAMP=997913689

input archived log thread=1 sequence=53 RECID=65 STAMP=998335888

input archived log thread=1 sequence=54 RECID=66 STAMP=998338260

channel ORA_DISK_1: starting piece 1 at 17-FEB-19

channel ORA_DISK_1: finished piece 1 at 17-FEB-19

piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T001730_g6jpp3vm_.bkp tag=TAG20190217T001730 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

Finished backup at 17-FEB-19

Starting backup at 17-FEB-19

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/prod/users01.dbf

channel ORA_DISK_1: starting piece 1 at 17-FEB-19

channel ORA_DISK_1: finished piece 1 at 17-FEB-19

piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_nnndf_TAG20190217T001807_g6jpq8g4_.bkp tag=TAG20190217T001807 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26

Finished backup at 17-FEB-19

Starting backup at 17-FEB-19

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=76 RECID=88 STAMP=1000426803

channel ORA_DISK_1: starting piece 1 at 17-FEB-19

channel ORA_DISK_1: finished piece 1 at 17-FEB-19

piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T002003_g6jptvqd_.bkp tag=TAG20190217T002003 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-FEB-19

Starting Control File and SPFILE Autobackup at 17-FEB-19

piece handle=/u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_17/o1_mf_s_1000426805_g6jptydc_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 17-FEB-19

Step 7) Configure the Listener and Tnsnames to resolve the connection from source to auxiliary clone servers.

Source listener file output: -

[oracle@prod admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = TCP)(HOST = prod.radical.com)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=prod)

(SID_NAME=prod)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=clone)

(SID_NAME=clone)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

Source Tnsnames file output: -

[oracle@prod admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = prod)

)

CLONE =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = clone)

(UR = A)

)

Target Server: -

Step 1) Add the database entry in /etc/oratab file.

[oracle@dr ~]$ cat /etc/oratab

# This file is used by ORACLE utilities. It is created by root.sh

# and updated by either Database Configuration Assistant while creating

# a database or ASM Configuration Assistant while creating an ASM instance.

# A colon, ':', is used as the field terminator. A new line terminates

# the entry. Lines beginning with a pound sign, '#', are comments.

# Entries are of the form:

# $ORACLE_SID:$ORACLE_HOME:<N|Y>:

# The first and second fields are the system identifier and home

# directory of the database respectively. The third filed indicates

# to the dbstart utility that the database should, "Y", or should not,

# "N", be brought up at system boot time.

# Multiple entries with the same $ORACLE_SID are not allowed.

clone:/u01/app/oracle/product/11.2.0/db_1

Step 2) Configuration of Listener and Tnsnames files.

Target listener file Output: -

[oracle@dr admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = TCP)(HOST = dr.radical.com)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=prod)

(SID_NAME=prod)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=clone)

(SID_NAME=clone)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

Target Tnsnames file Output: -

[oracle@dr admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

CLONE =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = clone)

(UR = A)

)

PROD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = prod)

)

[oracle@dr admin]$

Step 3) Copy the parameter file, password file and backups from source to the target server.

Parameter file: -

[oracle@prod dbs]$ scp initprod.ora oracle@192.168.2.21:/u01/app/oracle/product/11.2.0/db_1/dbs/initclone.ora

oracle@192.168.2.21's password:

initprod.ora 100% 1393 1.4KB/s 00:00

[oracle@prod dbs]$

Password file: -

[oracle@prod dbs]$ scp orapwprod oracle@192.168.2.21:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwclone

oracle@192.168.2.21's password:

orapwprod 100% 1536 1.5KB/s 00:00

[oracle@prod dbs]$

Directory creation output: -

Note:- Create the backup directory in the target server side, It is the same as the source server.

[oracle@dr admin]$ mkdir -p /u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17

[oracle@dr admin]$

Backup pieces copy output: -

[oracle@prod 2019_02_17]$ scp /u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/*.bkp oracle@192.168.2.21:/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/

oracle@192.168.2.21's password:

o1_mf_annnn_TAG20190217T001730_g6jpp3vm_.bkp 100% 436MB 11.2MB/s 00:39

o1_mf_annnn_TAG20190217T002003_g6jptvqd_.bkp 100% 68KB 68.0KB/s 00:00

o1_mf_nnndf_TAG20190217T001807_g6jpq8g4_.bkp 100% 1107MB 9.9MB/s 01:52

[oracle@prod 2019_02_17]$

Step 4) Edit the pfile, make necessary directories and then start the database in the nomount stage.

[oracle@dr dbs]$ mkdir -p /u01/app/oracle/admin/clone/adump

[oracle@dr dbs]$ mkdir -p /u01/app/oracle/oradata/clone

[oracle@dr dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2257880 bytes

Variable Size 545262632 bytes

Database Buffers 289406976 bytes

Redo Buffers 2355200 bytes

SQL>

Now create spfile from pfile and then start the database again using spfile.

SQL> create spfile from pfile;

File created.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2257880 bytes

Variable Size 545262632 bytes

Database Buffers 289406976 bytes

Redo Buffers 2355200 bytes

SQL>

SQL> show parameter spfile;

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0

/db_1/dbs/spfileclone.ora

Now go to the Source server-side and log in from RMAN to connect source as well as Auxiliary database.

[oracle@prod 2019_02_17]$ rman target / auxiliary sys/sys@clone

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Feb 17 01:30:02 2019

connected to target database: PROD (DBID=407246072)

connected to auxiliary database: CLONE (not mounted)

RMAN> duplicate target database to "clone";

Starting Duplicate Db at 17-FEB-19

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:

{

sql clone "alter system set db_name =

''PROD'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''CLONE'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''CLONE'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 839282688 bytes

Fixed Size 2257880 bytes

Variable Size 545262632 bytes

Database Buffers 289406976 bytes

Redo Buffers 2355200 bytes

Starting restore at 17-FEB-19

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_17/o1_mf_s_1000426805_g6jptydc_.bkp

channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_17/o1_mf_s_1000426805_g6jptydc_.bkp

ORA-19505: failed to identify file "/u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_17/o1_mf_s_1000426805_g6jptydc_.bkp"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

failover to previous backup

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_09/o1_mf_s_999737268_g5vogwod_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/autobackup/2019_02_09/o1_mf_s_999737268_g5vogwod_.bkp tag=TAG20190209T004748

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/app/oracle/oradata/clone/control01.ctl

output file name=/u01/app/oracle/oradata/clone/control03.ctl

output file name=/u01/app/oracle/oradata/clone/control04.ctl

Finished restore at 17-FEB-19

database mounted

contents of Memory Script:

{

set until scn 1619368;

set newname for datafile 1 to

"/u01/app/oracle/oradata/clone/system01.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/clone/sysaux01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/clone/undotbs01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/clone/users01.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/clone/goms01.dbf";

set newname for datafile 6 to

"/u01/app/oracle/oradata/clone/goms_data01.dbf";

set newname for datafile 7 to

"/u01/app/oracle/oradata/clone/users03.dbf";

set newname for datafile 8 to

"/u01/app/oracle/oradata/clone/recovery_catalog.dbf";

restore

clone database

;

}

executing Memory Script

executing the command: SET until clause

executing the command: SET NEWNAME

Starting restore at 17-FEB-19

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/clone/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/clone/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/clone/undotbs01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/clone/users01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/clone/goms01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/clone/goms_data01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/clone/users03.dbf

channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/clone/recovery_catalog.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_nnndf_TAG20190217T001807_g6jpq8g4_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_nnndf_TAG20190217T001807_g6jpq8g4_.bkp tag=TAG20190217T001807

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:56

Finished restore at 17-FEB-19

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=12 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=13 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=14 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=15 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=16 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/goms01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=17 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/goms_data01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=18 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/users03.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=19 STAMP=1000431074 file name=/u01/app/oracle/oradata/clone/recovery_catalog.dbf

contents of Memory Script:

{

set until scn 1619368;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing the command: SET until clause

Starting recover at 17-FEB-19

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=72

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=73

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=74

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=75

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T001730_g6jpp3vm_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T001730_g6jpp3vm_.bkp tag=TAG20190217T001730

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15

archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_72_g6jv0m02_.arc thread=1 sequence=72

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_72_g6jv0m02_.arc RECID=84 STAMP=1000431083

archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_73_g6jv0n24_.arc thread=1 sequence=73

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_73_g6jv0n24_.arc RECID=85 STAMP=1000431084

archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_74_g6jv0nmf_.arc thread=1 sequence=74

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_74_g6jv0nmf_.arc RECID=86 STAMP=1000431084

archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_75_g6jv0nsv_.arc thread=1 sequence=75

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_75_g6jv0nsv_.arc RECID=87 STAMP=1000431084

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=76

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T002003_g6jptvqd_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2019_02_17/o1_mf_annnn_TAG20190217T002003_g6jptvqd_.bkp tag=TAG20190217T002003

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_76_g6jv0xok_.arc thread=1 sequence=76

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/CLONE/archivelog/2019_02_17/o1_mf_1_76_g6jv0xok_.arc RECID=88 STAMP=1000431093

media recovery complete, elapsed time: 00:00:01

Finished recover at 17-FEB-19

Oracle instance started

Total System Global Area 839282688 bytes

Fixed Size 2257880 bytes

Variable Size 545262632 bytes

Database Buffers 289406976 bytes

Redo Buffers 2355200 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''CLONE'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set db_name = ''CLONE'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to an auxiliary database (not started)

Oracle instance started

Total System Global Area 839282688 bytes

Fixed Size 2257880 bytes

Variable Size 545262632 bytes

Database Buffers 289406976 bytes

Redo Buffers 2355200 bytes

SQL statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( '/u01/app/oracle/oradata/clone/redo01.log' ) SIZE 50 M REUSE,

GROUP 2 ( '/u01/app/oracle/oradata/clone/redo02.log' ) SIZE 50 M REUSE,

GROUP 3 ( '/u01/app/oracle/oradata/clone/redo03.log' ) SIZE 50 M REUSE

DATAFILE

'/u01/app/oracle/oradata/clone/system01.dbf'

CHARACTER SET WE8MSWIN1252

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/app/oracle/oradata/clone/temp01.dbf";

switch clone tempfile all;

catalog clone datafilecopy "/u01/app/oracle/oradata/clone/sysaux01.dbf",

"/u01/app/oracle/oradata/clone/undotbs01.dbf",

"/u01/app/oracle/oradata/clone/users01.dbf",

"/u01/app/oracle/oradata/clone/goms01.dbf",

"/u01/app/oracle/oradata/clone/goms_data01.dbf",

"/u01/app/oracle/oradata/clone/users03.dbf",

"/u01/app/oracle/oradata/clone/recovery_catalog.dbf";

switch clone datafile all;

}

executing Memory Script

executing the command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/clone/temp01.dbf in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/clone/sysaux01.dbf RECID=1 STAMP=1000431109

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/clone/undotbs01.dbf RECID=2 STAMP=1000431109

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/clone/users01.dbf RECID=3 STAMP=1000431109

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/clone/goms01.dbf RECID=4 STAMP=1000431109

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/clone/goms_data01.dbf RECID=5 STAMP=1000431109

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/clone/users03.dbf RECID=6 STAMP=1000431109

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/clone/recovery_catalog.dbf RECID=7 STAMP=1000431109

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/goms01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=5 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/goms_data01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=6 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/users03.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=7 STAMP=1000431109 file name=/u01/app/oracle/oradata/clone/recovery_catalog.dbf

Reenabling controlfile options for auxiliary database

Executing: alter database force logging

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 17-FEB-19

RMAN>

Now login to the Auxiliary database and check the status. It's up and running.

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

NAME OPEN_MODE DATABASE_ROLE

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

CLONE READ WRITE PRIMARY

If this blog is helpful please like, comment below and let me know in which topic should I create a blog.

Thank you!!

Oracle 12c Grid ASM installation Part-2 | Karan Rajpoot

2019-02-15T23:44:00.000+05:30

Oracle 12c grid ASM installation Part-2

Till now we have seen the Oracle 12c binary installation, Grid ASM installation till Configure and Create the disks, Now we will see the last step which is to executes the runInstaller command to do the final installation.

Step 8) Now login through respective user and go to the folder where files have been unzipped.

[grid@dr ~]$ cd /opt

[grid@dr opt]$ ll

total 631840

drwxr-xr-x 7 oracle oinstall 4096 Aug 27 2013 database

drwxr-xr-x 7 grid oinstall 4096 Jul 7 2014 grid

Now inside grid folder you need executes the runInstaller command to start the installation.

Note: - If you will get below error then do panic just open a new window and execute xhost + command from the root user.

Error: -

[grid@dr grid]$ ./runInstaller

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 415 MB. Actual 1660 MB Passed

Checking swap space: must be greater than 150 MB. Actual 4199 MB Passed

Checking monitor: must be configured to display at least 256 colors

>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation,

Continue? (y/n) [n] n

User Selected: No

Exiting Oracle Universal Installer, log for this session can be found at /u01/app/oraInventory/logs/installActions2019-02-15_12-29-19AM.log

[grid@dr grid]$

Solution: - open a new window and execute xhost + command from the root user.

[root@dr sf_Oracle_DBA_Softwares]# xhost +

access control disabled, clients can connect from any host

[root@dr sf_Oracle_DBA_Softwares]#

Now again executes the runInstaller command

1) Choose Install and Configure Oracle Grid Infrastructure for a Standalone server.

2) Choose the default language English.

3) Select disk group name, Redundancy, Allocation Unit Size, and Candidate Disks as per your requirement.

4) Choose the ASM Password as per your requirement, you can put the same password for below users or you can choose a different password for both the users.

5) You can choose Enterprise Manager (EM) Cloud Control as a managament purpose as per your requirement.

6) Select the OS groups as per your requirement or you can put it as a default.

7) Select the Oracle base and Oracle Home as per your requirement.

8) From 12c onwards, you have the option to run the root scripts automatically.

9) In this step all the prerequisites will review, in case you will get some warning you can simply click on fix&check Again option to fix the issues.

10) In 12c you will also get edit options available in the summary option.

11) Grid Infrastructure for a standalone server was successfully installed.

If this blog is helpful please like, comment below and let me know which topic should I create a blog.

Thank you!!

Oracle 12c Grid ASM installation Part-1 | Karan Rajpoot

2019-02-15T01:54:00.001+05:30

Oracle 12c grid ASM installation Part-1

In my previous blog, I have shown you how to install Oracle 12c grid binary, In this blog, I will show you how to do Oracle 12c ASM installation.

In case you are unable to find Oracle 12c grid binary blog Click Here.

High-Level Steps for Oracle 12c grid ASM installation

1) Need to create ASM disks to store data. (Size as per the requirement)

2) Unzip the 12c grid software.

3) Install the necessary RPM's

4) Format all the raw disks.

5) Add the necessary groups.

6) Create or modify the user.

7) Configure and create all the disks.

8) Execute the runInstaller command to do the final installation.

Here is the step by step installation of Oracle 12c ASM.

Step 1) We need to create ASM disks to store data.

Steps to create ASM disks.

1) Go to Oracle Virtual box, select the Machine Name and click on setting tab.

2) Click on storage and then click on the below tab to create the disks.

3) We can see in the below output, 6 ASM disks are now ready for use.

Step 2) I have already unzip the grid software in my previous Oracle 12c grid binary blog.

Step 3) Now we need to install necessary RPM's to configure the ASM.

Note: - Before you install the RPM packages, first you need to identify your O.S version through a command called uname -r

Package name for oel6: -

oracleasm-support-2.1.8-1.el6.x86_64.rpm

oracleasmlib-2.0.4-1.el6.x86_64.rpm

Step 4) Now need to format all the raw disks.

Note: - You need to format these disk the only sdb,sdc,sdd,sde,sdf,sdh, don't touch sda because it is used for your OS level use.

[root@dr sf_Oracle_DBA_Softwares]# fdisk /dev/sdb

Device contains neither a valid DOS partition table nor Sun, SGI or OSF disklabel

Building a new DOS disklabel with disk identifier 0x7dd54d7f.

Changes will remain in memory only until you decide to write them.

After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to

switch off the mode (command 'c') and change display units to

sectors (command 'u').

Command (m for help): u

Changing display/entry units to sectors

Command (m for help): n

Command action

e extended

p primary partition (1-4)

Partition number (1-4): 1

First sector (63-20971519, default 63): ----Press Enter

Using default value 63

Last sector, +sectors or +size{K,M,G} (63-20971519, default 20971519): ----Press Enter

Using default value 20971519

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

[root@dr sf_Oracle_DBA_Softwares]#

Note: - Do the same steps for all the remaining disks.

Steps 5) I have already added necessary groups in my previous Oracle 12c grid binary blog.

Step 6) I have already created a user in my previous Oracle 12c grid binary blog.

Step 7) Now need to Configure and Create all the disks.

Configure steps

[root@dr sf_Oracle_DBA_Softwares]# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library

driver. The following questions will determine whether the driver is

loaded on boot and what permissions it will have. The current values

will be shown in brackets ('[]'). Hitting <ENTER> without typing an

answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: grid

Default group to own the driver interface []: asmadmin

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

Initializing the Oracle ASMLib driver: [ OK ]

Scanning the system for Oracle ASMLib disks: [ OK ]

Disks Creation steps

[root@dr sf_Oracle_DBA_Softwares]# oracleasm createdisk OCR_VOTE01 /dev/sdb1

Writing disk header: done

Instantiating disk: done

[root@dr sf_Oracle_DBA_Softwares]# oracleasm createdisk OCR_VOTE02 /dev/sdc1

Writing disk header: done

Instantiating disk: done

[root@dr sf_Oracle_DBA_Softwares]# oracleasm createdisk OCR_VOTE03 /dev/sdd1

Writing disk header: done

Instantiating disk: done

[root@dr sf_Oracle_DBA_Softwares]# oracleasm createdisk DATA01 /dev/sde1

Writing disk header: done

Instantiating disk: done

[root@dr sf_Oracle_DBA_Softwares]# oracleasm createdisk DATA02 /dev/sdf1

Writing disk header: done

Instantiating disk: done

[root@dr sf_Oracle_DBA_Softwares]# oracleasm createdisk FRA01 /dev/sdg1

Writing disk header: done

Instantiating disk: done

Scan all the disks

[root@dr sf_Oracle_DBA_Softwares]# oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Scanning system for ASM disks...

[root@dr sf_Oracle_DBA_Softwares]#

List all the disks

[root@dr sf_Oracle_DBA_Softwares]# oracleasm listdisks

DATA01

DATA02

FRA01

OCR_VOTE01

OCR_VOTE02

OCR_VOTE03

[root@dr sf_Oracle_DBA_Softwares]#

I will show the last step (Execute the runInstaller command to do the final installation) in my next blog otherwise, the blog will get too lengthy.

If this blog is helpful please like, comment below and let me know which topic should I create a blog.

Thank you!!

Oracle 12c Grid binary installation | Karan Rajpoot

2019-02-12T23:40:00.000+05:30

Oracle 12c Grid binary installation

High-Level Steps for Oracle 12c grid binary installation

1) Download the below grid software

linuxamd64_12102_grid_1of2
linuxamd64_12102_grid_2of2.

2) Add the necessary groups.

eg) asmdba, asmadmin, asmoper.

3) Create or modify a user.

eg) Grid or Oracle user.

4) Unzip the grid software from that user.

eg) unzip <file name>

Note:- Unzip the files in sequential way, first linuxamd64_12102_grid_1of2 then linuxamd64_12102_grid_2of2

Now let's see the above steps with the help of practical

Step 1) First thing that needs to be done after download, Copy the zip files in some other folder to change the ownership from root to respective user (Oracle/Grid).

[root@dr sf_Oracle_DBA_Softwares]# cp linuxamd64_12102_grid_1of2.zip linuxamd64_12102_grid_2of2.zip /opt

Step 2) Create the necessary groups and user.

Group Creation

[root@dr sf_Oracle_DBA_Softwares]# groupadd asmdba
[root@dr sf_Oracle_DBA_Softwares]# groupadd asmadmin
[root@dr sf_Oracle_DBA_Softwares]# groupadd asmoper

User Creation

[root@dr sf_Oracle_DBA_Softwares]# useradd -g oinstall -G asmdba,asmadmin,asmoper grid

Step 3) Now change the ownership of the zip file from root to grid user.

[root@dr opt]# chown grid:oinstall linuxamd64_12102_grid_1of2.zip linuxamd64_12102_grid_2of2.zip

[root@dr opt]# ll
total 2337936
-rwxr-x--- 1 grid oinstall 1747043545 Feb 12 22:49 linuxamd64_12102_grid_1of2.zip
-rwxr-x--- 1 grid oinstall 646972897 Feb 12 22:50 linuxamd64_12102_grid_2of2.zip

Step 4) Now Unzip both the below files from grid user.

Command: - unzip linuxamd64_12102_grid_1of2.zip
unzip linuxamd64_12102_grid_2of2.zip

After Unzipping the file you will get a folder called "grid".

Inside the "grid" folder you will get the option called "runInstaller" which is used to configure the ASM.

Recovery Catalog Database Using RMAN - Karan Rajpoot

2019-02-09T01:30:00.001+05:30

Recovery Catalog Database Using RMAN

By default, RMAN connects to the target database in NOCATALOG mode, meaning that it uses the control files in the target database as the sole repository of RMAN metadata. Perhaps the most important decision you make when using RMAN is whether to create a recovery catalog as the RMAN repository for normal production operations. A recovery catalog is a schema created in a separate database that contains metadata obtained from the target control file.

With the use of the recovery catalog database, you can put all the backup or pieces information of the multiple databases in a single location.

==> Benefits of Using the Recovery Catalog as the RMAN Repository

When you use a recovery catalog, RMAN can perform a wider variety of automated backup and recovery functions than when you use the control file in the target database as the sole repository of metadata.

==>The following features are available only with a catalog

1) You can store metadata about multiple target databases in a single catalog.

2) You can store metadata about multiple incarnations of a single target database in the catalog. Hence, you can restore backups from any incarnation.

3) Resynchronizing the recovery catalog at intervals less than the CONTROL_FILE_RECORD_KEEP_TIME setting, you can keep historical metadata.

4) You can report the target database schema at a noncurrent time.

5) You can store RMAN scripts in the recovery catalog.

==> Steps to create a Recovery Catalog Database

Step 1) First to create a tablespace for the recovery catalog database to put the information.

Step 2) Next step is to create a recovery catalog user and assign tablespace which we have created in the first step.

Step 3) Grant the recovery_catalog_owner permission to the user.

Step 4) Connect to the RMAN to create the catalog database.

Step 5) Now Register the databases which will use this database as a recovery catalog.

Step 6) Configure the RMAN persistent parameters.

Step 7) Take the backup plus archivelog from below command.

Command: - backup database plus archivelog.

Step 8) You can also check how many databases are registered under the catalog database by using below command.

Note: - First you need to login to the catalog user.

View: - rc_database

Command: - select name,DBID,DB_KEY from rc_database;

Example: -

Thank you!!

How to Configure Flash Recovery Area in Oracle - Karan Rajpoot

2019-02-06T23:25:00.002+05:30

Configure Flash Recovery Area

To Configure Flash Recovery Area first there is a need to set below parameters.

Parameter 1) db_recovery_file_dest_size

Parameter 2) db_recovery_file_dest

Now let's start

We can check the current location of Flash Recovery Area through the below query.

Query: - select NAME,SPACE_LIMIT,SPACE_USED,NUMBER_OF_FILES from V$RECOVERY_FILE_DEST;

Step 1) Now set the db_recovery_file_dest_size parameter

Step 2) Now set the db_recovery_file_dest parameter

Step 3) Now we will bounce the database to reflect the changes in the spfile and then will check the status of the parameters through below command

Command: - show parameter recover;

We can see from the above output both the parameter's has been set as per the requirement.

Step 4) Check the database mode, it should be in archive log mode. If it is not set to archive log then need to change the mode manually.

Step 5) You can also check the usage of FRA through below command.

Command:- select * from v$flash_recovery_area_usage;

Thank you!!

ORA-12899: value too large for column | Karan Rajpoot

2019-02-04T00:50:00.000+05:30

ORA-12899: value too large for column

Error: - ORA-12899: value too large for column.

Cause: - This error occurs when you have given the data type value which is greater than its maximum value.

Solution: - Try to give the data type value under its maximum assign value or try to increase the size of the maximum value.

let's understand the things with the help of the below example: -

Example: -

Step 1)

SQL> create table employee (emp_id number, emp_name varchar(10), emp_desig varchar(10));

Table created.

Step 2)

SQL> insert into employee values (1001, 'Paul', 'Project_Engineer');

insert into employee values (1001, 'Paul', 'Project_Engineer')

ERROR at line 1:

ORA-12899: value too large for column "SYS"."EMPLOYEE"."EMP_DESIG" (actual: 16,

maximum: 10)

Note: - At the time of table creation the data type value of the emp_desig has given as 10 but the value of emp_design which has been inserted it was greater than its data type value, this is the cause that error has occurred.

Hope that will resolve your issue.

ORA-00984: column not allowed here - Karan Rajpoot

2019-02-04T00:25:00.000+05:30

ORA-00984: column not allowed here

Error: - ORA-00984: column not allowed here.

Cause: - When you try to insert the value in a table and forget to enclose the string value in a single quote, then you will get this error.

Solution: - Put the string value under single quotes and re-execute the command again.

Example: -

SQL> insert into employee values (1001, Paul, Engineer);

insert into employee values (1001, Paul, Engineer)

ERROR at line 1:

ORA-00984: column not allowed here

Note: - In the above output string value has not enclosed with single quotes, To resolve this, Put the string value under single quotes and re-run the command again like below.

SQL> insert into employee values (1001, 'Paul', 'Engineer');

1 row created.

Hope that will resolve your issue.

Types of SQL Statements - Karan Rajpoot

2019-02-03T16:11:00.000+05:30

Types of SQL Statements

The SQL statement can be categorized into the following four ways: -

1) DDL ( Data Definition Language)

2) DML ( Data Manipulation language)

3) DCL ( Data Control Language)

4) TCL ( Transaction Control Language)

Now we will understand each of the above statements with examples: -

1) DDL ( Data Definition Language): -

The DDL statement provides commands for defining relation schemas, that is for creating tables, indexes, sequences and so on and also commands for dropping, altering renaming objects.

a) CREATE: - It is used to create objects in the database.

Example: -

Object Creation

b) ALTER: - It is used to alter the structure of the database.

Example: -

Alter the structure of the database

c) RENAME: - To rename an object.

Example: -

Rename of a tablespace

d) DROP: - This command is used to delete the objects from the database.

Example: -

Drop object from the database

2) DML (Data Manipulation Language): -

The DML statements are used to alter or modification of the database. The databases are managed with the help of below statements.

a) SELECT: - It is used to retrieve the data from the database.

Example: -

Retrieve the data from the database

b) INSERT: - This command is used to insert the data into a table.

Example: -

Insert data into a table

c) UPDATE: - This command is used to update the existing records within the table.

Example: -

Updated the existing record

3) DCL (Data Control language): -

The DCL statements are used to Grant the permission from the user or to revoke the already given permission from the user.

a) GRANT: - This command is used to give the permission of the user.

Example: -

Grant Permission

b) Revoke: - This command is used to revoke the already given permission from the user.

Example: -

Revoke Permission

4) TCL (Transaction Control Language): -

The TCL statement is used to commit the transaction after work done and restore the database to its original stage since the last commit.

a) COMMIT: -

Commit the transaction

b) ROLLBACK: -

Rollback the transaction

Thank You!!

ORA-01119: error in creating database file | Karan Rajpoot

2019-01-30T23:12:00.000+05:30

ORA-01119: error in creating database file

Error: - ORA-01119: error in creating a database file

Cause: - Datafile size has not mentioned in the datafile creation command.

Solution: - Mention the datafile size at the end of the command and re-execute it.

Example: -

SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/prod/users02.dbf';

alter tablespace users add datafile '/u01/app/oracle/oradata/prod/users02.dbf'

ERROR at line 1:

ORA-01119: error in creating database file

'/u01/app/oracle/oradata/prod/users02.dbf'

ORA-17610: file '/u01/app/oracle/oradata/prod/users02.dbf' does not exist and

no size specified

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Note: - In the above command datafile size has not mentioned at the end of the command, Put the datafile size and re-execute command again like below

SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/prod/users02.dbf'' size 50m;

Hope that will resolve your issue.

ORA-02140: invalid tablespace name | Karan Rajpoot

2019-01-30T22:45:00.000+05:30

ORA-02140: invalid tablespace name

Error: - ORA-02140: invalid tablespace name

Cause: - Tablespace name has not provided correctly while running the alter command

Solution: - Use the correct tablespace name and again retry to execute alter command

Command to check the tablespaces name: - select name from v$tablespace;

Example: -

SQL> alter tablespace user add datafile '/u01/app/oracle/oradata/prod/user02.dbf' ;

alter tablespace user add datafile '/u01/app/oracle/oradata/prod/user02.dbf'

ERROR at line 1:

ORA-02140: invalid tablespace name

Note: - In the above command, tablespace name provided incorrectly, Correct tablespace name was USERS. Now again rerun the command using correct tablespace name.

Hope that will resolve your issue.

How to Add, Resize, Drop and Rename of a datafile in Tablespace

2019-01-30T01:13:00.000+05:30

How to Add, Resize, Drop and Rename a datafile in Tablespace

In order to create a data file first, need to create a tablespace, To know how to create a tablespace Click here

To demonstrate, I will use USERS tablespace to Add, Resize, Drop and Rename a data file.

First, we can check how many data files are present under USERS tablespace through below command.

Command: - select name from v$datafile;

We can see from above output only a single data file is present under USERS tablespace.

Addition of Datafile: -

Command to add a datafile: - alter tablespace users add datafile '/u01/app/oracle/oradata/prod/users02.dbf' size 50m;

The new data file has been added to my USERS tablespace.

Resize of a Datafile: -

Command to resize the datafile: - alter database datafile '/u01/app/oracle/oradata/prod/users02.dbf' resize 20m;

Renaming of a Datafile: -

We have a few steps to rename a data file: -

1) Take the tablespace offline.

2) From Host need to move the data of a file from a new file which we want to rename.

3) From SQL prompt need to alter the changes.

4) Take the tablespace online.

Deletion of datafile: -

Command to drop a datafile: - alter tablespace USERS drop datafile '/u01/app/oracle/oradata/prod/user03.dbf

How to Create/Drop/Rename Tablespace in Oracle | Karan Rajpoot

2019-01-27T20:49:00.002+05:30

How to Add/Drop/Rename Tablespace in Oracle

Tablespace: -

Tablespaces are the first level of logical origination in your database. Data for Oracle tables, indexes, etc is stored in data files, but never when an object is defined, the object is associated with a file directly. All the time the Oracle objects are located in the tablespaces. The tablespaces are logical concepts and each tablespace is in relation with one or more physical file. So, when an object is created in a tablespace, the data will be stored automatically in the files associated with that tablespace.

In our database, we have several tablespaces which store our data: -

1) System Tablespace: - This tablespace is created automatically when the database is created. The SYSTEM tablespace is always online when the database is open.

2) Sysaux Tablespace: - Sysaux tablespace is also a mandatory tablespace and it is also created at the time of database creation. In this tablespace, some Oracle tools are stored and if this tablespace goes down then it does not affect the core functionality but some features will affect which uses this tablespace.

3) Undo Tablespace: -The undo tablespace will always hold the before-image of table data
for users.

There are the various purpose of undo tablespace: -

Providing read consistency for SQL queries
Rolling back unwanted active transactions
Recovering terminated transactions
Analyzing older data by using Flashback Query
Recovering from logical corruptions using the Flashback features

4) Temp Tablespace: - The temp tablespace is used to manage space for sorting purpose.

5) User Tablespace: - The user tablespace stores the actual data of the users, Which is owned by different schemas.

Examples: -

Status of Tablespace: -

1) Check how many tablespaces are present with the help of below command.

Command: - select ts#, name from v$tablespace;

Creation of tablespace

2) Now I will Create one more tablespace with the help of below command.

Command: - Create tablespace karan datafile '/u01/app/oracle/oradata/karan01.dbf' size 50m;

Now I can check from the above output, I have a new tablespace called Karan.

Renaming of Tablespace

3) Now I will rename my Karan tablespace to Karan01.

Command: - alter tablespace karan rename to karan01;

Now I can see from above output tablespace name has been changed from Karan to Karan01.

Deletion of Tablespace

4) Now I will drop my Karan01 tablespace.

Command: - drop tablespace karan01;

Now I can check from above output my Karan01 tablespace has been dropped successfully.

Thank you!!

Oracle "Redo log Groups addition/deletion" | Karan Rajpoot

2019-01-26T21:03:00.001+05:30

Oracle "Redo log Groups addition/deletion"

Step 1) To add redo log group first we will check the log groups, members details, location, and size.

Example: -

From the above output, we can see we have 3 groups and inside every group, we have 1 member each of size 50 MB and the location is /u01/app/oracle/oradata/karan/

Step 2) Now we will add one group and check the status of that group.

Example: -

Now again we can check the details of log groups and member.

Now the 4th group and a member of 50 MB has been added successfully.

Step 3) Now I will add one more member to the 4th log group.

Example: -

We can see from the above output that in group 4, now 2 members are there.

Steps to drop the latest added group and member

Note: - We cannot drop CURRENT members as shown in the above output, So I will add a new member in group 3 and then I will drop that member.

Step 1) Addition of new member from group 3.

Example: -

We can see I have added a new member in group 3. Now I can drop that member because its status is INACTIVE

Step 2) Deletion of a new member from group 3.

Example: -

Note: - We can see our latest added member has been dropped from group 3.

Step 3) Now I will drop group 3 as well.

Example: -

Note: - We can see that I have dropped group 3 as well.

Thank you!!

How to do "Multiplexing of Control Files" through spfile ( server parameter file) in Oracle 11g

2019-01-25T21:42:00.000+05:30

Step 1) To perform "Multiplexing of Control Files" through spfile ( server parameter file)" first we need to create spfile from pfile.

Example to create spfile from pfile: -

Step 2) Copy the control files in physical location from 'cp' command.

Note: - At present, I have 2 control files, I will add one more Control files in the same location.

Example: -

Now you can see from above output control04.ctl file has been physically added successfully under /u01/app/oracle/oradata/prod/control04.ctl location.

Step 3) Now you need to set the same through alter command.

a) first shut down the database

b) Startup (Start the database through spfile)

Step 4) Now through SQL prompt alter the changes.

Example: -

Step 5) Now shutdown and start the database again and check the status of added Control file.

Example: -

a) Shut Immediate

b) Startup

c) Status of the added Control file

As we can check from above output 4th control files has been added and the database is also up and running.

Thank you!!

How to do "Multiplexing of Control Files" through pfile (parameter file) in Oracle 11g

2019-01-25T00:34:00.000+05:30

Step 1) First step is to check how many control files are in IN USE.

Example: -
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/control01.ctl
/u01/app/oracle/fast_recovery_area/prod/control02.ctl

Note: - As from the above output we can see 2 control files are in IN USE. Now I will add one more control file in /u01/app/oracle/oradata/prod location.

Step 2) Second step is to edit pfile which is under ORACLE_HOME/dbs location.

Example: -
prod.__pga_aggregate_target=339738624
prod.__sga_target=503316480
prod.__shared_io_pool_size=0
prod.__shared_pool_size=142606336
prod.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/prod/control01.ctl','/u01/app/oracle/fast_recovery_area/prod/control02.ctl','/u01/app/oracle/oradata/prod/control03.ctl'

Note: - I have added third control file in pfile.

Step 3) Now we need to copy the content through 'cp' command to make the control files in sync.

Example: -
[oracle@prod dbs]$ cp /u01/app/oracle/oradata/prod/control01.ctl /u01/app/oracle/oradata/prod/control03.ctl
[oracle@prod dbs]$ cp /u01/app/oracle/fast_recovery_area/prod/control02.ctl /u01/app/oracle/oradata/prod/control03.ctl

Step 4) Now shut down the database through shut immediate command then start it again.

Example: -
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2257880 bytes

Variable Size 545262632 bytes

Database Buffers 289406976 bytes

Redo Buffers 2355200 bytes

Database mounted.

Database opened.

Note: - In case you will get an error during startup, then you need to shut down the database and repeat the copy step again to make control files in sync.

Step 5) Now check the control file status from below command.

Example: -

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/prod/control01.ctl

/u01/app/oracle/fast_recovery_area/prod/control02.ctl

/u01/app/oracle/oradata/prod/control03.ctl

Note: - Now we can see from the above output that our 3rd Control file has been added successfully.

How to resolve "TNS-12541 : TNS : no listener" error

2019-01-24T21:40:00.000+05:30

To resolve "TNS-12541: TNS: no listener" error we need to check whether our listener is up and running.

Step 1) To check whether our listener is up or running, we need to run below command

Command : - ps -ef | grep tns

Example: -

Note: - In this case, my listener is not started, So I need to start my listener

Step 2) To start the listener, we need to run below command

Command : - lsnrctl start

Example: -

Step 3) Now again check the status of listener whether it is up and running, It should be up and running

Command: - ps -ef | grep tns

Example: -

Note: - Now my listener is up and running properly

Startup stages of database "Nomount" , "Mount" , "Open"

2019-01-23T20:34:00.000+05:30

There are three stages of startup of Oracle database which are as follows below: -

Stage 1) No mount stage: - In this stage, Oracle reads pfile(parameter file) or spfile(server parameter file) to make the instance up and running.

Instance: - SGA(System Global Area)+Background Process

Note:- In this stage, the only instance will up and running not database.

Location of pfile and spfile: - ORACLE_HOME/dbs

Example: -

Stage 2) Mount stage: - In mount stage, Oracle reads the "Control File" to make the database in mount stage.

Example: -

Stage 3) Open stage: -The final stage is the open stage. In this stage user can connects to the database to perform their activity.

Example: -

How to change database Mode from "Non Archive" to "Archive" in RAC

2019-01-21T23:52:00.001+05:30

High-Level Steps to change database Mode from "Non-Archive" to "Archive" in RAC

1) Cluster_database=false

2) Stop the cluster database

3) Startup the database from one instance in mount stage

4) Change the database mode to archive mode

5) Cluster_database=true

6) Shut down the database

7) Start the cluster database

Step by Step Example : -

Step 1) Set Cluster_database=false

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

Step 2) Stop the Cluster database through SRVCTL command

[oracle@rac1 dbs]$ srvctl stop database -d karan

[oracle@rac1 dbs]$

Step 3) Start the database from one instance in mount stage

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1235959808 bytes

Fixed Size 2252784 bytes

Variable Size 788529168 bytes

Database Buffers 436207616 bytes

Redo Buffers 8970240 bytes

Database mounted.

Step 4) Change the DB mode to archive

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 19

Next log sequence to archive 20

Current log sequence 20

Step 5) Set Cluster_database=true

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

Step 6) Shut Down the database

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Step 7) Start the cluster database again through SRVCTL command

[oracle@rac1 dbs]$ srvctl start database -d karan

[oracle@rac1 dbs]$