Convert Admin managed to Policy Managed Database in RAC

I haven’t come across Policy Managed database in RAC till now, so testing out the coversion of Admin managed to PMD and viceversa.

Convert Admin Managed to Policy Managed Database:

Current Config Status of database

$ srvctl config database -d TESTCDB
Database unique name: TESTCDB
Database name: TESTCDB
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA_GRP_1/TESTCDB/PARAMETERFILE/spfile.315.928144379
Password file: +DATA_GRP_1/TESTCDB/PASSWORD/pwdtestcdb.300.928143681
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO_GRP_2,DATA_GRP_1
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: TESTCDB1,TESTCDB2
Configured nodes: VM1,VM2
Database is administrator managed

Server pool List:

$ srvctl status srvpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 2

Add new Server pool TEST

$ srvctl add srvpool -serverpool TEST -min 0 -max 2
$ srvctl status srvpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 2
Server pool name: TEST
Active servers count: 0

Modify TESTCDB to server pool TEST.

$ srvctl modify database -d TESTCDB -g TEST -force

$ srvctl config database -d TESTCDB
Database unique name: TESTCDB
Database name: TESTCDB
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA_GRP_1/TESTCDB/PARAMETERFILE/spfile.315.928144379
Password file: +DATA_GRP_1/TESTCDB/PASSWORD/pwdtestcdb.300.928143681
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TEST
Disk Groups: REDO_GRP_2,DATA_GRP_1
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances:
Configured nodes:
Database is policy managed

Convert Policy Managed Database to Admin Managed Database:

To convert the policy managed to admin managed, you have to remove it from cluster registry. To gracefully remove,stop the database and then remove. As it is test instance I am using “-Force” option.

$ srvctl remove database -d TESTCDB
PRKO-3141 : Database TESTCDB could not be removed because it was running
$ srvctl remove database -d TESTCDB -force

$ srvctl status database -d TESTCDB
PRCD-1120 : The resource for database TESTCDB could not be found.
PRCR-1001 : Resource ora.testcdb.db does not exist

Add database and instances to cluster again.

$ srvctl add database -d TESTCDB -o /u01/app/oracle/product/12.1.0.2/db_1 -y AUTOMATIC
$ srvctl config database -d TESTCDB
Database unique name: TESTCDB
Database name:
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile:
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances:
Configured nodes:
Database is administrator managed

Add Instances , spfile and password file to the registry

$ srvctl add instance -d TESTCDB -i TESTCDB1 -n VM1
$ srvctl add instance -d TESTCDB -i TESTCDB2 -n VM2
$ srvctl modify database -d TESTCDB -p +DATA_GRP_1/TESTCDB/PARAMETERFILE/spfile.315.928144379
$ srvctl modify database -d TESTCDB -pwfile +DATA_GRP_1/TESTCDB/PASSWORD/pwdtestcdb.300.928143681
$ srvctl config database -d TESTCDB
Database unique name: TESTCDB
Database name:
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA_GRP_1/TESTCDB/PARAMETERFILE/spfile.315.928144379
Password file: +DATA_GRP_1/TESTCDB/PASSWORD/pwdtestcdb.300.928143681
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: TESTCDB1,TESTCDB2
Configured nodes: VM1,VM2
Database is administrator managed

Ref: https://docs.oracle.com/database/121/RACAD/admcon.htm#RACAD7282

Advertisements

DELETE ASMDISK from DISKGROUP with VOTEDISK

Recent Oracle community conversation on replacing disk from a diskgroup having votedisk triggered me to test the scenario

https://community.oracle.com/thread/3785252?sr=stream

Currently OCR/Votedisk is on External redundancy diskgroup

