Skip to Main Content
Forums

Hardware

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Converting Single-Instance Oracle Databases for SAP to Oracle RAC

steph-choyer-OracleNov 28 2016 — edited Jan 23 2017

by Jan Brosowski, Victor Galis, Gia-Khanh Nguyen, and Pierre Reynes

This article provides the steps to convert a single-instance Oracle Database for SAP to an Oracle Real Application Clusters (Oracle RAC) implementation. The procedure given here provides step-by-step instructions that can be used in constructing a highly available database implementation for SAP.

|

Introduction

When an Oracle Database for SAP is migrated to a new platform (in this example to Oracle SuperCluster), the migration results in the creation of a single-instance Oracle Database on the destination system. To achieve a highly scalable and available database implementation, it is necessary to convert this single-instance deployment to Oracle RAC, a clustered database with a shared cache architecture.

| |

|

OOS_Logo_small125.png
Oracle Optimized Solutions provide tested and proven best practices for how to run software products on Oracle systems. Learn more.

|

|

This article is a follow-on article to an article series called "Best Practices for Migrating SAP Systems to Oracle Infrastructure" that documents best practices and procedures to migrate SAP environments to Oracle engineered systems. To compile the article series, a team of Oracle engineers and SAP experts worked together to validate and tune different migration methods using Oracle SuperCluster M7 as a test system. Regardless of which migration method is used to copy the database instance, the step-by-step instructions in this article can help you convert a single-instance Oracle Database to Oracle RAC, configuring the database environment for a highly available and scalable SAP implementation. The steps tested and described in this paper are also valid when converting a single-instance Oracle Database to Oracle RAC when an SAP installation does not require high availability (HA).

Another article series, "Setting up Highly Available SAP Systems on Oracle SuperCluster," describes how to install and configure the SAP ABAP stack in an HA configuration (live links lead to each article that is currently available):

  • Part 1: Configuring Virtualization for SAP on Oracle SuperCluster
  • Part 2: Deploying and Configuring Oracle Solaris Cluster for SAP on Oracle SuperCluster
  • Part 3: Installing Highly Available SAP with Oracle Solaris Cluster on Oracle SuperCluster

Converting from a Single Instance to Oracle RAC

For production as well as quality assurance environments, Oracle RAC is recommended to optimize availability and scalability. Managers of many non-production database instances might also find value in implementing Oracle RAC for non-production SAP systems. For deployments that support many development instances, for example, Oracle RAC can often make it easier to manage maintenance windows and reduce downtime, improving service levels for large development teams.

Once a database migration is completed (see the "Best Practices for Migrating SAP Systems to Oracle Infrastructure" series), the duplicated database instance is up and running as a single instance and is an identical copy of the source system's database. In the example deployment documented here, the source database is named PR1 and the single-instance destination database is named PR2. After the migration steps are complete (using one of the three migration methods outlined in the series "Best Practices for Migrating SAP Systems to Oracle Infrastructure"), the SAP basis team can point application servers to the database and perform maintenance tasks such as license updates, RFC destination changes, logon groups updates, and so forth.

There are two basic steps to move a single-instance database to an Oracle RAC implementation:

  • Prepare all new nodes to run the Oracle Database (PR2 in the example deployment described here)
  • Modify the database configuration to run as Oracle RAC

Preparing the Second Node

The example deployment uses two nodes for Oracle RAC. To prepare the second node, set it up for the PR2 database instance with the appropriate mounts and initialization settings (these steps are similar to steps used to prepare the first node to run the single-instance database). The hostnames for node 1 and 2 are sapm7zdbadm1c1 and sapm7zdbadm2c1, respectively.

On node 2, perform Step 1 through Step 7 below. However, the commands in Step 3 (given in Listing 3) must be performed on both nodes to properly configure NFS client services on both nodes. After performing these steps, both nodes should, in general, have the same configuration.

Step 1. Configure vfstab to automount the oracle share.

Add an entry for /oracle to /etc/vfstab.

sapm7-h2-storIB:/export/sapm7/ORACLE/oracle  -  /oracle  nfs  -  yes  rw,bg,hard,nointr,rsize=131072,wsize=131072,proto=tcp,vers=3

Listing 1: Adding /oracle to /etc/vfstab.

Step 2. Mount the oracle share.

Create the directory /oracle and mount the share.

root@sapm7zdbadm2c1:~# mkdir /oracle

root@sapm7zdbadm2c1:~# mount /oracle

Listing 2: Creating and mounting /oracle.

Step 3. Configure NFS client services for both database nodes.

Enable NFS services on both Oracle RAC nodes:

root@sapm7zdbadm2c1:~# svcadm enable nfs/client

root@sapm7zdbadm2c1:~# svcadm enable nfs/mapid

root@sapm7zdbadm2c1:~# svcadm enable nfs/cbd

root@sapm7zdbadm2c1:~# svcs -a | grep nfs

disabled 20:51:16 svc:/network/nfs/server:default

disabled 20:51:24 svc:/network/nfs/rquota:default

online 20:51:22 svc:/network/nfs/fedfs-client:default

online 20:55:37 svc:/network/nfs/status:default

online 20:55:37 svc:/network/nfs/nlockmgr:default

online 20:57:49 svc:/network/nfs/client:default

online 20:57:58 svc:/network/nfs/mapid:default

online 20:58:05 svc:/network/nfs/cbd:default

Listing 3: Configuring NFS services on Oracle RAC nodes.

Note: Test the database zone by rebooting the nodes; NFS mounts on both nodes should be mounted automatically during the boot process.

Step 4. Add an oratab entry for PR2

The file /var/opt/oracle/oratab must contain the following entry:

<SAPSID>:<ORACLE_HOME>:N

Add the entry below to the file /var/opt/oracle/oratab:

PR2:/oracle/PR2/121:N

Listing 4: Adding PR2 to oratab.

Step 5. Verify that /etc/project and /etc/group are identical on both nodes.

Make sure that the files /etc/project and /etc/group have identical entries on both nodes.

