Month: March 2020

Unconfigure Oracle 18c Clusterware Without Removing Binaries

We might encounter an error on one or more cluster nodes during installation when running the root.sh command, such as a missing operating system package on one node. By running “rootcrs.sh -deconfig -force” on nodes where you encounter an installation error, you can unconfigure Oracle Clusterware on those nodes, correct the cause of the error, and then run root.sh again.

[root@racnode1 install]# cd /u01/app/18.0.0/grid/crs/install
[root@racnode1 install]# ./rootcrs.sh -deconfig -force
Using configuration parameter file: /u01/app/18.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u01/app/grid/crsdata/racnode1/crsconfig/crsdeconfig_racnode1_2020-03-12_09-58-18PM.log
PRCR-1070 : Failed to check if resource ora.net1.network is registered
CRS-0184 : Cannot communicate with the CRS daemon.
PRCR-1070 : Failed to check if resource ora.helper is registered
CRS-0184 : Cannot communicate with the CRS daemon.
PRCR-1070 : Failed to check if resource ora.ons is registered
CRS-0184 : Cannot communicate with the CRS daemon.

2020/03/12 21:58:25 CLSRSC-180: An error occurred while executing the command '/u01/app/18.0.0/grid/bin/srvctl config nodeapps'
2020/03/12 21:58:36 CLSRSC-336: Successfully deconfigured Oracle Clusterware stack on this node
[root@racnode1 install]# ls -ltr /u01/app/grid/crsdata/racnode1/crsconfig/crsdeconfig_racnode1_2020-03-12_09-58-18PM.log
-rw-rw-r-- 1 grid oinstall 45152 Mar 12 21:58 /u01/app/grid/crsdata/racnode1/crsconfig/crsdeconfig_racnode1_2020-03-12_09-58-18PM.log
[root@racnode1 install]#

crsdeconfig_racnode1_2020-03-12_09-58-18PM.log

Install Oracle Database 18c Using RPM Packages

From Oracle Database 18c, we can install below Oracle software using RPM packages.

  • a single-instance Oracle Database.
  • an Oracle Database Instant Client software.

After software is installed, Oracle database can be created by:

  • Database Configuration Assistant (Oracle DBCA).
  • /etc/init.d/oracledb_ORCLCDB-18c service configuration script.

Restrictions and Guidelines for RPM-Based Installations.

  • Patching is still to use the OPatch utility.
  • Standard Edition 2 has no RPM-based Oracle Database installation available for.
  • Upgrades using rpm -Uvh is not supported. For Oracle Database upgrades, follow the regular upgrade process.
  • An RPM-based installation supports the installation of multiple Oracle Database software versions into different Oracle homes on the same machine.

Installing Oracle Database RPM using YUM

1)Log in as root.
2)If you are using Oracle Linux and have subscribed to Unbreakable Linux Network (ULN), you can install Oracle Database via a single yum command.

# yum -y install oracle-database-ee-18c

This command downloads and installs the Oracle Preinstallation RPM and the Oracle Database RPM packages.

Installing Oracle Database RPM Manually

1)Log in as root.
2)Download and install the Oracle Preinstallation RPM:

  1. On Oracle Linux.
    # yum -y install oracle-database-preinstall-18c 
    [root@oemnode1 ~]# rpm -qa | grep oracle-database-preinstall-18 oracle-database-preinstall-18c-1.0-1.el7.x86_64
  2. On Red Hat Enterprise Linux.
    # curl -o oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm 

    # yum -y localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

    After successful installation, you can delete the downloaded RPM file:

    # rm oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

3)Download oracle-database-ee-18c-1.0-1.x86_64.rpm to the /tmp directory.

4) Install the database software using the yum localinstall command.

#chown oracle:oinstll /opt/oracle 
#cd /tmp
[root@oemnode1 tmp]# yum -y localinstall oracle-database-ee-18c-1.0-1.x86_64.rpm Loaded plugins: ulninfo Examining oracle-database-ee-18c-1.0-1.x86_64.rpm: oracle-database-ee-18c-1.0-1.x86_64 Marking oracle-database-ee-18c-1.0-1.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package oracle-database-ee-18c.x86_64 0:1.0-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================================================================================================================================================= Package Arch Version Repository Size ======================================================================================================== Installing: oracle-database-ee-18c x86_64 1.0-1 /oracle-database-ee-18c-1.0-1.x86_64 7.8 G Transaction Summary ======================================================================================================== Install 1 Package Total size: 7.8 G Installed size: 7.8 G Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : oracle-database-ee-18c-1.0-1.x86_64 1/1 [INFO] Executing post installation scripts... [INFO] Oracle home installed successfully and ready to be configured. To configure a sample Oracle Database you can execute the following service configuration script as root: /etc/init.d/oracledb_ORCLCDB-18c configure Verifying : oracle-database-ee-18c-1.0-1.x86_64 1/1 Installed: oracle-database-ee-18c.x86_64 0:1.0-1 Complete!

