Oracle Application DBA Information

Restart Enable Database OR ADD & Remove Database with Modify in ASM/CRS as Services 12CR1

ADD Database in ASM/Cluster Services for Auto Start & Stop (Reboot)

1- Add Database in OCR from Oracle User

[oracle@oda12 ~]$ hmdb ------------------------- (Environment write in .bash_profile)

[oracle@oda12 ~]$ cd $ORACLE_HOME

[oracle@oda12 db_1]$ pwd

/u01/app/oracle/product/12.1.0/db_1

[oracle@oda12 db_1]$ srvctl add database -d hmdb -o /u01/app/oracle/product/12.1.0/db_1

Note:

When we add database in ASM Services we will use switch “–o”. Its mean you are defining Oracle Home like above and if you perfume database adding step from grid user you will face below mention error.

[grid@oda12 ~]$ srvctl add database -d hmdb -o /u01/app/oracle/product/12.1.0/db_1

PRCD-1025 : Failed to create database hmdb

PRKH-1014 : Current user "grid" is not the oracle owner user "oracle" of oracle home "/u01/app/oracle/product/12.1.0/db_1"

2- Check Status of Database from Grid User

[grid@oda12 ~]$ srvctl status database -db HMDB

Database is not running.

3- Start Database & Check Again Status of Database from Grid User

[grid@oda12 ~]$ srvctl start database -db HMDB

[grid@oda12 ~]$ srvctl status database -db HMDB

Database is running.

[grid@oda12 ~]$ crsctl stat res -t

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.CRS.dg

ONLINE ONLINE oda12 STABLE

ora.DATA.dg

ONLINE ONLINE oda12 STABLE

ora.FRA.dg

ONLINE ONLINE oda12 STABLE

ora.HMDB_LSN.lsnr

ONLINE ONLINE oda12 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE oda12 STABLE

ora.asm

ONLINE ONLINE oda12 Started,STABLE

ora.ons

OFFLINE OFFLINE oda12 STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

1 ONLINE ONLINE oda12 STABLE

ora.diskmon

1 OFFLINE OFFLINE STABLE

ora.evmd

1 ONLINE ONLINE oda12 STABLE

ora.hmdb.db

1 ONLINE ONLINE oda12 Open,STABLE

ora.orcl.db

1 ONLINE ONLINE oda12 Open,STABLE

--------------------------------------------------------------------------------

[grid@oda12 ~]$