oracle@sapm7zdbadm1c1:~$ cat /etc/project

system:0::::

user.root:1::::

noproject:2::::

default:3::::

group.staff:10::::

user.oracle:105::::process.max-file-descriptor=(basic,65536,deny);process.max-sem-nsems=

(privileged,4096,deny);process.max-sem-ops=(privileged,2048,deny);project.max-msg-ids=

(privileged,4096,deny);project.max-sem-ids=(privileged,65535,deny);project.max-shm-ids=

(privileged,4096,deny);project.max-shm-memory=(privileged,18446744073709551615,deny)

oracle@sapm7zdbadm1c1:~$ ls -l /etc/project

-rw-r--r-- 1 root sys 424 Jul 19 10:15 /etc/project

Listing 5: Verifying that /etc/project is the same on both nodes.

Add the entries in Listing 6 to the /etc/group file on the second node.

sapinst::700:

sapsys::701:

oper::1003:

asmoper::1004:

asmadmin::1005:

asmdba::1006:

Listing 6: Verifying that /etc/group contains the same entries on both nodes.

Step 6. Add the PR2 definition to tnsnames.ora.

Add the PR2 definition (such as the one in Listing 7) to the file /oracle/PR2/121/network/admin/tnsnames.ora.

PR2 =

  (DESCRIPTION =

    (ADDRESS = 

      (PROTOCOL = TCP)

      (HOST = sapm7zdb2c1-ib-vip)

      (PORT = 1521)

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PR2)

    )

  )

Listing 7: Adding PR2 to tnsnames.ora.

Step 7. Create the PFILE for PR2.

If the database was migrated by following the tasks outlined in the previous database migration series, the current PFILE should be very simple and contain only a pointer to the SPFILE, which is in Oracle Automatic Storage Management (Oracle ASM), a feature of Oracle Database.

oracle@sapm7zdbadm1c1:~$ cat $ORACLE_HOME/dbs/initPR2.ora

spfile = (+DATAC1/PR2/spfilePR2.ora)

Listing 8: Viewing the PFILE for PR2.

If the file does not already exist on node 1, create it using the following command:

oracle@sapm7zdbadm1c1:~$ echo 'spfile = (+DATAC1/PR2/spfilePR2.ora)' > $ORACLE_HOME/dbs/initPR2.ora

Listing 9: Creating the PFILE for PR2 on node 1.

Once the PFILE is verified to exist with the correct content on node 1, copy it using scp from the $ORACLE_HOME/dbs directory on node 1 to the $ORACLE_HOME/dbs directory on node 2.

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ scp initPR2.ora sapm7zdbadmc2m1:/oracle/PR2/121/dbs

Listing 10: Creating the PFILE for PR2 on node 2.

Step 8. Add the single-instance database to the Oracle Clusterware configuration and switch the database instance to node 2.

Once the environment is set up, the PR2 database can be started on any of the nodes. Listing 11 is an example showing how to restart the database on a different node using srvctl commands:

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl status database -d PR2

Instance PR2 is running on node sapm7zdbadm1c1

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl stop database -d PR2

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl remove database -d PR2

Remove the database PR2? (y/[n]) y

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl add database -d PR2 -oraclehome \

/oracle/PR2/121 -dbtype single -node sapm7zdbadm2c1 -policy AUTOMATIC -diskgroup \

DATAC1,RECOC1 -spfile +DATAC1/PR2/spfilePR2.ora

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl start database -d PR2

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl status database -d PR2

Instance PR2 is running on node sapm7zdbadm2c1

Listing 11: Switching single-instance database from one node to another.

Note: For non-production single-instance databases, this can be an efficient way to avoid longer down times during maintenance windows or to balance resource use.

Recommended Configuration Changes for Oracle RAC

SAP and Oracle recommend the following configuration changes for Oracle RAC databases:

  • Use UNDO tablespaces in an Oracle RAC environment. Create UNDO tablespaces, one per instance. The naming of the UNDO tablespaces should follow the instance naming convention, using either one- or three-digit numbers.
  • Every database instance needs its own group of redo log files. The naming convention is GROUP mn, with the first digit m being the instance number and the second digit n being the group number. Four groups per instance should be created.

In this example, the UNDO tablespace and the redo log files already exist because the PR2 database instance was migrated from a source Oracle RAC database. The following steps can also be adapted when increasing the number of instances for an Oracle RAC database.

Configuring Automatic UNDO Management with UNDO Tablespaces

For additional information on this section, read the reference white paper "Configuration of SAP NetWeaver for Oracle Grid Infrastructure 11.2.0.2 and Oracle Real Application Clusters 11g Release 2: A Best Practices Guide."

Oracle and SAP recommend using UNDO tablespaces in an Oracle RAC environment. The use of rollback segments is deprecated and should be avoided in an Oracle RAC configuration with SAP.

Note: This section applies to upgrades from single-instance databases to Oracle RAC or when changing the number of Oracle RAC nodes in comparison to the source database. Otherwise, skip to the next section, "Preparing the Initialization File with Instance-Specific Information."

Prepare to use automatic UNDO management for an SAP installation with the following naming conventions used by SAP: PSAPUNDO001, PSAPUNDO002, ... PSAPUNDO00_n_, using one UNDO tablespace per Oracle RAC instance in the cluster.

Step 1. Check for UNDO tablespaces.

SQL> select name from v$tablespace;

NAME

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

SYSTEM

PSAPTEMP

SYSAUX

PSAPUNDO001

PSAPUNDO002

PSAPSR3

PSAPSR3740

PSAPSR3USR

8 rows selected.

Listing 12: The UNDO tablespaces already exist.

The UNDO tablespaces PSAPUNDO001 and PSAPUNDO002 already exist.

Note: You can skip Step 2 and Step 3 (Listing 13 and Listing 14) if the number and datafile sizes of your UNDO tablespaces are already sufficient for the target platform.

Step 2. Create new UNDO tablespaces.

If necessary, create a new UNDO tablespace using the following commands. For example, use these commands to create an UNDO tablespace named PSAPUNDO003:

