REPAIR THE INVALID ORACLE PACKAGES AND TYPES IN DBA_REGISTRY ORA-01775

The problem can happen if the database has been upgraded from Oracle 11gR2 to Oracle 12c 

COMP_NAME                                     STATUS                           VERSION
------------------------------------- -------------------------------- ------------------------------
Oracle Database Catalog Views                 INVALID                          12.1.0.2.0
Oracle Database Packages and Types            INVALID                          12.1.0.2.0

cause: 

1. Applied a patch and after the patch application because of some dependent object status change registry can become invalid
2. Installed a new component and the new component installation got failed then registry components could become invalid
3. catalog.sql or catproc.sql was not successfully run after database creation. Any of them would have failed somewhere or any of the dependent objects got invalid afterward

To repair and validate the invalid ‘Packages and Types’ component we can execute the catalog and catproc.sql script

SQL> shutdown immediate;
SQL> startup restrict
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> SELECT comp_name, status, version FROM dba_registry where STATUS not like 'VALID';

COMP_NAME                                     STATUS                           VERSION
------------------------------------- -------------------------------- ------------------------------
Oracle Database Catalog Views                 VALID                          12.1.0.2.0
Oracle Database Packages and Types            VALID                          12.1.0.2.0

Shutdown your database in the normal mode and startup with the normal mode.

Cause

Catprog.sql failed with ORA-01775: looping chain of synonyms

Solution

Drop synonyms
drop public synonym existsnode;
drop public synonym extract;

This solution was not applicable for our case as these synonyms were not present in the database.

ACTION PLAN
============
1. drop the synonym:
SQL> drop public synonym XMLCONCAT;

2. recompile the package
SQL> alter package DBMS_SQLTUNE_INTERNAL compile body;

3. if it works, run utlrp.sql to recompile the other INVALID objects
@?/rdbms/admin/utlrp.sql

For us, the above solution worked and we were able to compile SYS.DBMS_SQLTUNE_INTERNAL successfully and utlrp.sql ran successfully, compiling all Invalid objects. The dependent synonym ‘XMLCONCAT’ didn’t have a base object.
I used the following query to obtain the list of public synonyms conflicting with synonyms being used to compile

select * from (
select * from dba_synonyms a
where not exists ( select 1
from dba_objects b
where a.table_owner = b.owner
and a.table_name = b.object_name))
where synonym_name not like '%/%' AND DB_LINK IS NULL;

Before we start the upgrade process, we need to drop all public synonyms whose base object doesn’t exist

 

DataGuard Vs GoldenGate

