Database Mode - Restrict Vs Exclusive & Drop database - Standalone Vs RAC
High-level steps to drop RAC Database:
==============================
$ORACLE_HOME/bin/srvctl status database -d <DBNAME>
$ORACLE_HOME/bin/srvctl stop database -d <DBNAME>
sqlplus / as sysdba
startup; (open mode) --- Unbale drop DB
startup mount; --- Unbale drop DB
startup mount exclusive --- Unbale drop DB
startup mount restrict --- Unbale drop DB
startup mount restrict exclusive; --- Unbale drop DB
show parameter cluster_database;
alter system set cluster_database=false scope=spfile;
shutdown;
startup mount exclusive restrict; --- Able to drop my database
drop database;
High-level steps to drop Standalone Database:
=================================
shutdown;
startup; (open mode) --- Unbale drop DB
startup mount; --- Unbale drop DB
startup mount exclusive --- Unbale drop DB
startup mount restrict --- Able to drop my database
startup mount exclusive restrict; --- Able to drop my database
drop database;
Actual logs of Standalone database drop:
===================================
[oracle@node1 ~]$ ps -ef|grep smon
oracle 14678 1 0 14:54 ? 00:00:00 ora_smon_ORA19C
root 16277 1 1 Nov02 ? 06:37:35 /u01/app/19.0.0.0/grid/bin/osysmond.bin
oracle 17721 1 0 Nov02 ? 00:01:18 ora_smon_RAC12C1
oracle 17735 1 0 Nov02 ? 00:01:18 ora_smon_CDBDB1
oraprod 18146 1 0 Nov02 ? 00:01:22 ora_smon_ORAPROD1
oracle 20401 13581 0 15:08 pts/3 00:00:00 grep --color=auto smon
oraprod 25407 1 0 Nov15 ? 00:00:10 ora_smon_SINGLE
[oracle@node1 ~]$ . oraenv
ORACLE_SID = [ORA19C] ? ORA19C
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@node1 ~]$ srvctl status database -d ORA19C
Instance ORA19C is running on node node1
[oracle@node1 ~]$ srvctl config database -d ORA19C
Database unique name: ORA19C
Database name: ORA19C
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/rbrkspfileORA19C.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: SINGLE
OSDBA group: oinstall
OSOPER group: oinstall
Database instance: ORA19C
Configured nodes: node1
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 22 15:10:42 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORA19C READ WRITE
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORA19C OPEN
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@node1 ~]$ srvctl stop database -d ORA19C
[oracle@node1 ~]$ srvctl status database -d ORA19C
Instance ORA19C is not running on node node1
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 22 15:13:39 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount exclusive;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 3070227808 bytes
Fixed Size 9139552 bytes
Variable Size 1358954496 bytes
Database Buffers 1694498816 bytes
Redo Buffers 7634944 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 3070227808 bytes
Fixed Size 9139552 bytes
Variable Size 1358954496 bytes
Database Buffers 1694498816 bytes
Redo Buffers 7634944 bytes
Database mounted.
SQL> drop database;
Database dropped.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> exit
[oracle@node1 ~]$
Actual logs of RAC database drop:
============================
[root@node1 ~]# su - oracle
Last login: Mon Nov 22 14:45:05 IST 2021 on pts/1
[oracle@node1 ~]$ clear
[oracle@node1 ~]$ ps -ef|grep smon
oracle 14678 1 0 14:54 ? 00:00:00 ora_smon_ORA19C
root 16277 1 1 Nov02 ? 06:37:34 /u01/app/19.0.0.0/grid/bin/osysmond.bin
oracle 17721 1 0 Nov02 ? 00:01:18 ora_smon_RAC12C1
oracle 17735 1 0 Nov02 ? 00:01:18 ora_smon_CDBDB1
oraprod 18146 1 0 Nov02 ? 00:01:22 ora_smon_ORAPROD1
oracle 20248 13548 0 15:07 pts/1 00:00:00 grep --color=auto smon
oraprod 25407 1 0 Nov15 ? 00:00:10 ora_smon_SINGLE
[oracle@node1 ~]$ . oraenv
ORACLE_SID = [oracle] ? CDBDB1
The Oracle base has been set to /u01/app/oracle
[oracle@node1 ~]$ srvctl status database -d CDBDB
Instance CDBDB1 is running on node node1
Instance CDBDB2 is running on node node2
[oracle@node1 ~]$ srvctl config database -d CDBDB
Database unique name: CDBDB
Database name: CDBDB
Oracle home: /u01/app/oracle/product/12.2.0.1/dbhome_1
Oracle user: oracle
Spfile: +DATA/CDBDB/PARAMETERFILE/spfile.334.1067561253
Password file: +DATA/CDBDB/PASSWORD/pwdcdbdb.289.1067560793
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group: oinstall
Database instances: CDBDB1,CDBDB2
Configured nodes: node1,node2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 22 15:11:19 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
CDBDB READ WRITE
SQL> select name, open_mode from gv$database;
NAME OPEN_MODE
--------- --------------------
CDBDB READ WRITE
CDBDB READ WRITE
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
CDBDB1 OPEN
SQL> select instance_name, status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
CDBDB1 OPEN
CDBDB2 OPEN
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation
SQL> startup mount restrict;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict;
ORACLE instance started.
Total System Global Area 3070230528 bytes
Fixed Size 8797488 bytes
Variable Size 838861520 bytes
Database Buffers 2214592512 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> SQL>
SQL>
SQL> startup mount restrict exclusive;
ORACLE instance started.
Total System Global Area 3070230528 bytes
Fixed Size 8797488 bytes
Variable Size 838861520 bytes
Database Buffers 2214592512 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation
SQL> show parameter cluster_database;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> shutdown;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict exclusive;
ORACLE instance started.
Total System Global Area 3070230528 bytes
Fixed Size 8797488 bytes
Variable Size 771752656 bytes
Database Buffers 2281701376 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> show parameter cluster_database;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
SQL> drop database;
Database dropped.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
Regards,
Mallik
The quality of the course was outstanding, with trainers ensuring every student received personal attention. I really enjoyed the flexibility of scheduling, which made it possible to balance my job and studies. The course content was up-to-date, covering both fundamental and advanced IT concepts in detail. I gained so much from hands-on projects that simulated real-world tasks. Reaching the halfway stage was rewarding because I could already see the difference. Best Azure Training Courses gave me confidence, skills, and direction.
ReplyDelete