Creating and Configuring an Oracle Database

  1. Tried to change ORACLE_SID, and PDB_NAME into whatever you  want in  configuration  file  “/etc/init.d/oracledb_ORCLCDB-18c”. But it did not work when creating database in the next step, so we have to use the default ones.

2. To create and configure a sample Oracle Database instance, run the following service configuration script:

[root@oemnode1 init.d]# /etc/init.d/oracledb_ORCLCDB-18c configure
Configuring Oracle Database ORCLCDB.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/ORCLCDB.
Database Information:
Global Database Name:ORCLCDB
System Identifier(SID):ORCLCDB
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" 
for further details.

Database configuration completed successfully. The passwords were auto 
generated, you must change them by connecting to the database using 
'sqlplus / as sysdba' as the oracle user.
[root@oemnode1 init.d]#

Removing RPM-based Oracle Database Installation

1)Drop instance, and listener configurations.

2)Run following command to remove the database installation by RPM.

[root@oemnode1 ContentsXML]# rpm -qa | grep 18c
oracle-database-preinstall-18c-1.0-1.el7.x86_64
oracle-database-ee-18c-1.0-1.x86_64
[root@oemnode1 ~]#  /etc/rc.d/init.d/oracledb_ORCLCDB-18c  -help
Usage: /etc/rc.d/init.d/oracledb_ORCLCDB-18c {start|stop|restart|configure}

[root@oemnode1 ~]#  /etc/rc.d/init.d/oracledb_ORCLCDB-18c stop
Shutting down Oracle Database instance ORCLCDB.
Oracle Database instance ORCLCDB shut down.
Stopping Oracle Net Listener.
Oracle Net Listener stopped.


[root@oemnode1 oracle]# yum -y remove oracle-database-ee-18c
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-ee-18c.x86_64 0:1.0-1 will be erased
--> Finished Dependency Resolution
Dependencies Resolved
=========================================================================
 Package                                                       Arch                                          Version                                      Repository                                                                    Size
=========================================================================
 oracle-database-ee-18c                                        x86_64                                        1.0-1                                        @/oracle-database-ee-18c-1.0-1.x86_64                                        7.8 G

Transaction Summary
==========================================================================
Remove  1 Package

Installed size: 7.8 G
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
[SEVERE] Listeners or databases are present in the system, remove them before proceeding with the deinstallation
error: %preun(oracle-database-ee-18c-1.0-1.x86_64) scriptlet failed, exit status 1
Error in PREUN scriptlet in rpm package oracle-database-ee-18c-1.0-1.x86_64
  Verifying  : oracle-database-ee-18c-1.0-1.x86_64                                                                                                                                                                                     1/1

Failed:
  oracle-database-ee-18c.x86_64 0:1.0-1

Complete!
[root@oemnode1 oracle]

If get above errors, please check :

a) There is no ORACLE_HOME entry in /etc/oratab.

b) There is no files under $ORACLE_HOME/dbs.

c) There is no files under $ORACLE_HOME/network/admin.

[root@oemnode1 ORCLCDB]# yum -y remove oracle-database-ee-18c
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-ee-18c.x86_64 0:1.0-1 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================
Removing:
oracle-database-ee-18c x86_64 1.0-1 @/oracle-database-ee-18c-1.0-1.x86_64 7.8 G

Transaction Summary
=============================================================================================================================================================================================================================================
Remove 1 Package

Installed size: 7.8 G
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Erasing : oracle-database-ee-18c-1.0-1.x86_64 1/1
warning: file /opt/oracle/product/18c/dbhome_1/network/admin/shrept.lst: remove failed: No such file or directory
warning: file /opt/oracle/product/18c/dbhome_1/network/admin/samples/tnsnames.ora: remove failed: No such file or directory
warning: file /opt/oracle/product/18c/dbhome_1/network/admin/samples/sqlnet.ora: remove failed: No such file or directory
warning: file /opt/oracle/product/18c/dbhome_1/network/admin/samples/listener.ora: remove failed: No such file or directory
warning: file /opt/oracle/product/18c/dbhome_1/network/admin/samples: remove failed: No such file or directory
warning: file /opt/oracle/product/18c/dbhome_1/dbs/init.ora: remove failed: No such file or directory
warning: file /etc/init.d/oracledb_ORCLCDB-18c: remove failed: No such file or directory
Verifying : oracle-database-ee-18c-1.0-1.x86_64 1/1