Oracle MAA (Maximum Availability Architecture is Oracle’s best practices blueprint based on proven Oracle high availability technologies, expert recommendations and customer experiences. The goal of MAA is to achieve optimal high availability for Oracle customers at the lowest cost and complexity.

RAC, DataGuard, Flashback, ASM, RMAN, GoldenGate are tools that are part of MAA architecture.

The configuration of Oracle GoldenGate / Data Guard has the purpose of synchronizing data between two or more systems .
The basic features of the two products may look similar but takes GoldenGate uses replication while dataguard not.

Oracle Data Guard and GoldenGate involving at least two systems where transactional data from one database is required to be moved to another database. The purpose of the replication of data can be disaster recovery, migration of data or preparation of a secondary system.

Data Guard is best for disaster recovery and data protection problems, GoldenGate is a more flexible heterogeneous replication mechanism and is also able to transform the data while it is being replicated.

 Data Guard is an Oracle specific technology while GoldenGate support heterogeneous database systems including all the major RDBMS as DB2,Sybase, MySql .

 Data Guard supports active-passive replication. One of the database is the primary database and the other one is in an inactive Data Guard mode.

 GoldenGate supports an active-active replication mode and allows both systems to work simultaneously while maintaining the data integrity.

 GoldenGate allows transformation of the data, with conflict management while it is being replicated between both database systems.

 GoldenGate allows replication across platform. Data can be extracted from a Unix platform and replicated to an Oracle database running on platform Windows.

 GoldenGate has many case of utilization. The use of flat files for data transportation and the support of heterogeneous systems makes the technology so very interesting

Oracle Database 12c provides several tools to improve the availability and reliability of your critical databases. the difference between Oracle GoldenGate and Oracle Data Guard. Here is my shot about explaining the core differences.

 Data Guard As blocks are changed in the database records are added to the redo log. Depending on the mode that you are running these log records will either be immediately copied to the standby or deferred. These are all changes to the database. Since they are log records and block data they are very quickly applied to the standby system. In the event of a failover to the standby recovery can occur very fast. Oracle has put decades of effort into optimizing the recovery process. All non-committed transactions are rolled-back, all committed transactions are rolled-forward. Recovery can happen in a matter of seconds.
 GoldenGate works in a completely different mode. The GoldenGate extract process mines the redo log, keeping transactional changes in memory until a commit record has been processed. It then builds its own transactions into the trail file based on primary key. This can sometimes take time.

 GoldenGate is awesome for replication, especially lots of small transactions, but can be challenged by very large transactions. The recovery time for a “failover” can be significant compared to Data Guard. In addition, whereas you can configure Data Guard to work synchronously, GoldenGate only replicates changes after the transaction is committed, so if you have a long running transaction it can take a while to replicate.

I am a huge fan of GoldenGate, but if you are purely looking for synchronous DR then Data Guard is the best solution.

If you are looking to do any of the following the GoldenGate is the best solution:
 Replicate one or many tables to a read-write database.
 Replicate and transform tables.
 Bi-directional replication.
 Zero or near-zero downtime upgrade.
 Heterogeneous replication

ORA-01720: grant option does not exist for

GRANT SELECT ON DWHBI.RB_CMS_PRODUCT_MAPPING_SV TO DWHBI_SELECT_ROLE 

ORA-01720: grant option does not exist for ‘BIDM.RB_CMS_PRODUCT_MAPPING‘

Solution

Grant select  on  BIDM.RB_CMS_PRODUCT_MAPPING to DWHBI with grant option

User A has created view V in user A’s schema.

View V is based on table T in schema B.

User A and C have select privs on table T in schema B via ROLE R.

User A tries to grant select on view V to user C and gets the following error:

ORA-01720: grant option does not exist for 

Or

grant select any table to DWHBI with admin option;

grant execute any procedure to DWHBI with admin option;

OGG-01820 Could not enable workspace.

GoldenGate replicat ABENDED OGG-01820  Could not enable workspace.
AIX 6.1.3.0
for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230

MAP resolved (entry RM.EQUIPMENT):

map “RM”.”EQUIPMENT”, target RM.EQUIPMENT;
Using following columns in default map by name:
EQUIPMENT_ID, RSC_SPEC_ID, AREA_ID, CODE, RSC_STATUS_CD,
CONTAINER_ID, DEVICE_ID, CAPACITY, CAPACITY_USED, CAPACITY_UNUSED,
CAPACITY_ERROR, CREATED_DT, EXP_DT, VERSION, TEMPLATE_ID, TML_ID,
ROOM_ID, NAME, RACK_ID, PARTY_ID, POSITION_X, POSITION_Y
Using the following key columns for target table RM.EQUIPMENT: EQUIPMENT_ID.

Source Context :
SourceModule            : [er.repio]
SourceID                : [/scratch/aime1/adestore/views/aime1_staxk12/oggcore/OpenSys/src/app/er/repio.cpp]
SourceFunction          : [replicate_io(file_def *, std_rec_hdr_def *, char *, extr_ptr_def *, int *, BOOL)]
SourceLine              : [1063]

2014-04-22 15:50:30  ERROR   OGG-01820  Could not enable workspace.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Reading /itsp/oggrm/ogg11.2/dirdat/ra002378, current RBA 51862047, 198 records

Report at 2014-04-22 15:50:30 (activity since 2014-04-22 15:50:29)

From Table RM.ONRACK to RM.ONRACK:
#                   inserts:        50
#                   updates:         0
#                   deletes:         0
#                  discards:         0

Source Context :
SourceModule : [er.repio]
SourceID : [/scratch.local/mmar/view_storage/mmar_14516066/oggcore/OpenSys/src/app/er/repio.cpp]
SourceFunction : [replicate_io]
SourceLine : [1078]

2014-07-28 10:49:02 ERROR OGG-01820 Could not enable workspace.

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

Last record for the last committed transaction is the following:
GGSCI (csrdbsp1) 4>

GGSCI (csrdbsp1) 4>

GGSCI (csrdbsp1) 4>

GGSCI (csrdbsp1) 4>

GGSCI (csrdbsp1) 4> exit

SOLUTION
-bash-4.1$ sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 28 11:35:02 2014

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, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

System altered.

SQL> exec dbms_goldengate_auth.grant_admin_privilege(‘oggate’,’*’,TRUE);

PL/SQL procedure successfully completed.

SQL>

ERROR OGG-01224 (1:Connection refused)

SYMPTOMS
A unidirectional replication from source site to target site.
The pump process is abending with the following errors in report file, but communication between source site and target site is working as expected.

2012-10-02 13:01:06 WARNING OGG-01223 TCP/IP error 146 (Connection refused).
2012-10-02 13:01:47 WARNING OGG-01223 TCP/IP error 146 (Connection refused).
2012-10-02 13:02:28 WARNING OGG-01223 TCP/IP error 146 (Connection refused).
2012-10-02 13:02:53 WARNING OGG-01223 Unknown error sending data over TCP. Please check if intended receiver program is running (for example server or manager).
2012-10-02 13:03:23 WARNING OGG-01223 TCP/IP error 146 (Connection refused).
2012-10-02 13:04:04 WARNING OGG-01223 TCP/IP error 146 (Connection refused).

Source Context :
SourceModule : [er.extrout]
SourceID : [/scratch/angorant/view_storage/angorant_fbo2_Sparc9_120423.0230/oggcore/OpenSys/src/app/er/extrout.c]
SourceFunction : [tcp_send_data]
SourceLine : [1352]

2012-10-02 13:04:45 ERROR OGG-01224 TCP/IP error 146 (Connection refused); retries exceeded.
2012-10-02 13:04:45 ERROR OGG-01668 PROCESS ABENDING.

Target site manager process is up and running and listening on specified port
CAUSE

Target site manager is not being able to start server collector process in a port as per connection refused error

On target site ggserr.log file we can see:

2012-10-08 13:25:13 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from EXTRACT on host qahq1 (START SERVER CPU -1 PRI -1 TIMEOUT 300 PARAMS ).
2012-10-08 13:25:13 INFO OGG-01677 Oracle GoldenGate Collector for Oracle: Waiting for connection (started dynamically).
2012-10-08 13:25:13 WARNING OGG-01834 Oracle GoldenGate Collector for Oracle: Failed setting IPv6 socket to dual stack mode (error: 99, Option not supported by protocol).
2012-10-08 13:25:13 ERROR OGG-01224 Oracle GoldenGate Collector for Oracle: opening port to MGR from SERVER failed with 1:Connection refused.
2012-10-08 13:25:13 ERROR OGG-01668 Oracle GoldenGate Collector for Oracle: PROCESS ABENDING.

SOLUTION
Target site /etc/hosts file contains an invalid ID for localhost:

::1 qadr localhost   —> Wrong ID
127.0.0.1 name1 localhost loghost
192.168.1.1 name1
192.168.1.2 name2
192.168.1.3 name3

Invalid ID line should be removed from /etc/hosts and manager at target site should be restarted.
127.0.0.1 name1 localhost loghost
192.168.1.1 name1
192.168.1.2 name2
192.168.1.3 name3

RAC Administration and Maintenance Tasks and Utilities

The below two commands are generally used to check the status of CRS. The first command lists the status of CRS on the local node where as the other command shows the CRS status across all the nodes in Cluster.

crsctl check crs < crsctl check cluster <

[root@node1-pub ~]# crsctl check crs

Cluster Synchronization Services appears healthy

Cluster Ready Services appears healthy

Event Manager appears healthy

[root@node1-pub ~]#

For the below command to run, CSS needs to be running on the local node. The “ONLINE” status for remote node says that CSS is running on that node. When CSS is down on the remote node, the status of “OFFLINE” is displayed for that node.

 [root@node1-pub ~]# crsctl check cluster

node1-pub ONLINE

node2-pub ONLINE

 Viewing Cluster name:

I use below command to get the name of Cluster. The similar information can be retrieved from the dump file.

ocrdump -stdout -keyname SYSTEM | grep -A 1 clustername | grep ORATEXT | awk ‘{print $3}’

   OR

ocrconfig -export /tmp/ocr_exp.dat -s online

for i in `strings /tmp/ocr_exp.dat | grep -A 1 clustername` ; do if [ $i != ‘SYSTEM.css.clustername’ ]; then echo $i; fi; done

OR

 Oracle creates a directory with the same name as Cluster under the $ORA_CRS_HOME/cdata.

 No. Of Nodes configured in Cluster:

The below command can be used to find out the number of nodes registered into the cluster. It also displays the node’s Public name, Private name and Virtual name along with their numbers.

olsnodes -n -p -i

[root@node1-pub ~]# olsnodes -n -p -i

node1-pub 1 node1-prv node1-vip

node2-pub 2 node2-prv node2-vip

Viewing Votedisk Information:

 The below command is used to view the no. of Voting disks configured in the Cluster.

 crsctl query css votedisk

Viewing OCR Information:

The ocrcheck command displays the no. of OCR files configured in the Cluster. It is primarily used to chck the integrity of the OCR files. It also displays the version of OCR as well as storage space information. You can only have 2 OCR files at max.

 [root@node1-pub ~]# ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 262120

Used space (kbytes) : 3848

Available space (kbytes) : 258272

ID : 744414276

Device/File Name : /u02/ocfs2/ocr/OCRfile_0

Device/File integrity check succeeded

Device/File Name : /u02/ocfs2/ocr/OCRfile_1

Device/File integrity check succeeded

 Cluster registry integrity check succeeded

 Various Timeout Settings in Cluster:

Disktimeout: Disk Latencies in seconds from node-to-Votedisk. Default Value is 200. (Disk IO)

Misscount: Network Latencies in second from node-to-node (Interconnect). Default Value is 60 Sec (Linux) and 30 Sec in Unix platform. (Network IO) Misscount < Disktimeout

IF

(Disk IO Time > Disktimeout) OR (Network IO time > Misscount)

THEN

REBOOT NODE

ELSE

DO NOT REBOOT

END IF;

 crsctl get css disktimeout

crsctl get css misscount

crsctl get css reboottime

 [root@node1-pub ~]# crsctl get css disktimeout

200

[root@node1-pub ~]# crsctl get css misscount

Configuration parameter misscount is not defined.

The above message indicates that the Misscount is not set manually and it is set to its default Value which is 60 seconds on Linux. It can be changed as below.

[root@node1-pub ~]# crsctl set css misscount 100

Configuration parameter misscount is now set to 100.

[root@node1-pub ~]# crsctl get css misscount

100

 The below command sets the value of misscount back to its default value.

crsctl unset css misscount

[root@node1-pub ~]# crsctl unset css misscount

[root@node1-pub ~]# crsctl get css reboottime

  Add/Remove OCR file in Cluster:

Removing OCR File

(1) Get the Existing OCR file information by running ocrcheck utility.

          [root@node1-pub ~]# ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 262120

Used space (kbytes) : 3852

Available space (kbytes) : 258268

ID : 744414276

Device/File Name : /u02/ocfs2/ocr/OCRfile_0 <– OCR

Device/File integrity check succeeded

Device/File Name : /u02/ocfs2/ocr/OCRfile_1 <– OCR Mirror

Device/File integrity check succeeded

Cluster registry integrity check succeeded

 (2) The First command removes the OCR mirror (/u02/ocfs2/ocr/OCRfile_1). If you want to remove the OCR file (/u02/ocfs2/ocr/OCRfile_1) run the next command.

           ocrconfig -replace ocrmirror

ocrconfig -replace ocr

 [root@node1-pub ~]# ocrconfig -replace ocrmirror

[root@node1-pub ~]# ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 262120

Used space (kbytes) : 3852

Available space (kbytes) : 258268

ID : 744414276

Device/File Name : /u02/ocfs2/ocr/OCRfile_0 <<– OCR File

Device/File integrity check succeeded

 Device/File not configured <– OCR Mirror not existed any more

 Cluster registry integrity check succeeded

Adding OCR

 You need to add OCR or OCR mirror file in a case where you want to move the existing OCR file location to the different devices. The below command add the OCR mirror file if OCR file already exists.

 (1) Get the Current status of OCR:

            [root@node1-pub ~]# ocrconfig -replace ocrmirror

[root@node1-pub ~]# ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 262120

Used space (kbytes) : 3852

Available space (kbytes) : 258268

ID : 744414276

Device/File Name : /u02/ocfs2/ocr/OCRfile_0 <<– OCR File

Device/File integrity check succeeded

 Device/File not configured <– OCR Mirror does not exist

 Cluster registry integrity check succeeded

 As it can be seen, there is only one OCR file but not the second file (OCR Mirror). Below command adds the second OCR file.

           ocrconfig -replace ocrmirror <File name>

 [root@node1-pub ~]# ocrconfig -replace ocrmirror /u02/ocfs2/ocr/OCRfile_1

[root@node1-pub ~]# ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 262120

Used space (kbytes) : 3852

Available space (kbytes) : 258268

ID : 744414276

Device/File Name : /u02/ocfs2/ocr/OCRfile_0

Device/File integrity check succeeded

Device/File Name : /u02/ocfs2/ocr/OCRfile_1

Device/File integrity check succeeded

 Cluster registry integrity check succeeded

You can have at most 2 OCR devices (OCR itself and its single Mirror) in a cluster. Adding extra Mirror gives you below error message

[root@node1-pub ~]# ocrconfig -replace ocrmirror /u02/ocfs2/ocr/OCRfile_2

PROT-21: Invalid parameter

[root@node1-pub ~]#

Add/Remove Votedisk file in Cluster:

Add/Remove Voting Disk in Cluster:

  Adding Votedisk:

Get the existing Vote Disks associated into the cluster. To be safe, Bring crs cluster stack down on all the nodes but one on which you are going to add votedisk from.

 (1)    Stop CRS on all the nodes in cluster but one.

 [root@node2-pub ~]# crsctl stop crs

 (2)    Get the list of Existing Vote Disks

 crsctl query css votedisk

 [root@node1-pub ~]# crsctl query css votedisk

0. 0 /u02/ocfs2/vote/VDFile_0

1. 0 /u02/ocfs2/vote/VDFile_1

2. 0 /u02/ocfs2/vote/VDFile_2

Located 3 voting disk(s).

(3)    Backup the Votedisk file

   Backup the existing votedisks as below as oracle:

 dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0

 [root@node1-pub ~]# su – oracle

[oracle@node1-pub ~]$ dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0

41024+0 records in

41024+0 records out

[oracle@node1-pub ~]$

 (4)    Add an Extra Votedisk into the Cluster: 

 If it is a OCFS, then touch the file as oracle. On raw devices, initialize the raw devices using “dd” command

 touch /u02/ocfs2/vote/VDFile_3 <<– as oracle

crsctl add css votedisk /u02/ocfs2/vote/VDFile_3 <<– as oracle

crsctl query css votedisks

 [root@node1-pub ~]# su – oracle

[oracle@node1-pub ~]$ touch /u02/ocfs2/vote/VDFile_3

[oracle@node1-pub ~]$ crsctl add css votedisk /u02/ocfs2/vote/VDFile_3

Now formatting voting disk: /u02/ocfs2/vote/VDFile_3.

Successful addition of voting disk /u02/ocfs2/vote/VDFile_3.

 (5)    Confirm that the file has been added successfully:

 [root@node1-pub ~]# ls -l /u02/ocfs2/vote/VDFile_3

-rw-r—– 1 oracle oinstall 21004288 Oct 6 16:31 /u02/ocfs2/vote/VDFile_3

[root@node1-pub ~]# crsctl query css votedisks

Unknown parameter: votedisks

[root@node1-pub ~]# crsctl query css votedisk

0. 0 /u02/ocfs2/vote/VDFile_0

1. 0 /u02/ocfs2/vote/VDFile_1

2. 0 /u02/ocfs2/vote/VDFile_2

3. 0 /u02/ocfs2/vote/VDFile_3

Located 4 voting disk(s).

 Removing Votedisk:

 Removing Votedisk from the cluster is very simple. The below command removes the given votedisk from cluster configuration.

crsctl delete css votedisk /u02/ocfs2/vote/VDFile_3

 [root@node1-pub ~]# crsctl delete css votedisk /u02/ocfs2/vote/VDFile_3

Successful deletion of voting disk /u02/ocfs2/vote/VDFile_3.

[root@node1-pub ~]#

 [root@node1-pub ~]# crsctl query css votedisk

0. 0 /u02/ocfs2/vote/VDFile_0

1. 0 /u02/ocfs2/vote/VDFile_1

2. 0 /u02/ocfs2/vote/VDFile_2

Located 3 voting disk(s).

[root@node1-pub ~]#

Backing up OCR:

Oracle performs physical backup of OCR devices every 4 hours under the default backup directory $ORA_CRS_HOME/cdata/<CLUSTER_NAME> and then it rolls that forward to Daily, weekly and monthly backup. You can get the backup information by executing below command.

 ocrconfig -showbackup

 [root@node1-pub ~]# ocrconfig -showbackup

node2-pub 2007/09/03 17:46:47 /u01/app/crs/cdata/test-crs/backup00.ocr

node2-pub 2007/09/03 13:46:45 /u01/app/crs/cdata/test-crs/backup01.ocr

node2-pub 2007/09/03 09:46:44 /u01/app/crs/cdata/test-crs/backup02.ocr

node2-pub 2007/09/03 01:46:39 /u01/app/crs/cdata/test-crs/day.ocr

node2-pub 2007/09/03 01:46:39 /u01/app/crs/cdata/test-crs/week.ocr

[root@node1-pub ~]#

 Manually backing up the OCR

 ocrconfig -manualbackup <<–Physical Backup of OCR

 The above command backs up OCR under the default Backup directory. You can export the contents of the OCR using below command (Logical backup).

 ocrconfig -export /tmp/ocr_exp.dat -s online <<– Logical Backup of OCR

 Restoring OCR:

The below command is used to restore the OCR from the physical backup. Shutdown CRS on all nodes.

 ocrconfig -restore <file name>

 Locate the available Backups

 [root@node1-pub ~]# ocrconfig -showbackup

node2-pub 2007/09/03 17:46:47 /u01/app/crs/cdata/test-crs/backup00.ocr

node2-pub 2007/09/03 13:46:45 /u01/app/crs/cdata/test-crs/backup01.ocr

node2-pub 2007/09/03 09:46:44 /u01/app/crs/cdata/test-crs/backup02.ocr

node2-pub 2007/09/03 01:46:39 /u01/app/crs/cdata/test-crs/day.ocr

node2-pub 2007/09/03 01:46:39 /u01/app/crs/cdata/test-crs/week.ocr

node1-pub 2007/10/07 13:50:41 /u01/app/crs/cdata/test-crs/backup_20071007_135041.ocr

 Perform Restore from previous Backup

   [root@node2-pub ~]# ocrconfig -restore /u01/app/crs/cdata/test-crs/week.ocr

 The logical backup of OCR (taken using export option) can be imported using the below command.

 ocrconfig -import /tmp/ocr_exp.dat

 Restoring Votedisks:

         Shutdown CRS on all the nodes in Cluster.

         Locate the current location of the Votedisks

         Restore each of the votedisks using “dd” command from the previous good backup of Votedisk taken using the same “dd” command.

         Start CRS on all the nodes.

  crsctl stop crs

crsctl query css votedisk

dd if=<backup of Votedisk> of=<Votedisk file> <<– do this for all the votedisks

crsctl start crs

 Changing Public and Virtual IP Address:

  Current Config Changed to

 Node 1:

 Public IP: 216.160.37.154 192.168.10.11

VIP: 216.160.37.153 192.168.10.111

subnet: 216.160.37.159 192.168.10.0

Netmask: 255.255.255.248 255.255.255.0

Interface used: eth0 eth0

Hostname: node1-pub.hingu.net node1-pub.hingu.net

 Node 2:

 Public IP: 216.160.37.156 192.168.10.22

VIP: 216.160.37.157 192.168.10.222

subnet: 216.160.37.159 192.168.10.0

Netmask: 255.255.255.248 255.255.255.0

Interface used: eth0 eth0

Hostname: node1-pub.hingu.net node2-pub.hingu.net

(A)   Take the Services, Database, ASM Instances and nodeapps down on both the Nodes in Cluster. Also disable the nodeapps, asm and database instances to prevent them from restarting in case if this node gets rebooted during this process.

srvctl stop service -d test

srvctl stop database -d test

srvctl stop asm -n node1-pub

srvctl stop asm -n node2-pub

srvctl stop nodeapps -n node1-pub,node1-pub2

srvctl disable instance -d test -i test1,test2

srvctl disable asm -n node1-pub

srvctl disable asm -n node2-pub

srvctl disable nodeapps -n node1-pub

srvctl disable nodeapps -n node2-pub

(B)   Modify the /etc/hosts and/or DNS, ifcfg-eth0 (local node) with the new IP values on All the Nodes

 (C)   Restart the specific network interface in order to use the new IP.

ifconfig eth0 down

ifconfig eth0 up

Or, you can restart the network. CAUTION: on NAS, restarting entire network may cause the node to be rebooted.

(D)   Update the OCR with the New Public IP information.

In case of public IP, you have to delete the interface first and then add it back with the new IP address. As oracle user, Issue the below command:

oifcfg delif -global eth0

oifcfg setif -global eth0/192.168.10.0:public

(E)    Update the OCR with the New Virtual IP.

 Virtual IP is part of the nodeapps and so you can modify the nodeapps to update the Virtual IP information. As privileged user (root), Issue the below commands:

srvctl modify nodeapps -n node1-pub -A 192.168.10.111/255.255.255.0/eth0 <– for Node 1

srvctl modify nodeapps -n node1-pub -A 192.168.10.222/255.255.255.0/eth0 <– for Node 2

(F)    Enable the nodeapps, ASM, database Instances for all the Nodes.

srvctl enable instance -d test -i test1,test2

srvctl enable asm -n node1-pub

srvctl enable asm -n node2-pub

srvctl enable nodeapps -n node1-pub

srvctl enable nodeapps -n node2-pub

(G)  Update the listener.ora file on each nodes with the correct IP addresses in case if it uses the IP address instead of the hostname.

(H)   Restart the Nodeapps, ASM and Database instance

srvctl start nodeapps -n node1-pub

srvctl start nodeapps -n node2-pub

srvctl start asm -n node1-pub

srvctl start asm -n node2-pub

srvctl start database -d test

Source >>
http://www.oracledba.org/11g/rac/11g_RAC_Admin_Maintenance_Tasks.html

Drop an Unmounted ASM diskgroup

We are getting our hands dirty with ASM and will be moving all our databases from RAW devices to ASM in the next couple of months. My worst fear came to be when a Unix Sys Admin used a disk already allocated to ASM for a local filesystem (luckily this did not happen on a production server!).
When ASM realised this is immediatly issued a ALTER DISKGROUP FLASH_D DISMOUNT FORCE command.
By the time the Unix Sys Admin returned the disk it was unrecognozable by ASM as the disk headers had been overwritten. The diskgroup was unrecoverable and unusable.

SQL> select GROUP_NUMBER, NAME, state from v$asm_diskgroup;
GROUP_NUMBER NAME STATE
------------ --------- ----------
0 FLASH_D DISMOUNTED
2 SIGP_D MOUNTED
3 HRGP_D MOUNTED
3 rows selected.

SQL>alter diskgroup FLASH_D mount;
alter diskgroup FLASH_D mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "19" is missing

SQL>drop diskgroup FLASH_D including contents;
drop diskgroup FLASH_D including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "FLASH_D" does not exist or is not mounted

SQL>alter diskgroup FLASH_D check;
alter diskgroup FLASH_D check
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15001: diskgroup "FLASH_D" does not exist or is not mounted

I had to recreate the whole diskgroup from scratch. I followed Metalink Note 387103.1 to recreate the diskgroup.
Basically you clean up the asm disk header of all the disks in the diskgroup. This will also remove the whole diskgroup from asm.

SQL> select 'dd if=/dev/zero of='PATH' bs=8192 count=100' from v$asm_disk where GROUP_NUMBER=0;

$dd if=/dev/zero of=/dev/rsigp_flash bs=8192 count=100
100+0 records in
100+0 records out

$dd if=/dev/zero of=/dev/rsigp_flash1 bs=8192 count=100
100+0 records in
100+0 records out
etc ...

SQL> select GROUP_NUMBER, NAME, state from v$asm_diskgroup;
GROUP_NUMBER NAME STATE
------------ --------- ----------
2 SIGP_D MOUNTED
3 HRGP_D MOUNTED
2 rows selected.

The diskgroup is now removed. I then used DBCA to recreate the ASM diskgroup, all these disks showed up as Candidate disks.

 

Oracle DBA Online Support

for any oracle database issue or if you need to ask about oracle please lo-gin  my Oracle DBA Online Support

 

Redundant Interconnect Usage in 11G R2 RAC

We have thought of implementing this new feature with Oracle 11G as we have frequent private NIC down events which further results in node evictions.

Redundant Interconnect without any 3rd-party IP failover technology (bond, IPMP or similar) is supported natively by Grid Infrastructure starting from 11.2.0.2.  Multiple private network adapters can be defined either during the installation phase or afterward using the oifcfg.  Oracle Database, CSS, OCR, CRS, CTSS, and EVM components in 11.2.0.2 employ it automatically.

Grid Infrastructure can activate a maximum of four private network adapters at a time even if more are defined. The ora.cluster_interconnect.haip resource will start one to four link local  HAIP on private network adapters for interconnect communication for Oracle RAC, Oracle ASM, and Oracle ACFS etc.

Grid automatically picks link local addresses from reserved 169.254.*.* subnet for HAIP, and it will not attempt to use any 169.254.*.* address if it’s already in use for another purpose. With HAIP, by default, interconnect traffic will be load balanced across all active interconnect interfaces, and corresponding HAIP address will be failed over transparently to other adapters if one fails or becomes non-communicative. .

The number of HAIP addresses is decided by how many private network adapters are active when Grid comes up on the first node in the cluster .  If there’s only one active private network, Grid will create one; if two, Grid will create two; and if more than two, Grid will create four HAIPs. The number of HAIPs won’t change even if more private network adapters are activated later, a restart of clusterware on all nodes is required for new adapters to become effective

While in previous releases bonding, trunking, teaming, or similar technology was required to make use of redundant network connections between the nodes to be used as redundant,dedicated, private communication channels or “interconnect”, Oracle Clusterware now provides an integrated solution to ensure “Redundant Interconnect Usage”. This functionality is available starting with Oracle Database 11g Release 2, Patch Set One (11.2.0.2).

The Redundant Interconnect Usage feature does not operate on the network interfaces directly. Instead, it is based on a multiple-listening-endpoint architecture, in which a highly available virtual IP (the HAIP) is assigned to each private network (up to a total number of 4 interfaces).By default, Oracle Real Application Clusters (RAC) software uses all of the HAIP addresses for private network communication, providing load balancing across the set of interfaces identified as the private network. If a private interconnect interface fails or becomes non-communicative,then Oracle Clusterware transparently moves the corresponding HAIP address to one of the remaining functional interfaces

Oracle RAC Databases, Oracle Automatic Storage Management (clustered ASM), and Oracle Clusterware components such as CSS, OCR, CRS, CTSS, and EVM components employ

Redundant Interconnect Usage starting with Oracle Database 11g Release 2, Patch Set One (11.2.0.2)

Steps

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

# $GRID_HOME/bin/oifcfg getif

eth0 10.2.156.0 global public

eth1 192.168.12.0 global cluster_interconnect

The interfaces that are currently stored in the GPnP profile, their subnets, and their role (public or cluster_interconnect) are displayed.

2. Add the remaining LLT links to the GPnP profile:

# $GRID_HOME/bin/oifcfg setif -global \

eth2/192.168.12.0:cluster_interconnect

ipmp1 192.10.1.0 global cluster_interconnect

3. Verify that the correct interface subnet is in use:

# $GRID_HOME/bin/oifcfg getif

eth0 10.2.156.0 global public

eth1 192.168.12.0 global cluster_interconnect

eth2 192.168.2.0 global cluster_interconnect

4.You must restart Oracle Clusterware on all members of the cluster when you make global changes. For local changes, you only need to perform a node restart.

Interconnect changes for the database occur at instance startup. However, the interconnect for Oracle Clusterware might be different.

11gR2 Grid Infrastructure Redundant Interconnect and ora.cluster_interconnect.haip [ID 1210883.1]

How to Modify Private Network Interface in 11.2 Grid Infrastructure [ID 1073502.1]

http://download.oracle.com/docs/cd/B28359_01/rac.111/b28255/oifcfg.htm

How to Change Interconnect/Public Network (Interface or Subnet) in Oracle Clusterware [ID 283684.1]

[11gR2 Grid Infrastructure Redundant Interconnect and ora.cluster_interconnect.haip [ID 1210883.1]]

 

Cluster interconnect: It’s a private interface used for the cluster-ware to provide inter-instance or

Cache Fusion communication. This is also called as private IP address.

We can change it at any stage, it require full outage.

Step 1: Determine private IP address

[oracle@test1 ~]$ oifcfg getif

eth0  192.168.1.0  global  public

eth1  10.0.0.0  global  cluster_interconnect

Step 2: Determine Ethernet card name

[oracle@test1 ~]$ oifcfg iflist

eth0  192.168.1.0

eth1  10.0.0.0

Step 3: Add new interface Subnet ID specification:

[oracle@test1 ~]$ oifcfg setif -global eth1/172.16.0.0:cluster_interconnect

Step 4 : Verify the changes:

[oracle@test1 ~]$ oifcfg getif

eth0  192.168.1.0  global  public

eth1  10.0.0.0  global  cluster_interconnect

eth1  172.16.0.0  global  cluster_interconnect

Step 5: Stop cluster on all node:

[oracle@test1 ~]# sudo /u01/app/11.2.0/grid/bin/crsctl stop cluster -all

Step 6: Modify netwrok address on network adapter of each box:

[root@test1 ~]# ifconfig eth1 172.16.0.10 netmask 255.255.0.0 broadcast 172.16.255.255

[root@test2 ~]# ifconfig eth1 172.16.0.11 netmask 255.255.0.0 broadcast 172.16.255.255

Step 7: Modify /etc/hosts file on each node.

Delete entry of old private interconnect and add new private interconnect on each node.

[root@test1 ~]# cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1    localhost.localdomain    localhost

#10.10.10.105    test1-priv.localdomain    test1-priv

172.16.0.10    test1-priv.localdomain    test1-priv

#10.10.10.107    test2-priv.localdomain  test2-priv

172.16.0.11    test2-priv.localdomain  test2-priv

Step 8: Start cluster on each node

[root@test1 ~]# /u01/app/11.2.0/grid/bin/crsctl start cluster -all

Step 9: Delete old entry of private interconnect from cluster registry

[oracle@test1 ~]$ oifcfg delif -global eth1/10.0.0.0:cluster_interconnect

Step 10: Verify the changes:

[oracle@test1 ~]$ oifcfg getif

eth0  192.168.1.0  global  public

eth1  172.16.0.0  global  cluster_interconnect

You can also verify:

[root@test1 ~]# ifconfig eth1

eth1      Link encap:Ethernet  HWaddr 00:0C:29:F9:B9:26

inet addr:172.16.0.10  Bcast:172.16.255.255  Mask:255.255.0.0

inet6 addr: fe80::20c:29ff:fef9:b926/64 Scope:Link

UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

RX packets:397513 errors:0 dropped:0 overruns:0 frame:0

TX packets:338579 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:247976796 (236.4 MiB)  TX bytes:159435440 (152.0 MiB)

Base address:0×2040 Memory:d8940000-d8960000

[oracle@test1 ~]$ ping test1-priv

PING test1-priv.localdomain (172.16.0.10) 56(84) bytes of data.

64 bytes from test1-priv.localdomain (172.16.0.10): icmp_seq=1 ttl=64 time=0.036 ms

64 bytes from test1-priv.localdomain (172.16.0.10): icmp_seq=2 ttl=64 time=0.015 ms

— test1-priv.localdomain ping statistics —

2 packets transmitted, 2 received, 0% packet loss, time 999ms

rtt min/avg/max/mdev = 0.015/0.025/0.036/0.011 ms

Oracle Database Administration Scripts | DBA Bundle

This tool call it the DBA Bundle , it’s a tar file contains a group of most useful shell scripts that help the database administrator in the day to day tasks in a smart, safe and easy way, some of these scripts are old but it still doing it’s job efficiently 🙂 for more detail’s please check the below blog for DBA Bundle owner Mr.Mahmmoud ADEL

http://dba-tips.blogspot.ae/

The bundle is downloadable from here:

This tool I call it DBA Bundle , it’s a tar file contains a group of shell scripts, you can DOWNLOAD the latest version from this link: [V. 2.4 25-Apr-2016]
https://www.dropbox.com/s/lgrprfazgkeoxb5/DBA_BUNDLE2.tar?dl=0

 

To call script “aliases_DBA_BUNDLE.sh” use “.” command: e.g.
# . aliases_DBA_BUNDLE.sh

What if I’ve more than one database on the server, how to use “aliases_DBA_BUNDLE.sh” script?

let’s say you’ve two databases running on the same servers (SALESDB & ORCL), if you want to use the shortcuts (alert,tns,bdump,..) for SALESDB just run “aliases_DBA_BUNDLE.sh” script and select SALESDB, once you want to use the shortcuts (aliases) for ORCL DB re-run script “aliases_DBA_BUNDLE.sh” and select ORCL this once, and so on.

That was regarding the shortcut commands (aliases), regarding the aliases of other scripts in this bundle each time you execute an alias of a script it will list to you the current running instances on the server to select the database you want the script to run against .

If you still can’t get the use of “aliases_DBA_BUNDLE.sh” script, still it’s easy, just whenever you want to run any script go to the location where you extracted the bundle and run the script you want from there.

Now let’s move to the function of each script in this bundle:

Script Name | Description
——————-|——————————————–
export_data.sh|  Script Export Full DB|SCHEMA|TABLE data
(gives your the option of using exp or expdp utility for the export).
——————-|——————————————–
RMAN_full.sh | Script Takes an online RMAN full backup for the database
(gives you the option of compressed backup).
—————— |——————————————–
tablespaces.sh | Script Lists All TABLESPACES and it’s size on a database.
——————|———————————————
datafiles.sh      | Script Lists All DATAFILES and it’s size on a database.
——————|———————————————
audit_records.sh| Script Retrieves the AUDIT data for a DB user  in a specific date or number of days back.
http://dba-tips.blogspot.ae/2014/02/extract-oracle-audit-records-script.html
——————|——————————————–
zAngA_zAngA.sh| Script Retrieves the AUDIT data for a DB user in a specific date or number of days back. (this is the one linked to the alias if used “aliases_DBA_BUNDLE.sh” script).

http://dba-tips.blogspot.ae/2014/02/extract-oracle-audit-records-script.html
zanga zanga is a Libyan term used in one of the Gaddafi speeches(the ex-leader of Libya), when he vowed to hunt down protesters alleyway by alleyway but indeed the rebels hunted him down in a sewerage tube.
——————|——————————————–
gather_stats.sh| Script Gathers STATISTICS on a SCHEMA or TABLE using DBMS_STATS
——————|——————————————–
invalid_objects.sh | Script Lists All Invalid Objects on the DB + it’s compile statements.
——————|———————————————
biggest_50_objects.sh | Script Lists the Biggest 50 Objects on a database.
——————|———————————————
session_details.sh| Script Lists the Details of a user session. (if no input, will list all sessions on the instance).
——————|———————————————
all_sessions_info.sh| Script Lists All current sessions on all running instances [RAC DB].
——————|———————————————
db_locks.sh | Script Lists the Blocking LOCKS on a database.
——————|———————————————
unlock_user.sh | Script Unlocks a specific DB User Account + the option of reset the user’s password.
——————|———————————————
sql_id_details.sh| Script Shows the details of a specific SQL STATEMENT by providing it’s SQL_ID.
——————|———————————————
parameter_val.sh| Script Shows the value of a Visible or Hidden initialization Parameter.
——————|———————————————
db_jobs.sh | Script Lists All database Jobs (dba_jobs + dba_scheduler_jobs).
——————|———————————————
process_info.sh| Script Shows the DB Session details when providing it’s Unix PID.
——————|———————————————
oracle_cleanup.sh| Script Backup & Clean up All DB & it’s Listener LOGs.
——————|———————————————
last_logon_report.sh| Script Shows the last login date of ALL users in the database.
——————|———————————————
failed_logins.sh| Script Shows the failed login attempts in the last provided n of days.
——————|———————————————
start_tracing.sh| Script Start TRACING an Oracle Session activities in a logfile.
http://dba-tips.blogspot.ae/2014/02/script-to-trace-oracle-sesson.html
——————|———————————————
stop_tracing.sh| Script Stop TRACING an already traced Oracle Session + provide the logfile and it’s TKPROFED log.
http://dba-tips.blogspot.ae/2014/02/script-to-trace-oracle-sesson.html
——————|———————————————
user_details.sh | Script Generates the Full SQL Creation script for a DB user.
——————|———————————————
object_ddl.sh | Script Generates Full SQL Creation script for a database Object.
——————|——————————————–
Archives_Delete.sh| Script Deletes all Archivelogs older than (provided) number of days.
——————|——————————————–
analyze_objects.sh| Script Analyze All tables under a specific SCHEMA (using ANALYZE command).

Scripts that are not aliased:
***********************************
The following scripts will not be included in the list of aliases because it’s not applicable or need to be used carefully:
————————————————————–
dbalarm.sh | Script Monitors the ALERTLOGs of ALL Databases and Listeners running
on the server and instantly report ORA- errors and TNS- errors that appears
in these logs to the DBA E-mail Address by sending an email to the DBA
(you have to modify this parameter in line number 11
to point to your E-mail Address):
MAIL_LIST=”youremail@yourcompany.com”

> Of course the sendmail service should be configured on the server.

The best use of this script is to schedule running it in the crontab
to run every 5 minutes (or less) to report ORA- and TNS- errors
when they appear in the DBs & listeners logs.
———————–|———————————————-
SHUTDOWN_All.sh| Script to SHUTDOWN ALL running Databases & Listeners on The server.
———————–|———————————————-
COLD_BACKUP.sh | Script Takes a COLD BACKUP of a specific database
(of course the database will be down during the cold backup ;-))
This script will shutdown the database, takes a cold backup,
creates a restore script (in case you want to restore this cold backup
later ) then indeed it will startup the database.
http://dba-tips.blogspot.ae/2014/02/cold-backup-script.html

That’s all regarding this bundle, please note that time by time I’ll update this bundle by adding new scripts or by improving the functionalities of existing ones.