[oracle@VM2 ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 0d67c8c5c1754fe5bfb3e4513cdad2f5 (ORCL:ASMDISK1) [DATA]

Created new Normal Redundancy diskgroup with 3 ASM Disks and Added OCR to this diskgroup, later removed the OCR from DATA diskgroup.

[root@VM1 ~]# ocrconfig -add +OCR_GROUP
[root@VM1 ~]# crsctl replace votedisk +OCR_GROUP
Successful addition of voting disk 8af044a05e624f15bfdd42383d299f79.
Successful addition of voting disk 4822dd1445194fffbf876bac9a8ac588.
Successful addition of voting disk 5c60703bc2d64f9abf5d65cb5157f4c1.
Successful deletion of voting disk 0d67c8c5c1754fe5bfb3e4513cdad2f5.
Successfully replaced voting disk group with +OCR_GROUP.
CRS-4266: Voting file(s) successfully replaced

[root@VM1 ~]# crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 8af044a05e624f15bfdd42383d299f79 (ORCL:ASMDISK5) [OCR_GROUP]
2. ONLINE 4822dd1445194fffbf876bac9a8ac588 (ORCL:ASMDISK6) [OCR_GROUP]
3. ONLINE 5c60703bc2d64f9abf5d65cb5157f4c1 (ORCL:ASMDISK7) [OCR_GROUP]
Located 3 voting disk(s).

Add 4th ASMDISK to normal redundancy diskgroup.

SQL> alter diskgroup OCR_GROUP add disk ‘ORCL:ASMDISK8’;

Diskgroup altered.

[root@VM1 ~]# crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 8af044a05e624f15bfdd42383d299f79 (ORCL:ASMDISK5) [OCR_GROUP]
2. ONLINE 4822dd1445194fffbf876bac9a8ac588 (ORCL:ASMDISK6) [OCR_GROUP]
3. ONLINE 5c60703bc2d64f9abf5d65cb5157f4c1 (ORCL:ASMDISK7) [OCR_GROUP]
Located 3 voting disk(s).

Drop one of ASMDISK which has votedisk.

SQL> alter diskgroup OCR_GROUP drop disk ASMDISK7;

Diskgroup altered.

ASM will choose the available Disk for placing the votedisk.

[root@VM1 ~]# crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 8af044a05e624f15bfdd42383d299f79 (ORCL:ASMDISK5) [OCR_GROUP]
2. ONLINE 4822dd1445194fffbf876bac9a8ac588 (ORCL:ASMDISK6) [OCR_GROUP]
3. ONLINE 1537fcd557584fd9bf349fb94dddc486 (ORCL:ASMDISK8) [OCR_GROUP]
Located 3 voting disk(s).

Conclusion: So as long as you have sufficient failgroups to maintain the required number of votedisk , ASM will move the votedisk to available disks after dropping any of the ASM DISK

Oracle CRS Enable/disable

Clusterware auto start:
crsctl enable/disable crs will make sure the crs to either automatically start clusterware or not . crsctl config crs will show if auto start of crs enabled or not. Below file is where the information is stored.

Check crs status:
[root@VM1 root]# crsctl config crs
CRS-4622: Oracle High Availability Services autostart is enabled.

[root@VM1 root]# pwd
/etc/oracle/scls_scr/vm1/root

[root@VM1 root]# ls -ltr
total 12
-rw-r–r– 1 root root 7 Dec 17 2013 crsstart
-rw-r–r– 1 root oinstall 7 Dec 17 2013 ohasdstr
-rw-r–r– 1 root oinstall 8 Jul 2 18:04 ohasdrun

[root@VM1 root]# cat /etc/oracle/scls_scr/<nodename>/root/ohasdstr
enable

Disable crs and check the status on file

[root@VM1 root]# crsctl disable crs
CRS-4621: Oracle High Availability Services autostart is disabled.

[root@VM1 root]# cat ohasdstr
disable

Enable the auto start of crs again:

[root@VM1 root]# crsctl enable crs
CRS-4622: Oracle High Availability Services autostart is enabled.

[root@VM1 root]# cat /etc/oracle/scls_scr/<nodename>/root/ohasdstr
enable

What does ohasdrun for in the location? It contains information if clustware needs to be started. ohasd daemon process will read this file every few seconds and start clusterware.

[root@VM1 root]# cat ohasdrun
restart

Stop crs and check the status of file.

[root@VM1 root]# crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘vm1’
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘vm1’

[root@VM1 root]# crsctl check crs
CRS-4639: Could not contact Oracle High Availability Services
Check the contents of the file ohasdrun

[root@VM1 root]# cat ohasdrun
stop

Also crsctl start crs command will return immediatly , because the command will simply change the contents of file ohasdrun to restart and clustware start will run in background.

[root@VM1 root]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

File contents modified to restart again.

[root@VM1 root]# cat ohasdrun
restart

Though not recommended , you can also manually edit the contents of the file to restart and check if clusterware starts automatically.  It does 🙂

Summary:

init.ohasd process started after server reboot by platform specific means. This process will read the contents of the files and startup clusterware automatically. Do not alter the permissions or contents of the file manually, they should always be modified using crsctl commands

 

 

 

ref: https://orainternals.wordpress.com/2013/06/05/clusterware-startup/

Password File Maintenance ASM, RAC

Prior 11GR2:
ASM password file needs to be updated on both instances whenever changes are made.
11GR2:
Starting with oracle 11Gr2 ASM password file will be updated on both nodes

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production

SQL> select * from gv$pwfile_users order by inst_id;

INST_ID USERNAME SYSDB SYSOP SYSAS
———- —————————— —– —– —–
1 SYS TRUE TRUE TRUE
1 ASMSNMP TRUE FALSE FALSE
2 SYS TRUE TRUE TRUE
2 ASMSNMP TRUE FALSE FALSE
SQL> create user rchemik identified by rakesh;

User created.

SQL> grant sysasm to rchemik;

Grant succeeded.

SQL> select * from gv$pwfile_users order by inst_id;

INST_ID USERNAME SYSDB SYSOP SYSAS
———- —————————— —– —– —–
1 SYS TRUE TRUE TRUE
1 ASMSNMP TRUE FALSE FALSE
1 RCHEMIK FALSE FALSE TRUE
2 SYS TRUE TRUE TRUE
2 ASMSNMP TRUE FALSE FALSE
2 RCHEMIK FALSE FALSE TRUE

6 rows selected.
SQL> drop user rchemik;

User dropped.

SQL> select * from gv$pwfile_users order by inst_id;

INST_ID USERNAME SYSDB SYSOP SYSAS
———- —————————— —– —– —–
1 SYS TRUE TRUE TRUE
1 ASMSNMP TRUE FALSE FALSE
2 SYS TRUE TRUE TRUE
2 ASMSNMP TRUE FALSE FALSE

RAC file:

The change in password file update is only for ASM , rac database password still needs to updated on both instances whenever change is made.

12C:

Starting Oracle 12c, ASM supports password file in a diskgroup. You can store the oracle database password file directly in an ASM diskgroup. This support is available for password file of the ASM cluster as well.

Ref:https://prutser.wordpress.com/2011/01/14/clustered-asm-and-rac-password-file-maintenance/

CRS-2640: Required resource ‘ora.DEMO.dg’ is missing

What Happened ?

After creating diskgroup DEMO for testing, I have created few tablespaces under this diskgroup and later dropped both the tablespaces and diskgroup. During the next reboot the database wasn’t started by cluster.

Issue Troubleshooting:

[root@VM1 ~]# crsctl start resource ora.test.db
CRS-2640: Required resource ‘ora.DEMO.dg’ is missing.
CRS-4000: Command Start failed, or completed with errors.

$ srvctl start database -d TEST
PRCR-1079 : Failed to start resource ora.test.db
CRS-2640: Required resource ‘ora.DEMO.dg’ is missing.

Check the resource start or shutdown dependency 

[root@VM1 ~]# crsctl status resource ora.test.db -p |grep ora.DEMO.dg
START_DEPENDENCIES=hard(ora.DATA.dg,ora.DATA2.dg,ora.DEMO.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,uniform:ora.eons) pullup(ora.DATA.dg,ora.DATA2.dg)
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg,shutdown:ora.DATA2.dg,shutdown:ora.DEMO.dg)

