This section describes maintenance and troubleshooting techniques for maintaining a well-performing Management Repository.
Specifically, this chapter contains the following sections:
To be sure that your management data is secure, reliable, and always available, consider the following settings and configuration guidelines when you are deploying the Management Repository:
Install a RAID-capable Logical Volume Manager (LVM) or hardware RAID on the system where the Management Repository resides. At a minimum the operating system must support disk mirroring and stripping. Configure all the Management Repository data files with some redundant configuration.
Use Real Application Clusters to provide the highest levels of availability for the Management Repository.
If you use Enterprise Manager to alert administrators of errors or availability issues in a production environment, be sure that the Cloud Control components are configured with the same level of availability. At a minimum, consider using Oracle Data Guard to mirror the Management Repository database. Configure Data Guard for zero data loss. Choose between Maximum Availability or Maximum Protection based on your environment and needs.
See Also:
Oracle Database High Availability Architecture and Best PracticesOracle Data Guard Concepts and Administration
Oracle strongly recommends that archive logging be turned on and that a comprehensive backup strategy be in place prior to an Enterprise Manager implementation going live in a production environment. The backup strategy should include archive backups and both incremental and full backups as required.
See Also:
Oracle Enterprise Manager Cloud Control Installation and Basic Configuration for information about the database initialization parameters required for the Management RepositoryOracle recommends that you not use SQL Plan Management (SQL plan baselines and capture) with the Enterprise Manager Cloud Control repository. If you do need to use it for a specific problem, shut it off immediately after using. Issues with the Enterprise Manager Cloud Control repository may occur when using SQL Plan Management, such as very poor SQL performance using unverified plans, and deadlocks between SQL Plan Management capture and the Enterprise Manager security VPD.
After enabling auditing for the repository database and for audit entries related to ORA- errors, error messages should be ignored if they are not reported in the Enterprise Manager application logs; for example, emoms.trc, the
MGMT_SYSTEM_ERROR_LOG table, or in the alert.log of the repository database. In these cases the errors are harmless.
To see a list of the regular maintenance activities that need to be performed for the repository, see the Sizing Your Enterprise Manager Deployment in the Oracle® Enterprise Manager Cloud Control Advanced Installation and Configuration Guide.
To monitor the repository database activities using the Enterprise Manager user interface, see Chapter 19, "Maintaining Enterprise Manager".
When the various components of Enterprise Manager are configured and running efficiently, the Oracle Management Service gathers large amounts of raw data from the Management Agents running on your managed hosts and loads that data into the Management Repository. This data is the raw information that is later aggregated, organized, and presented to you in the Cloud Control console.
After the Oracle Management Service loads information into the Management Repository, Enterprise Manager aggregates and purges the data over time.
The following sections describe:
The default aggregation and purging policies used to maintain data in the Management Repository.
How you can modify the length of time the data is retained before it is aggregated and then purged from the Management Repository.
Enterprise Manager aggregates collected metric data by hour and by day to enhance query performance and help minimize the size of the Management Repository. Before the data is aggregated, each data point is stored in a raw metric data table. Once a day, the previous day's raw metric data is rolled up, or aggregated, into a one-hour and a one-day table. These hourly and daily records will have hourly and daily metric data averages, minimums, maximums and standard deviations respectively.
After Enterprise Manager aggregates the data, the data is then considered eligible for purging. A certain period of time must pass for data to actually be purged. This period of time is called the retention time.
The raw data, with the highest insert volume, has the shortest default retention time, which is set to 7 days. As a result, 7 days after it is aggregated into a one-hour record, a raw data point is eligible for purging.
Note:
This data retention policy varies for JVMD and ADP data.Hourly aggregate metric data records are purged after 31 days. The highest level of aggregation, one day, is kept for 12 months (roughly 365 days).
The default data retention policies are summarized in Table 20-1.
Table 20-1 Default Repository Purging Policies
| Aggregate Level | Retention Time |
|---|---|
|
Raw metric data |
7 days |
|
Hourly aggregated metric data |
31 days |
|
Daily aggregated metric data |
24 months |
If you have configured and enabled Application Performance Management, Enterprise Manager also gathers, saves, aggregates, and purges response time data. The response time data is purged using policies similar to those used for metric data. The Application Performance Management purging policies are shown in Table 20-2.
Table 20-2 Default Repository Purging Policies for Application Performance Management Data
| Aggregate Level | Retention Time |
|---|---|
|
Raw response time data |
24 hours |
|
One-hour aggregated response time data |
7 days |
|
One-hour distribution response time data |
24 hours |
|
One-day aggregated response time data |
31 days |
|
One-day distribution aggregated response time data |
31 days |
If you do not want to keep severity data for the default period (6 months), and want to reduce the retention period for the EVENTS purge policy, you can use the following command:
em_purge.modify_purge_policy_group('EVENTS',NULL,*l_new_purge_hours*);
This command will modify only the purge policy group which will affect all the purge policies associated with that group. Note that if a purge policy is associated with a purge group, the retention period is taken as the retention period of the group. When the retention of a purge policy (associated with a purge policy group) is changed, then the retention is determined from the purge policy and not from the purge policy group.
To modify an individual purge policy use the following command:
em_purge.modify_purge_policy( p_policy_name IN VARCHAR2, p_retention_hours IN NUMBER )
You can modify the purge policy and also the partition retention values by choosing Manage Cloud Control from the Setup menu, then selecting Repository. From that page, choose the Schema tab and then make any necessary changes in the Purge Policies section (click Modify) or Partition Retention section.
Events data is partitioned and maintains six months of historical data by default. You can change the default retention period using the procedure described above. The severity data is tied to the events data purge policy and will be adjusted accordingly.
The fixed set of tables affected by this data purge are listed below:
EM_EVENT_SEQUENCES EM_EVENT_RAW EM_EVENT_MSGS EM_EVENT_CONTEXT EM_EVENT_ANNOTATIONS EM_EVENTS_INCIDENT EM_ISSUES_INTERNAL EM_ISSUES_MSG EM_ISSUES_ANNOTATIONS EM_INCIDENT_ISSUE EM_PROBLEM_ISSUE EM_INCIDENTS_PROBLEM
The following list is a dynamic set of tables that store data for different event types supported by Enterprise Manager. This list can vary over time as new event types or unsupported event types are added or removed:
EM_EV_CS_RULE_VIOLATION EM_EV_CS_SCORE EM_EV_JOB_STATUS_CHANGE EM_EV_METRIC_ALERT EM_EV_METRIC_ERROR EM_EV_MEXT_UPDATE EM_EV_MNTR_DISRUPTION EM_EV_SELFUPDATE EM_EV_SLA_ALERT EM_EV_TARGET_AVAILABILITY EM_EV_USER_REPORTED EM_EV_ADP_ALERT EM_EV_APM_KPI_ALERT EM_EV_JVMDIAG_ALERT EM_EV_HA_EVENT
Besides the metric data and Application Performance Monitoring data, other types of Enterprise Manager data accumulates over time in the Management Repository.
For example, the last availability record for a target will also remain in the Management Repository indefinitely, so the last known state of a target is preserved.
The Enterprise Manager default aggregation and purging policies were designed to provide the most available data for analysis while still providing the best performance and least disk-space requirements for the Management Repository. As a result, you should not modify these policies to improve performance or increase your available disk space.
However, if you plan to extract or review the raw or aggregated data using data analysis tools other than Enterprise Manager, you may want to increase the amount of raw or aggregated data available in the Management Repository. You can accomplish this by increasing the retention times for the raw or aggregated data.
A PL/SQL API has been provided to modify the default retention time for the core metric data tables in the Enterprise Manager repository. Table 20-3 shows the default number of partitions retained for each of the three tables and the size of the partitions for each table. The API will allow you to change the number of partitions retained only.
Table 20-3 Core EM Metric Data Tables and Default Data Retention in the Management Repository
| Table Name | Partitions Retained | Partition Size |
|---|---|---|
|
7 |
DAY |
|
|
32 |
DAY |
|
|
24 |
MONTH |
To modify the retention period for any of the above tables, execute the following command:
SQL> execute gc_interval_partition_mgr.set_retention('SYSMAN', <table name>, <number of partitions to retain>);
Replace the <table name> by name of table as listed above. The API will allow you to change the number of partitions retained only.
For example, to modify the default retention time for the table EM_METRIC_VALUES from 7 partitions to 14 partitions, follow these steps:
Use SQL*Plus to connect to the repository database as the SYSMAN user.
Check the current value of the retention periods:
SQL> select table_name, partitions_retained
from em_int_partitioned_tables
where table_name in ('EM_METRIC_VALUES','EM_METRIC_VALUES_HOURLY','EM_METRIC_VALUES_DAILY');
TABLE_NAME PARTITIONS_RETAINED
------------------------- -------------------
EM_METRIC_VALUES 7
EM_METRIC_VALUES_HOURLY 32
EM_METRIC_VALUES_DAILY 24
To modify the default retention time for the table EM_METRIC_VALUES from 7 partitions to 14, execute the following command:
SQL> execute gc_interval_partition_mgr.set_retention('SYSMAN', 'EM_METRIC_VALUES', 14);
Verify that the retention period has been modified:
SQL> select table_name, partitions_retained
from em_int_partitioned_tables
where table_name in ('EM_METRIC_VALUES','EM_METRIC_VALUES_HOURLY','EM_METRIC_VALUES_DAILY');
TABLE_NAME PARTITIONS_RETAINED
------------------------- -------------------
EM_METRIC_VALUES 14
EM_METRIC_VALUES_HOURLY 32
EM_METRIC_VALUES_DAILY 24
Enterprise Manager Cloud Control has a default purge policy which removes all finished job details which are older than 30 days. This section provides details for modifying this default purge policy.
The actual purging of completed job history is implemented via a DBMS_SCHEDULER job that runs once a day in the repository database. When the job runs, it looks for finished jobs that are 'n' number of days older than the current time (value of sysdate in the repository database) and deletes these jobs. The value of 'n' is, by default, set to 30 days.
The default purge policy cannot be modified via the Enterprise Manager console, but it can be changed using SQL*Plus.
To modify this purge policy, follow these steps:
Log in to the repository database as the SYSMAN user, via SQL*Plus.
Check the current values for the purge policies using the following command:
SQL> select * from mgmt_job_purge_policies;
POLICY_NAME TIME_FRAME -------------------------------- ---------- SYSPURGE_POLICY 30 REFRESHFROMMETALINKPURGEPOLICY 7 FIXINVENTORYPURGEPOLICY 7 OPATCHPATCHUPDATE_PAPURGEPOLICY 7
The purge policy responsible for the job deletion is called SYSPURGE_POLICY. As seen above, the default value is set to 30 days.
To change the time period, you must drop and recreate the policy with a different time frame:
SQL> execute MGMT_JOBS.drop_purge_policy('SYSPURGE_POLICY');
PL/SQL procedure successfully completed.
SQL> execute MGMT_JOBS.register_purge_policy('SYSPURGE_POLICY', 60, null);
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL> select * from mgmt_job_purge_policies;
POLICY_NAME TIME_FRAME -------------------------------- ---------- SYSPURGE_POLICY 60 ....
The above commands increase the retention period to 60 days. The time frame can also be reduced below 30 days, depending on the requirement.
You can check when the purge job will be executed next. The actual time that the purge runs is set to 5 AM repository time and can be verified using these steps:
Login to the Repository database using the SYSMAN account.
Execute the following command:
SQL> select job_name,
to_char(last_start_date, 'DD-MON-YY HH24:MI:SS') last_run,
to_char(next_run_date, 'DD-MON-YY HH24:MI:SS') next_run
from all_scheduler_jobs
where job_name ='EM_JOB_PURGE_POLICIES';
JOB_NAME LAST_RUN NEXT_RUN
--------------------- ------------------ ------------------
EM_JOB_PURGE_POLICIES 07-SEP-11 05:00:00
The schedule can also be verified from the Enterprise Manager console by following these steps:
From the Setup menu, select Management Service, then select Repository.
Click the Repository Operations tab.
Find the Next Scheduled Run and Last Scheduled Run information for Job Purge in the list.
Please note that the time of the next scheduled execution of the Job Purge does not represent the cutoff time for the retention period; the cutoff time is determined by the purge policy at the time the Job Purge runs.
Enterprise Manager uses the database scheduler (dbms_scheduler) to run various processes in the repository. When the dbms_scheduler is stopped or has insufficient resources to operate, the Enterprise Manager processes do not run or are delayed. The following is a list of common causes that may prohibit the dbms_scheduler from running normally.
Job Queue Processes
The dbms_scheduler uses a separate job-queue process for each job it runs. The maximum number of these processes is controlled by the database parameter,
job_queue_processes. If all processes are in use, no new jobs will be started.
The following query returns the number of currently running jobs.
SQL> SELECT count(*) FROM dba_scheduler_running_jobs;
If the count is close to the setting of job_queue_processes, it could mean that Enterprise Manager dbms_scheduler jobs cannot be started (on time). Determine if any of the running dbms_scheduler jobs are stuck and consider increasing the setting for
job_queue_processes.
The dbms_scheduler also depends on the setting of the dbms_scheduler property MAX_JOB_SLAVE_PROCESSES. If the number of running dbms_scheduler jobs exceeds this setting, no new jobs will be started. This attribute can be checked using this query.
SQL> SELECT value FROM dba_scheduler_global_attribute WHERE attribute_name='MAX_JOB_SLAVE_PROCESSES';
If the count equals the number of running dbms_scheduler jobs, then determine if any of the running dbms_scheduler jobs are stuck and consult the dbms_scheduler documentation about how to adjust this attribute.
DBMS_SCHEDULER Program Disabled
The dbms_scheduler has an attribute that can be set to disable this feature in the database. When set, the Enterprise Manager dbms_scheduler jobs will not run. To check if this attribute has been set (inadvertently), run this query.
SQL> SELECT * FROM dba_scheduler_global_attribute WHERE attribute_name = 'SCHEDULER_DISABLED';
When a row is returned, the dbms_scheduler is disabled. Execute
dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');
Consult the dbms_scheduler documentation about how to remove this attribute.
Too Many Database Sessions
Each dbms_scheduler job requires two database sessions. When no more sessions are available, Enterprise Manager dbms_scheduler jobs will not run. The following two queries give the maximum number of allowed sessions and the current number of active sessions:
SQL> SELECT value FROM v$parameter WHERE name='sessions';
SQL> SELECT count(*)FROM v$session;
When the current number of sessions approaches the maximum, then you should determine if any of the sessions are stuck and consult the Oracle Database documentation about how to increase the maximum number of sessions.
Also the high water mark of the number of sessions may indicate that this issue has played a role in the past:
SQL> select * from v$resource_limit where resource_name = 'sessions' ;
If the MAX_UTILIZATION column indicates a value that is close the maximum number of sessions, it could explain why some of the Enterprise Manager dbms_scheduler jobs may not have run (on time) in the past.
Insufficient Memory
The database may not be able to spawn a new job queue process when there is insufficient memory available. The following message in the database alert file, Unable to spawn jobq slave processes, in combination with, (free memory = 0.00M), would be indicative of this problem. Please consult the Oracle Database documentation about how to diagnose this memory problem further.
This section provides information about dropping the Management Repository from your existing database and recreating the Management Repository after you install Enterprise Manager.
It should be noted here that there is no recovery from the drop command so this action is only appropriate if you are decommissioning an Enterprise Manager site.
To recreate the Management Repository, you first remove the Enterprise Manager schema from your Management Repository database. You accomplish this task using the -action drop argument to the RepManager script, which is described in the following procedure.
To remove the Management Repository from your database:
Locate the RepManager script in the following directory of the Middleware Home where you have installed and deployed the Management Service:
ORACLE_HOME/sysman/admin/emdrep/bin
At the command prompt, enter the following command:
$PROMPT> RepManager repository_host repository_port repository_SID -sys_password password_for_sys_account -action drop
In this syntax example:
repository_host is the machine name where the Management Repository database is located
repository_port is the Management Repository database listener port address, usually 1521
repository_SID is the Management Repository database system identifier
password_for_sys_account is the password of the SYS user for the database. For example, change_on_install
-action drop indicates that you want to drop the Management Repository, MDS, OPSS, APM, and Schemas. If you use drop, the command drops only the Management Repository.
Alternatively, you can use a connect descriptor to identify the database on the RepManager command line. The connect descriptor identifies the host, port, and name of the database using a standard Oracle database syntax.
For example, you can use the connect descriptor as follows to create the Management Repository:
$PROMPT> ./RepManager -connect "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=host1)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=servicename)))" -sys_password efkl34lmn -action drop
See Also:
"Establishing a Connection and Testing the Network" in the Oracle Enterprise Manager Licensing Information for more information about connecting to a database using connect descriptors.The preferred method for creating the Management Repository is to create the Management Repository during the Enterprise Manager installation procedure, which is performed using Oracle Universal Installer.
See Also:
Oracle Enterprise Manager Cloud Control Installation and Basic Configuration for information about installing Enterprise Manager.In the event a repository is dropped, you cannot create the repository alone using the "RepManager create" command. The command will not create all the required users in the repository database. To create the repository you must completely reinstall Cloud Control.
If you are following recommended best practices by regularly backing up the repository, then you can use a backup of the repository as long as any one of the following is true:
The primary OMS home is intact
There is an export/config of the primary OMS
There is a file system back up of the primary OMS
You can use a connect descriptor to identify the database on the RepManager command line. The connect descriptor identifies the host, port, and name of the database using a standard Oracle database syntax.
For example, you can use the connect descriptor as follows to create the Management Repository:
$PROMPT> ./RepManager -connect "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=host1)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=servicename)))" -sys_password efkl34lmn -action create
See Also:
"Establishing a Connection and Testing the Network" in the Oracle Enterprise Manager Licensing Information for more information about connecting to a database using a connect descriptorThe ability to use a connect string allows you to provide an address list as part of the connection string. The following example shows how you can provide an address list consisting of two listeners as part of the RepManager command line. If a listener on one host becomes unavailable, the second listener can still accept incoming requests:
$PROMPT> ./RepManager -connect "(DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521) (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521) (CONNECT_DATA=(SERVICE_NAME=servicename)))" -sys_password efkl34lmn -action create
See Also:
Oracle Database High Availability Architecture and Best PracticesOracle Enterprise Manager Cloud Control Installation and Basic Configuration
Oracle Universal Installer creates the Management Repository using a configuration step at the end of the installation process. If the repository configuration tool fails, note the exact error messages displayed in the configuration tools window, wait until the other configuration tools have finished, exit from Universal Installer, and then use the following sections to troubleshoot the problem.
If the creation of your Management Repository is interrupted, you may receive the following error when you attempt to create or drop the Management Repository at a later time:
SQL> ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-04068: existing state of packages has been discarded ORA-04067: not executed, package body "SYSMAN.MGMT_USER" does not exist ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at "SYSMAN.SETEMUSERCONTEXT", line 5 ORA-06512: at "SYSMAN.CLEAR_EMCONTEXT_ON_LOGOFF", line 4 ORA-06512: at line 4
To fix this problem, see "General Troubleshooting Techniques for Creating the Management Repository".
If you receive an error such as the following when you try to connect to the Management Repository database, you are likely using an unsupported version of the Oracle Database:
Server Connection Hung
To remedy the problem, upgrade your database to the supported version as described in Oracle Enterprise Manager Cloud Control Installation and Basic Configuration.
If you encounter an error while creating the Management Repository, drop the repository by running the -drop argument to the RepManager script.
If the RepManager script drops the repository successfully, try creating the Management Repository again.
If the RepManager -action drop/drop fails for any reason, perform the following steps:
Apply the Bundle Patch to the 12c OMS home. Note that this step is only applicable to 12.1.0.1 OMS. Refer to My Oracle Support Note 1393173.1: Enterprise Manager Cloud Control Installation Instructions for Bundle Patch 1 and 12.1.0.2 Plug-ins for instructions.
Stop the OMS and verify that all the WLS / OMS processes have been stopped in the OMS home:
cd <ORACLE_HOME>/bin emctl stop oms -all
Note:
You should use the -all option so that the Admin Server is stopped as wellVerify that there are no WLS / OMS processes still running:
$ ps -ef | grep EMGC $ ps -ef | grep java
Drop the repository objects using the "Repmanager drop" command:
cd <ORACLE_HOME>/sysman/admin/emdrep/bin RepManager <database hostname> <database listener port> <database sid> -action drop -dbUser sys -dbPassword <sys user password> -dbRole sysdba -mwHome <Middleware Home> -mwOraHome <Middleware Home> -oracleHome <OMS Home>
For example:
RepManager repomachine.domain 1521 orcl -action drop -dbUser sys -dbPassword oracle123 -dbRole sysdba -mwHome /home/oracle/Middleware -mwOraHome /home/oracle/Middleware -oracleHome /home/oracle/Middleware/oms
Log in to the Repository Database as sys or any DBA user and verify that all the repository objects have been dropped:
SQL> select username,account_status from dba_users where username in ('SYSMAN', 'SYSMAN_MDS','MGMT_VIEW','SYSMAN_BIPLATFORM','SYSMAN_APM','BIP','SYSMAN_OPSS','SYSMAN_RO') ;
SQL> select owner,synonym_name from dba_synonyms where table_owner in ('SYSMAN', 'SYSMAN_MDS','MGMT_VIEW','SYSMAN_BIPLATFORM','SYSMAN_APM','BIP','SYSMAN_OPSS','SYSMAN_RO') ;
SQL> select tablespace_name from dba_tablespaces where tablespace_name like 'MGMT%';
SQL> select comp_name from SCHEMA_VERSION_REGISTRY;
None of the above queries should return any rows. If any of the above queries return any rows, then raise an SR with Oracle Support.
Note:
The above solution is applicable if the OMS is in working condition. If the OMS home is not available or not intact, raise an SR with Oracle Support.There are user requirements for migrating an Enterprise Manager repository across servers - same and cross platforms.
The Enterprise Manager repository migration process is not exactly the same as database migration. In the case of Enterprise Manager Repository migration you must take care of Enterprise Manager specific data, options, and pre-requisites for the repository move. You should make sure data integrity is maintained from both the Enterprise Manager and Oracle database perspective.
This raises the need for defining the process that can be followed by end users for successful and reliable migration of repository in minimum time and with maximum efficiency.
The overall strategy for migration depends on:
The source and target database version
The amount of data/size of repository
Actual data to migrate [selective/full migration]
If the source and target is not on release 12c then export/import is the only way to get the data migrated cross platform.
More details on cross platform transportable tablespace, data pump, and export/import options can be found at the Oracle Technology Network (OTN) or in the Oracle Database Administrator's Guide.
The following lists the common prerequisites for a repository migration:
Source and target database must use the same character set and should be at same version.
The source and target database platform must be at the same endian format.
The target database should meet all the pre-requisites for the Enterprise Manager Repository software requirements mentioned in the Oracle Enterprise Manager Installation Guide.
If the source and target database are on release 10gR2 and higher rdbms versions, and provided they are meeting other prerequisites, cross platform transportable database migration can be used for cross platform repository migration.
You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
To plug a transportable tablespace set into an Oracle Database on a different platform, both databases must have compatibility set to at least Release 10.0.
Most of the platforms (but not all) are supported for cross-platform tablespace transport. You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine their platform IDs and their endian format (byte ordering).
Source and Destination host should have Enterprise Manager Management Agent running and configured to the instance which is to be migrated.
If the target database has an Enterprise Manager repository installed, it should be first dropped using RepManager before target database related steps are carried out.
The following sections discuss two methodologies for a repository migration:
Oracle's transportable database feature allows users to quickly move a user tablespace across Oracle databases. It is the most efficient way to move bulk data between databases. With the cross platform transportable database, you can transport tablespaces across platforms.
Cross platform transportable database allows a database to be migrated from one platform to another (use with Data Pump or Import/Export). The following set of steps for migration using Transportable Database can be used for migrations between same-endian platforms:
Verify whether migration is possible on the destination platform from v$db_transportable_platform.
SQL> select platform_name from v$db_transportable_platform;
You may see a list of platforms similar to the list below:
Microsoft Windows IA (32-bit) Linux IA (32-bit) HP Tru64 UNIX Linux IA (64-bit) HP Open VMS Microsoft Windows IA (64-bit) Linux x86 64-bit Microsoft Windows x86 64-bit Solaris Operating System (x86) HP IA Open VMS Solaris Operating System (x86-64)
Verify that the external tables and files exist in the database.
SQL> set serveroutput on SQL> declare x boolean; 2 begin x := dbms_tdb.check_external; 3 end; 4 /
The following output results:
The following external tables exist in the database: SH.SALES_TRANSACTIONS_EXT The following directories exist in the database: SYS.SUBDIR, SYS.SS_OE_XMLDIR, SYS.MEDIA_DIR, SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.XMLDIR, SYS.DATA_PUMP_DIR, SYS.ORACLE_OCM_CONFIG_DIR The following BFILEs exist in the database: PM.PRINT_MEDIA
Enter the following command:
SQL> select directory_path from dba_directories;
The following output results:
DIRECTORY_PATH -------------------------------------------------------------------------------- /home/oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep /home/oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/ /home/oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/ /home/oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/ /ade/b/1191423112/oracle/rdbms/xml /home/oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/ /home/oracle/app/oracle/admin/orcl/dpdump/ /home/oracle/app/oracle/product/11.2.0/dbhome_1/ccr/state 8 rows selected.
Enter the following command:
SQL> select directory_path||'/'||location External_file_path from dba_directories a, dba_external_locations b where a.directory_name=b.directory_name;
The following output results:
EXTERNAL_FILE_PATH ---------------------------------------------------------------------------- /home/oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history//sale1v3.dat
Enter the following command:
SQL> @tgt_get_bfile_dirs.sql
The following output results:
The following directories contain external files for BFILE columns Copy the files within these directories to the same path on the target system /home/oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/ There are 1 directories, 4 total BFILEs SQL> @tgt_get_bfiles.sql External files for BFILE column AD_GRAPHIC in table PM.PRINT_MEDIA /home/oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media//monitor.jpg /home/oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media//mousepad.jpg /home/oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media//keyboard.jpg /home/oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media//modem.jpg
Stop the OMS.
emctl stop oms -all
Enter the following SQL commands:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1473089536 bytes Fixed Size 1336596 bytes Variable Size 1124076268 bytes Database Buffers 335544320 bytes Redo Buffers 12132352 bytes Database mounted.
Open the database in read-only mode.
SQL> alter database open read only;
Enter the following SQL commands:
SQL> set serveroutput on
SQL> declare
2 retcode boolean;
3 begin
4 retcode := dbms_tdb.check_db('Linux IA (64-bit)',dbms_tdb.skip_none);
5 end;
6 /
SQL> declare
2 retcode boolean;
3 begin
4 retcode := dbms_tdb.check_db('Linux x86 64-bit',dbms_tdb.skip_none);
5 end;
6 /
PL/SQL procedure successfully completed.
Generate the RMAN conversion script.
[oracle]$ ./rman Recovery Manager: Release 11.2.0.1.0 - Production on Fri dd-mm-yy 12:10:29 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> connect target / connected to target database: ORCL (DBID=1308105793) RMAN> convert database on target platform 2> convert script '/tmp/convert_mydb.rman' 3> transport script '/tmp/transport_mydb.sql' 4> new database 'mydb' 5> format '/tmp/mydb%U' 6> db_file_name_convert '/home/oracle/app/oracle/oradata/mydb/','/tmp'; Starting conversion at source at dd-mm-yy using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=135 device type=DISK External table SH.SALES_TRANSACTIONS_EXT found in the database Directory SYS.SUBDIR found in the database Directory SYS.SS_OE_XMLDIR found in the database Directory SYS.MEDIA_DIR found in the database Directory SYS.LOG_FILE_DIR found in the database Directory SYS.DATA_FILE_DIR found in the database Directory SYS.XMLDIR found in the database Directory SYS.DATA_PUMP_DIR found in the database Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database BFILE PM.PRINT_MEDIA found in the database User SYS with SYSDBA and SYSOPER privilege found in password file channel ORA_DISK_1: starting to check datafiles input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00 channel ORA_DISK_1: starting to check datafiles input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00 channel ORA_DISK_1: starting to check datafiles input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/mgmt.dbf channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00 channel ORA_DISK_1: starting to check datafiles input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00 channel ORA_DISK_1: starting to check datafiles input datafile file number=00008 name=/home/oracle/app/oracle/oradata/orcl/mgmt_ad4j.dbf channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00 channel ORA_DISK_1: starting to check datafiles input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00 channel ORA_DISK_1: starting to check datafiles input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/mgmt_depot.dbf channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00 channel ORA_DISK_1: starting to check datafiles input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00 Edit init.ora file /tmp/init_mydb00nbs6dl_1_0.ora. This PFILE will be used to create the database on the target platform Run RMAN script /tmp/convert_mydb.rman on target platform to convert datafiles Run SQL script /tmp/transport_mydb.sql on the target platform to create database To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform To change the internal database identifier, use DBNEWID Utility Finished conversion at source at dd-mm-yy
Copy all the required files to a temporary location and mount on the target machine.
convert_mydb.rman init_mydb.ora transport_mydb.sql (and other data files listed in rman script [convert_mydb.rman] and redolog files)
Execute the scripts generated in Step 5 on the target machine.
The RMAN script contains convert datafile commands. The SQL script contains control file creation, invalidating objects, and recompiling objects. On the target machine, execute the following:
RMAN> connect target /
RMAN> @/home/oracle/migrate/convert_mydb.rman
Ensure the database is up and running and the database is registered with the listener.
RMAN> STARTUP NOMOUNT PFILE = '/home/oracle/migrate/init_mydb00nbs6dl_1_0.ora'; database is already started
Start the OMS to ensure the admin server is up.
[oracle]$ ./emctl status oms
[oracle]$ ./emctl start oms
Stop the OMS.
[oracle]$ ./emctl stop oms
Update repository database connection details.
[oracle]$ ./emctl config oms -store_repos_details -repos_conndesc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=evildead.idc.oracle.com)(PORT=1521)))(CONNECT_DATA=(SID=mydb)))" -repos_user SYSMAN -repos_pwd Oracle123
If there are multiple Oracle Management Services in this environment, run this store_repos_details command on all of them.
Restart the OMS.
[oracle]$ ./emctl start oms
[oracle]$ ./emctl status oms
Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.4.0 Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved. WebTier is Up Oracle Management Server is Up
Relocate Management Services and the Repository target.
The following steps describe the process you can use to migrate a repository using Physical Standby. This method can be used when the source and target platforms are supported. See My Oracle Support Note:413484.1 for details of which platform combinations are supported.
Install the database ORACLE_HOME on the target machine. The binaries should be the same version as the source.
If the target machine is Windows, install and configure CYGWIN on the Windows box for Management Agent deployment.
Deploy the Management Agent to the target server.
Create a Physical Standby as described in the Data Guard documentation.
Configure the Data Guard broker as described in the Data Guard Broker documentation.
Shutdown the OMS.
emctl stop oms -all
Check the OMS connect descriptor.
./emctl config oms -list_repos_details
Switchover the database using dgmgrl.
Use the following commands:
DGMGRL> switchover to target_db verify show configuration show database target_db show database source_db
Start the OMS admin server.
emctl start oms -admin_only
Update connect descriptor to point to the Standby Database.
eemctl config oms -store_repos_details -repos_conndesc "(DESC= )" -repos_user sysman
[oracle]$ ./emctl config oms -store_repos_details -repos_conndesc "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sample2.us.company.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test_win)))" -repos_user sysman
Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
Enter Repository User's Password :
Successfully updated datasources and stored repository details in Credential Store.
If there are multiple Oracle Management Services in this environment, run this store_repos_details command on all of them.
Stop all the Oracle Management Services.
emctl stop oms -all
Start the OMS.
emctl start oms
Relocate Oracle Management Services and the Repository.
emctl config emrep -conn_desc
[oracle]$ ./emctl config emrep -conn_desc "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sample2.us.company)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test_win)))" Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.4.0 Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved. Please enter repository password: Enter password : Login successful Moved all targets from sample.us.company.com:3872 to sample2.us.company.com:3872 Command completed successfully! Enter password : Login successful Moved all targets from sample.us.company.com:3872 to sample2.us.company.com:3872 Command completed successfully!
Create a backup of the OMS (on the OMS where the Admin server is running).
$ <ORACLE_HOME>/bin/emctl exportconfig oms [-sysman_pwd <sysman password>]
Specify the directory in which to store backup file
[-dir <backup dir>]
Specify the following parameter if the OMS was installed using a virtual hostname (using ORACLE_HOSTNAME=<virtual_hostname>)
[-keep_host]
These verification steps should be carried out post migration to ensure that the migration was completely successful:
Verify any discrepancy in objects by comparing source and target databases through Enterprise Manager.
Verify the migrated database through Enterprise Manager to determine whether the database is running without any issues.
Verify the repository operations, dbms jobs and whether any management system errors are reported.
Verify that all Enterprise Manager functionalities are working correctly after the migration.
Make sure Management Services and the Repository target is properly relocated by verifying it through Enterprise Manager.