Wednesday, December 27, 2017

Resolving TNS – Message 3511 not found

C:\Users\Administrator.ANURAG-PC>tnsping ducat

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 27-DEC-2017 21:46:23

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

Message 3511 not found; No message file for product=NETWORK, facility=TNSTNS-03505: Message 3505 not found; No message file for product=NETWORK, facility=TNS

C:\Users\Administrator.ANURAG-PC>set ORACLE_BASE=D:\app\ANURAG

C:\Users\Administrator.ANURAG-PC>tnsping ducat

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 27-DEC-2017 21:53:16

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

Message 3511 not found; No message file for product=NETWORK, facility=TNSTNS-03505: Message 3505 not found; No message file for product=NETWORK, facility=TNS

C:\Users\Administrator.ANURAG-PC>set ORACLE_HOME=D:\app\ANURAG\product\11.2.0\client_1

C:\Users\Administrator.ANURAG-PC>
C:\Users\Administrator.ANURAG-PC>


Solution
=============

C:\Users\Administrator.ANURAG-PC>set ORACLE_BASE=D:\app\ANURAG

C:\Users\Administrator.ANURAG-PC>tnsping ducat

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 27-DEC-2017 21:53:16

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

Message 3511 not found; No message file for product=NETWORK, facility=TNSTNS-03505: Message 3505 not found; No message file for product=NETWORK, facility=TNS

C:\Users\Administrator.ANURAG-PC>set ORACLE_HOME=D:\app\ANURAG\product\11.2.0\client_1

C:\Users\Administrator.ANURAG-PC>
C:\Users\Administrator.ANURAG-PC>
C:\Users\Administrator.ANURAG-PC>tnsping ducat

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 27-DEC-2017 21:53:44

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

Used parameter files:
D:\app\ANURAG\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.48)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ducat)))
OK (0 msec)

C:\Users\Administrator.ANURAG-PC>

Tuesday, December 19, 2017

Connecting to an Oracle Instance

Connecting to an Oracle Instance


Before users can submit SQL statements to an Oracle database, they must connect to an instance.





• The user starts a tool such as SQL*Plus,sql developer,toad or runs an application developed using a tool such as Oracle Forms. This application or tool is executed as a user process.

• In the most basic configuration, when a user logs on to the Oracle server, a process is created on the computer running the Oracle server. This process is called a server process. The server process communicates with the Oracle instance on behalf of the user process that runs on the client. The server process executes SQL statements on behalf of the user.

User Process is started at the time a database user request  a connection to the oracle server .
Server Process connect to the oracle instance and started when a user establish a session .

user process & server process are used to manage the execution of sql/plsql statements .

one-to-one correspondence between the User and Server Processes. This is called a Dedicated Server connection. An alternative configuration is to use a Shared Server where more than one User Process shares a Server Process.

Server Process is the go-between for a Client Process and the Oracle Instance.

Dedicated Server environment – there is a single Server Process to serve each Client Process.

Shared Server environment – a Server Process can serve several User Processes, although with some performance reduction


Connection
A connection is a communication pathway between a user process and an Oracle server. A database user can connect to an Oracle server using tool, sqlplus etc.

Sessions
A session is a specific connection of a user to an Oracle server. The session starts when the user is validated by the Oracle server, and it ends when the user logs out or when there is an abnormal termination. For a given database user, many concurrent sessions are possible if the user logs on from many tools, applications, or terminals at the same time.




Saturday, November 11, 2017

Restore and Recover OCR from export backup (Oracle Cluster Registry)

Restore and Recover OCR from export backup (Oracle Cluster Registry) in ORACLE RAC


How to Recover OCR from EXPORT Backup?

1. verify all nodes ocr are good using below command

ocrcheck
cluvfy comp ocr -n all -verbose

2 . Take export backup of OCR from any node using root user

ocrconfig -export /tmp/ocr_cluster_backup_11112017.ocr



3. Stop the Oracle clusterware on all the nodes.

crsctl stop crs -f

