Showing posts with label Oracle DB. Show all posts
Showing posts with label Oracle DB. Show all posts

Sunday, February 2, 2020

DNS Configuration for Oracle RAC Environment to use SCAN


Reference site:https://jtechspot.com/dns-setup-for-oracle-rac-environment/
This articles provides basic DNS Configuration setup which is important part in Oracle RAC Environment to use Single Client Access Name (SCAN) 

# cd /var/named/chroot/etc

# vi named.conf

options {

        listen-on port 53 { 192.168.78.51; };
        listen-on-v6 port 53 { ::1; };
        directory       "/var/named";
};
zone "racdomain.com"
{
        type master;
        file "racdomain.com.fwd.zone";
};
zone "localhost"
{
        type master;
        file "localhost.fwd.zone";
};
zone "78.168.192.in-addr.arpa"
{
        type master;
        file "192.168.78.rev.zone";
};
zone "0.0.127.in-addr.arpa"
{
        type master;
        file "localhost.rev.zone";
};

# cd ../var/named

# vi racdomain.com.fwd.zone (forward zone)

$TTL 1D
@ IN SOA racnode1.racdomain.com. root.localhost (
2015111000 ; serial
8H ; refresh
4H ; retry
1W ; expiry
1D ) ; minimum
@ IN NS racnode1.racdomain.com.
localhost IN A 127.0.0.1
dns1 IN A 192.169.78.51
racnode1 IN A 192.168.78.51
racnode2 IN A 192.168.78.52
racnode1-priv IN A 172.16.100.50
racnode2-priv IN A 172.16.100.60
racnode1-vip IN A 192.168.78.53
racnode2-vip IN A 192.168.78.54
rac-scan IN A 192.168.78.60
IN A 192.168.78.61
IN A 192.168.78.62

# vi localhost.fwd.zone

(forward zone)

$TTL 1D
@ IN SOA racnode1.racdomain.com. root.localhost (
2015111000 ; serial
8H ; refresh
4H ; retry
1W ; expiry
1D ) ; minimum
IN NS @
IN A 127.0.0.1

# vi 192.168.78.rev.zone

(reverse zone)

$TTL 1D
@ IN SOA racnode1.racdomain.com. root.localhost (
2015111000 ; serial
8H ; refresh
4H ; retry
1W ; expiry
1d ) ; minimum
@ IN NS racnode1.racdomain.com.
51 IN PTR racnode1.racdomain.com.
52 IN PTR racnode2.racdomain.com.
53 IN PTR racnode1-vip.racdomain.com.
54 IN PTR racnode2-vip.racdomain.com.

# vi localhost.rev.zone

(reverse zone)

$TTL 1D
@ IN SOA racnode1.racdomain.com. root.localhost (
2015111000 ; serial
8H ; refresh
4H ; retry
1W ; expiry
1d ) ; minimum
IN NS localhost.
1 IN PTR localhost.

# cat /etc/resolv.conf

; generated by /sbin/dhclient-script
search racdomain.com
nameserver 192.168.78.51
nameserver 127.0.0.1
options attempts: 2
options timeout: 1

Disable the Firewall run below commands

# chkconfig iptables off
# service iptables stop

-- Prevent to reset the resolv.conf on reboot

# chattr +i /etc/resolv.conf

to undo
# chattr -i /etc/resolv.conf
# lsattr /etc/resolv.conf ---## shows the output editable or not

Restart the Service
# service named start

OUTPUT
Starting named: [ OK ]

# nslookup racnode1

OUTPUT
Server: 192.168.78.51
Address: 192.168.78.51#53

Name: racnode1.racdomain.com
Address: 192.168.78.51

# nslookup rac-scan

OUTPUT
Server: 192.168.78.51
Address: 192.168.78.51#53

Name: rac-scan.racdomain.com
Address: 192.168.78.62
Name: rac-scan.racdomain.com
Address: 192.168.78.60
Name: rac-scan.racdomain.com
Address: 192.168.78.61
Reference site:https://jtechspot.com/dns-setup-for-oracle-rac-environment/