Solution:

Remove the dependency of the diskgroup DEMO for the database

[root@VM1 ~]# srvctl modify database -d TEST -a “DATA,DATA2”

Diskgroup was already dropped manually, but try removing it using srvctl

[root@VM1 ~]# srvctl disable diskgroup -g DEMO
PRCR-1001 : Resource ora.DEMO.dg does not exist

[root@VM1 ~]# srvctl remove diskgroup -g DEMO -f
PRCR-1001 : Resource ora.DEMO.dg does not exist

Validate:

[root@VM1 ~]# crsctl status resource ora.test.db -p |grep ora.DEMO.dg

[root@VM1 ~]# srvctl start database -d TEST

[root@VM1 ~]# srvctl status database -d TEST

Instance TEST1 is running on node vm1
Instance TEST2 is running on node vm2

ASM DISK “_DROPPED_0001_DATA2” STATE FORCING

Issue: 

After cluster restart , one of the disk corresponding to a normal redundancy diskgroup state shown as FORCING

SQL> select group_number,name,state,HEADER_STATUS,path,label from v$asm_disk;

GROUP_NUMBER NAME STATE HEADER_STATU PATH LABEL
———— —————————— ——– ———— ———–
0 NORMAL MEMBER ORCL:ASMDISK4 ASMDISK4
0 NORMAL FORMER ORCL:ASMDISK5 ASMDISK5
0 NORMAL FORMER ORCL:ASMDISK6 ASMDISK6
0 NORMAL FORMER ORCL:ASMDISK7 ASMDISK7
0 NORMAL PROVISIONED ORCL:ASMDISK8 ASMDISK8
1 _DROPPED_0001_DATA2 FORCING UNKNOWN
2 ASMDISK1 NORMAL MEMBER ORCL:ASMDISK1 ASMDISK1
2 ASMDISK2 NORMAL MEMBER ORCL:ASMDISK2 ASMDISK2
1 ASMDISK3 NORMAL MEMBER ORCL:ASMDISK3 ASMDISK3