4.Start the Oracle Clusterware stack on one node in exclusive mode by running the following command as root:
# crsctl start crs -excl -nocrs
The -nocrs option ensures that the crsd process and OCR do not start with the rest of the Oracle Clusterware stack.
Ignore any errors that display.
Check whether crsd is running. If it is, then stop it by running the following command as root:
# crsctl stop resource ora.crsd -init

Caution:
Do not use the -init flag with any other command.

5. If you want to restore OCR to an Oracle ASM disk group, then you must first create a disk group using SQL*Plus that has the same name as the disk group you want to restore and mount it on the local node.
start clusterware  on local node 

crsctl start crs -excl -nocrs



6. Restore the OCR backup using ocr export file loction

ocrconfig -import /tmp/ocr_cluster_backup_11112017.ocr (Import metadata of OCR using command)

7. stop clusterware 

 crsctl stop crs -f

8. Restart the Clusterware on all nodes

 Begin to start Oracle Clusterware by running the following command as root on all of the nodes:

# crsctl start crs



9. Verify OCR integrity of all of the cluster nodes that are configured as part of your cluster by running the following CVU command:

cluvfy comp ocr -n all -verbose


=================================================================



[root@rac2 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3084
         Available space (kbytes) :     259036
         ID                       :  918475055
         Device/File Name         :     +DATA2
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@rac2 ~]#

[root@rac1 rac1]#
[root@rac1 rac1]# ocrconfig -showbackup

rac1     2017/10/23 20:13:00     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup00.ocr

rac2     2017/08/08 01:09:36     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup01.ocr

rac1     2017/07/31 22:15:16     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup02.ocr

rac1     2017/10/23 20:13:00     /u01/app/11.2.0/grid_1/cdata/rac-cluster/day.ocr

rac1     2017/10/23 20:13:00     /u01/app/11.2.0/grid_1/cdata/rac-cluster/week.ocr

rac2     2017/11/11 08:54:18     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup_20171111_085418.ocr

rac2     2017/11/11 08:18:06     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup_20171111_081806.ocr

rac2     2016/09/26 03:04:55     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup_20160926_030455.ocr

rac1     2016/09/26 02:52:42     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup_20160926_025242.ocr
[root@rac1 rac1]#
[root@rac1 rac1]#
[root@rac1 rac1]#

[root@rac1 rac1]#
[root@rac1 rac1]# ocrconfig -showbackup auto

rac1     2017/10/23 20:13:00     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup00.ocr

rac2     2017/08/08 01:09:36     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup01.ocr

rac1     2017/07/31 22:15:16     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup02.ocr

rac1     2017/10/23 20:13:00     /u01/app/11.2.0/grid_1/cdata/rac-cluster/day.ocr

rac1     2017/10/23 20:13:00     /u01/app/11.2.0/grid_1/cdata/rac-cluster/week.ocr
[root@rac1 rac1]#
[root@rac1 rac1]#
[root@rac1 rac1]#
[root@rac1 rac1]# ocrconfig -showbackup  manual

rac2     2017/11/11 08:54:18     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup_20171111_085418.ocr

rac2     2017/11/11 08:18:06     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup_20171111_081806.ocr

rac2     2016/09/26 03:04:55     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup_20160926_030455.ocr

rac1     2016/09/26 02:52:42     /u01/app/11.2.0/grid_1/cdata/rac-cluster/backup_20160926_025242.ocr
[root@rac1 rac1]#
[root@rac1 rac1]#

[root@rac1 rac1]# ps -ef | grep d.bin | grep -v grep
root      2677     1  0 07:39 ?        00:00:31 /u01/app/11.2.0/grid_1/bin/ohasd.bin reboot
grid      3011     1  0 07:39 ?        00:00:00 /u01/app/11.2.0/grid_1/bin/mdnsd.bin
grid      3022     1  0 07:40 ?        00:00:03 /u01/app/11.2.0/grid_1/bin/gpnpd.bin
grid      3033     1  0 07:40 ?        00:00:25 /u01/app/11.2.0/grid_1/bin/gipcd.bin
root      3047     1  1 07:40 ?        00:01:18 /u01/app/11.2.0/grid_1/bin/osysmond.bin
grid      3093     1  0 07:40 ?        00:00:29 /u01/app/11.2.0/grid_1/bin/ocssd.bin
root      3258     1  0 07:40 ?        00:00:16 /u01/app/11.2.0/grid_1/bin/octssd.bin reboot
grid      3348     1  0 07:40 ?        00:00:15 /u01/app/11.2.0/grid_1/bin/evmd.bin
root      3616     1  0 07:41 ?        00:00:48 /u01/app/11.2.0/grid_1/bin/crsd.bin reboot
[root@rac1 rac1]#
[root@rac1 rac1]#