Tuesday, February 23, 2016

Physical Standby Database Lags Far Behind the Primary Database

In cases where a physical standby database is far behind the primary database, an RMAN incremental backup can be used to roll the standby database forward faster than redo log apply. In this procedure, the RMAN BACKUP INCREMENTAL FROM SCN command is used to create an incremental backup on the primary database that starts at the current SCN of the standby and is used to roll forward the standby database.
Note:
The steps in this section can also be used to resolve problems if a physical standby database has lost or corrupted archived redo data or has an un resolvable archive gap.
  1. On the standby database, stop the managed recovery process (MRP):
2.  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  1. On the standby database, find the SCN which will be used for the incremental backup at the primary database:
4.  SQL> SELECT CURRENT_SCN FROM V$DATABASE;
  1. In RMAN, connect to the primary database and create an incremental backup from the SCN derived in the previous step:
6.  RMAN> BACKUP INCREMENTAL FROM SCN <SCN from previous step>
7.  DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
Note:
RMAN does not consider the incremental backup as part of a backup strategy at the source database. Hence:
o    The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database
o    The backup is not cataloged at the source database
o    The backup sets produced by this command are written to the /dbs location by default, even if the flash recovery area or some other backup destination is defined as the default for disk backups.
o    You must create this incremental backup on disk for it to be useful. When you move the incremental backup to the standby database.
  1. Transfer all backup sets created on the primary system to the standby system (note that there may be more than one backup file created). For example:
9.  SCP /tmp/ForStandby_* standby:/tmp
  1. Connect to the standby database as the RMAN target, and catalog all incremental backup pieces:
11.  RMAN> CATALOG START WITH '/tmp/ForStandby';
  1. Recover the standby database with the cataloged incremental backup:
13.  RMAN> RECOVER DATABASE NOREDO;
  1. In RMAN, connect to the primary database and create a standby control file backup:
15.  RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
  1. Copy the standby control file backup to the standby system. For example:
17.  SCP /tmp/ForStandbyCTRL.bck standby:/tmp
  1. Shut down the standby database and startup nomount:
19.  RMAN> SHUTDOWN;
20.  RMAN> STARTUP NOMOUNT;
  1. In RMAN, connect to standby database and restore the standby control file:
22.  RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
  1. Shut down the standby database and startup mount:
24.  RMAN> SHUTDOWN;
25.  RMAN> STARTUP MOUNT;
  1. If the primary and standby database data file directories are identical, skip to step 13. If the primary and standby database data file directories are different, then in RMAN, connect to the standby database, catalog the standby data files, and switch the standby database to use the just-cataloged data files. For example:
27.  RMAN> CATALOG START WITH '+DATA_1/CHICAGO/DATAFILE/'; 
28.  RMAN> SWITCH DATABASE TO COPY;
  1. If the primary and standby database redo log directories are identical, skip to step 14. Otherwise, on the standby database, use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths. For example, LOG_FILE_NAME_CONVERT='/BOSTON/','/CHICAGO/'.
  2. On the standby database, clear all standby redo log groups (there may be more than 3):
31.  SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
32.  SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
33.  SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
  1. On the standby database, restart Flashback Database:
35.  SQL> ALTER DATABASE FLASHBACK OFF;
36.  SQL> ALTER DATABASE FLASHBACK ON;
  1. On the standby database, restart MRP:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;


Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14239/scenarios.htm#CIHEGFEG

Monday, October 12, 2015

Oracle 10.2.0.1 in RHEL 5.4 64bit libXp.so.6: cannot open shared object file

Error while installing Oracle 10.2.0.1 in RHEL 5.4 64bit libxp.so.6: cannot open shared object file

while issuing

$ ./runInstaller