SQL> create undo tablespace PSAPUNDO003 datafile '+DATAC1' size 1000m reuse;

SQL> select file_name, tablespace_name from dba_data_files where tablespace_name='PSAPUNDO003';

FILE_NAME                                                           TABLESPACE_NAME

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

+DATAC1/PR2/DATAFILE/psapundo003.523.912952045                      PSAPUNDO003

Listing 13: Creating a new UNDO tablespace.

Step 3. Removing UNDO tablespaces.

Because the example environment uses only two instances, Listing 14 shows how to remove the third UNDO tablespace (PSAPUNDO003) that was just created and is not needed:

SQL> drop tablespace PSAPUNDO003 including contents and datafiles;

Tablespace dropped.

Listing 14: Removing unneeded UNDO tablespaces.

Step 4. Add UNDO tablespace management in the initialization file.

Leveraging automatic UNDO management also requires changes to the SPFILE file of an instance. The entries for the rollback segments need to be deleted or commented out. In the SPFILE, add these lines for UNDO tablespace management. The section "Preparing the Initialization File with Instance-Specific Information" provides details on how to add changes to the SPFILE:

*.undo_management = auto

<dbsid>001.undo_tablespace = PSAPUNDO001

<dbsid>002.undo_tablespace = PSAPUNDO002

...

<dbsid>00n.undo_tablespace = PSAPUNDO00n

Listing 15: Adding UNDO tablespace management in the PFILE.

This change is also applicable for migrations from older SAP installations. While the example environment for this article used Oracle Database 12_c_, the same methodology is applicable to Oracle Database 11_g_ databases.

Step 5. Checking the status of redo log file groups.

Query V$LOG to see which log file group is current (Listing 16).

SQL> select group#, archived, status from v$log;

    GROUP# ARC STATUS

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

        11 YES INACTIVE

        12 YES INACTIVE

        13 NO  CURRENT

        14 YES INACTIVE

        21 YES UNUSED

        22 YES UNUSED

        23 YES UNUSED

        24 YES UNUSED

8 rows selected.

Listing 16: Checking the status of log file groups.

Currently PR2 is a single-instance database, so groups 21, 22, 23, and 24 are unused. (They are used only when a second database instance is active.) Groups 21 to 24 already exist because PR2 is the result of a migration from an Oracle RAC database with two instances. One set of groups is required per Oracle RAC instance.

Note: You can skip the commands for Step 6 through Step 8 (Listing 17 through Listing 19) if you are migrating from an Oracle RAC database and keeping the same number of instances.

The example deployment uses groups of two redo log files to guard against data block corruption. If you are using Oracle ASM to triple-mirror (per SAP recommendations), it is acceptable—and it provides better performance—to use only one redo log file per group.

Step 6. If necessary, create a new redo log file group.

If the source database was not an Oracle RAC database and group 21 does not already exist after the database migration, run the command shown in Listing 17, which shows how to create a new redo log file group.

SQL> alter database add logfile thread 2 group 21 ( ́'+DATAC1','+RECOC1' ́)  size 2G reuse

Listing 17: Creating a new redo log file group.

Depending on the environment, it might be necessary to modify the size of the redo log file (Listing 17 shows the size as 2G).

Note: For Oracle ASM disk group recommendations for SAP databases, refer to the white paper "Using SAP NetWeaver with Oracle Database 12_c_ on Oracle Exadata".

Switch the current log file to new groups.

If new groups are created, do as many log switches as needed until the current log is in the newly created groups.

SQL> alter database enable public thread 1;

SQL> alter database enable public thread 2;

SQL> alter system switch logfile;

SQL> alter system checkpoint;

SQL> alter system switch logfile;

SQL> alter system checkpoint;

SQL> alter system switch logfile;

SQL> alter system checkpoint;

SQL> alter system switch logfile;

SQL> alter system checkpoint;

Listing 18: Performing log switches.

Step 7. Remove the old log file groups.

Single-instance SAP databases typically have groups named 101 to 104. Once the current log file is in the new groups, the old log file groups can be dropped safely:

SQL> alter database drop logfile group 101;

SQL> alter database drop logfile group 102;

SQL> alter database drop logfile group 103;

SQL> alter database drop logfile group 104;

Listing 19: Dropping the old log file groups.

Check and then apply the specific naming convention for your database.

Preparing the Initialization File with Instance-Specific Information

Before you can change a database from a single instance to an Oracle RAC instance, it is necessary to set the following parameters for the database:

PR2001.thread=001

PR2002.thread=002

PR2001.undo_tablespace='PSAPUNDO001'

PR2002.undo_tablespace='PSAPUNDO002'

PR2001.instance_name='PR2001'

PR2002.instance_name='PR2002'

PR2001.instance_number=001

PR2002.instance_number=002

PR2001.service_names='PR2','PR2001'

PR2002.service_names='PR2','PR2002'

Listing 20: Sample initialization file.

New parameters need to be added to the existing SPFILE. The PFILE does not contain parameters; it only points to the SPFILE in Oracle ASM that was created during steps described in the previous database migration series. The SPFILE can either be modified directly with ALTER system commands, or a temporary PFILE can be created, edited, and then used to re-create a new SPFILE. Steps 1a and 1b, respectively, show both options.

Step 1a. Option 1—Modify an existing SPFILE.

Listing 21 shows an example of using ALTER system commands to modify the existing SPFILE initialization file.

SQL> alter system set instance_number = 1 scope = spfile sid = 'PR2001';

System altered.

Listing 21: Using ALTER system commands to modify the SPFILE.

Step 1b. Option 2—Create a temporary PFILE.

Listing 22 shows an example of creating and editing a PFILE to generate a new SPFILE.

SQL> create PFILE='/tmp/initPR2.ora' from SPFILE;

File created.

SQL> !vi /tmp/initPR2.ora

[Insert the following content.]

PR2001.thread=001

PR2002.thread=002

PR2001.undo_tablespace='PSAPUNDO001'

PR2002.undo_tablespace='PSAPUNDO002'