[root@rac1 rac1]#
[root@rac1 rac1]#
[root@rac1 rac1]#
[root@rac1 rac1]# cat /etc/oracle/ocr.loc
#Device/file +DATA1 getting replaced by device +DATA2
ocrconfig_loc=+DATA2
local_only=false[root@rac1 rac1]#
[root@rac1 rac1]#



Activity log for reference
=========================

[root@rac1 tmp]#
[root@rac1 tmp]# crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@rac1 tmp]#
[root@rac1 tmp]#
[root@rac1 tmp]# crsctl check cluster  -all
**************************************************************
rac1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[root@rac1 tmp]#
[root@rac1 tmp]#
[root@rac1 tmp]#
[root@rac1 tmp]#


[root@rac1 tmp]#
[root@rac1 tmp]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3104
         Available space (kbytes) :     259016
         ID                       :  918475055
         Device/File Name         :     +DATA2
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@rac1 tmp]#


stop crs on node 2
=======================

[root@rac2 ~]#
[root@rac2 ~]#
[root@rac2 ~]# crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac2'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'rac2'
CRS-2673: Attempting to stop 'ora.orcl.db' on 'rac2'
CRS-2673: Attempting to stop 'ora.oc4j' on 'rac2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac2'
CRS-2673: Attempting to stop 'ora.cvu' on 'rac2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'rac2'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'rac2'
CRS-2677: Stop of 'ora.cvu' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cvu' on 'rac1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.rac2.vip' on 'rac2'
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'rac2'
CRS-2676: Start of 'ora.cvu' on 'rac1' succeeded
CRS-2677: Stop of 'ora.scan2.vip' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.scan2.vip' on 'rac1'
CRS-2677: Stop of 'ora.orcl.db' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.DATA1.dg' on 'rac2'
CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'rac2'
CRS-2677: Stop of 'ora.rac2.vip' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.rac2.vip' on 'rac1'
CRS-2677: Stop of 'ora.scan3.vip' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.scan3.vip' on 'rac1'
CRS-2676: Start of 'ora.scan2.vip' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'rac1'
CRS-2676: Start of 'ora.rac2.vip' on 'rac1' succeeded
CRS-2676: Start of 'ora.scan3.vip' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN3.lsnr' on 'rac1'
CRS-2676: Start of 'ora.LISTENER_SCAN2.lsnr' on 'rac1' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN3.lsnr' on 'rac1' succeeded
CRS-2677: Stop of 'ora.DATA2.dg' on 'rac2' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.oc4j' on 'rac1'
CRS-2676: Start of 'ora.oc4j' on 'rac1' succeeded
CRS-2677: Stop of 'ora.DATA1.dg' on 'rac2' succeeded
CRS-2679: Attempting to clean 'ora.DATA1.dg' on 'rac2'
CRS-2681: Clean of 'ora.DATA1.dg' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac2'
CRS-2677: Stop of 'ora.asm' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'rac2'
CRS-2677: Stop of 'ora.ons' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac2'
CRS-2677: Stop of 'ora.net1.network' on 'rac2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac2' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'rac2'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac2'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac2'
CRS-2673: Attempting to stop 'ora.asm' on 'rac2'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac2'
CRS-2677: Stop of 'ora.crf' on 'rac2' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac2'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac2'
CRS-2677: Stop of 'ora.cssd' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac2'
CRS-2677: Stop of 'ora.gipcd' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac2'
CRS-2677: Stop of 'ora.gpnpd' on 'rac2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@rac2 ~]#