Error 500--Internal Server Error
java.lang.UnsatisfiedLinkError: /home/hpsindia/bea/jrockit81sp6_142_10/jre/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
at java.lang.ClassLoader$NativeLibrary.load(Ljava.lang.String; )V(Native Method)
at java.lang.ClassLoader.loadLibrary0(Ljava.lang.Class;Ljava.io.File; )Z(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Ljava.lang.Class;Ljava.lang.String;Z)V(Unknown Source)
at java.lang.Runtime.loadLibrary0(Runtime.java:788)
at java.lang.System.loadLibrary(Ljava.lang.String; )V(Unknown Source)
at sun.security.action.LoadLibraryAction.run(LoadLibraryAction.java:50)
at java.awt.Toolkit.loadLibraries(Toolkit.java:1437)
at java.awt.Toolkit.(Toolkit.java:1458)
at java.awt.Color.(Color.java:250)
at net.sf.jasperreports.engine.xml.JRXmlConstants.getColor(JRXmlConstants.java:1251)
at net.sf.jasperreports.engine.xml.JRElementFactory.createObject(JRElementFactory.java:138)
at org.apache.commons.digester.FactoryCreateRule.begin(FactoryCreateRule.java:389)
at org.apache.commons.digester.Digester.startElement(Digester.java:1361)
at weblogic.apache.xerces.parsers.AbstractSAXParser.startElement(AbstractSAXParser.java:459)
at weblogic.apache.xerces.parsers.AbstractXMLDocumentParser.emptyElement(AbstractXMLDocumentParser.java :221)
at weblogic.apache.xerces.impl.xs.XMLSchemaValidator.emptyElement(XMLSchemaValidator.java:618)


SOLUTION: Install the required rpm's

# rpm -ivh libXp-1.0.0-8.1.el5.i386.rpm
# rpm -ivh libXp-1.0.0-8.1.el5.x86_64.rpm
# rpm -ivh libXp-devel-1.0.0-8.1.el5.i386.rpm
# rpm -ivh libXp-devel-1.0.0-8.1.el5.x86_64.rpm

Saturday, February 14, 2015

Restoring a NOARCHIVELOG Database to a New Location

In this scenario, you restore the database files to an alternative location because the original location is damaged by a media failure.

To restore the most recent whole database backup to a new location:
If the database is open, then shut it down. For example, enter:
SHUTDOWN IMMEDIATE

