Tuesday, January 23, 2018

RAC Database Basics

Hai, this article will explain basics of RAC database Administration


================================================================================================================
List of Topics (Linux, Database, RAC, EBS)

Unable to access Weblogic Console page then delete the deny rule line form following file 
[root@rac1 ~]#
[root@rac1 ~]# vi $EBS_DOMAIN_HOME/config/config.xml
. . . . .
. . . . . 
. . . . .  
<connection-filter-rule>0.0.0.0/0 * * deny</connection-filter-rule>
. . . . . 
. . . . . 
. . . . . 
vi :/deny
================================================================================================================
To check list of background process running in Cluster
[root@rac1 ~]#
[root@rac1 ~]# ps -ef | grep d.bin
How to check master node in RAC Cluster Ware:
[root@rac1 ~]#
[root@rac1 ~]# olsnodes
rac1
rac2

[root@rac1 ~]#

RAC Cluster Version
[root@rac1 ~]# 
[root@rac1 ~]# crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.1.0]
[root@rac1 ~]# 
[root@rac1 ~]# cat /u01/app/grid_home/log/rac1/crsd/crsd.log | grep 'OCR MASTER' | tail -1
2018-01-18 11:27:20.201: [  OCRMAS][1223633216]th_master:12: I AM THE NEW OCR MASTER at incar 1. Node Number 1
[root@rac1 ~]#
[root@rac2 ~]# cat /u01/app/grid_home/log/rac2/crsd/crsd.log | grep 'OCR MASTER' | tail -1
2018-01-18 11:27:25.170: [  OCRMAS][1220979008]th_master: NEW OCR MASTER IS 1
[root@rac2 ~]#

 Enable Archivelog for RAC database:
SQL>
SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string
SQL>
SQL> show parameter standby

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
SQL>

SQL>
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           2

SQL>
SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
DELL2            OPEN
DELL1            OPEN

SQL>
SQL> select LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;

LOG_MODE     FLASHBACK_ON       FOR
------------ ------------------ ---
NOARCHIVELOG NO                 NO

SQL>