Removed:
oracle-database-ee-18c.x86_64 0:1.0-1

Complete!

Deinstall 12cR1 Oracle Database Software

[oracle@racnode1 ~]$ cd $ORACLE_HOME/deinstall
[oracle@racnode1 deinstall]$ ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /u01/app/oraInventory/logs/

############ ORACLE DECONFIG TOOL START ############


######################### DECONFIG CHECK OPERATION START #########################
## [START] Install check configuration ##


Checking for existence of the Oracle home location /u01/app/oracle/product/12.1.0/dbhome_1
Oracle Home type selected for deinstall is: Oracle Real Application Cluster Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
Checking for existence of the Oracle Grid Infrastructure home /u01/app/18.0.0/grid
The following nodes are part of this cluster: racnode1
Checking for sufficient temp space availability on node(s) : 'racnode1'

## [END] Install check configuration ##


Network Configuration check config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check2020-03-12_05-38-36-PM.log

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check2020-03-12_05-38-37-PM.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home [RACTEST1,RACTEST]:

###### For Database 'RACTEST1' ######

Specify the type of this database (1.Single Instance Database|2.Oracle Restart Enabled Database|3.RAC Database|4.RAC One Node Database) [1]: 3
Specify the list of nodes on which this database has instances [racnode1]:
Specify the list of instance names [RACTEST1]:
Specify the local instance name on node [RACTEST1]:
Specify the diagnostic destination location of the database [/u01/app/oracle/diag/rdbms/ractest1]:
Specify the storage type used by the Database ASM|FS []: ASM

###### For Database 'RACTEST' ######

Specify the type of this database (1.Single Instance Database|2.Oracle Restart Enabled Database|3.RAC Database|4.RAC One Node Database) [1]: 3
Specify the list of nodes on which this database has instances [racnode1]:
Specify the list of instance names [RACTEST]:
Specify the local instance name on node [RACTEST]:
Specify the diagnostic destination location of the database [/u01/app/oracle/diag/rdbms/ractest]:
Specify the storage type used by the Database ASM|FS []: ASM


Database Check Configuration END
Oracle Configuration Manager check START
OCM check log file location : /u01/app/oraInventory/logs//ocm_check7431.log
Oracle Configuration Manager check END

######################### DECONFIG CHECK OPERATION END #########################


####################### DECONFIG CHECK OPERATION SUMMARY #######################
Oracle Grid Infrastructure Home is: /u01/app/18.0.0/grid
The following nodes are part of this cluster: racnode1

WARNING !
Deinstall utility has detected that Oracle Clusterware processes are not running on the local node, 
hence entries of Real Application Cluster Databases that are being deinstalled will not be removed from Oracle Cluster Registry (OCR). 
For complete deinstallation of Real Application Cluster Databases, it is recommended to run the deinstall utility with Oracle Clusterware running.

The cluster node(s) on which the Oracle home deinstallation will be performed are:racnode1
Oracle Home selected for deinstall is: /u01/app/oracle/product/12.1.0/dbhome_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
The following databases were selected for de-configuration : RACTEST1,RACTEST
Database unique name : RACTEST1
Storage used : ASM
Database unique name : RACTEST
Storage used : ASM
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2020-03-12_05-38-34-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2020-03-12_05-38-34-PM.err'

######################## DECONFIG CLEAN OPERATION START ########################
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_clean2020-03-12_05-41-05-PM.log
Database Clean Configuration START RACTEST1
This operation may take few minutes.
Database Clean Configuration END RACTEST1
Database Clean Configuration START RACTEST
This operation may take few minutes.
Database Clean Configuration END RACTEST

Network Configuration clean config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_clean2020-03-12_05-41-46-PM.log

De-configuring Listener configuration file on all nodes...
Listener configuration file de-configured successfully.

De-configuring Naming Methods configuration file on all nodes...
Naming Methods configuration file de-configured successfully.

De-configuring Local Net Service Names configuration file on all nodes...
Local Net Service Names configuration file de-configured successfully.

De-configuring Directory Usage configuration file on all nodes...
Directory Usage configuration file de-configured successfully.

