Friday, July 1, 2016

Moving Role Separated Oracle Restart Setup to Single User Setup

Oracle role separation setup involves installing the grid infrastructure and oracle database software as different users. Commonly GI is installed as grid user and oracle database software as oracle user. On rare occasions this role separation could result in behavior that is not experienced when both GI and Oracle DB is installed using a single user. Issues with secure password store and external tables are just two examples.
This post shows steps to moving from a role separated Oracle restart setup to a single user setup.
The way to do this is to remove the GI software installed with grid user and reinstall it with oracle user. However it is not possible to remove the the existing GI home while oracle database home is configured on it.
[grid@rhel7 deinstall]$ ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /opt/app/oraInventory/logs/
...
Traces log file: /opt/app/oraInventory/logs//crsdc_2016-02-24_07-28-15PM.log
ERROR: Can't deconfigure Oracle Restart before removing or downgrading managed Oracle databases.
So the option is to manually remove the grid home. This oracle restart environment used here is 12.1.0.2 on RHEL 7
1. Create a pfile from the ASM spfile
SQL> create pfile='/home/grid/asmpfile.ora' from spfile;

[grid@rhel7 ~]$ more asmpfile.ora
+ASM.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from in memory value
+ASM.asm_diskgroups='FRA'#Manual Mount
*.asm_diskstring='/dev/sd*'
*.asm_power_limit=1
*.large_pool_size=12M
*.remote_login_passwordfile='EXCLUSIVE'
2. Backup the oratab file

3. Stop the HAS service
crsctl stop has
4. As root user, delete the GI home installed with grid user
[root@rhel7 12.1.0]# pwd
/opt/app/oracle/product/12.1.0
[root@rhel7 12.1.0]# rm -rf grid
5. Remove other HAS related files
cd /etc
rm -rf ora*
find . -name *ohasd -exec rm {} \;