Restore all of the datafiles and control files of the whole database backup, not just the damaged files. If the hardware problem has not been corrected and some or all of the database files must be restored to alternative locations, then restore the whole database backup to a new location. For example, enter:
% cp /backup/*.dbf /new_disk/oradata/trgt/

If necessary, edit the restored parameter file to indicate the new location of the control files. For example:
CONTROL_FILES = "/new_disk/oradata/trgt/control01.dbf"

Start an instance using the restored and edited parameter file and mount, but do not open, the database. For example:
STARTUP MOUNT

If the restored datafile filenames will be different (as will be the case when you restore to a different file system or directory, on the same node or a different node), then update the control file to reflect the new datafile locations. For example, to rename datafile 1 you might enter:
ALTER DATABASE RENAME FILE '?/oradata/trgt/system01.dbf' TO
                           '/new_disk/oradata/system01.dbf';

If the online redo logs were located on a damaged disk, and the hardware problem is not corrected, then specify a new location for each affected online log. For example, enter:
ALTER DATABASE RENAME FILE '?/oradata/trgt/redo01.log' TO
                           '/new_disk/oradata/redo_01.log';
ALTER DATABASE RENAME FILE '?/oradata/trgt/redo02.log' TO
                           '/new_disk/oradata/redo_02.log';

Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must first mimic incomplete recovery:
RECOVER DATABASE UNTIL CANCEL;
CANCEL;

Open the database in RESETLOGS mode. This command clears the online redo logs and resets the log sequence to 1:
ALTER DATABASE OPEN RESETLOGS;

Note that restoring a NOARCHIVELOG database backup and then resetting the log discards all changes to the database made from the time the backup was taken to the time of the failure.

Making an Index Unusable

When you make an index unusable, it is ignored by the optimizer and is not maintained by DML. When you make one partition of a partitioned index unusable, the other partitions of the index remain valid.

You must rebuild or drop and re-create an unusable index or index partition before using it.
The following procedure illustrates how to make an index and index partition unusable, and how to query the object status.

To make an index unusable: 
  1. Query the data dictionary to determine whether an existing index or index partition is usable or unusable.
For example, issue the following query (output truncated to save space):

hr@PROD> SELECT INDEX_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED
  2  FROM   USER_INDEXES
  3  UNION ALL
  4  SELECT PARTITION_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED
  5  FROM   USER_IND_PARTITIONS;

INDEX OR PART NAME             STATUS   SEG
------------------------------ -------- ---
I_EMP_ENAME                    N/A      N/A
JHIST_EMP_ID_ST_DATE_PK        VALID    YES
JHIST_JOB_IX                   VALID    YES
JHIST_EMPLOYEE_IX              VALID    YES
JHIST_DEPARTMENT_IX            VALID    YES
EMP_EMAIL_UK                   VALID    NO
.
.
.
COUNTRY_C_ID_PK                VALID    YES
REG_ID_PK                      VALID    YES
P2_I_EMP_ENAME                 USABLE   YES
P1_I_EMP_ENAME                 UNUSABLE NO

22 rows selected.

The preceding output shows that only index partition p1_i_emp_ename is unusable.
  1. Make an index or index partition unusable by specifying the UNUSABLE keyword.
The following example makes index emp_email_uk unusable:

hr@PROD> ALTER INDEX emp_email_uk UNUSABLE;

Index altered.

The following example makes index partition p2_i_emp_ename unusable:

hr@PROD> ALTER INDEX i_emp_ename MODIFY PARTITION p2_i_emp_ename UNUSABLE;

Index altered.
  1. Optionally, query the data dictionary to verify the status change.
For example, issue the following query (output truncated to save space):

hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS,
  2  SEGMENT_CREATED
  3  FROM   USER_INDEXES
  4  UNION ALL
  5  SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS,
  6  SEGMENT_CREATED
  7  FROM   USER_IND_PARTITIONS;

INDEX OR PARTITION NAME        STATUS   SEG
------------------------------ -------- ---
I_EMP_ENAME                    N/A      N/A
JHIST_EMP_ID_ST_DATE_PK        VALID    YES
JHIST_JOB_IX                   VALID    YES
JHIST_EMPLOYEE_IX              VALID    YES
JHIST_DEPARTMENT_IX            VALID    YES
EMP_EMAIL_UK                   UNUSABLE NO
.
.
.
COUNTRY_C_ID_PK                VALID    YES
REG_ID_PK                      VALID    YES
P2_I_EMP_ENAME                 UNUSABLE NO
P1_I_EMP_ENAME                 UNUSABLE NO

22 rows selected.

A query of space consumed by the i_emp_ename and emp_email_uk segments shows that the segments no longer exist:

hr@PROD> SELECT SEGMENT_NAME, BYTES
  2  FROM   USER_SEGMENTS
  3  WHERE  SEGMENT_NAME IN ('I_EMP_ENAME', 'EMP_EMAIL_UK');


no rows selected

Rebuilding an Existing Index

Before rebuilding an existing index, compare the costs and benefits associated with rebuilding to those associated with coalescing indexes.

When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.

The following statement rebuilds the existing index emp_name:

SQL> ALTER INDEX emp_name REBUILD;

The REBUILD clause must immediately follow the index name, and precede any other options. It cannot be used with the DEALLOCATE UNUSED clause.

If have the option of rebuilding the index online. The following statement rebuilds the emp_name index online:

SQL> ALTER INDEX emp_name REBUILD ONLINE;

If you do not have the space required to rebuild an index, you can choose instead to coalesce the index. Coalescing an index can also be done online.

Sunday, February 8, 2015

ORA-00020: maximum number of processes 150 exceeded

PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     150

SQL> alter system set processes=250 scope=spfile;
System altered.
(the new value takes effect the next time you start an instance of the database.)

if you issue:
SQL> alter system set processes=250 scope=both;
alter system set processes=250 scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

after you restart database query for the changes

SQL> select * from gv$resource_limit;
or
SQL>show parameter processes