[root@rac2 ~]#
[root@rac2 ~]# ps -ef | grep -i d.bin | grep -v grep
root      2688     1  0 07:38 ?        00:00:35 /u01/app/11.2.0/grid_1/bin/ohasd.bin reboot
grid      2997     1  0 07:39 ?        00:00:00 /u01/app/11.2.0/grid_1/bin/mdnsd.bin
grid      3027     1  0 07:39 ?        00:00:03 /u01/app/11.2.0/grid_1/bin/gpnpd.bin
grid      3042     1  0 07:39 ?        00:00:27 /u01/app/11.2.0/grid_1/bin/gipcd.bin
root      3056     1  1 07:39 ?        00:01:23 /u01/app/11.2.0/grid_1/bin/osysmond.bin
grid      3105     1  0 07:39 ?        00:00:33 /u01/app/11.2.0/grid_1/bin/ocssd.bin
root      3322     1  0 07:40 ?        00:00:17 /u01/app/11.2.0/grid_1/bin/octssd.bin reboot
grid      3348     1  0 07:40 ?        00:00:15 /u01/app/11.2.0/grid_1/bin/evmd.bin
root      3626     1  0 07:41 ?        00:00:52 /u01/app/11.2.0/grid_1/bin/crsd.bin reboot
[root@rac2 ~]#
[root@rac2 ~]#
[root@rac2 ~]#
[root@rac2 ~]#
[root@rac2 ~]# ps -ef | grep -i d.bin | grep -v grep
[root@rac2 ~]#
[root@rac2 ~]#
[root@rac2 ~]#


[root@rac2 ~]#
[root@rac2 ~]# ocrcheck
PROT-602: Failed to retrieve data from the cluster registry
PROC-26: Error while accessing the physical storage
ORA-29701: unable to connect to Cluster Synchronization Service

[root@rac2 ~]#





Now crs running on me node
================================
[root@rac1 tmp]#
[root@rac1 tmp]# crsctl check cluster  -all
**************************************************************
rac1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[root@rac1 tmp]#
[root@rac1 tmp]#


stop crs on first node

[root@rac1 tmp]#
[root@rac1 tmp]# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac1'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac1'
CRS-2673: Attempting to stop 'ora.cvu' on 'rac1'
CRS-2673: Attempting to stop 'ora.orcl.db' on 'rac1'
CRS-2673: Attempting to stop 'ora.rac2.vip' on 'rac1'
CRS-2673: Attempting to stop 'ora.oc4j' on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac1'
CRS-2677: Stop of 'ora.cvu' on 'rac1' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac1' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'rac1'
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'rac1'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'rac1'
CRS-2677: Stop of 'ora.rac2.vip' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.rac1.vip' on 'rac1'
CRS-2677: Stop of 'ora.orcl.db' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.DATA1.dg' on 'rac1'
CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'rac1'
CRS-2677: Stop of 'ora.scan1.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.scan2.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.scan3.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.rac1.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.DATA2.dg' on 'rac1' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'rac1' succeeded
CRS-2677: Stop of 'ora.DATA1.dg' on 'rac1' succeeded
CRS-2679: Attempting to clean 'ora.DATA1.dg' on 'rac1'
CRS-2681: Clean of 'ora.DATA1.dg' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac1'
CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'rac1'
CRS-2677: Stop of 'ora.ons' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac1'
CRS-2677: Stop of 'ora.net1.network' on 'rac1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac1' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac1'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac1'
CRS-2673: Attempting to stop 'ora.asm' on 'rac1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac1'
CRS-2677: Stop of 'ora.evmd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac1'
CRS-2677: Stop of 'ora.cssd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'rac1'
CRS-2677: Stop of 'ora.crf' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac1'
CRS-2677: Stop of 'ora.gipcd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac1'
CRS-2677: Stop of 'ora.gpnpd' on 'rac1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@rac1 tmp]#



