If you are creating your Pearson VUE account and you have already passed some Oracle certifications earlier, you need to put your old Prometric id so that your old certifications at Prometric get merged automatically with the new Pearson account. But, if you forgot your old Prometric id, as happened to me, you can probably get it by raising SR with Oracle certification team at:
I got my ID by raising SR at this link, for this, I provided my first name, last name and Date-of-Birth. Oracle did not ask any further questions and replied back with my testing-id 🙂
In this post we discussed about GoldenGate(GG) concepts and setting GG without DataPump Process.
Now I will talk about DataPump process and show how to setup GG replication which also uses DataPump.
In GG environment, extract process will write the data to local trail files and then pump process reads from this local trail file and sends the data, over the network, to destination trail file where this will be read by replicat process to write the changes to destination database.
The advantage of using datapump process is to improve the performance of overall replication as it will free-up the extract process from sending the data to destination side.
To setup this, download and extract the GG software as discussed in earlier post(Step 1-7).
Once the GG software is extracted, go the directory where it is extracted, know as GG home directory.
In this case it will be /u01/app/GGS.
1. Configure and start Manager process:
GGSCI (gg1.company.com) 1> edit params mgr
It will open the parameter file for manager. Enter the following in the file:
Target system:
==============
1. On target database, we need to configure Replicat process which will apply the changes
to target database after reading from remote trail file.
Syntax:
ADD REPLICAT <REPLICAT name>, EXTTRAIL <trail file path/two -- character trail id> Name of the replicat process.
GGSCI (gg2.company.com) 8> add replicat rep_gg2, exttrail /u01/app/GGS/dirdat/rt
ERROR: No checkpoint table specified for ADD REPLICAT.
2. Add checkpoint table.
A checkpoint table is a small table in the target database used by the Replicat
process to maintain checkpoints. A checkpoint records a known position in the trail from which
to start after an expected or unexpected shutdown. By default, a record of these checkpoints is maintained
in a file on disk in the GoldenGate directory. Optionally, the checkpoint record can also be maintained in a
checkpoint table in the target database.
More Information about check point table can be found in MOS note: 965698.1
GGSCI (gg2.company.com) 10> add checkpointtable chktbl
Successfully created checkpoint table CHKTBL.
GGSCI (gg2.company.com) 13> add replicat rep_gg2, exttrail /u01/app/GGS/dirdat/rt, checkpointtable gggate.chktbl
REPLICAT added.
GGSCI (gg2.company.com) 14> info *
REPLICAT REP_GG2 Initialized 2012-06-10 16:58 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:12 ago)
Log Read Checkpoint File /u01/app/GGS/dirdat/rt000000
First Record RBA 0
Now start all the processes on Source system.
GGSCI (gg1.company.com) 11> start manager
Manager started.
GGSCI (gg1.company.com) 12> start extract ext_gg1
Sending START request to MANAGER ...
EXTRACT EXT_GG1 starting
GGSCI (gg1.company.com) 13> start extract pump_gg1
Sending START request to MANAGER ...
EXTRACT PUMP_GG1 starting
GGSCI (gg1.company.com) 15> info *
EXTRACT EXT_GG1 Last Started 2012-06-10 17:02 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint Oracle Redo Logs
2012-06-10 17:02:20 Seqno 108, RBA 14447104
EXTRACT PUMP_GG1 Last Started 2012-06-10 17:02 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:30:18 ago)
Log Read Checkpoint File /u01/app/GGS/dirdat/lt000000
First Record RBA 0
[oracle@gg1 GGS]$ ls -ltr /u01/app/GGS/dirdat/lt000000
-rw-rw-rw- 1 oracle oinstall 983 Jun 10 17:02 /u01/app/GGS/dirdat/lt000000
GGSCI (gg1.company.com) 2> exit
Once the processes are strated, we will create the sample tables for replication using Oracle provided scripts.
These scripts can be found under /u01/app/GGS
[oracle@gg1 GGS]$ ls -ltr *demo*
-r--r--r-- 1 oracle oinstall 2275 Oct 15 2010 demo_ora_misc.sql
-r--r--r-- 1 oracle oinstall 4015 Oct 15 2010 demo_ora_lob_create.sql
-r--r--r-- 1 oracle oinstall 821 Oct 15 2010 demo_ora_insert.sql
-r--r--r-- 1 oracle oinstall 883 Oct 15 2010 demo_ora_create.sql
-r--r--r-- 1 oracle oinstall 2520 Oct 15 2010 demo_ora_pk_befores_updates.sql
-r--r--r-- 1 oracle oinstall 1227 Oct 15 2010 demo_ora_pk_befores_insert.sql
-r--r--r-- 1 oracle oinstall 1269 Oct 15 2010 demo_ora_pk_befores_create.sql
-r--r--r-- 1 oracle oinstall 967 Oct 15 2010 demo_more_ora_insert.sql
-r--r--r-- 1 oracle oinstall 1217 Oct 15 2010 demo_more_ora_create.sql
we will create tables for mapping:
Source:
======
SQL> connect scott/tiger
Connected.
SQL> @demo_ora_create.sql
DROP TABLE tcustmer
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
DROP TABLE tcustord
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
SQL>
Target:
=======
SQL> conn scott/tiger
Connected.
SQL> @demo_ora_create.sql
DROP TABLE tcustmer
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
DROP TABLE tcustord
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
SQL> select count(*) from tcustmer;
COUNT(*)
----------
0
SQL>
Once the structure is created, we will tell the extract, pump and replicat process
to capture/apply the changes done under these tables. For that we need to configure
the respective parameter files and these should look like:
Once the parameter files are updated with the table information, we can use the
Oracle provided scripts to test the replication i.e “demo_ora_insert.sql”
After running the insert on source, we can see the count(*) on both source and target,
which will confirm the changes applied at the target side.
The first problem reported by my EM 12c is an ORA-7445 error. After checking the alert log following is the exact message:
Sun Feb 19 12:09:41 2012
Errors in file /u01/app/oracle/diag/rdbms/pkldb/pkldb/trace/pkldb_j035_14712.trc (incident=52961):
ORA-07445: exception encountered: core dump [kokscold()+849] [SIGSEGV] [ADDR:0x50] [PC:0x115E16F] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/pkldb/pkldb/incident/incdir_52961/pkldb_j035_14712_i52961.trc
From the incident trace file:
*** 2012-02-19 12:09:43.016
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=azmq8z48r7wk4) -----
/* SQL Analyze(117,1) */ MERGE INTO MGMT_ARU_PATCH_RECOMMENDATIONS p USING (SELECT :1 as patch_guid, :2 as PATCH_TYPE, :3 as BUG_ID, :4 as REQUEST_ID, :5 as PLATFORM_ID, :6 as PRODUCT_ID, :7 as RELEASE_ID , :8 as LANGUAGE_ID, :9 as CLASSIFICATION_ID, :10 as PLATFORM, :11 as PRODUCT, :12 as RELEASE, :13 as LANGUAGE, :14 as CLASSIFICATION, :15 as ARU_TARGET_TYPE, :16 as ABSTRACT, :17 as RELEASE_DATE, :18 as LOAD_TIMESTAMP, :19 as TEXT_CONTENT, :20 as SUPPORT_LEVEL, :21 as SUPPORT_LEVEL_ID FROM DUAL) n ON (p.patch_guid = n.patch_guid) WHEN MATCHED THEN UPDATE SET p.PATCH_TYPE = n.PATCH_TYPE, p.BUG_ID = n.BUG_ID, p.REQUEST_ID = n.REQUEST_ID, p.PLATFORM_ID = n.PLATFORM_ID, p.PRODUCT_ID = n.PRODUCT_ID, p.RELEASE_ID = n.RELEASE_ID, p.LANGUAGE_ID = n.LANGUAGE_ID, p.CLASSIFICATION_ID = n.CLASSIFICATION_ID, p.PLATFORM = n.PLATFORM, p.PRODUCT = n.PRODUCT, p.RELEASE = n.RELEASE, p.LANGUAGE = n.LANGUAGE, p.CLASSIFICATION = n.CLASSIFICATION, p.ARU_TARGET_TYPE = n.ARU_TARGET_TYPE, p.ABSTRACT = n.ABSTRACT, p.RELEASE_DATE = n.RELEASE_DATE,p.LOAD_TIMESTAMP = n.LOAD_TIMESTAMP, p.TEXT_CONTENT = n.TEXT_CONTENT, p.SUPPORT_LEVEL = n.SUPPORT_LEVEL, p.SUPPORT_LEVEL_ID = n.SUPPORT_LEVEL_ID WHEN NOT MATCHED THEN INSERT (PATCH_GUID, PATCH_TYPE, BUG_ID, REQUEST_ID, PLATFORM_ID, PRODUCT_ID, RELEASE_ID, LANGUAGE_ID, CLASSIFICATION_ID, PLATFORM, PRODUCT, RELEASE, LANGUAGE, CLASSIFICATION, ARU_TARGET_TYPE, ABSTRACT, RELEASE_DATE,LOAD_TIMESTAMP, TEXT_CONTENT, SUPPORT_LEVEL, SUPPORT_LEVEL_ID) VALUES (n.PATCH_GUID, n.PATCH_TYPE, n.BUG_ID, n.REQUEST_ID, n.PLATFORM_ID, n.PRODUCT_ID, n.RELEASE_ID, n.LANGUAGE_ID, n.CLASSIFICATION_ID, n.PLATFORM, n.PRODUCT, n.RELEASE, n.LANGUAGE, n.CLASSIFICATION, n.ARU_TARGET_TYPE, n.ABSTRACT, n.RELEASE_DATE,n.LOAD_TIMESTAMP, n.TEXT_CONTENT, n.SUPPORT_LEVEL, n.SUPPORT_LEVEL_ID)
----- PL/SQL Call Stack -----
object line object
handle number name
0x80bd47c0 11774 package body SYS.DBMS_SQLTUNE_INTERNAL
0x80b50a80 7 SYS.WRI$_ADV_SQLTUNE
0x82d550f8 545 package body SYS.PRVT_ADVISOR
0x82d550f8 2613 package body SYS.PRVT_ADVISOR
0x80bf0da8 241 package body SYS.DBMS_ADVISOR
0x81987268 772 package body SYS.DBMS_SQLTUNE
0x8187d290 4 anonymous block
*** 2012-02-19 12:09:42.839
> (0x115e16f) mov 0x50(%r11),%rdx
(0x115e173) movzbl 0x1(%r13),%ecx
(0x115e178) movzbl 0x1(%r15),%r9d
(0x115e17d) mov 0x8(%rdx),%edx
(0x115e180) lea 0x10(%r13),%r8
The error seems to be coming from some automated scheduled SQL Tuning Task (Yet to figureout that). Searching at MOS gives reference to bug 9594372, which shows that the problem is with merge statement. It says that applying the patch will fix the issue.
While checking the error from my EM12c, it also gives a search button to search the error at MOS( I have already installed the MOS plug-in) which I found is very good thing as I need not to login to separate MOS windows.
This post describes the steps to upgrade an existing 11g Enterprise Manager Grid Control (11.1.0.1.0) to 12c (12.1.0.1.0). The Complete Upgrade process will be carried out under 3 different stages:
1. Patch existing 11g OMS to get the 12c upgrade console. 2. Deploy and Configure 12c agents. 3. Upgrade 11g OMS to 12c.
The approach used for this upgrade is 1-system upgrade approach. This approach enables you to upgrade to Enterprise Manager Cloud Control on the same host where your earlier release of Enterprise Manager is running. This approach also upgrades the Oracle Management Repository (Management Repository) in the existing database. Since the upgrade happens on the same host, there is a reasonable downtime involved. This approach does not refer to upgrading of an Enterprise Manager system in an environment with one Oracle Management Service (OMS). It refers to upgrading of an Enterprise Manager system on the same host as the old one, and having only one Enterprise Manager system existing at any point.
The supported earlier releases for upgrade include:
Oracle Management Agent 10g Release 2 (10.2.x.x.x), Oracle Management Agent 11g Release 1 (11.1.0.1.0), Oracle Management Service 10g Release 5 (10.2.0.5.0) and Oracle Management Service 11g Release 1(11.1.0.1.0).
Unlike earlier releases Oracle Management Service 12c communicates only with Oracle Management Agent 12c. Therefore, unlike the earlier releases, you must first upgrade the Management Agents in your system before upgrading your existing OMS.
1. Patch existing 11g OMS to get the 12c upgrade console
To start the upgrade process we need to get the 12c upgrade console, which we can get by installing patch for bug# 10044087 on your existing Enterprise Manager system. To apply this patch OMS needs to be down which means that there is a downtime required for the existing GridControl environment. Download the patch and apply for your platform click HERE for download link.
This patch application is a simple patch apply process with opatch. Following are the mandatory things before you start patch apply:
— It is mandatory to apply PSU1(10065631) or later on the existing OMS home
— There is a chance of hitting bug 9953045 and bug 12360281, to escape these situations, set SQLNET.ENCRYPTION_TYPES_SERVER= (3DES168) in sqlnet.ora of your oracle database sqlnet file.
Follow the patch readme to apply this patch. Once this patch is applied and the post scripts(puc_dblink_pkgdef.sql, puc_dblink_pkgbody.sql and pre_upg_console.sql) are run and OMS is started, you can see the “Enterprise manager 12c Upgrade Console” under “Deployments” tab of Grid Control.
2. Deploy and Configure 12c agents:
Download the following software, and stage them to an accessible location:
– Oracle Management Agent 12c – All the required plug-ins
Don’t unzip the binaries after downloading them. Verify the checksum after downloading the zip files. To manage information about the location of the core Management Agent software and its associated plug-ins, follow these steps:
1. In Grid Control, click Deployments. 2. On the Deployments page, in the Upgrade section, click Enterprise Manager 12c Upgrade Console. 3. On the Upgrade Console page, in the Select Upgrade Type section, select one of the following:
– To upgrade your Enterprise Manager system with downtime (even if you manually install Management Agents), select 1-System. – To upgrade your Enterprise Manager system with “near zero” downtime (even if you manually install Management Agents), select 2-System. – To upgrade your Enterprise Manager system with downtime on a different host, select 1-System on a Different Host.
Select 1-system approach here.
Enterprise Manager Grid Control refreshes the page and displays a table with a list of tasks you need to perform for the upgrade approach you selected.
4. In the Preupgrade Steps section, from the table, click Manage Software. 5. On the Manage Software page, in the Agent Upgradability and Target Upgradability pie charts, click the hyperlinks in the respective legends and identify the Management Agents and targets that can be upgraded, and that cannot be upgraded due to unavailability of the Management Agent software or the plug-in software.
6. In the Provide Software Location section, enter the absolute path to the directory where the core Management Agent software and the plug-in software are present for the required platforms.
For example, /u01/app/plugin
And then click Validate to register that location with the Enterprise Manager system. Once validate is complete you will see the below pie charts adn these should be in all green. If they are not in green, it means that you have missed some plugin(you will find the list of all required plugins at the botton of same page), download and stage the missing plugin and validate again.
Validate
Once the software have been verified we can continue to deploy and configure the software binaries of Oracle Management Agent 12c.
1. In Grid Control, click Deployments. 2. On the Deployments page, in the Upgrade section, click Enterprise Manager 12c Upgrade Console. 3. On the Upgrade Console page, in the Select Upgrade Type section, select 1-system approach.
In the “Preupgrade Steps” section, from the table, click Deploy and Configure Agents.
4. On the Deploy and Configure Agents page, for Operation Name, enter a unique name for the deployment operation you are about to perform. The custom name you enter can be any intuitive name, and need not necessarily be in the same format as the default name. For example, Deploy_Agents_Phase1_2010_12_27. 6. In the Select Operation Type section, select Deploy Agent and Plug-In Software and Configure Agent and Plug-In Software. 7. In the Search Agents section, search and add the existing, old Management Agents for which you want to deploy the software. In the table that lists the Management Agents, enter an installation base directory and an instance home directory for each of the Management Agents.
In the Agent Credentials section, retain the default selection, that is, Use Oracle Home Preferred Credentials,if you have already set these, so that the preferred credentials stored in the Management Repository can be used for this job.In the OMS Host and Port section, validate the name and the secure port of the host where you plan to install Oracle Management Service 12c. To change the values, click Edit. After filling up the port details click next, do not select any pre and post upgrade script. Provide the root credentials and click submit. Agent will start a job with the name that you have provided, click at the job to see the status.
Generate Health Report of Deployed Oracle Management Agents. Follow the steps by clicking the “Generate help report” , once it is completed, you will see following screen:
Verify and sign off the health check report. After this you have to Switch-Over to 12c agents. Under “Agent Upgrade Steps” click switch agents:
Search for all the agents and provide the oracle preferred credentials. Click submit.
Once this job completes, the old agents will be stopped and new agents from 12g home will be started.
3. Upgrade 11g OMS to 12c
Download and unzip the 12c software from the link mentioned earlier in the post. It is mandatory to meet all the required prerequisites mentioned at ORACLE DOCUMENTATION otherwise the installation will fail at some later stage. I will list one import prerequisite step here:
Copy EMkey to repository: To do so, check the status of emkey as: [oracle@cloud1 bin]$ emctl status emkey
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password : xxxxx
The EMKey is configured properly.
Copy the emkey as:
$ emctl config emkey -copy_to_repos
After meeting all the prerequisites, continue with the OMS upgrade:
Invoke runInstaller from 12c unzip location:
$ ./runInstaller
Choose your option to receive updates via MOS and click next. I opted to remain uninformed from MOS updates.
On the Software Updates screen, select one of the following sources from where the software updates can be installed while the Enterprise Manager system gets upgraded. If you do not want to apply them now, then select Skip. I opted to skip these updates for the installation. Click next.
On the Prerequisite Checks screen, check the status of the prerequisite checks run by the installation wizard, and verify whether your environment meets all the minimum requirements for a successful upgrade. It may show “Failed” for some of the packages, make sure that those were installed or higher versions of those packages are already in place. Once confirmed all settings, click next.
Under Install Type, select to upgrade an existing instance option and choose 1-system upgrade approach. It will automatically find the existing middleware home. Enter a valid middleware home location where it will install the required components for 12c. Provide the location of Middleware home . Click next.
Provide sys and sysman password and click next.
You can select the plug-ins that you want to install, the already installed plugins will be greyed-out. Click next after selecting the plugins.
If you are upgrading from Enterprise Manager 11g Grid Control Release 1 (11.1.0.1.0), then on the Extend WebLogic Server Domain screen, validate the AdminServer host name and its port, and the WebLogic user name. Enter the WebLogic user account password for extending the existing Oracle WebLogic Server Domain to the upgraded release.
Installation status.. once the installation finishes, it will ask you to run allroot.sh as root user. Run the script as root user and click finish.
Launch the Cloud Console as you will see like:
Login using sysman and now you are ready to control clouds 🙂
In one of my databases, one application query suddenly started to pick Nested-Loop joins instead of Hash-Joins and took almost 6 hours to complete which gets completed in less than 10 secs with Hash-Joins. The same query in another similar database with same configuration and same data is doing fine and using hash joins. There is no difference in data/stats/OS/init parameter etc. (Though I know that no two databases are same)
About the query: — It is a simple select statement which selects data from a complex view. — The view comprises of 5 different tables, four of which have more than 15K rows and one have less then 50 rows. — Statistics are up-to-date in both databases.
I can see the optimizer behavior using 10053 event for the next run of this query but want to know what else can be checked to know why the plan changed suddenly, in this case, before using 10053 event.
I was not able to join Oracle President Mark Hurd’s webcast where he supposed to announce a new Oracle product[It was at 1AM my time and I was sleeping], now viewing this webcast HERE
Kerry Osborne has shared information about this product, click link to see it, here you will see some good information about ODA.
This blog reflect our own views and do not necessarily represent the views of our current or previous employers.
The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.
Recent Comments