De-configuring backup files on all nodes...
Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START
OCM clean log file location : /u01/app/oraInventory/logs//ocm_clean7431.log
Oracle Configuration Manager clean END

######################### DECONFIG CLEAN OPERATION END #########################


####################### DECONFIG CLEAN OPERATION SUMMARY #######################
Successfully de-configured the following database instances : RACTEST1,RACTEST
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
#######################################################################


############# ORACLE DECONFIG TOOL END #############

Using properties file /tmp/deinstall2020-03-12_05-38-26PM/response/deinstall_2020-03-12_05-38-34-PM.rsp
Location of logs /u01/app/oraInventory/logs/

############ ORACLE DEINSTALL TOOL START ############


####################### DEINSTALL CHECK OPERATION SUMMARY #######################
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2020-03-12_05-38-34-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2020-03-12_05-38-34-PM.err'

######################## DEINSTALL CLEAN OPERATION START ########################
## [START] Preparing for Deinstall ##
Setting LOCAL_NODE to racnode1
Setting CLUSTER_NODES to racnode1
Setting CRS_HOME to false
Setting oracle.installer.invPtrLoc to /tmp/deinstall2020-03-12_05-38-26PM/oraInst.loc
Setting oracle.installer.local to false

## [END] Preparing for Deinstall ##

Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START

Detach Oracle home '/u01/app/oracle/product/12.1.0/dbhome_1' from the central inventory on the local node : Done

Delete directory '/u01/app/oracle/product/12.1.0/dbhome_1' on the local node : Done

The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/app/oracle/product/12.2.0/dbhome_1'.

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END


## [START] Oracle install clean ##

Clean install operation removing temporary directory '/tmp/deinstall2020-03-12_05-38-26PM' on node 'racnode1'

## [END] Oracle install clean ##


######################### DEINSTALL CLEAN OPERATION END #########################


####################### DEINSTALL CLEAN OPERATION SUMMARY #######################
Successfully detached Oracle home '/u01/app/oracle/product/12.1.0/dbhome_1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/12.1.0/dbhome_1' on the local node.
Oracle Universal Installer cleanup was successful.

Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################


############# ORACLE DEINSTALL TOOL END #############

Check and Confirm Inventory Updated.
[oracle@racnode1 app]$ ls -ltr /u01/app/oracle/product/12.1.0
total 0
[oracle@racnode1 app]$
[oracle@racnode1 ContentsXML]$ cat inventory.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2014, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>12.1.0.2.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDB12Home2" LOC="/u01/app/oracle/product/12.2.0/dbhome_1" TYPE="O" IDX="4"/>
<HOME NAME="OraGI18Home1" LOC="/u01/app/18.0.0/grid" TYPE="O" IDX="5" CRS="true"/>
<HOME NAME="OraGI12Home1" LOC="/u01/app/12.1.0/grid" TYPE="O" IDX="1" REMOVED="T"/>
<HOME NAME="OraGI12Home2" LOC="/u01/app/12.2.0.1/grid" TYPE="O" IDX="3" REMOVED="T"/>
<HOME NAME="OraDB12Home1" LOC="/u01/app/oracle/product/12.1.0/dbhome_1" TYPE="O" IDX="2" REMOVED="T"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
[oracle@racnode1 ContentsXML]$

How to Set SQL or Oracle Database to Single-User Mode and How to Check Single User or Multi User Mode

In Oracle database, place the instance in restricted mode.  The users already connected to the database will not get disconnected. You need to manually kill these sessions.

SQL> alter system enable restricted session;

OR

Startup in restricted mode.

SQL> startup restrict;

Check the instance mode

SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

In SQL database,  Below are the two most popular ways to set up database to single-user mode in SQL Server:

Using SQL Server Management Studio

Right-click the database to change, and then click Properties -> click the Options page->From the Restrict Access option, select Single.
If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

Using Transact-SQL

USE master;
GO
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Check SQL Database Single User or Multi User Mode

a)  Select ServerProperty(‘IsSingleUser’)

It should return 1, 0, or Null.

1 = Single user.
0 = Not single user
NULL = Input is not valid, or an error.

use MyDatabase
go
Select ServerProperty('IsSingleUser')

b) Select from sys.database,  it should retuen:

  • SINGLE_USER
  • MULTI_USER
SELECT user_access_desc 
FROM sys.databases 
WHERE name = 'MyDatabase'

3) From SSMS, Right-click the database , and then click Properties -> click the Options page->From the Restrict Access option.