PR2001.instance_name='PR2001'

PR2002.instance_name='PR2002'

PR2001.instance_number=001

PR2002.instance_number=002

PR2001.service_names='PR2','PR2001'

PR2002.service_names='PR2','PR2002'

SQL> create SPFILE from PFILE='/tmp/initPR2.ora';

File created.

SQL> exit

Listing 22: Modifying SPFILE by creating and updating a temporary PFILE.

Step 2. Restart the database.

After preparing the initialization file using one of the two methods above, restart the database.

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl stop database -d PR2

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl start database -d PR2

Listing 23: Restarting the database with srvctl.

Starting PR2 as an Oracle RAC–Enabled Instance

The database can now be converted to a clustered Oracle RAC database. For customers that regularly perform migrations as a part of system refresh procedures (for example, to refresh QA systems), the steps shown in Listing 24 through Listing 28 need to be executed only once, as part of the first migration.

Step 1. Add PR2001 to oratab on node 1.

Add the following entry to /var/opt/oracle/oratab on node 1:

PR2001:/oracle/PR2/121:N

Listing 24: Adding PR2001 to oratab on node 1.

Step 2. Add PR2002 to oratab on node 2.

Add the following entry to /var/opt/oracle/oratab on node 2:

PR2002:/oracle/PR2/121:N

Listing 25: Adding PR2002 to oratab on node 2.

Step 3. Add PR2001 to tnsnames.ora on node 1.

Add the PR2001 definition in /oracle/PR2/121/network/admin/tnsnames.ora on node 1:

PR2001 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = sapm7zdb1c1-ib-vip)(PORT = 1521))

(CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PR2001)

      (UR = A)

    )

  )

Listing 26: Adding PR2001 to tnsnames.ora on node 1.

Step 4. Add PR2002 to tnsnames.ora on node 2.

Add the PR2002 definition in /oracle/PR2/121/network/admin/tnsnames.ora on node 2:

PR2002 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = sapm7zdb2c1-ib-vip)(PORT = 1521))

  (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PR2002)

      (UR = A)

    )

  )

Listing 27: Adding PR2002 to tnsnames.ora on node 2.

Step 5. Set environment variables for PR2001 on node 1.

Use the oraenv command to define environment variables and set the ORACLE_SID for PR2001 on node 1:

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ . oraenv

ORACLE_SID = [PR2] ? PR2001

The Oracle base remains unchanged with value /u01/app/oracle

Resetting ORACLE_BASE to its previous value or ORACLE_HOME

The Oracle base remains unchanged with value /u01/app/oracle

Listing 28: Adding PR2001 environment variables.

Step 6. Create PFILEs for instances PR2001 and PR2002.

Create PFILEs for both instances:

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ cp initPR2.ora initPR2001.ora

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ scp initPR2.ora \ 

sapm7zdbadmc2m1:/oracle/PR2/121/dbs/initPR2002.ora

Listing 29: Creating PFILEs for instances PR2001 and PR2002.

Step 7. Verify that each node has the same content in the PFILE.

View the PFILE on node 1:

oracle@sapm7zdbadm1c1:~$ cat /oracle/PR2/121/dbs/initPR2001.ora 

spfile = (+DATAC1/PR2/spfilePR2.ora)

Listing 30: Listing the PFILE for instance PR2001.

View the PFILE on node 2:

oracle@sapm7zdbadm2c1:~$ cat /oracle/PR2/121/dbs/initPR2002.ora 

spfile = (+DATAC1/PR2/spfilePR2.ora)

Listing 31: Listing the PFILE for instance PR2002.

Step 8. Set the CLUSTER_DATABASE parameter to TRUE.

The final step in enabling the database instance for an Oracle RAC configuration is to set the parameter CLUSTER_DATABASE to TRUE (Listing 32).

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 27 18:42:11 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 9495904256 bytes

Fixed Size                  5282808 bytes

Variable Size            4798284808 bytes

Database Buffers         4664066048 bytes

Redo Buffers               28270592 bytes

Database mounted.

Database opened.

SQL> ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;

System altered.

SQL> shutdown immediate

ORACLE instance shut down.

SQL> exit

Listing 32: Setting CLUSTER_DATABASE to TRUE.

Step 9. Validate that both Oracle RAC instances are working correctly.

Verify that the database is working correctly and that both instances can be started, mounted, and opened. Start the first instance (Listing 33).

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 27 19:10:17 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 9495904256 bytes

Fixed Size                  5282808 bytes

Variable Size            4798284808 bytes

Database Buffers         4664066048 bytes

Redo Buffers               28270592 bytes

SQL> show parameter cluster

NAME                                 TYPE        VALUE

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

cluster_database                     boolean     TRUE

cluster_database_instances           integer     2

cluster_interconnects                string

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL>

Listing 33: Mounting and opening the database.

In addition, confirm that the instance can be started on node 2. First, use the oraenv command to define environment variables and set ORACLE_SID for PR2002 on node 2:

oracle@sapm7zdbadm2c1:/oracle/PR2/121/dbs$ . oraenv

ORACLE_SID = [PR2] ? PR2002

The Oracle base remains unchanged with value /u01/app/oracle

Resetting ORACLE_BASE to its previous value or ORACLE_HOME

The Oracle base remains unchanged with value /u01/app/oracle

Listing 34: Setting ORACLE_SID using oraenv.

Confirm that the database PR2 is running with two clustered instances (Listing 35).

oracle@sapm7zdbadm2c1:~$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 27 19:11:19 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 9495904256 bytes

Fixed Size                  5282808 bytes

Variable Size            4798284808 bytes

Database Buffers         4664066048 bytes

Redo Buffers               28270592 bytes

Database mounted.

Database opened.

Listing 35: Starting the second database instance of PR2.

Using a sqlplus session, verify that the PR2 database is enabled for Oracle RAC and running with two instances.

SQL> col INSTANCE_NAME form a20

SQL> col HOST_NAME form a20

SQL> col status form a10

SQL> select instance_name, host_name, status from gv$instance;