[oracle@oda12 db_1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 29 01:47:26 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string hmdb

SQL> select INSTANCE_NAME,STATUS,VERSION from v$INSTANCE;

INSTANCE_NAME STATUS VERSION

---------------- ------------ -----------------

hmdb OPEN 12.1.0.2.0

SQL> col NAME format a70

SQL> select name from v$datafile;

NAME

----------------------------------------------------------------------

+DATA/HMDB/DATAFILE/system.267.1053832677

+DATA/HMDB/DATAFILE/sysaux.268.1053832775

+DATA/HMDB/DATAFILE/undotbs1.269.1053832851

+DATA/HMDB/DATAFILE/users.271.1053832873

SQL>

[root@oda12 ~]# reboot

oracle@192.168.10.10's password:

Last login: Thu Oct 29 01:35:00 2020

[oracle@oda12 ~]$ hmdb

[oracle@oda12 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 29 02:17:28 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string hmdb

SQL> select INSTANCE_NAME,STATUS,VERSION from v$INSTANCE;

INSTANCE_NAME STATUS VERSION

---------------- ------------ -----------------

hmdb OPEN 12.1.0.2.0

SQL> col NAME format a70

SQL> select name from v$datafile;

NAME

----------------------------------------------------------------------

+DATA/HMDB/DATAFILE/system.267.1053832677

+DATA/HMDB/DATAFILE/sysaux.268.1053832775

+DATA/HMDB/DATAFILE/undotbs1.269.1053832851

+DATA/HMDB/DATAFILE/users.271.1053832873

SQL>

Note: Database is Properly Start and Stop.

4- Check Database Configuration from Grid User

[grid@oda12 ~]$ srvctl config database -d hmdb

Database unique name: hmdb

Database name:

Oracle home: /u01/app/oracle/product/12.1.0/db_1

Oracle user: oracle

Spfile:

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups: DATA,FRA

Services:

OSDBA group:

OSOPER group:

Database instance: hmdb

[grid@oda12 ~]$

Remove Database from Cluster Services

1- Check Database Status from Grid User

[grid@oda12 ~]$ srvctl status database -d HMDB

Database is running.

[grid@oda12 ~]$ crsctl stat res -init -t

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.CRS.dg

ONLINE ONLINE oda12 STABLE

ora.DATA.dg

ONLINE ONLINE oda12 STABLE

ora.FRA.dg

ONLINE ONLINE oda12 STABLE

ora.HMDB_LSN.lsnr

ONLINE ONLINE oda12 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE oda12 STABLE

ora.asm

ONLINE ONLINE oda12 Started,STABLE

ora.ons

OFFLINE OFFLINE oda12 STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

1 ONLINE ONLINE oda12 STABLE

ora.diskmon

1 OFFLINE OFFLINE STABLE

ora.evmd

1 ONLINE ONLINE oda12 STABLE

ora.hmdb.db

1 ONLINE ONLINE oda12 Open,STABLE

ora.orcl.db

1 ONLINE ONLINE oda12 Open,STABLE

--------------------------------------------------------------------------------

2- Stop Database from Grid User

[grid@oda12 ~]$ srvctl stop database -db HMDB

3- Remove Database into OCR from Grid User

[grid@oda12 ~]$ srvctl remove database -db HMDB –f

[grid@oda12 ~]$ srvctl remove database -db HMDB

PRKO-3141 : Database HMDB could not be removed because it was running

Note:

If Your Database is running so you will remove your database with switch “–f” for force fully.

4- Check Database Status Again from Grid User

[grid@oda12 ~]$ srvctl status database -db HMDB

PRCD-1120 : The resource for database HMDB could not be found.

PRCR-1001 : Resource ora.hmdb.db does not exist

[grid@oda12 ~]$ crsctl stat res -init -t

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.CRS.dg

ONLINE ONLINE oda12 STABLE

ora.DATA.dg

ONLINE ONLINE oda12 STABLE

ora.FRA.dg

ONLINE ONLINE oda12 STABLE

ora.HMDB_LSN.lsnr

ONLINE ONLINE oda12 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE oda12 STABLE

ora.asm

ONLINE ONLINE oda12 Started,STABLE

ora.ons

OFFLINE OFFLINE oda12 STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

1 ONLINE ONLINE oda12 STABLE

ora.diskmon

1 OFFLINE OFFLINE STABLE

ora.evmd

1 ONLINE ONLINE oda12 STABLE

ora.orcl.db

1 ONLINE ONLINE oda12 Open,STABLE

--------------------------------------------------------------------------------

[oracle@oda12 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 29 03:00:28 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string hmdb

SQL> select INSTANCE_NAME, STATUS, VERSION from v$INSTANCE;

INSTANCE_NAME STATUS VERSION

---------------- ------------ -----------------

hmdb OPEN 12.1.0.2.0

SQL>

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

[oracle@oda12 admin]$ exit

logout

[root@oda12 ~] # reboot

PolicyKit daemon disconnected from the bus.

We are no longer a registered authentication agent.

root@192.168.10.10's password:

Last login: Thu Oct 29 01:29:31 2020 from 192.168.10.9

[root@oda12 ~]# su - oracle

Last login: Thu Oct 29 01:26:01 PKT 2020 on pts/1

[oracle@oda12 ~]$ hmdb ------------------------- (Environment write in .bash_profile)

[oracle@oda12 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 29 01:35:10 2020

Connected to an idle instance.

SQL>

[grid@oda12 ~]$ srvctl status database -db HMDB

PRCD-1120 : The resource for database HMDB could not be found.

PRCR-1001 : Resource ora.hmdb.db does not exist

[grid@oda12 ~]$ crsctl stat res -init -t

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.CRS.dg

ONLINE ONLINE oda12 STABLE

ora.DATA.dg

ONLINE ONLINE oda12 STABLE

ora.FRA.dg

ONLINE ONLINE oda12 STABLE

ora.HMDB_LSN.lsnr

ONLINE ONLINE oda12 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE oda12 STABLE

ora.asm

ONLINE ONLINE oda12 Started,STABLE

ora.ons

OFFLINE OFFLINE oda12 STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

1 ONLINE ONLINE oda12 STABLE

ora.diskmon

1 OFFLINE OFFLINE STABLE

ora.evmd

1 ONLINE ONLINE oda12 STABLE

ora.orcl.db

1 ONLINE ONLINE oda12 Open,STABLE

--------------------------------------------------------------------------------

[grid@oda12 ~]$ crsctl stat res -t

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.CRS.dg

ONLINE ONLINE oda12 STABLE

ora.DATA.dg

ONLINE ONLINE oda12 STABLE

ora.FRA.dg

ONLINE ONLINE oda12 STABLE

ora.HMDB_LSN.lsnr

ONLINE ONLINE oda12 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE oda12 STABLE

ora.asm

ONLINE ONLINE oda12 Started,STABLE

ora.ons

OFFLINE OFFLINE oda12 STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

1 ONLINE ONLINE oda12 STABLE

ora.diskmon

1 OFFLINE OFFLINE STABLE

ora.evmd

1 ONLINE ONLINE oda12 STABLE

ora.orcl.db

1 ONLINE ONLINE oda12 Open,STABLE

--------------------------------------------------------------------------------

ADD Database Spfile in ASM/CRS through Modification

1- Source DBS Environment and Check Spfile Location

[oracle@oda12 ~]$ hmdb

[oracle@oda12 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 29 21:45:18 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/12.1.0/db_1/dbs/spfilehmdb.ora

2- Create Pfile and Check Pfile is Creating in Default Location

SQL> create pfile from spfile;

File created.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

[oracle@oda12 ~]$ cd $ORACLE_HOME/dbs

[oracle@oda12 dbs]$ ls init

inithmdb.ora init.ora initorcl.ora

[oracle@oda12 dbs]$ ls inithmdb.ora

inithmdb.ora

[oracle@oda12 dbs]$ pwd

/u01/app/oracle/product/12.1.0/db_1/dbs

[oracle@oda12 dbs]$ ls /u01/app/oracle/product/12.1.0/db_1/dbs/inithmdb.ora

/u01/app/oracle/product/12.1.0/db_1/dbs/inithmdb.ora

3- Create Spfile in ASM File (Disk Group)

[oracle@oda12 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 29 22:18:57 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> create spfile='+DATA' from pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/inithmdb.ora';

File created.

4- Check Spfile Location ASM File (Disk Group) from Grid User

[grid@oda12 ~]$ asmcmd

ASMCMD> cd DATA

ASMCMD> ls

HMDB/

ORCL/

ASMCMD> cd HMDB

ASMCMD> ls

BACKUPSET/

CONTROLFILE/

DATAFILE/

ONLINELOG/

PARAMETERFILE/

TEMPFILE/

ASMCMD> cd PARAMETERFILE/

ASMCMD> ls

spfile.277.1055110761

ASMCMD> find +data sp*

+data/HMDB/PARAMETERFILE/spfile.277.1055110761

+data/ORCL/PARAMETERFILE/spfile.265.1052778363

5- Add and Modify Spfile in ASM/CRS from Grid User

[grid@oda12 ~]$ srvctl config database -d hmdb

Database unique name: hmdb

Database name:

Oracle home: /u01/app/oracle/product/12.1.0/db_1

Oracle user: oracle

Spfile:

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups: DATA, FRA

Services:

OSDBA group:

OSOPER group:

Database instance: hmdb

[oracle@oda12 dbs]$ srvctl modify database -d hmdb -p +data/HMDB/PARAMETERFILE/spfile.277.1055110761

[grid@oda12 ~]$ srvctl config database -d hmdb

Database unique name: hmdb

Database name:

Oracle home: /u01/app/oracle/product/12.1.0/db_1

Oracle user: oracle

Spfile: +data/HMDB/PARAMETERFILE/spfile.277.1055110761

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups: DATA, FRA

Services:

OSDBA group:

OSOPER group:

Database instance: hmdb

6- Verify The Database is Running with newly migrate Spfile from Oracle User

[oracle@oda12 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 29 22:24:07 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> show parameter spfile

NAME TYPE VALUE

---------------------------- ------------ ------------------------------

spfile string /u01/app/oracle/product/12.1.0/db_1/dbs/spfilehmdb.ora

7- Stop Then Start database from Grid user

[grid@oda12 ~]$ srvctl stop database -d hmdb

[grid@oda12 ~]$ srvctl start database -d hmdb

8- Verify Again the Database is Running with newly migrate Spfile from Oracle User

[oracle@oda12 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 29 22:26:03 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> show parameter spfile

NAME TYPE VALUE

----------------------------- ----------- ------------------------------

spfile string +DATA/HMDB/PARAMETERFILE/spfile.277.1055110761

Completed.

Note: My Future Post Will Linked with this Post

Oracle Application DBA Information

Wednesday 11 November 2020

No comments:

Post a Comment