Converting an Administrator-Managed Database to a Policy-Managed Database and vise versa.

Hi, 
Cluster database configuration can be Policy-Managed or Administrator-Managed. A Policy-Managed database is dynamic with instances managed automatically based on server pools for effective resource utilization. Admin-Managed database results in instances tied to specific servers.

When we create 11g Release 2 RAC database, on database configuration assistant’s 3 of 13 step we must choose Configuration type : Admin-Managed or Polisy-Managed.

As you know, we can convert configuration type of Oracle RAC Database after installation.In this post I want share steps of conversion of Admin-Managed database to Policy managed (Case #1) and Policy-Managed to Admin-Managed (Case #2). In  my case my test RAC Database (racdb) is created Adminstrator-Managed. 

Case #1 : Convert Administrator-Managed Database to Policy-Managed Database

Checking configuration of racdb database.

[oracle@oel62-rac1 /]$ srvctl config database -d racdb   
 Database unique name: racdb  
 Database name:   
 Oracle home: /u01/app/oracle/product/11.2.0/dbhome  
 Oracle user: oracle  
 Spfile:   
 Domain:   
 Start options: open  
 Stop options: immediate  
 Database role: PRIMARY  
 Management policy: AUTOMATIC  
 Server pools: racdb  
 Database instances: racdb1,racdb2  
 Disk Groups: DATA,FRA  
 Mount point paths:   
 Services:   
 Type: RAC  
 Database is administrator managed  
 [oracle@oel62-rac1 /]$  

1. Create a server pool for the Policy-Managed database

 [oracle@oel62-rac1 /]$ srvctl add srvpool -g PMSRVPOOL -l 0 -u 2  
 [oracle@oel62-rac1 /]$ srvctl config serverpool  
 Server pool name: Free  
 Importance: 0, Min: 0, Max: -1  
 Candidate server names:   
 Server pool name: Generic  
 Importance: 0, Min: 0, Max: -1  
 Candidate server names: oel62-rac1,oel62-rac2  
 Server pool name: PMSRVPOOL  
 Importance: 0, Min: 0, Max: 2  
 Candidate server names:   
 [oracle@oel62-rac1 /]$   

2. Modify the database to be in the new server pool, as follows:

 [oracle@oel62-rac1 /]$ srvctl modify database -d racdb -g PMSRVPOOL  
 [oracle@oel62-rac1 /]$ srvctl config database -d racdb   
 Database unique name: racdb  
 Database name:   
 Oracle home: /u01/app/oracle/product/11.2.0/dbhome  
 Oracle user: oracle  
 Spfile:   
 Domain:   
 Start options: open  
 Stop options: immediate  
 Database role: PRIMARY  
 Management policy: AUTOMATIC  
 Server pools: PMSRVPOOL  
 Database instances:   
 Disk Groups: DATA,FRA  
 Mount point paths:   
 Services:   
 Type: RAC  
 Database is policy managed  

Servers already  located in PMSRVPOOL

 [oracle@oel62-rac1 /]$ srvctl status srvpool  
 Server pool name: Free  
 Active servers count: 0  
 Server pool name: Generic  
 Active servers count: 0  
 Server pool name: PMSRVPOOL  
 Active servers count: 2  

3. Last step is configuration of Oracle Enterprise Manager

Documentation says : 
Configure Oracle Enterprise Manager to recognize the change you made in the previous procedure, as follows:

1. In order for Oracle Enterprise Manager Database Control to recognize the new database instances, you must change the instance name fromdb_unique_name# to db_unique_name_# (notice the additional underscore (_) before the number sign (#) character).
2. Rename the orapwd file in the dbs/database directory (or create a new orapwd file by running the orapwd command). By default, there is an orapwd file with the instance name appended to it, such as orapwdORCL1. You must change the name of the file to correspond to the instance name you changed in the previous step. For example, you must change orapwdORCL1 to orapwdORCL_1 or create a new orapwd file.
3. Run emca to reconfigure Oracle Enterprise Manager Database Control, as follows:

   emca -config dbcontrol db –cluster

Database is Policy Managed, now.

 [oracle@oel62-rac1 /]$ srvctl config database -d racdb   
 Database unique name: racdb  
 Database name:   
 Oracle home: /u01/app/oracle/product/11.2.0/dbhome  
 Oracle user: oracle  
 Spfile:   
 Domain:   
 Start options: open  
 Stop options: immediate  
 Database role: PRIMARY  
 Management policy: AUTOMATIC  
 Server pools: PMSRVPOOL  
 Database instances:   
 Disk Groups: DATA,FRA  
 Mount point paths:   
 Services:   
 Type: RAC  
 Database is policy managed  
 [oracle@oel62-rac1 /]$   

Case #2 : Convert Policy Managed Database to Administrator Managed Database

Documentation says:
You cannot directly convert a policy-managed database to an administrator-managed database. Instead, you can remove the policy-managed configuration using the srvctl remove database and srvctl remove service commands, and then register the same database as an administrator-managed database using the srvctl add database and srvctl add instance commands. Once you register the database and instance, you must use the srvctl add service command to add back the services as you removed them.

1. Remove database with SRVCTL tool. 

 [oracle@oel62-rac1 /]$ srvctl remove database -d racdb  
 PRKO-3141 : Database racdb could not be removed because it was running  

If your database running you can use –f (force) for remove running database. But this way is not recommended. 

Stopping database and removing.

 [oracle@oel62-rac1 /]$ srvctl stop database -d racdb   
 [oracle@oel62-rac1 /]$ srvctl status database -d racdb   
 Instance racdb1 is not running on node oel62-rac1  
 Instance racdb2 is not running on node oel62-rac2  
 [oracle@oel62-rac1 /]$ srvctl remove database -d racdb  
 Remove the database racdb? (y/[n]) y  
 [oracle@oel62-rac1 /]$ srvctl status database -d racdb  
 PRCD-1120 : The resource for database racdb could not be found.  
 PRCR-1001 : Resource ora.racdb.db does not exist  
 [oracle@oel62-rac1 /]$   

2. Add database Administrator Managed

 [oracle@oel62-rac1 /]$ srvctl add database -d racdb -o /u01/app/oracle/product/11.2.0/dbhome -y automatic  
 [oracle@oel62-rac1 /]$ srvctl config database -d racdb  
 Database unique name: racdb  
 Database name:   
 Oracle home: /u01/app/oracle/product/11.2.0/dbhome  
 Oracle user: oracle  
 Spfile:   
 Domain:   
 Start options: open  
 Stop options: immediate  
 Database role: PRIMARY  
 Management policy: AUTOMATIC  
 Server pools: racdb  
 Database instances:   
 Disk Groups:   
 Mount point paths:   
 Services:   
 Type: RAC  
 Database is administrator managed  
 [oracle@oel62-rac1 /]$   

3. Adding database instances

 [oracle@oel62-rac1 /]$ srvctl add instance -d racdb -i racdb1 -n oel62-rac1  
 [oracle@oel62-rac1 /]$ srvctl add instance -d racdb -i racdb2 -n oel62-rac2  
 [oracle@oel62-rac1 /]$   

 [oracle@oel62-rac1 /]$ srvctl start database -d racdb   
 [oracle@oel62-rac1 /]$ srvctl status database -d racdb   
 Instance racdb1 is running on node oel62-rac1  
 Instance racdb2 is running on node oel62-rac2  

 [oracle@oel62-rac1 /]$ srvctl config database -d racdb  
 Database unique name: racdb  
 Database name:   
 Oracle home: /u01/app/oracle/product/11.2.0/dbhome  
 Oracle user: oracle  
 Spfile:   
 Domain:   
 Start options: open  
 Stop options: immediate  
 Database role: PRIMARY  
 Management policy: AUTOMATIC  
 Server pools: racdb  
 Database instances: racdb1,racdb2  
 Disk Groups: DATA,FRA  
 Mount point paths:   
 Services:   
 Type: RAC  
 Database is administrator managed  
 [oracle@oel62-rac1 /]$   

4. You must  configure Oracle Enterprise Manager on last step.

References :
Oracle Real Application Clusters Administration and Deployment Guide 11g Release 2 (11.2)
Policy Managed Databases and Server Pools – by Aychin Gasimov

Regards
Mahir M. Quluzade

Which device is mapped Oracle ASM disk?

Hi,

Yesterday Yuva Kishore is commented on my Oracleasm - Instantiating disk : failed post and asked a question: We can list the asm disks (listdisks) but where can we find the mapping that ASMDISK1 is mapped to /dev/sda1 and so on? 

I want answer this question with this post.
If you  are using ASMLib for configure ASM Disks, then you can use following steps for define which device mapped which ASM Disk.

As you  know, oracleasm listdisks is return only ASM disk names

[grid@oel62-rac1 ~]$ oracleasm listdisks
ASMDISK01
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
[grid@oel62-rac1 ~]$

If you want find which ASM disk is mapped which device, then you must use oracleasm querydisk with ASM disk name.

[grid@oel62-rac1 ~]$ oracleasm querydisk -d ASMDISK01
Disk "ASMDISK01" Is a valid ASM disk On device[8,17]

As see you, ASMDISK01 is valid ASM disk on device [8,17].

What is means?
It means ASMDISK01 is mapped to device [8,17].

How to find [8,17] device?
We must use Linux ls –l  command as below.

[grid@oel62-rac1 ~]$ ls -l /dev/* | grep 8, | grep 17
brw-rw----  1 root disk 8,  17 Apr 16 15:27 /dev/sdb1

Now, we can  say ASMDISK01 is mapped to /dev/sdb1.


Regards
Mahir M. Quluzade

Oracle Data Guard 11g - Redo Transport User

Hi,

As you know when we create Data Guard Configuration, we must copy password file. Because SYS user is default redo transport user on Data Guard configurations. 

SYS password is changed on primary

When we change SYS password on primary database, redo transport service is stopping and we are getting error: ORA-01031: insufficient privileges. When redo transport failed for this reason we can recreate standby password file with copy from primary side. Same time we can change redo transport user, by setting REDO_TRANSPORT_USER with SYSOPER privileged user. I  demonstrated in two  case, when SYS password changing on primary database.

Password files.

Traditionally we are coping password file from primary with operating system command (cp, copy). After Oracle Database 11g version RMAN can duplicate password file with DUPLICATE command, so when we using RMAN DUPLICATE command with FOR STANDBY FROM ACTIVE DATABASE option, we can use PASSWORD FILE option for copy primary database password file to standby database.

Documentation says :

PASSWORD FILE

Specifies that RMAN should use the password file on the source database to overwrite the password file currently used by the auxiliary instance. This option is only valid when FROM ACTIVE DATABASE is specified; otherwise, RMAN signals an error.If FOR STANDBY is specified, then RMAN copies the password file by default; if not specified, then RMAN does not copy the password file by default. You can use PASSWORD FILE to request that RMAN overwrite the existing password file with the password file from the source database. If you want the duplicate database to contain all the passwords available on your production database, then use the PASSWORD FILE option. 

Demonstrations.

#1. In this case transport service running on Primary  database, Standby  database opened Read Only With Apply (Active Data Guard is running).

REDO_TRANSPORT_USER parameter is not set on both side.

SQL> show parameter redo_transport_user 
NAME                 TYPE             VALUE 
------------------- ------------- ----------------- 
redo_transport_user string

On primary side :

SQL> alter system switch logfile; 
System altered. 
SQL> select max(sequence#)  from v$archived_log; 
MAX(SEQUENCE#) 
-------------- 
    307 
SQL> select process, status from v$managed_standby; 
PROCESS   STATUS 
--------- ------------ 
ARCH   CLOSING 
ARCH   CLOSING 
ARCH   CONNECTED 
ARCH   CLOSING 
LNS      WRITING

Alter log of primary database:

****************************************************************** 
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 
****************************************************************** 
Wed May 01 12:06:51 2013 
Archived Log entry 611 added for thread 1 sequence 307 ID 0xf23a6e3f dest 1: LNS: Standby redo logfile selected for thread 1 sequence 308 for destination LOG_ARCHIVE_DEST_2

On standby side :

SQL> select max(sequence#)  from v$archived_log;
MAX(SEQUENCE#) 
-------------- 
   307 

Alert log of standby  database:

 Media Recovery Waiting for thread 1 sequence 308   
 Wed May 01 12:06:51 2013   
 Archived Log entry 308 added for thread 1 sequence 307 
 ID 0xf23a6e3f dest 1:   
 Wed May 01 12:06:51 2013   
 Primary database is in MAXIMUM PERFORMANCE mode   

ASYNC transport going normally.

I changing SYS password of primary, now.

 SQL> alter user SYS identified by SYSPass1;   
 User altered.   
 SQL> alter system switch logfile;   
 System altered.
 SQL> select process, status from v$managed_standby;   
 PROCESS  STATUS   
 --------- ------------   
 ARCH  OPENING   
 ARCH  CLOSING   
 ARCH  CONNECTED   
 ARCH  CLOSING   
 LNS  OPENING   

Alert log of primary  database:

 ******************************************************************   
 LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2   
 ******************************************************************   
 Error 1031 received logging on to the standby   
 Error 1031 for archive log file 3 to 'stldb'   
 Wed May 01 12:15:15 2013   
 Archived Log entry 613 added for thread 1 sequence 308 ID 0xf23a6e3f dest 1:   
 Errors in file /u01/app/oracle/diag/rdbms/admdb/admdb/trace/admdb_nsa2_2698.trc:   
 ORA-01031: insufficient privileges   
 Wed May 01 12:16:01 2013   
 Error 1031 received logging on to the standby   
 PING[ARC2]: Heartbeat failed to connect to standby 'stldb'. Error is 1031.   

Yes, we are getting error when changed SYS password. I coping primary password for standby with Linux copy command (cp)

 [oracle@oel62-x64 dbs]$ cp orapwadmdb orapwstldb  

Trying again.

 SQL> alter system switch logfile;   
 System altered.   
 SQL> /   
 System altered.   
 SQL> select max(sequence#) from v$archived_log;   
 MAX(SEQUENCE#)   
 --------------   
        310   

Alert log of primary database: 

 ******************************************************************   
 LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2   
 ******************************************************************   
 Wed May 01 12:19:06 2013   
 Archived Log entry 614 added for thread 1 sequence 309 ID 0xf23a6e3f dest 1:   
 LNS: Standby redo logfile selected for thread 1 sequence 310 for destination LOG_ARCHIVE_DEST_2   
 Wed May 01 12:19:07 2013   
 ARC3: Standby redo logfile selected for thread 1 sequence 309 for destination LOG_ARCHIVE_DEST_2   
 Thread 1 cannot allocate new log, sequence 311   
 Checkpoint not complete   
  Current log# 1 seq# 310 mem# 0: /u01/app/oracle/oradata/admdb/redo01.log   
 Thread 1 advanced to log sequence 311 (LGWR switch)   
  Current log# 2 seq# 311 mem# 0: /u01/app/oracle/oradata/admdb/redo02.log   

On Standby:

 SQL> select max(sequence#) from v$archived_log;   
 MAX(SEQUENCE#)   
 --------------   
        310   

Alert log of standby database:

 RFS[18]: Assigned to RFS process 5039   
 RFS[18]: Selected log 4 for thread 1 sequence 308 dbid -231090113 branch 813174214   
 Recovery of Online Redo Log: Thread 1 Group 4 Seq 308 Reading mem 0   
  Mem# 0: /u01/app/oracle/oradata/stldb/sredo01.log   
 Wed May 01 12:15:14 2013   
 Media Recovery Waiting for thread 1 sequence 309   
 Wed May 01 12:15:14 2013   
 Archived Log entry 309 added for thread 1 sequence 308 ID 0xf23a6e3f dest 1:   
 Wed May 01 12:19:05 2013   
 Primary database is in MAXIMUM PERFORMANCE mode   
 RFS[19]: Assigned to RFS process 5184   
 RFS[19]: Selected log 4 for thread 1 sequence 310 dbid -231090113 branch 813174214   
 Wed May 01 12:19:07 2013   
 RFS[20]: Assigned to RFS process 5186   
 RFS[20]: Selected log 5 for thread 1 sequence 309 dbid -231090113 branch 813174214   
 Wed May 01 12:19:08 2013   
 Recovery of Online Redo Log: Thread 1 Group 5 Seq 309 Reading mem 0   
  Mem# 0: /u01/app/oracle/oradata/stldb/sredo02.log   
 Wed May 01 12:19:08 2013   
 Archived Log entry 310 added for thread 1 sequence 309 ID 0xf23a6e3f dest 1:   
 Media Recovery Waiting for thread 1 sequence 310 (in transit)   
 Recovery of Online Redo Log: Thread 1 Group 4 Seq 310 Reading mem 0   

Transport continue normally after copy password file of primary  database to standby side.

#2. In this case I create a user and granting SYSOPER and setting REDO_TRANSPORT_USER to this user. 

On primary side :

SQL> create user RTU identified by rtu; 
User created. 
SQL> grant SYSOPER to RTU; 
Grant succeeded. 
SQL> select *  from v$PWFILE_USERS; 
USERNAME         SYSDB SYSOP SYSAS 
------------------------------ ----- ----- ----- 
SYS          TRUE  TRUE  FALSE 
RTU          FALSE TRUE  FALSE 

 
SQL> alter system set REDO_TRANSPORT_USER='RTU'; 
System altered. 
SQL> show parameter REDO_TRANSPORT_USER 
NAME         TYPE  VALUE 
------------------ --------- --------------- 
redo_transport_user  string  RTU 
SQL>

Alert log of primary database :

Wed May 01 12:29:10 2013 
redo_transport_user changed to <RTU> 

On Standby side :

SQL> select username from all_users 
2> where username ='RTU'; 

USERNAME 
------------------------------ 
RTU 

SQL> select *  from v$pwfile_users; 

USERNAME         SYSDB SYSOP SYSAS 
------------------------------ ----- ----- ----- 
SYS          TRUE  TRUE  FALSE 

SQL> grant SYSOPER to RTU; 
Grant succeeded. 

SQL> alter system set REDO_TRANSPORT_USER=RTU; 
System altered. 

Now we can check, transport process.

On primary :

SQL> alter system switch logfile; 
System altered. 
 
SQL> select max(sequence#)  from v$archived_log; 

MAX(SEQUENCE#) 
-------------- 
    314 

 On standby side :

SQL> select max(sequence#)  from v$archived_log; 

MAX(SEQUENCE#) 
-------------- 
    314 




Redo transport service using RTU user for transport redo from primary to standby database. I changing SYS user password on primary again. 


SQL> alter user sys identified by SYSPass2; 

User altered.

On primary :

SQL> alter system switch logfile; 

System altered. 

SQL> select max(sequence#)  from v$archived_log; 
MAX(SEQUENCE#) 
-------------- 
    315 

On Standby side :

SQL> select max(sequence#) from v$archived_log; 

MAX(SEQUENCE#) 
-------------- 
    315 

 It means transport is not stopped. If we change RTU user’s password then Redo Transport will stop, Because RTU user is privileged SYSOPER and this password change must be on password file. It means, if we change RTU user’s password we must copy password file from primary to standby side, again.

Conclusion

Password  files must be same for Data Guard Configuration databases.

In a Data Guard configuration, all physical and snapshot standby databases must use a copy of the password file from the primary database, and that copy must be refreshed whenever the SYSOPER or SYSDBA privilege is granted or revoked, and after the password of any user with these privileges is changed.

Regards
Mahir M. Quluzade

Cluvfy error : Framework setup check failed on all the nodes

Hi

As you know Oracle recommends  before installation Oracle Real Application Clusters 11g Release 2 run Cluster Verification utility with  stage previous and post installation cluster ware or database.

Cluster verification utility help you for getting error before and  after installation Oracle Grid Infrastructure and Real Application Clusters Database.

I getting following error : 

[grid@oel62-rac1 grid]$ ./runcluvfy.sh stage -pre crsinst -n oel62-rac1,oel62-rac2 –verbose

…..

Checking user equivalence...

Check: User equivalence for user "grid"

  Node Name                             Status                 

  ------------------------------------  ------------------------
  oel62-rac2                            passed                 
  oel62-rac1                            passed              

   

Result: User equivalence check passed for user "grid"

Verifying node connectivity
Version of exectask could not be retrieved from node "oel62-rac2"
Version of exectask could not be retrieved from node "oel62-rac1"

ERROR:
Framework setup check failed on all the nodes

…

First call of Cluster Verification Utility (runcluvfy.sh) shall  create a folder /tmp/CVU_11.2.0.3.0_[grid|oracle|resource|...]. This folders contains shell scripts as fixup.sh. I solved my problem with remove /tmp/CVU_11.2.0.3.0_* folders both nodes.

How to I got this error ?

I  using Oracle VM Virtualbox as my test environment. I  create a virtual machine, preparing this machine for installation (network settings, required packages, users, groups, system  configurations) Oracle RAC. After completion preparation I cloning this virtual machine for second node. Before clone of machine I called a time Cluster Verification Utility. First time of call Cluster Verification Utility created a temporary folder   with name /tmp/CVU_11.2.0.3.0_grid. If this folder is copied from first node to second node then you can get error Framework setup check failed on all the nodes.

Regards

Mahir M. Quluzade

                

Oracle VM 3.2 Now Available

Oracle Virtualization product Oracle VM 3.2.1 now available. 

You can read press release here and Oracle VM 3.2.1 documentation here. Download Oracle VM from  edelivery.oracle.com 

Installation 

MySQL Database Support: MySQL Database is used as the bundled database for the Oracle VM Manager management repository for simple installations. Support for an existing Oracle SE/EE Database is still included within the installer so that you can perform a custom installation to take advantage of your existing infrastructure. Simple installation using the bundled MySQL Database is fully supported within production environments.

Discontinued inclusion of Oracle XE Databases: Oracle VM Manager no longer bundles the Oracle XE database as a backend database. If you are currently running Oracle VM Manager using Oracle XE and you intend to upgrade you must first migrate your database to Oracle SE or Oracle EE.

Improved Usability

Oracle VM Command Line Interface (CLI): The new Oracle VM Command Line Interface can be used to perform the same functions as the Oracle VM Manager Web Interface, such as managing all your server pools, servers and guests.

Health tab: Monitor the overall health and status of your virtualization environment and view historical statistics such as memory and CPU usage.

Sizes of disks, ISOs and vdisks: Oracle VM Manager now shows the sizes of disks, ISOs and vdisks within the virtual machine edit dialog, to make it easier to select a disk.

Hot-add a VNIC to a virtual machine: It is now possible to add a VNIC directly to a running virtual machine from within Oracle VM Manager.

Send messages to a virtual machine: Facilities have been provided within Oracle VM Manager to send messages directly to a virtual machine in the form of key-value pairs

New features more 

Regards

Mahir M. Quluzade