INSTANCE_NAME        HOST_NAME            STATUS

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

PR2001               sapm7zdbadm1c1       OPEN

PR2002               sapm7zdbadm2c1       OPEN

Listing 36: PR2 is an Oracle RAC database running with two instances.

As Listing 36 shows, the Oracle RAC database PR2 is running with two instances, PR2001 and PR2002.

Registering the Oracle RAC Database with Oracle Clusterware

Oracle RAC uses the standalone Oracle Grid Infrastructure feature as the foundation for Oracle RAC database systems. Oracle Grid Infrastructure includes Oracle Clusterware and Oracle Automatic Storage Management (Oracle ASM), which enable efficient sharing of server and storage resources in a highly available and scalable database cloud environment.

The following series of steps is necessary to make Oracle Clusterware aware that PR2 is an Oracle RAC database.

Step 1. Add Oracle RAC database configuration information to Oracle Clusterware.

Remove the current database configuration information, add the new configuration information, and then add the instance configuration information.

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl status database -d PR2

Instance PR2002 is running on node sapm7zdbadm2c1

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl stop database -d PR2

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl remove database -d PR2

Remove the database PR2? (y/[n]) y

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl add database -d PR2 -oraclehome \

/oracle/PR2/121 -dbtype RAC -r PRIMARY -policy AUTOMATIC -s OPEN -t NORMAL -diskgroup \

DATAC1,RECOC1 -spfile +DATAC1/PR2/spfilePR2.ora

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl add instance -d PR2 -i PR2001 -n sapm7zdbadm1c1

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl add instance -d PR2 -i PR2002 -n sapm7zdbadm2c1

Listing 37: Adding PR2 with its two instances to Oracle Clusterware.

Listing 38 shows that the database instances are running but that Oracle Clusterware is not aware of them.

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl status database -d PR2

Instance PR2001 is not running on node sapm7zdbadm1c1

Instance PR2002 is not running on node sapm7zdbadm2c1

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ ps -eaf |grep pmon

root 12137 10695   0   Mar 15 ?           3:04 /usr/bin/perl -w 

/opt/oracle.cellos/compmon/exadata_mon_hw_asr.pl -server

  oracle 13176 10695   0   Mar 15 ?          10:35 asm_pmon_+ASM1

  oracle 56261 10695   0 19:10:54 ?           0:00 ora_pmon_PR2001

  oracle 64442 45456   0 19:25:57 pts/2       0:00 grep pmon

Listing 38: Oracle Clusterware is not aware of the PR2 instances running.

Listing 38 shows that instance PR2001 is running on node 1, even though the database status command shows that Oracle Clusterware is not aware of it. A database restart (Listing 39) is necessary for Oracle Clusterware to be aware of the PR2 database instances.

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 27 19:26:09 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl start database -d PR2

oracle@sapm7zdbadm1c1:/oracle/PR2/121/dbs$ srvctl status database -d PR2

Instance PR2001 is running on node sapm7zdbadm1c1

Instance PR2002 is running on node sapm7zdbadm2c1

Listing 39: Restarting PR2.

Adding a Database Service for the SAP Primary Application Server

Skip this section if sapinst is to be run to install a new SAP system. SAP Software Provisioning Manager (SWPM) creates the database service for a SAP instance when the installation points to an Oracle RAC database.

Perform the steps in this section if the migration process is creating a new database for an existing SAP environment. Database services are not created as a part of the migration, so they need to be created on the newly configured database.

It is best practice to create one database service for each SAP application instance. This section shows how to create a database service for the SAP Primary Application Server (PAS).

oracle@sapm7zdbadm1c1:~$ srvctl add service -s PR2_DVEBMGS00 -d PR2 -preferred PR2001 \

-available PR2002 -tafpolicy BASIC -policy AUTOMATIC -notification TRUE -failovertype \

SELECT -failovermethod BASIC -failoverretry 3 -failoverdelay 5

oracle@sapm7zdbadm1c1:~$ srvctl start service -s PR2_DVEBMGS00 -d PR2

oracle@sapm7zdbadm1c1:~$ srvctl status service -d PR2

Service PR2_DVEBMGS00 is running on instance(s) PR2001

Listing 40: Adding a database service for PAS.

Verify that this database service can move between Oracle RAC instances:

oracle@sapm7zdbadm1c1:~$ srvctl relocate service -d PR2 -s PR2_DVEBMGS00 -t PR2002 \

-oldinst PR2001

oracle@sapm7zdbadm1c1:~$ srvctl status service -d PR2

Service PR2_DVEBMGS00 is running on instance(s) PR2002

oracle@sapm7zdbadm1c1:~$ srvctl relocate service -d PR2 -s PR2_DVEBMGS00 -t PR2001 \

-oldinst PR2002

oracle@sapm7zdbadm1c1:~$ srvctl status service -d PR2

Service PR2_DVEBMGS00 is running on instance(s) PR2001

Listing 41: Relocating the database service for PAS.

The SAP installer creates a service for each application server so there are as many entries in tnsnames.ora as application servers deployed.

To avoid managing many services in larger deployments, the SAP basis team can create groups of application servers that access the same service. A grouping example would be based on logon groups. All application servers in a logon group can connect using the same service. The factors affecting this decision are

  • Availability. When a database service is restarted on a different node, the current SAP transaction running in an application server connected to the service might fail. Having multiple application servers connected to the same service can result in failures that impact more users.
  • Performance. Users modifying the same tables are more efficiently served from the same database instance. Having multiple application servers always connecting to the same instance can provide performance benefits.

Database Connection over InfiniBand

Database connectivity using InfiniBand (IB) virtual IP (VIP) addresses requires an IB listener. If an IB listener is not set up already, refer to "Creating a DB listener on InfiniBand" in Oracle Support Document 1955833.1. (Access to Oracle Support Documents requires logon and authentication to My Oracle Support.) Note that instead of using port 1522 for IB listeners as described in Oracle Support Document 1955833.1, port 1521 is used, as shown in Listing 42.