[root@rac1 tmp]#
[root@rac1 tmp]# crsctl start crs -excl -nocrs
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.mdnsd' on 'rac1'
CRS-2676: Start of 'ora.mdnsd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rac1'
CRS-2676: Start of 'ora.gpnpd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac1'
CRS-2672: Attempting to start 'ora.gipcd' on 'rac1'
CRS-2676: Start of 'ora.cssdmonitor' on 'rac1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rac1'
CRS-2672: Attempting to start 'ora.diskmon' on 'rac1'
CRS-2676: Start of 'ora.diskmon' on 'rac1' succeeded
CRS-2676: Start of 'ora.cssd' on 'rac1' succeeded
CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'rac1'
CRS-2672: Attempting to start 'ora.ctssd' on 'rac1'
CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac1'
CRS-2676: Start of 'ora.ctssd' on 'rac1' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'rac1'
CRS-2676: Start of 'ora.asm' on 'rac1' succeeded
[root@rac1 tmp]#


[root@rac1 tmp]#
[root@rac1 tmp]# ps -ef | grep -i d.bin | grep -v grep
root     19821     1  1 10:46 ?        00:00:02 /u01/app/11.2.0/grid_1/bin/ohasd.bin exclusive
grid     19970     1  0 10:46 ?        00:00:00 /u01/app/11.2.0/grid_1/bin/mdnsd.bin
grid     19981     1  0 10:46 ?        00:00:00 /u01/app/11.2.0/grid_1/bin/gpnpd.bin
grid     20001     1  0 10:46 ?        00:00:00 /u01/app/11.2.0/grid_1/bin/gipcd.bin
grid     20057     1  0 10:46 ?        00:00:00 /u01/app/11.2.0/grid_1/bin/ocssd.bin -X
root     20183     1  0 10:46 ?        00:00:00 /u01/app/11.2.0/grid_1/bin/octssd.bin
[root@rac1 tmp]#
[root@rac1 tmp]#


[root@rac1 tmp]#
[root@rac1 tmp]# ocrconfig -import /tmp/latest_ocr11112017.dmp
[root@rac1 tmp]#
[root@rac1 tmp]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3084
         Available space (kbytes) :     259036
         ID                       : 2125290321
         Device/File Name         :     +DATA2
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@rac1 tmp]# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac1'
CRS-2673: Attempting to stop 'ora.asm' on 'rac1'
CRS-2677: Stop of 'ora.ctssd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac1'
CRS-2677: Stop of 'ora.cssd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac1'
CRS-2677: Stop of 'ora.gipcd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac1'
CRS-2677: Stop of 'ora.gpnpd' on 'rac1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@rac1 tmp]#

[root@rac1 tmp]#
[root@rac1 tmp]# ps -ef | grep -i d.bin | grep -v grep
[root@rac1 tmp]#
[root@rac1 tmp]#
[root@rac1 tmp]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
[root@rac1 tmp]#
[root@rac1 tmp]#

[root@rac1 tmp]# crsctl check cluster -all
**************************************************************
rac1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[root@rac1 tmp]#
[root@rac1 tmp]#


[root@rac1 tmp]#
[root@rac1 tmp]# crsctl check cluster -all
**************************************************************
rac1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[root@rac1 tmp]#
[root@rac1 tmp]#

after restore ocr


[grid@rac1 rac1]$
[grid@rac1 rac1]$
[grid@rac1 rac1]$ cluvfy comp ocr -n all -verbose

Verifying OCR integrity

Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations


ASM Running check passed. ASM is running on all specified nodes

Checking OCR config file "/etc/oracle/ocr.loc"...

OCR config file "/etc/oracle/ocr.loc" check successful


Disk group for ocr location "+DATA2" available on all the nodes


NOTE:
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.

OCR integrity check passed

Verification of OCR integrity was successful.
[grid@rac1 rac1]$
[root@rac1 tmp]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3084
         Available space (kbytes) :     259036
         ID                       : 2125290321
         Device/File Name         :     +DATA2
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@rac1 tmp]#