[oracle@rac1 ~]$ srvctl status database -d DELL
Instance DELL1 is running on node rac1
Instance DELL2 is running on node rac2
[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl stop database -d DELL
[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl status database -d DELL
Instance DELL1 is not running on node rac1
Instance DELL2 is not running on node rac2
[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl start database -d DELL -o mount
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl status database -d DELL
Instance DELL1 is running on node rac1
Instance DELL2 is running on node rac2
[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 23 14:25:36 2018

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


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

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
DELL2            MOUNTED
DELL1            MOUNTED

SQL>
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           2

SQL>

SQL> alter database archivelog;

Database altered.

SQL>
SQL> alter database flashback on;

Database altered.

SQL>
SQL> alter database force logging;

Database altered.

SQL>
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL>
SQL> select LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;

LOG_MODE     FLASHBACK_ON       FOR
------------ ------------------ ---
ARCHIVELOG   YES                YES

SQL>

 Adding Standby Redologs:
SQL>
SQL> select * from v$standby_log;

no rows selected

SQL>
SQL> select * from gv$standby_log;


no rows selected

SQL>
SQL> select * from gv$log;

    INST_ID     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1          1   52428800        512          1 NO
INACTIVE                945184 30-JAN-18       949294 30-JAN-18

          1          2          1          2   52428800        512          1 NO
CURRENT                 949294 30-JAN-18   2.8147E+14

          1          3          2          1   52428800        512          1 NO
CURRENT                 954820 30-JAN-18   2.8147E+14 30-JAN-18
   INST_ID     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          4          2          0   52428800        512          1 YES
UNUSED                       0                      0

          2          1          1          1   52428800        512          1 NO
INACTIVE                945184 30-JAN-18       949294 30-JAN-18

          2          2          1          2   52428800        512          1 NO
CURRENT                 949294 30-JAN-18   2.8147E+14
   INST_ID     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         2          3          2          1   52428800        512          1 NO
CURRENT                 954820 30-JAN-18   2.8147E+14 30-JAN-18

          2          4          2          0   52428800        512          1 YES
UNUSED                       0                      0
8 rows selected.
SQL> 
SQL> col MEMBER for a35;
SQL> 
SQL> SELECT * FROM GV$LOGFILE;

   INST_ID     GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ---------- ------- ------- ------------------------------ ---
         1          2         ONLINE  +DATA/dell/redo02.log          NO
         1          1         ONLINE  +DATA/dell/redo01.log          NO
         1          3         ONLINE  +DATA/dell/redo03.log          NO
         1          4         ONLINE  +DATA/dell/redo04.log          NO
         2          2         ONLINE  +DATA/dell/redo02.log          NO
         2          1         ONLINE  +DATA/dell/redo01.log          NO
         2          3         ONLINE  +DATA/dell/redo03.log          NO
         2          4         ONLINE  +DATA/dell/redo04.log          NO

 8 rows selected.

 SQL>

SQL> alter database add standby logfile '+DATA/dell/stbyredo05.log' size 50M;

Database altered.

SQL> alter database add standby logfile '+DATA/dell/stbyredo06.log' size 50M;

Database altered.

SQL> alter database add standby logfile '+DATA/dell/stbyredo07.log' size 50M;

Database altered.

SQL> alter database add standby logfile '+DATA/dell/stbyredo08.log' size 50M;

Database altered.

SQL>
SQL> SELECT * FROM V$LOGFILE;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         2         ONLINE  +DATA/dell/redo02.log                    NO
         1         ONLINE  +DATA/dell/redo01.log                    NO
         3         ONLINE  +DATA/dell/redo03.log                    NO
         4         ONLINE  +DATA/dell/redo04.log                    NO
         5         STANDBY +DATA/dell/stbyredo05.log                NO
         6         STANDBY +DATA/dell/stbyredo06.log                NO
         7         STANDBY +DATA/dell/stbyredo07.log                NO
         8         STANDBY +DATA/dell/stbyredo08.log                NO

8 rows selected.

SQL>

 SQL> SELECT * FROM GV$LOGFILE;

    INST_ID     GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ---------- ------- ------- ------------------------------ ---
         1          2         ONLINE  +DATA/dell/redo02.log          NO
         1          1         ONLINE  +DATA/dell/redo01.log          NO
         1          3         ONLINE  +DATA/dell/redo03.log          NO
         1          4         ONLINE  +DATA/dell/redo04.log          NO
         1          5         STANDBY +DATA/dell/stbyredo05.log      NO
         1          6         STANDBY +DATA/dell/stbyredo06.log      NO
         1          7         STANDBY +DATA/dell/stbyredo07.log      NO
         1          8         STANDBY +DATA/dell/stbyredo08.log      NO
         2          2         ONLINE  +DATA/dell/redo02.log          NO
         2          1         ONLINE  +DATA/dell/redo01.log          NO
         2          3         ONLINE  +DATA/dell/redo03.log          NO

    INST_ID     GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ---------- ------- ------- ------------------------------ ---
         2          4         ONLINE  +DATA/dell/redo04.log          NO
         2          5         STANDBY +DATA/dell/stbyredo05.log      NO
         2          6         STANDBY +DATA/dell/stbyredo06.log      NO
         2          7         STANDBY +DATA/dell/stbyredo07.log      NO
         2          8         STANDBY +DATA/dell/stbyredo08.log      NO

 16 rows selected.
SQL>

SQL> select bytes from v$standby_log;

     BYTES
----------
  52428800
  52428800
  52428800
  52428800

SQL>
SQL>
SQL> select bytes from gv$standby_log;

      BYTES
----------
  52428800
  52428800
  52428800
  52428800
  52428800
  52428800
  52428800
  52428800

 8 rows selected.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl stop database -d DELL
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl start database -d DELL
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl status database -d DELL
Instance DELL1 is running on node rac1
Instance DELL2 is running on node rac2
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 30 09:19:44 2018

 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

 SQL>
SQL> select instance_name,status from gv$instance;

 INSTANCE_NAME    STATUS
---------------- ------------
DELL1            OPEN
DELL2            OPEN

 SQL>
SQL> select LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;

 LOG_MODE     FLASHBACK_ON       FOR
------------ ------------------ ---
ARCHIVELOG   YES                YES
SQL>
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

SQL>

================================================================================================================
PRVF-5184 : Check of following Udev attributes



PRVF-5184 : Check of following Udev attributes of "rac2:/dev/oracleasm/disks/DELLASM" failed:"[Owner: Found='root' Expected='grid',Group:Found='root' Explected='asmadmin',Permissions:Found='0600'
Expected='0660']"
-Cause: Found incorrect attributes for the specified device.
-Action: Ensure that the device attributes are set correctly. See Configurable Dynamic Device Naming documentation (udev) for futher information.

Solution: Ignore this and proceeds with installation


================================================================================================================
PRVF-4007 : User equivalence check failed

Solution: run sshUserSetup.sh for all users & nodes form RAC1 present in Grid Software Location:  

[grid@rac1 grid]$
[grid@rac1 grid]$ ls
install      response  runcluvfy.sh  sshsetup  welcome.html
readme.html  rpm       runInstaller  stage
[grid@rac1 grid]$ cd sshsetup/
[grid@rac1 sshsetup]$ ll
total 32
-rwxrwxr-x 1 grid oinstall 32343 Aug 26  2013 sshUserSetup.sh
[grid@rac1 sshsetup]$
[grid@rac1 sshsetup]$ ./sshUserSetup.sh -user grid -hosts "rac1 rac2" -noPromptPassphrase
The output of this script is also logged into /tmp/sshUserSetup_2018-07-02-16-27-50.log
Hosts are rac1 rac2
user is grid
Platform:- Linux
Checking if the remote hosts are reachable
PING rac1.dell.com (192.168.1.11) 56(84) bytes of data.
64 bytes from rac1.dell.com (192.168.1.11): icmp_seq=1 ttl=64 time=0.008 ms
64 bytes from rac1.dell.com (192.168.1.11): icmp_seq=2 ttl=64 time=0.034 ms
64 bytes from rac1.dell.com (192.168.1.11): icmp_seq=3 ttl=64 time=0.032 ms
64 bytes from rac1.dell.com (192.168.1.11): icmp_seq=4 ttl=64 time=0.032 ms
64 bytes from rac1.dell.com (192.168.1.11): icmp_seq=5 ttl=64 time=0.032 ms

--- rac1.dell.com ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4001ms
rtt min/avg/max/mdev = 0.008/0.027/0.034/0.011 ms
PING rac2.dell.com (192.168.1.12) 56(84) bytes of data.
64 bytes from rac2.dell.com (192.168.1.12): icmp_seq=1 ttl=64 time=0.270 ms
64 bytes from rac2.dell.com (192.168.1.12): icmp_seq=2 ttl=64 time=0.250 ms
64 bytes from rac2.dell.com (192.168.1.12): icmp_seq=3 ttl=64 time=0.236 ms
64 bytes from rac2.dell.com (192.168.1.12): icmp_seq=4 ttl=64 time=0.252 ms
64 bytes from rac2.dell.com (192.168.1.12): icmp_seq=5 ttl=64 time=0.266 ms

--- rac2.dell.com ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4002ms
rtt min/avg/max/mdev = 0.236/0.254/0.270/0.023 ms
Remote host reachability check succeeded.
The following hosts are reachable: rac1 rac2.
The following hosts are not reachable: .
All hosts are reachable. Proceeding further...
firsthost rac1
numhosts 2
The script will setup SSH connectivity from the host rac1.dell.com to all
the remote hosts. After the script is executed, the user can use SSH to run
commands on the remote hosts or copy files between this host rac1.dell.com
and the remote hosts without being prompted for passwords or confirmations.

NOTE 1:
As part of the setup procedure, this script will use ssh and scp to copy
files between the local host and the remote hosts. Since the script does not
store passwords, you may be prompted for the passwords during the execution of
the script whenever ssh or scp is invoked.

NOTE 2:
AS PER SSH REQUIREMENTS, THIS SCRIPT WILL SECURE THE USER HOME DIRECTORY
AND THE .ssh DIRECTORY BY REVOKING GROUP AND WORLD WRITE PRIVILEDGES TO THESE
directories.

Do you want to continue and let the script make the above mentioned changes (yes/no)?
yes

The user chose yes
User chose to skip passphrase related questions.
Creating .ssh directory on local host, if not present already
Creating authorized_keys file on local host
Changing permissions on authorized_keys to 644 on local host
Creating known_hosts file on local host
Changing permissions on known_hosts to 644 on local host
Creating config file on local host
If a config file exists already at /home/grid/.ssh/config, it would be backed up to /home/grid/.ssh/config.backup.
Removing old private/public keys on local host
Running SSH keygen on local host with empty passphrase
Generating public/private rsa key pair.
Your identification has been saved in /home/grid/.ssh/id_rsa.
Your public key has been saved in /home/grid/.ssh/id_rsa.pub.
The key fingerprint is:
e4:96:95:8e:f5:e1:59:13:e3:c0:3b:0b:65:9c:c5:ad grid@rac1.dell.com
Creating .ssh directory and setting permissions on remote host rac1
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR grid. THIS IS AN SSH REQUIREMENT.
The script would create ~grid/.ssh/config file on remote host rac1. If a config file exists already at ~grid/.ssh/config, it would be backed up to ~grid/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host rac1.
Warning: Permanently added 'rac1,192.168.1.11' (RSA) to the list of known hosts.
grid@rac1's password:grid
Done with creating .ssh directory and setting permissions on remote host rac1.
Creating .ssh directory and setting permissions on remote host rac2
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR grid. THIS IS AN SSH REQUIREMENT.
The script would create ~grid/.ssh/config file on remote host rac2. If a config file exists already at ~grid/.ssh/config, it would be backed up to ~grid/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host rac2.
Warning: Permanently added 'rac2,192.168.1.12' (RSA) to the list of known hosts.
grid@rac2's password:grid
Done with creating .ssh directory and setting permissions on remote host rac2.
Copying local host public key to the remote host rac1
The user may be prompted for a password or passphrase here since the script would be using SCP for host rac1.
grid@rac1's password:grid
Done copying local host public key to the remote host rac1
Copying local host public key to the remote host rac2
The user may be prompted for a password or passphrase here since the script would be using SCP for host rac2.
grid@rac2's password:grid
Done copying local host public key to the remote host rac2
cat: /home/grid/.ssh/known_hosts.tmp: No such file or directory
cat: /home/grid/.ssh/authorized_keys.tmp: No such file or directory
SSH setup is complete.

------------------------------------------------------------------------
Verifying SSH setup
===================
The script will now run the date command on the remote nodes using ssh
to verify if ssh is setup correctly. IF THE SETUP IS CORRECTLY SETUP,
THERE SHOULD BE NO OUTPUT OTHER THAN THE DATE AND SSH SHOULD NOT ASK FOR
PASSWORDS. If you see any output other than date or are prompted for the
password, ssh is not setup correctly and you will need to resolve the
issue and set up ssh again.
The possible causes for failure could be:
1. The server settings in /etc/ssh/sshd_config file do not allow ssh
for user grid.
2. The server may have disabled public key based authentication.
3. The client public key on the server may be outdated.
4. ~grid or ~grid/.ssh on the remote host may not be owned by grid.
5. User may not have passed -shared option for shared remote users or
may be passing the -shared option for non-shared remote users.
6. If there is output in addition to the date, but no password is asked,
it may be a security alert shown as part of company policy. Append the
additional text to the <OMS HOME>/sysman/prov/resources/ignoreMessages.txt file.
------------------------------------------------------------------------
--rac1:--
Running /usr/bin/ssh -x -l grid rac1 date to verify SSH connectivity has been setup from local host to rac1.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Mon Jul  2 16:28:12 AST 2018
------------------------------------------------------------------------
--rac2:--
Running /usr/bin/ssh -x -l grid rac2 date to verify SSH connectivity has been setup from local host to rac2.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Mon Jul  2 16:28:12 AST 2018
------------------------------------------------------------------------
SSH verification complete.

[grid@rac1 sshsetup]$

You should now be able to SSH and SCP between servers without entering passwords.
Follow the same for ORACLE User also:

[grid@rac1 sshsetup]$ ./sshUserSetup.sh -user oracle -hosts "rac1 rac2" -noPromptPassphrase


                       
Thanks for Reading.

Suggested Topics :

Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)