3 Database Management

Oracle Enterprise Manager Ops Center uses an Oracle Database 11g Enterprise Edition instance to store product data. You can manage this database.

The following features and topics are covered in this chapter:

Introduction to Database Management
Installing and Configuring a Customer-Managed Database
Migrating to a Customer-Managed Database
Changing the Customer-Managed Database Location
Verifying the Database
Upgrading a Customer-Managed Database
Changing the Database Credentials

Introduction to Database Management

The Enterprise Controller uses an Oracle Database 11g Enterprise Edition database to store Oracle Enterprise Manager Ops Center data. This can be the embedded database installed with the Enterprise Controller, or a new or existing customer-managed database. You can manage the existing database, change, back up, or recover the database schema, or migrate the data to a customer-managed database.

The backup and recovery actions available in Oracle Enterprise Manager Ops Center can be used to back up an Enterprise Controller with either an embedded or a customer-managed database. These options do not require database administrator privileges. These options are discussed in Chapter 8, "Backup and Recovery".

Some of the procedures described in this section use the ecadm command. See the Oracle Enterprise Manager Ops Center Feature Reference Guide for more information about this command.

On Oracle Solaris systems, this command is in the /opt/SUNWxvmoc/bin/ directory.
On Linux systems, this command is in the /opt/sun/xvmoc/bin/ directory.

Installing and Configuring a Customer-Managed Database

If you want to use a customer-managed database, you must install and configure it to work with Oracle Enterprise Manager Ops Center.

To Install and Configure a Customer-Managed Database

Install the database according to the Oracle Database 11g Enterprise Edition installation documentation.
Copy the createOCschema_remote.sql script from the Enterprise Controller to the database.
- On Oracle Solaris for x86 systems, this script is in the /var/tmp/OC/dvd/SunOS_i386/Product/installer/scripts directory.
- On Oracle Solaris for SPARC systems, this script is in the /var/tmp/OC/dvd/SunOS_SPARC/Product/installer/scripts directory.
- On Linux systems, this script is in the/var/tmp/OC/dvd/Linux_i686/Product/installer/scripts directory.
For example:
```
# scp root@EnterpriseController:/var/tmp/OC/dvd/SunOS_i386/Product/installer/scripts/createOCschema_remote.sql .
Password:
createOCschema_remote.sql  100% |*********************| 1486     00:00
```

As the database administrator, run the createOCschema_remote.sql script and enter the following information:

Oracle Enterprise Manager Ops Center user name – This is a database user that is created by the script. It is used by Oracle Enterprise Manager Ops Center to access the database.
Oracle Enterprise Manager Ops Center password – This is the password for the database user.
Oracle Enterprise Manager Ops Center read-only user name – This is a read-only database user used by Oracle Enterprise Manager Ops Center to view the database.
Oracle Enterprise Manager Ops Center read-only password – This is the password for the read-only database user.
Default tablespace – This is the default tablespace for the Oracle Enterprise Manager Ops Center user.
Temporary tablespace – This is the temporary tablespace for the Oracle Enterprise Manager Ops Center user.
Oracle Enterprise Manager Ops Center dump directory – This directory must exist and must be owned by the oracle user.

For example:

$ sqlplus / as sysdba @createOCSchema_remote.sql
 
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 15 16:55:34 2011
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
Enter username for Ops Center database login: TESTSCHEMA
Enter password for Ops Center database login:
Enter username for read only Ops Center database login: TESTSCHEMA_RO
Enter password for read only Ops Center database login:
Enter default tablespace for Ops Center user: USERS
Enter temporary tablespace for Ops Center user: TEMP
Enter Oracle Data Pump destination directory: /var/tmp/ocdumpdir
 
"Done creating OC_SYSTEM_ROLE and OC_RO_ROLE"
"Done creating Schema 'TESTSCHEMA'. Roles and privileges have been granted."
"Done creating Schema 'TESTSCHEMA_RO'. Roles and privileges have been granted."
"Done creating OC_DUMP_DIR at /var/tmp/ocdumpdir"
"Done granting privs to users and profiles"
"Testing connectivity to the new schema: 'TESTSCHEMA'"
Connected.
"Testing connectivity to the new read only schema: 'TESTSCHEMA_RO'"
Connected.
 