Listing 42 shows the resulting database configuration for listeners.

SQL> show parameter listener

NAME                                 TYPE        VALUE

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

listener_networks                    string      ((NAME=network2)(LOCAL_LISTENE

                                                 R=LISTENER_IBLOCAL)(REMOTE_LIS

                                                 TENER=LISTENER_IBREMOTE)), ((N

                                                 AME=network1)(LOCAL_LISTENER=L

                                                 ISTENER_IPLOCAL)(REMOTE_LISTEN

                                                 ER=LISTENER_IPREMOTE))

local_listener                       string

remote_listener                      string      sapm7-c1-scan:1521

oracle@sapm7zdbadm1c1:~$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): sapm7zdbadm1c1,sapm7zdbadm2c1

Listener LISTENER_IB is enabled

Listener LISTENER_IB is running on node(s): sapm7zdbadm1c1,sapm7zdbadm2c1

oracle@sapm7zdbadm1c1:~$ srvctl stop listener

oracle@sapm7zdbadm1c1:~$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is not running

Listener LISTENER_IB is enabled

Listener LISTENER_IB is not running

oracle@sapm7zdbadm1c1:~$ srvctl start listener

oracle@sapm7zdbadm1c1:~$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): sapm7zdbadm1c1,sapm7zdbadm2c1

Listener LISTENER_IB is enabled

Listener LISTENER_IB is running on node(s): sapm7zdbadm1c1,sapm7zdbadm2c1

Listing 42: Showing the configured database listeners.

SAP Servers Using InfiniBand Connection

When installing SAP servers using sapinst and the installation points to an Oracle RAC database, the installation should result in Transparent Network Substrate (TNS) definitions that use Oracle RAC IB listeners. Listing 43 shows the changes made to the service PR2_DVEBMGS00.WORLD in /sapmnt/PR1/profile/oracle/tnsnames.ora to use IB VIP addresses for the SAP database connection:

PR2_DVEBMGS00.WORLD = 

  (DESCRIPTION = 

    (ADDRESS_LIST = 

      (ENABLE=broken)

      (FAILOVER=on)

      (load_balance=off)

      (ADDRESS = 

        (COMMUNITY = SAP.WORLD)

        (PROTOCOL = TCP)

        (HOST = sapm7zdb1c1-ib-vip)

        (PORT = 1521)

      )

      (ADDRESS =

        (COMMUNITY = SAP.WORLD)

        (PROTOCOL = TCP)

        (HOST = sapm7zdb2c1-ib-vip)

        (PORT = 1521)

      )

    )

    (CONNECT_DATA = 

      (SERVICE_NAME = PR2_DVEBMGS00)

      (GLOBAL_NAME = PR2_DVEBMGS00.WORLD)

      (FAILOVER_MODE = (TYPE=SELECT)(METHOD=BASIC))

    )

  )

Listing 43: Creating PR2_DVEBMGS00.WORLD to use IB VIP addresses.

Verify that the PAS instance profile is using this TNS definition for the database connection:

pr1adm@em7pr1-haapps-01:~$ grep tnsname /sapmnt/PR1/profile/PR1_*-lh

/sapmnt/PR1/profile/PR1_DVEBMGS00_im7pr1-pas-lh:SETENV_06 = dbs_ora_tnsname=PR2_DVEBMGS00

Listing 44: Verifying that the TNS definition specifies the PR2_DVEBMGS00 database service.

After setting the tnsnames.ora entry for PR2_DVEBMGS00.WORLD to use the IB VIP addresses, it is necessary to start SAP and validate database connectivity.

Using the internal IB connections in Oracle SuperCluster is recommended because they offer several advantages:

  • IB connections reduce security risks because Oracle Database domains do not need to be connected to the external 10GbE network.
  • IB connections offer higher bandwidth and better performance.

To use the scan listener for database connections, modify /sapmnt/PR1/profile/oracle/tnsnames.ora. Change all entries to point to PR2 using the scan:

PR2_DVEBMGS00.WORLD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ENABLE=broken)

      (ADDRESS =

        (COMMUNITY = SAP.WORLD)

        (PROTOCOL = TCP)

        (HOST = sapm7-c1-scan)

        (PORT = 1521)

      )

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PR2_DVEBMGS00)

      (GLOBAL_NAME = PR2_DVEBMGS00.WORLD)

      (FAILOVER_MODE = (TYPE=SELECT)(METHOD=BASIC))

    )

  )

Listing 45: Changing the PR2_DVEBMGS00.WORLD entry to use the scan listener.

After changing the file, verify that the PAS instance profile is using this TNS definition for the database connection.

pr1adm@em7pr1-haapps-01:~$ grep tnsname /sapmnt/PR1/profile/PR1_*-lh

/sapmnt/PR1/profile/PR1_DVEBMGS00_im7pr1-pas-lh:SETENV_06 = dbs_ora_tnsname=PR2_DVEBMGS00

Listing 46:Verifying that the PAS profile uses the correct TNS entry.

After creating the TNS entry for PR2_DVEBMGS00.WORLD, it is necessary to start SAP and validate database connectivity.

Final Steps

To configure SAP application services for high availability using Oracle Solaris Cluster and zone clustering, see the article series "Setting up Highly Available SAP Systems on Oracle SuperCluster" (a live link will lead to this series when it is available).

Once the SAP system is up and running and pointing to the newly migrated/converted database, additional tasks must still be executed to verify that the new SAP system is set up for its intended purpose (such as for preproduction, QA, disaster recovery, and so forth). Typical additional steps include (but are not limited to) the following:

  • Removing scheduled jobs
  • Updating RFC connections
  • Managing users and logon groups
  • Changing the SAP system name

These tasks are well-known SAP administrative tasks and are outside the scope of this article.

See Also

Refer to these resources for more information:

About the Authors

Jan Brosowski is a principal sales consultant for Oracle Systems in Europe North. Located in Walldorf, Germany, he is responsible for developing customer-specific architectures and operating models for both SAP and Hyperion systems, accompanying the projects from the requirements specification process to going live. Brosowski holds a Master of Business and Engineering degree and has been working for over 15 years with SAP systems in different roles.