you can also check log
2017-11-11 10:46:41.394:
[ctssd(20183)]CRS-2407:The new Cluster Time Synchronization Service reference node is host rac1.
2017-11-11 10:51:10.895:
[client(21540)]CRS-1001:The OCR was formatted using version 3.
2017-11-11 10:51:23.457:
[client(21540)]CRS-1004:The OCR was imported from /tmp/latest_ocr11112017.dmp.
2017-11-11 10:52:24.178:
[mdnsd(19970)]CRS-5602:mDNS service stopping by request.
2017-11-11 10:52:24.191:

Reference:


http://www.shannura.com/archives/2226261108_ocr_01.html
https://somireddy.wordpress.com/2013/06/21/restoring-ocr-disk-voting-disk-on-asm-disk-in-11gr2/
http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_65.shtml


Monday, October 9, 2017

Change Characterset to UTF8 in Oracle Database

Change Characterset to UTF8 in oracle
======================================

SQL> startup mount;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;


SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET UTF8;


SQL> shutdown immediate;
SQL> startup;

Monday, September 11, 2017

Error RMAN-20045: must specify FORMAT for BACKUP INCREMENTAL FROM SCN command


Error  "RMAN-20045"  during taking backup using RMAN
===========================================


RMAN> backup incremental from scn 1501331 database;

Starting backup at 11-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=54 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/hr/test01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/hr/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/hr/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/hr/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/hr/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/hr/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-SEP-17
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/11/2017 09:09:16
RMAN-20045: must specify FORMAT for BACKUP INCREMENTAL FROM SCN command
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 11-SEP-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/11/2017 09:09:17
RMAN-20045: must specify FORMAT for BACKUP INCREMENTAL FROM SCN command


solution
=================

backup incremental from scn 1501331 database FORMAT '/u01/app/inc_backup/inc_bkp_%U';

How to Recover Standby Database from a Missing Archive Log by applying the Incremental backup from Production Database


Data guard GAP Detection and Resolution
=============================================

How to Recover Standby Database from a Missing Archive Log by applying the Incremental backup from Production Database

Resyncing the standby data using RMAN incremental backup taken from the primary database.

Manually Resolving a Gap using incremental backup



On your physical standby database:


SQL> SELECT * FROM V$ARCHIVE_GAP;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1            32             64

SQL>


Get the current scn of the standby database.

SQL>  select current_scn from v$database;

CURRENT_SCN
-----------
    1501331

SQL>

On Primary database
******************************************

Connect to the primary database as the RMAN target and create an incremental backup from the current SCN (for a standby lagging far behind the primary):
----------------------------------------------------------------------------------------------
backup incremental from scn 1501331 database FORMAT '/u01/app/inc_backup/inc_bkp_%U';