9 rows selected.

Solution: 

2) Create diskgroup with DISK with the missing member , in this case ASMDISK4(Status of disk is showing as member but diskgroup number as Zero) . Use Force option

SQL> create diskgroup DATA3 external redundancy DISK ‘ORCL:ASMDISK4’ force;

Diskgroup created.

3) Drop Diskgroup.

SQL> drop diskgroup DATA3;

Diskgroup dropped.

4) Add disk to the diskgroup.

SQL> alter diskgroup DATA2 add failgroup DISK2 DISK ‘ORCL:ASMDISK4’ ;

Diskgroup altered.

5) check the status after sometime..

SQL> select group_number,name,state,HEADER_STATUS,path,label from v$asm_disk;

GROUP_NUMBER NAME STATE HEADER_STATU PATH LABEL
———— —————————— ——– ———— ———–
0 NORMAL FORMER ORCL:ASMDISK5 ASMDISK5
0 NORMAL FORMER ORCL:ASMDISK6 ASMDISK6
0 NORMAL FORMER ORCL:ASMDISK7 ASMDISK7
0 NORMAL PROVISIONED ORCL:ASMDISK8 ASMDISK8
2 ASMDISK1 NORMAL MEMBER ORCL:ASMDISK1 ASMDISK1
2 ASMDISK2 NORMAL MEMBER ORCL:ASMDISK2 ASMDISK2
1 ASMDISK3 NORMAL MEMBER ORCL:ASMDISK3 ASMDISK3
1 ASMDISK4 NORMAL MEMBER ORCL:ASMDISK4 ASMDISK4

8 rows selected.