Victor Galis is a master sales consultant, part of the global Oracle Solution Center organization. He supports customers and sales teams architecting SAP environments based on Oracle hardware and technology. He works with SAP Basis and DBA teams, systems and storage administrators, as well as business owners and executives. His role is to understand current environments, business requirements, and pain points as well as future growth and map them to SAP landscapes that meet both performance and high availability expectations. He has been involved with many SAP on Oracle SuperCluster customer environments as an architect and has provided deployment and go-live assistance. Galis is a SAP-certified consultant and Oracle Database administrator.

Gia-Khanh Nguyen is an architect for Oracle Solaris Cluster. He contributed to the product requirement and design specifications for features supporting HA and DR enterprise solutions and developed demonstrations of key features.

Pierre Reynes is a solution manager for Oracle Optimized Solution for SAP and Oracle Optimized Solution for PeopleSoft. He is responsible for driving the strategy and efforts to help raise customer and market awareness for Oracle Optimized Solutions in these areas. Reynes has over 25 years of experience in the computer and network industries.

Comments

472969
Hi,

Instead of ALTER TABLE test DROP CONSTRAINT xpk_test; statement you can submit the statement ALTER TABLE test DROP CONSTRAINT xpk_test CASCADE; it will work.

Regards,
Kamal Shrivastava
USER101
Or

Alter table test drop primary key;

Hope this helps
user492066
Hi,

yes both statements mentioned above
ALTER TABLE test DROP CONSTRAINT xpk_test CASCADE;
and
Alter table test drop primary key;

successfully remove the primary key, but only the pk. :-(
The index persists when these statements run on a database migrated from Oracle 9 to 10! So unfortunately no difference to the statement we used before.

TIA,
F.
user492066
Hi,

the following statement does what we were looking for:

ALTER TABLE test DROP PRIMARY KEY DROP INDEX;

Removes PK and Index and produces identical results on Oracle 9, Oracle 10 and databases that were migrated form 9 to 10!

Thanx for all hints,
F.
Kamal Kishore

That is expected behaviour. If an index on the primary key columns already exists, then adding the primary key constraint does not add a new index. Later when you drop the primary key, index is not dropped since it was not created as part of the add primary key constraint statement.

On the other hand, if you do not have a prior index on the primary key columns, a index is created as part of the add constraint primary key statement. In this case, dropping the constraint will drop the iindex as well.

Since you did an export and import, the import works by creating the index separate and then adding the primary key constraint separate. Since in case of import, the two thiings happen separately, dropping the primary key now will NOT drop the index.

Look at the below scenario, here no export import is involved, but in first case index is not dropped automatically:

SQL> create unique index pk_emp1 on emp1(empno) ;
 
Index created.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
PK_EMP1                        EMP1                           UNIQUE
 
1 row selected.
 
SQL> alter table emp1 add constraint pk_emp1 primary key(empno) ;
 
Table altered.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
PK_EMP1                        EMP1                           UNIQUE
 
1 row selected.
 
SQL> alter table emp1 drop primary key ;
 
Table altered.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
PK_EMP1                        EMP1                           UNIQUE
 
1 row selected.
 
SQL>
SQL> drop index pk_emp1 ;
 
Index dropped.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
no rows selected
 
SQL> alter table emp1 add constraint pk_emp1 primary key(empno) ;
 
Table altered.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
PK_EMP1                        EMP1                           UNIQUE
 
1 row selected.
 
SQL> alter table emp1 drop primary key ;
 
Table altered.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
no rows selected
 
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
448776
Dear All

All methods are fine, I don't know whether you have privileged to drop the index. I hope while you have created the table using table level. From oracle 9i we have one new feature creating index while we creating primary key.
(E.g) like
create table g1(a1 number(2) primary key using index(create index index_g1on g1(a1))); then if you follow earlier mentioned dropping procedure, your index will get dropped out .
user492066
That is expected behaviour. If an index on the
primary key columns already exists, then adding the
primary key constraint does not add a new index.
Later when you drop the primary key, index is not
dropped since it was not created as part of the add
primary key constraint statement.

On the other hand, if you do not have a prior index
on the primary key columns, a index is created as
part of the add constraint primary key statement. In
this case, dropping the constraint will drop the
index as well.

Since you did an export and import, the import works
by creating the index separate and then adding the
primary key constraint separate. Since in case of
import, the two thiings happen separately, dropping
the primary key now will NOT drop the index.
[...]
Thanx for your confirmation and explanation of this behaviour. We overlooked that export/import causes this change due to the separate re-creation of the prio existing pk and index. Nevertheless we never had this problems when moving from oracle 8 to 9. So even though it's "expected behaviour" it looks like new or changed behaviour. ;-)

Thanx,
F.
user492066
Dear All

All methods are fine, I don't know whether you have
privileged to drop the index. I hope while you have
created the table using table level. From oracle 9i
we have one new feature creating index while we
creating primary key.
(E.g) like
create table g1(a1 number(2) primary key using
index(create index index_g1on g1(a1))); then if you
follow earlier mentioned dropping procedure, your
index will get dropped out .
Yes, there would be no problem, if all PKs where created during table creation, i.e. in one statement. But in our databases all PKs where created by separate ADD-CONSTRAINT-statements after the CREATE-TABLE-statement. For the future we can use the "one-statement-approach". But to avoid future problems in the already migrated databases -- where in other words the link between existing PKs and related indexes is broken -- the "...DROP PRIMARY KEY DROP INDEX"-approach is the only one working.

Thanx,
F.
Kamal Kishore
I'm wondering why is there a need for you to drop the primary key on a table when moving from 9i to 10G?
Is it that the column list for the primary key is changing or that table no longer needs to have a primary key?
what happens to the existing application queries that were previously relying on this index?
user492066
I'm wondering why is there a need for you to drop the
primary key on a table when moving from 9i to 10G?
Oh, that's a coincidence. There's no direct connection between the change of the PK and the migration. It was just bad luck: The PK was altered (i.e. dropped from one and re-created on another column) in the context of a table redesign during further development.

The long story: We develop standard-software which supports different databases, amongst others both Oracle 9 and 10 versions. The original PK was created years ago -- before even oracle 9 existed ;-) -- and now changed during a major redesign.