[oracle@pri inc_backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 11 09:12:12 2017

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

connected to target database: HR (DBID=3602606687)

RMAN> backup incremental from scn 1501331 database FORMAT '/u01/app/inc_backup/inc_bkp_%U';

Starting backup at 11-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=54 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/hr/test01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/hr/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/hr/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/hr/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/hr/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/hr/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-SEP-17
channel ORA_DISK_1: finished piece 1 at 11-SEP-17
piece handle=/u01/app/inc_backup/inc_bkp_16se66jg_1_1 tag=TAG20170911T091216 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 11-SEP-17
channel ORA_DISK_1: finished piece 1 at 11-SEP-17
piece handle=/u01/app/inc_backup/inc_bkp_17se66o2_1_1 tag=TAG20170911T091216 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-SEP-17

RMAN>


Create a standby controlfile 
------------------------------------------

alter database create standby controlfile as '/u01/app/inc_backup/CONTROL01.CTL';


[oracle@pri inc_backup]$ sqlp

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 11 09:16:44 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from gv$database;

NAME      OPEN_MODE
--------- --------------------
HR        READ WRITE

SQL>
SQL> alter database create standby controlfile as '/u01/app/inc_backup/CONTROL01.CTL';

Database altered.




[oracle@pri inc_backup]$ ls -lrt
total 92504
-rw-r----- 1 oracle oinstall 74719232 Sep 11 09:14 inc_bkp_16se66jg_1_1
-rw-r----- 1 oracle oinstall 10027008 Sep 11 09:14 inc_bkp_17se66o2_1_1
-rw-r----- 1 oracle oinstall  9977856 Sep 11 09:17 CONTROL01.CTL
[oracle@pri inc_backup]$


Transfer all the backup pieces,control file backup created on the primary to the standby.


[oracle@pri inc_backup]$
[oracle@pri inc_backup]$
[oracle@pri inc_backup]$ scp * oracle@stby:/u01/app/inc_backup/
oracle@stby's password:
CONTROL01.CTL                                                                                                                         100% 9744KB   9.5MB/s   00:00
inc_bkp_16se66jg_1_1                                                                                                                  100%   71MB  35.6MB/s   00:02
inc_bkp_17se66o2_1_1                                                                                                                  100% 9792KB   9.6MB/s   00:01
[oracle@pri inc_backup]$
[oracle@pri inc_backup]$
[oracle@pri inc_backup]$




Now on Standby database:
******************************************

verify backup which transfered from pramiry


[oracle@stby inc_backup]$
[oracle@stby inc_backup]$ ls -lrt
total 92504
-rw-r-----. 1 oracle oinstall  9977856 Sep  7 02:56 CONTROL01.CTL
-rw-r-----. 1 oracle oinstall 74719232 Sep  7 02:56 inc_bkp_16se66jg_1_1
-rw-r-----. 1 oracle oinstall 10027008 Sep  7 02:56 inc_bkp_17se66o2_1_1
[oracle@stby inc_backup]$
[oracle@stby inc_backup]$


Recreate the standby controlfile on the standby
--------------------------------------------------

shut database


[oracle@stby inc_backup]$
[oracle@stby inc_backup]$ sqlp

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 7 02:58:38 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut abort
ORACLE instance shut down.
SQL>


open database into nomount mode
----------------------------------------


[oracle@stby inc_backup]$
[oracle@stby inc_backup]$ sqlp

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 7 02:59:02 2017

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             788533224 bytes
Database Buffers          452984832 bytes
Redo Buffers                8892416 bytes
SQL>


connect to the rman prompt and connect to the target database

[oracle@stby inc_backup]$
[oracle@stby inc_backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Sep 7 03:00:20 2017

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

connected to target database: HR (not mounted)

RMAN>


restore the controlfile using the below command.
--------------------------------------------------------

restore controlfile from '/u01/app/inc_backup/CONTROL01.CTL';


RMAN> restore controlfile from '/u01/app/inc_backup/CONTROL01.CTL';

Starting restore at 07-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/hrsty/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/hrsty/control02.ctl
Finished restore at 07-SEP-17

RMAN>

RMAN>


Mount the standby database.
---------------------------------------

SQL>
SQL> alter database mount;

Database altered.

SQL>



SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         3         ONLINE  /u01/app/oracle/oradata/hrsty/redo03.log                                         NO
         2         ONLINE  /u01/app/oracle/oradata/hrsty/redo02.log                                         NO
         1         ONLINE  /u01/app/oracle/oradata/hrsty/redo01.log                                         NO
         4         STANDBY /u01/app/oracle/oradata/hrsty/stby_redo01.log                                    NO
         5         STANDBY /u01/app/oracle/oradata/hrsty/stby_redo02.log                                    NO
         6         STANDBY /u01/app/oracle/oradata/hrsty/stby_redo03.log                                    NO
         7         STANDBY /u01/app/oracle/oradata/hrsty/stby_redo04.log                                    NO

7 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/hrsty/system01.dbf
/u01/app/oracle/oradata/hrsty/sysaux01.dbf
/u01/app/oracle/oradata/hrsty/undotbs01.dbf
/u01/app/oracle/oradata/hrsty/users01.dbf
/u01/app/oracle/oradata/hrsty/example01.dbf
/u01/app/oracle/oradata/hrsty/test01.dbf

6 rows selected.

SQL>


catalog backup pieces if the location on the standby is different from the primary.
-----------------------------------------------------------------------------------------------
 catalog start with '/u01/app/inc_backup/';


 [oracle@stby inc_backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Sep 7 03:09:08 2017

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

connected to target database: HR (DBID=3602606687, not open)

RMAN> catalog start with '/u01/app/inc_backup/';

Starting implicit crosscheck backup at 07-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 07-SEP-17

Starting implicit crosscheck copy at 07-SEP-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 07-SEP-17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_07/o1_mf_1_87_dv0rb5m9_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_81_dtp4o6qo_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_69_dtp3t104_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_71_dtp3t1mg_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_75_dtp3t2p7_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_72_dtp3t1pq_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_79_dtp3x58l_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_68_dtp3t0wt_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_66_dtp3szgr_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_77_dtp3t3h5_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_70_dtp3t11d_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_74_dtp3t2gw_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_80_dtp4h979_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_67_dtp3szf6_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_73_dtp3t1sc_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_76_dtp3t2tt_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_78_dtp3wbcl_.arc

searching for all files that match the pattern /u01/app/inc_backup/

List of Files Unknown to the Database
=====================================
File Name: /u01/app/inc_backup/inc_bkp_17se66o2_1_1
File Name: /u01/app/inc_backup/CONTROL01.CTL
File Name: /u01/app/inc_backup/inc_bkp_16se66jg_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/inc_backup/inc_bkp_17se66o2_1_1
File Name: /u01/app/inc_backup/CONTROL01.CTL
File Name: /u01/app/inc_backup/inc_bkp_16se66jg_1_1

RMAN>



Apply incremental backup to the standby.
------------------------------------------------------
recover database noredo;



RMAN> recover database noredo;

Starting recover at 07-SEP-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/hrsty/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/hrsty/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/hrsty/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/hrsty/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/hrsty/example01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/hrsty/test01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/inc_backup/inc_bkp_16se66jg_1_1
channel ORA_DISK_1: piece handle=/u01/app/inc_backup/inc_bkp_16se66jg_1_1 tag=TAG20170911T091216
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished recover at 07-SEP-17

RMAN>


Start the media recovery process in standby database
--------------------------------------------------------------------

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

alter database recover managed standby database disconnect from session;

SQL> select name,open_mode from gv$database
  2  /

NAME      OPEN_MODE
--------- --------------------
HR        MOUNTED

SQL>
SQL> alter database open ;

Database altered.


SQL> select name,open_mode from gv$database;

NAME      OPEN_MODE
--------- --------------------
HR        READ ONLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> select name,open_mode from gv$database;

NAME      OPEN_MODE
--------- --------------------
HR        READ ONLY WITH APPLY

SQL>



on primary
------------------

[oracle@pri inc_backup]$ sqlp

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 11 09:40:23 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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



on standby
--------------------

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     89
Next log sequence to archive   0
Current log sequence           91
SQL>
SQL>





SQL> select sequence#,archived,applied from v$archived_log order by 1;

 SEQUENCE# ARC APPLIED
---------- --- ---------
        66 YES YES
        67 YES YES
        68 YES YES
        69 YES YES
        70 YES YES
        71 YES YES
        72 YES YES
        73 YES YES
        74 YES YES
        75 YES YES
        76 YES YES
        77 YES YES
        78 YES YES
        79 YES YES
        80 YES YES
        81 YES YES
        87 YES YES
        88 YES YES
        89 YES YES
        90 YES IN-MEMORY

SQL> SELECT * FROM V$ARCHIVE_GAP;

no rows selected

SQL>

*********************************************************************
                  Now both primary and standby database in sync mode 

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


==================================================================

Use the RMAN CATALOG command to register the backup sets in the RMAN repository at the duplicate. With an RMAN client connected to the standby database and the recovery catalog (if you use one at the standby), mount the standby and run the following command:

RMAN> CATALOG START WITH '/backup_location/';

The backups are now available for use in recovery of the standby.


Use the RMAN RECOVER command with the NOREDO option to apply the incremental backup to the standby database. All changed blocks captured in the incremental backup are updated at the standby database, bringing it up to date with the primary database. With an RMAN client connected to the standby database, run the following command:

RMAN> RECOVER DATABASE NOREDO;

You can now resume managed recovery at the standby. Any redo logs required at the standby with changes since those contained in the incremental are automatically requested from the primary and applied.