"Create is Complete. OC can now be used with the new schema: 'TESTSCHEMA'"
 
 
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$

Migrating to a Customer-Managed Database

If you are using the embedded database, you can migrate to a customer-managed database. You must install and configure the customer-managed database in the destination location as described in the Installing and Configuring a Customer-Managed Database procedure before beginning this procedure.

Note:

The Enterprise Controller system must be routable by hostname from the customer-managed database server for the migration to succeed.

To Migrate to a Customer-Managed Database

Create a customer-managed database properties file on the Enterprise Controller system. The database properties file must contain the location of the customer-managed database and a user name and password that can access the database.

For example:
```
# vi /var/tmp/OC/DB/RemoteDBProps.txt
mgmtdb.appuser=TESTSCHEMA
mgmtdb.password=TESTSCHEMA_PWD
mgmtdb.roappuser=TESTSCHEMA_RO
mgmtdb.ropassword=TESTSCHEMA_RO_PWD
mgmtdb.dburl=jdbc:oracle:thin:@<database hostname>:<port>/<database service name>
```
Use the ecadm command with the backup subcommand to back up the Enterprise Controller. This procedure is described in Chapter 8, "Backup and Recovery".
Use the ecadm command with the migrate subcommand and the --remoteDBprops <path to properties file> and -l <log file> options to migrate to a customer-managed database. The log file is created in the /var/opt/sun/xvm/logs directory.

For example:
```
# ./ecadm migrate --remoteDBprops /var/tmp/remotedbprops.txt -l migrate.log
```

Changing the Customer-Managed Database Location

You can change the location of the customer-managed database. You must install and configure the database in the destination location as described in the Installing and Configuring a Customer-Managed Database section before beginning this procedure.

You must have root access to the Enterprise Controller system and database administrator access to the source database and destination database to complete this procedure.

To Change the Customer-Managed Database Location

As root, log in to the Enterprise Controller system.

Use the ecadm command with the stop subcommand and the -w option to shut down the Enterprise Controller.

For example:

./ecadm stop -w
ecadm: Shutting down Enterprise Controller using SMF...
ecadm: Enterprise Controller services have stopped
#

Edit the database properties file to point to the new database.

For example:

# vi /var/opt/sun/xvm/db.properties
...
mgmtdb.dburl=jdbc:oracle:thin:@<source database hostname>:<port>/<source database service name>

Change the mgmtdb.dburl line to point to the new database hostname and name.

For example:

...
mgmtdb.dburl=jdbc:oracle:thin:@<destination database hostname>:<port>/<destination database service name>

As a user with database administrator privileges, log in to the source database.

Use the datapump export utility to export the source database. When prompted for a user name, enter / as sysdba.

For example:

$ expdp DIRECTORY=oc_dump_dir DUMPFILE=OC_schema.dmp SCHEMAS=OC,OC_RO LOGFILE=expdp_OC_schema.log
Export: Release 11.2.0.1.0 - Production on Mon May 23 10:14:33 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
<output omitted>
Job "SYS"."SYS_EXPORT_SCHEMA.01" succesfully completed at <timestamp>
$

Review the log file and verify that there were no errors.

For example:

$ vi expdp_OC_schema.log
<output omitted>
Job "SYS"."SYS_EXPORT_SCHEMA.01" succesfully completed at <timestamp>

Move the dump file from the source database server to the destination database server.

For example:

$ scp OC_schema.dmp <destination database hostname>
Password:
OC_schema.dmp      100% |***************************| 18192 KB     00:01

Delete the dump file on the source database system.

For example:
```
$ rm OC_schema.dmp
```
As a user with database administrator privileges, log in to the destination database.
Create a database directory that points to the location of the dump file, then exit.

For example:
```
SQL> create or replace directory oc_dump_dir as '/var/tmp/ocdumpdir';
SQL> exit;
```

Use the import utility to import the database dump file into the source database.

For example:

$ impdp DIRECTORY=oc_dump_dir DUMPFILE=OC_schema.dmp LOGFILE=impdp_OC_schema.log
Import: Release 11.2.0.1.0 - Production on Mon May 23 10:14:33 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
<output omitted>
Job "SYS"."SYS_IMPORT_SCHEMA.01" succesfully completed at <timestamp>

Review the log file and verify that there were no errors.

For example:

$ vi impdp_OC_schema.log
<output omitted>
Job "SYS"."SYS_IMPORT_SCHEMA.01" succesfully completed at <timestamp>

Delete the dump file on the destination database system.

For example:
```
$ rm OC_schema.dmp
```
As root, log in to the Enterprise Controller system.

Use the ecadm command with the start subcommand and the -w option to start the Enterprise Controller.

For example:

# ./ecadm start -w
ecadm: Starting Enterprise Controller with SMF...
ecadm: Enterprise Controller services have started
#

Verifying the Database

You can verify that the configured database is operational and accessible from the Enterprise Controller.

To Verify the Database

Use the ecadm command with the verify-db subcommand to verify the database.

For example:
```
./ecadm verify-db
ecadm:    --- Verified database
#
```

Upgrading a Customer-Managed Database

You can upgrade the database software. To do so, you must shut down the Enterprise Controller. Before upgrading the database software, verify that the version you intend to upgrade to is supported.

To Upgrade the Customer-Managed Database

Use the ecadm command with the stop subcommand and the -w option to shut down the Enterprise Controller.

For example:

./ecadm stop -w
ecadm: Shutting down Enterprise Controller using SMF...
ecadm: Enterprise Controller services have stopped
#

Upgrade the customer-managed database according to the Oracle Database 11g Enterprise Edition upgrade documentation.

Use the ecadm command with the start subcommand and the -w option to start the Enterprise Controller.

For example:

# ./ecadm start -w
ecadm: Starting Enterprise Controller with SMF...
ecadm: Enterprise Controller services have started
#

Changing the Database Credentials

You can change the database password for the Oracle Enterprise Manager Ops Center user or the read-only user on an embedded or customer-managed database.

Changing the Database Credentials for the Ops Center User

To Change the Database Credentials for the Ops Center User

Create a temporary file containing the new password and secure it with 600 permissions.

For example:
```
# touch /tmp/password
# chmod 600 /tmp/password
# vi /tmp/password
newpassword
```

Use the ecadm command with the change-db-password subcommand and the -p <password file> option to change the database password. When prompted, confirm the Enterprise Controller restart.

For example:

# ./ecadm change-db-password -p /tmp/password
The Enterprise Controller will be restarted after the database password is changed. Continue? (y/n)
y
ecadm:    --- Changed database password, restarting.
ecadm: shutting down Enterprise Controller using SMF...
ecadm: Enterprise Controller services have stopped
ecadm: Starting Enterprise Controller with SMF...
ecadm: Enterprise Controller services have started
#

If you have a high availability configuration, the ecadm command will copy the new database properties to each remote cluster node. Enter the root password for each remote cluster node.

For example:

ecadm:    --- Changed database password, restarting.
The DB configuration file must now be copied to each remote cluster node.
You will be prompted for the root password for each node to perform the copy.
Copying to node OC-secondary
Password: password
<output omitted>
ecadm:    --- Enterprise Controller successfully started HA
#

Remove the temporary file containing the new password.

For example:
```
# rm /tmp/password
```

Changing the Database Credentials for the Read-Only User

To Change the Database Credentials for the Ops Center User

Create a temporary file containing the new password.

For example:
```
# vi /tmp/password
newpassword
```

Use the ecadm command with the change-db-password subcommand and the -p <password file> and -r options to change the database password. When prompted, confirm the Enterprise Controller restart.

For example:

# ecadm change-db-password -r -p /tmp/password
The Enterprise Controller will be restarted after the database password is changed. Continue? (y/n)
y
ecadm:    --- Changed database password, restarting.
ecadm: shutting down Enterprise Controller using SMF...
ecadm: Enterprise Controller services have stopped
ecadm: Starting Enterprise Controller with SMF...
ecadm: Enterprise Controller services have started
#

If you have a high availability configuration, the ecadm command copies the new database properties to each remote cluster node. Enter the root password for each remote cluster node.

For example:

ecadm:    --- Changed database password, restarting.
The DB configuration file must now be copied to each remote cluster node.
You will be prompted for the root password for each node to perform the copy.
Copying to node OC-secondary
Password: password
<output omitted>
ecadm:    --- Enterprise Controller successfully started HA
#

Remove the temporary file containing the new password.

For example:
```
# rm /tmp/password
```