So we were very surprised that there is a difference in behaviour between versions 8/9 and 10. When you create a PK in versions 8 or 9, make different exports/imports and then drop the PK, the index is gone too. Doing the same in Oracle 10, the index persists after one export/import, even without a migration.

Well this might be new expected behaviour, when taking a closer look an the obviously new way this constraints are handled during export/import. But since this was not the behaviour of versions 8 and 9, in our eyes this is more a misbehaviour, or in other words a bug, than a new feature! :-(

Is there any way -- e.g. a server option or something -- to get the old behaviour back?

TIA,
F.
USER101

Very strange.. My database was not a export and import and i am on 9.2.0.7

This is what my test produced.

SQL> create table test(a number,b number);

Table created.

SQL> create unique index test_i on test(a,b);

Index created.

SQL> alter table test add constraint test_i primary key (a,b) using index tablespace user_data;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME
------------------------------
TEST_I

SQL> alter table test drop primary key;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

no rows selected

/[PRE]

G
USER101
Sorry.. i misread the post.

I concurr with Kamal
user492066
Don't mention it!

That's a good demonstration. Let's just do the same on Oracle 10.1.0.2.0. This time I did no export/import between any statement. Then it looks like this:

create table test(a number,b number);

Table created.

SQL> create unique index test_i on test(a,b);

Index created.

SQL> alter table test add constraint test_i primary key (a,b) using index tablespace users;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME                                                                      
------------------------------                                                  
TEST_I                                                                          

SQL> alter table test drop primary key;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME                                                                      
------------------------------                                                  
TEST_I                                                                          

SQL>
This demonstrates very clearly that there is a change in behaviour, isn't it. Any ideas how to switch back to the old behaviour?

TIA,
F.
John Spencer
Actually, I'm not sure you want to revert to the 9i behaviour. It seems to me to be wrong.

In both tests, you created a unique index on the columns, then later created a primary key, which used the existing index to enforce the key. In 9i, dropping the PK also dropped the pre-existing unique constraint. It was the dropping of the unique constaint that dropped the index, not directly the PK.

In 10g, Oracle maintained the pre-existing unique constraint even after you dropped the PK.

In 8i and 9i if you create the index without the UNIQUE, then it is maintained even when you drop the PK.
user492066
Hmm, yes and no. The last tests where only another demonstation of the general change in behaviour. Our core problem refers to indexes automatically created with PKs.

For indexes that existed before a PK maintaining is right, yes. I concur with that.

But the original discussion referred to indexes automatically created with PKs. And in my eyes it's not wrong to drop auto-indexes, when the PK that triggered them is removed. Particularly if these new behaviour changes in dependence of a export/import: The auto-index is removed as long as you don't perform any export/imports, but the auto-index is not removed as soon as you performed one export/import. Sorry, no offense, but if this is a correction, than it looks not very consistent. ;-) And no, the new behaviour seems not right to me.

Greetings,
F.
93654
Hello,

As most of the third party softwares Toad etc. will generate this kind of code.
When you create a table with a primay key the code looks like this in SQL*Plus

<< SQL*Plus>>

create table test (a number, b number);

alter table test add constraint test_i primary key (a,b)
using index tablespace users;

The same code when you launch TOAD you will see unique index + primary key constraint. It is Toad's behaviour.

<< TOAD>>

create table test (a number, b number);

create unique index test_i on test(a, b);

alter table test add constraint test_i primary key (a,b)
using index tablespace users;

First thing to understand is when you create a primary key it will create a unique index by default with a not null constraint. You dont have to create a primary key constraint and again a unique index.

So if you go as per the TOAD's code and try to drop primary key you will still see the unique index. Which you have to manually drop it.

Just wondering why you are concerned about dropping Primary Key?

-Sri

Message was edited by:
Srikanth Pulikonda
user492066
So if you go as per the TOAD's code and try to drop
primary key you will still see the unique index.
Which you have to manually drop it.
Yes, why this happens the way it does, has been made clear. I understand why people see that the new behaviour is the right one when taking a closer look on the way constraints are handled. Still it's not consistent that behaviour changes due to export/import (see statements above!).

And besides of consistent behaviour we wish there would be a kind of backward compatibility to the "old" behaviour of versions 8 and 9. Right behaviour is one thing, compatibility is another. And other databases like e.g. PostgreSQL or MS SQL Server or MySQL or even Informix handle auto-indexes and PK as ORACLE Versions 8 and 9 did: Auto-drop them as soon the PK is removed.
Just wondering why you are concerned about dropping
Primary Key?
Because we develop standard software and we support different DBMS and different versions of these DBMS, amongst others Oracle 9 and 10. No, not all of these mentioned above! ;-) Well, this change gives another annoying point more, where version-specific and/or DBMS-specific SQLs are needed. Before a ALTER TABLE ... DROP CONSTRAINT met most DBMS/versions. Just call me inflexible and stubborn. ;-)

But don't take this too serious that's just a side blow. I am aware that SQL is not the standard it should be and that there will always be DBMS-specifics. The main concern is the inconsistent behaviour!

Sorry giving you folks a hard time: Is there any way (server option whatever) to get this "wrong", but consistent behaviour of versions 8 and 9 back? ;-)

Thanks a lot to all of you for your help and especially your patience!
F.

Message was edited by:
user492066
525856
Dear all,

I would also be very interested in knowing if there is a way to get back to the version 8 and 9 behaviour.

I'm experiencing the same problem with a third-party product for which I of course do not have the code. Even if the vendor is going to provide a fix, this would take time and it would be great if there were a workaround that we can apply ...
1 - 18

Post Details

Added on Nov 28 2016
#legacy-documents, #optimized-solutions
1 comment
3,737 views