rm /etc/systemd/system/oracle-ohasd.service
rm /etc/init.d/*ohasd
rm -rf /opt/ORCLfmap
rm -rf /var/tmp/.oracle
Removing of "/var/tmp/.oracle" is important without removing it, trying to reinstall will fail. More on 1997268.1
2016/02/24 20:04:38 CLSRSC-318: Failed to start Oracle OHASD service
Died at /opt/app/oracle/product/12.1.0/grid_2/crs/install/crsinstall.pm line 3041.
6. Backup the existing oraInventory and create a new oraInventory directory as oracle user
drwxrwx---.  6 grid   oinstall   97 Feb  5 19:38 oraInventory

# mv oraInventory oraInventorybak
# mkdir oraInventory
# chown oracle:oinstall oraInventory
7. Change udev file so that owner is the oracle user
OWNER="oracle", GROUP="asmadmin", MODE="0660"
8. Remove grid user owned GI related folders from ORACLE_BASE
 cd $ORACLE_BASE
# ls -l
drwxrwx---.  4 grid   oinstall   29 Sep 29 16:58 admin
drwxr-x---.  3 oracle oinstall   18 Sep 29 17:26 audit
drwxrwxr-x.  6 grid   oinstall   56 Sep 30 15:38 cfgtoollogs
drwxr-xr-x.  2 grid   oinstall    6 Sep 29 16:43 checkpoints
drwxrwx---.  4 grid   oinstall   32 Sep 29 16:39 crsdata
drwxr-x---. 19 grid   oinstall 4096 Sep 29 16:38 diag
drwxr-xr-x.  3 grid   oinstall   17 Feb  4 17:44 log
drwxrwxr-x.  3 grid   oinstall   19 Sep 29 16:33 product
drwxr-xr-x.  3 grid   oinstall   24 Sep 29 16:39 rhel7

rm -rf admin/+ASM
rm -rf cfgtoollogs
rm -rf checkpoints
rm -rf crsdata
rm -rf diag
rm -rf log
rm -rf rhel7/

# ls -l

drwxrwx---. 3 grid   oinstall 18 Feb 24 19:39 admin
drwxr-x---. 3 oracle oinstall 18 Sep 29 17:26 audit
drwxrwxr-x. 3 grid   oinstall 19 Sep 29 16:33 product
Change the ownership of the remaining files to oracle user
chown oracle:oinstall product/12.1.0
chown oracle:oinstall product
chown oracle:oinstall admin

# ls -l

drwxrwx---. 3 oracle oinstall 18 Feb 24 19:39 admin
drwxr-x---. 3 oracle oinstall 18 Sep 29 17:26 audit
drwxrwxr-x. 3 oracle oinstall 19 Sep 29 16:33 product
9. Reboot the server. Once restarted verify the permission on the block devices used for ASM
ls -l /dev/sd*
brw-rw----. 1 oracle asmadmin 8, 17 Feb 24 19:42 /dev/sdb1
brw-rw----. 1 oracle asmadmin 8, 33 Feb 24 19:42 /dev/sdc1
10. Add to oracle user asm related os groups. The final group list for oracle user is as below
id oracle
uid=1001(oracle) gid=1001(oinstall) groups=1001(oinstall),1002(dba),1003(oper),1005(asmoper),1006(asmdba),1007(asmadmin),1008(backupdba),1009(dgdba),1010(kmdba)
11. Install the GI software selecting "software only" option.

12. Run the root scripts when prompted
# /opt/app/oraInventory/orainstRoot.sh
Changing permissions of /opt/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /opt/app/oraInventory to oinstall.
The execution of the script is complete.

# /opt/app/oracle/product/12.1.0/grid_2/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /opt/app/oracle/product/12.1.0/grid_2

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

To configure Grid Infrastructure for a Stand-Alone Server run the following command as the root user:
/opt/app/oracle/product/12.1.0/grid_2/perl/bin/perl -I/opt/app/oracle/product/12.1.0/grid_2/perl/lib -I/opt/app/oracle/product/12.1.0/grid_2/crs/install /opt/app/oracle/product/12.1.0/grid_2/crs/install/roothas.pl

To configure Grid Infrastructure for a Cluster execute the following command as oracle user:
/opt/app/oracle/product/12.1.0/grid_2/crs/config/config.sh
This command launches the Grid Infrastructure Configuration Wizard. The wizard also supports silent operation, and the parameters can be passed through the response file that is available in the installation media.
Run the command to configure Oracle restart.
# /opt/app/oracle/product/12.1.0/grid_2/perl/bin/perl -I/opt/app/oracle/product/12.1.0/grid_2/perl/lib -I/opt/app/oracle/product/12.1.0/grid_2/crs/install /opt/app/oracle/product/12.1.0/grid_2/crs/install/roothas.pl
Using configuration parameter file: /opt/app/oracle/product/12.1.0/grid_2/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node rhel7 successfully pinned.
2016/03/01 18:39:59 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'

rhel7     2016/03/01 18:40:32     /opt/app/oracle/product/12.1.0/grid_2/cdata/rhel7/backup_20160301_184032.olr     0
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rhel7'
CRS-2673: Attempting to stop 'ora.evmd' on 'rhel7'
CRS-2677: Stop of 'ora.evmd' on 'rhel7' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rhel7' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2016/03/01 18:40:49 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ons
               OFFLINE OFFLINE      rhel7                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       rhel7                    STABLE
--------------------------------------------------------------------------------


13. Add ASM home entry to the /etc/oratab file
+ASM:/opt/app/oracle/product/12.1.0/grid_2:N
14. Add listener and ASM to HAS configuration
$ srvctl add listener -l listener -o /opt/app/oracle/product/12.1.0/grid_2 -p 1521
$ srvctl start listener -l listener
$ srvctl status listener -l listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): rhel7

$ srvctl add asm -l listener  -d "/dev/sd*"
$ srvctl start asm
$ srvctl config asm
ASM home: 
Password file:
ASM listener: LISTENER
Spfile:
ASM diskgroup discovery string: /dev/sd*

[oracle@rhel7 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       rhel7                    STABLE
ora.asm
               ONLINE  ONLINE       rhel7                    Started,STABLE
ora.ons
               OFFLINE OFFLINE      rhel7                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       rhel7                    STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       rhel7                    STABLE
--------------------------------------------------------------------------------
15. Modify the inventory for GI Home to include crs=true. The inventory.xml content before and after the modification
<HOME_LIST>
<HOME NAME="OraGI12Home1" LOC="/opt/app/oracle/product/12.1.0/grid_2" TYPE="O" IDX="1"/>
</HOME_LIST>

./runInstaller -updateNodeList ORACLE_HOME=/opt/app/oracle/product/12.1.0/grid_2 CRS=TRUE -silent

<HOME_LIST>
<HOME NAME="OraGI12Home1" LOC="/opt/app/oracle/product/12.1.0/grid_2" TYPE="O" IDX="1" CRS="true"/>
</HOME_LIST>
16. Patch the GI home to the same level as the Oracle database home.

17. Attach the Oracle database home
./runInstaller -silent -attachHome ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME="oracle_home"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4097 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'AttachHome' was successful.
Inventory content before and after oracle home attached
Before 
<HOME_LIST>
<HOME NAME="OraGI12Home1" LOC="/opt/app/oracle/product/12.1.0/grid" TYPE="O" IDX="1" CRS="true"/>
</HOME_LIST>

After
<HOME_LIST>
<HOME NAME="OraGI12Home1" LOC="/opt/app/oracle/product/12.1.0/grid_2" TYPE="O" IDX="1" CRS="true"/>
<HOME NAME="oracle_home" LOC="/opt/app/oracle/product/12.1.0/dbhome_2" TYPE="O" IDX="2"/>
</HOME_LIST>
18. Mount the ASM disk groups
SQL>  select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
FRA                            DISMOUNTED
DATA                           DISMOUNTED

alter diskgroup data mount;
alter diskgroup fra mount;
19. If does not exists create spfile alias and add the database to HAS configuration. Also db entry to oratab file.
mkalias parameterfile/spfile.266.891711477 spfilese2db.ora

srvctl add database -d se2db -o /opt/app/oracle/product/12.1.0/dbhome_2 -p +DATA/se2db/spfilese2db.ora -a "data,fra"
srvctl start database -d se2db
20. Add auto start options to the components
crsctl modify resource ora.DATA.dg -attr "AUTO_START"="always" -unsupported
crsctl modify resource ora.FRA.dg -attr "AUTO_START"="always"    -unsupported
crsctl modify resource ora.LISTENER.lsnr -attr "AUTO_START"="always" -unsupported
crsctl modify resource ora.se2db.db -attr "AUTO_START"="always"  -unsupported
21. The HAS stack should be now up and running
$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rhel7                    STABLE
ora.FRA.dg
               ONLINE  ONLINE       rhel7                    STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       rhel7                    STABLE
ora.asm
               ONLINE  ONLINE       rhel7                    Started,STABLE
ora.ons
               OFFLINE OFFLINE      rhel7                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       rhel7                    STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       rhel7                    STABLE
ora.se2db.db
      1        ONLINE  ONLINE       rhel7                    Open,STABLE
--------------------------------------------------------------------------------
This concludes the steps for moving a role separated oracle restart setup to a single user setup.

Useful metalink notes
Oracle Restart: 12.1 grid installation root.sh fails while starting ohasd [ID 1997268.1]

Related Posts
Recover From a Clusterware Home Deletion
Moving non-RAC Database and ASM Between Servers
Changing The Cluster Name