DBA Junkies: May 2014

Friday, May 30, 2014

Install Agent 12c by AgentPull.sh

With metta
Quang Phan

Oracle Support Document 1665029.1 (EM 12c: Enterprise Manager 12c Cloud Control Silent Agent Installation Fails to Start Agent with Error: emctl start agent command has failed with status2)

Oracle Support Document 1665029.1 (EM 12c: Enterprise Manager 12c Cloud Control Silent Agent Installation Fails to Start Agent with Error: emctl start agent command has failed with status2) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1665029.1

Oracle Support Document 1645261.1 (EM 12c: How to Properly Re-synchronize an Enterprise Manager12c Cloud Control Management Agent)

Oracle Support Document 1645261.1 (EM 12c: How to Properly Re-synchronize an Enterprise Manager12c Cloud Control Management Agent) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1645261.1

Thursday, May 22, 2014

Using chmod command

Using chmod on the unix command line

Syntax: chmod mode file
Example: chmod 766 readme.txt

chmod codes

Each digit in the mode parameter represents the permissions for a user or a class of users:

the first digit corresponds to the owner of the file
the second digit corresponds to the file's group
the final digit corresponds to everybody else.

There are eight digits that can be used in the mode parameter.

0 - Deny all
1 - Execute Only
2 - Write Only
3 - Execute + Write
4 - Read Only
5 - Read + Execute
6 - Read + Write
7 - Read + Write + Execute

Examples: You set your directory permissions to 755. This means that the directory owner can read, write, and execute, while group and world can read and execute (use) the directory.
You set your file permissions to 644. This means that the file owner can read and write (edit) the file, while everyone else can only read it.

With metta
Quang Phan

How to deploy Oracle Agent

http://www.oracle.com/technetwork/oem/install-upgrade/em-12103-agent-deployment-1967206.pdf

--

With metta
Quang Phan

Wednesday, May 21, 2014

Troubleshooting Oracle agent 12.1.0.1.0

Posted by Martin Bach on October 28, 2011

As you may have read on this blog I recently moved from Oracle Enterprise Manager 11.1 GRID control to the full control of the cloud-12.1 has taken its place in the lab.

I also managed to install agents via self download (my OEM is x86 to reduce the footprint) on a 2 node 11.2.0.3 cluster: rac11203node1 and rac11203node2. After a catastrophic crash of both nodes followed by a reboot none of the agents wanted to report back to the OMS.

The difference

Oracle 12.1 has a new agent structure: where you used the agent base directory in previous releases to create the AGENT_HOME this now changed. In 11.1 I could specify the agent base to be /u01/app/oracle/product, and OUI would deploy everything in a subdirectory it creates, called agent11g (or agent 10g for 10.2.x).

Now I set the agent base to the same value and installed my agents in parallel, but found that there is no agent12c directory under the base. Instead I found these:

[oracle@rac11203node1 product]$ ls -l

total 48

drwxr-xr-x. 73 oracle oinstall 4096 Oct 27 22:40 11.2.0.3

-rw-rw-r--. 1 oracle oinstall 91 Sep 23 08:52 agentimage.properties

drwxr-xr-x. 6 oracle oinstall 4096 Oct 28 14:57 agent_inst

drwxr-xr-x. 3 oracle oinstall 4096 Oct 15 21:35 core

drwx------. 2 oracle oinstall 16384 Oct 14 21:02 lost+found

drwxr-xr-x. 8 oracle oinstall 4096 Oct 15 21:50 plugins

-rwxr-xr-x. 1 oracle oinstall 223 Oct 15 21:25 plugins.txt

-rw-r--r--. 1 oracle oinstall 298 Oct 15 21:42 plugins.txt.status

drwxr-xr-x. 5 oracle oinstall 4096 Oct 15 21:43 sbin

So it's all a bit different. The core/ directory contains the agent binaries. The agent_inst directory contains the the sysman directory. This is where all the configuration and state information is stored. In that respect the sysman directory is the same as in 11.1.

Now back to my problem-both agents that previously used to work fine were reported "unavailable". The agent information is no longer in the setup-agents-management agents.

For 12.1 you need to navigate to setup-agents from the top down menu in the upper right corner.This takes you to the overview page. OK, so I could see the agents weren't communicating with the OMS.

On the machine I could see this:

[oracle@rac11203node1 log]$ emctl status agent

Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0

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

Agent Version : 12.1.0.1.0

OMS Version : (unknown)

Protocol Version : 12.1.0.1.0

Agent Home : /u01/app/oracle/product/agent_inst

Agent Binaries : /u01/app/oracle/product/core/12.1.0.1.0

Agent Process ID : 13270

Parent Process ID : 13215

Agent URL : https://rac11203node1.localdomain:3872/emd/main/

Repository URL : https://oem12oms.localdomain:4901/empbs/upload

Started at : 2011-10-26 18:30:17

Started by user : oracle

Last Reload : (none)

Last successful upload : (none)

Last attempted upload : (none)

Total Megabytes of XML files uploaded so far : 0

Number of XML files pending upload : 1,858

Size of XML files pending upload(MB) : 8.05

Available disk space on upload filesystem : 49.16%

Collection Status : Collections enabled

Last attempted heartbeat to OMS : 2011-10-27 15:42:47

Last successful heartbeat to OMS : (none)

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

Agent is Running and Ready

The settings are correct, I have verified that with another, uploading and otherwise fine agent. I have also secured the agent, and $AGENT_BASE/agent_inst/sysman/log/secure.log as well as the emctl secure agent commands reported normal, successful operation.

Still the stubborn thing doesn't want to talk to the OMS – in the agent overview page both agents are listed as "unavailable", but not blocked. When I force an upload, I get this:

[oracle@rac11203node1 log]$ emctl upload

Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0

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

EMD   upload error:full upload has failed: uploadXMLFiles skipped :: OMS   version not checked yet. If this issue persists check trace files for   ping to OMS related errors. (OMS_DOWN)

However it's not down, I can reach it from another agent (which happens to be on the same box as the OMS)

[oracle@oem12oms 12.1.0.1.0]$ $ORACLE_HOME/bin/emctl status agent

Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0

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

Agent Version : 12.1.0.1.0

OMS Version : 12.1.0.1.0

Protocol Version : 12.1.0.1.0

Agent Home : /u01/gc12.1/agent/agent_inst

Agent Binaries : /u01/gc12.1/agent/core/12.1.0.1.0

Agent Process ID : 2964

Parent Process ID : 2910

Agent URL : https://oem12oms.localdomain:3872/emd/main/

Repository URL : https://oem12oms.localdomain:4901/empbs/upload

Started at : 2011-10-15 21:00:37

Started by user : oracle

Last Reload : (none)

Last successful upload : 2011-10-27 15:46:38

Last attempted upload : 2011-10-27 15:46:38

Total Megabytes of XML files uploaded so far : 0

Number of XML files pending upload : 0

Size of XML files pending upload(MB) : 0

Available disk space on upload filesystem : 49.16%

Collection Status : Collections enabled

Last attempted heartbeat to OMS : 2011-10-27 15:48:34

Last successful heartbeat to OMS : 2011-10-27 15:48:34

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

Agent is Running and Ready

And no, the firewall is turned off and I can connect to the upload from any machine in the network:

[oracle@rac11203node1 log]$ wget --no-check-certificate https://oem12oms.localdomain:4901/empbs/upload

--2011-10-27 15:55:46-- https://oem12oms.localdomain:4901/empbs/upload

Resolving oem12oms.localdomain... 192.168.99.28

Connecting to oem12oms.localdomain|192.168.99.28|:4901... connected.

WARNING:   cannot verify oem12oms.localdomain's certificate, issued by   "/O=EnterpriseManager on oem12oms.localdomain/OU=EnterpriseManager on   oem12oms.localdomain/L=EnterpriseManager on   oem12oms.localdomain/ST=CA/C=US/CN=oem12oms.localdomain":

Self-signed certificate encountered.

HTTP request sent, awaiting response... 200 OK

Length: 314 [text/html]

Saving to: "upload.1"

100%[======================================>] 314 --.-K/s in 0s

2011-10-27 15:55:46 (5.19 MB/s) - "upload.1" saved [314/314]

The agent complains about this in gcagent.log:

2011-10-27 15:56:08,947 [37:3F09CD9C] WARN – improper ping interval (EM_PING_NOTIF_RESPONSE: BACKOFF::180000)
2011-10-27 15:56:18,471 [167:E3E93C4C] WARN – improper ping interval (EM_PING_NOTIF_RESPONSE: BACKOFF::180000)
2011-10-27 15:56:18,472 [167:E3E93C4C] WARN – Ping protocol error
o.s.gcagent.ping.PingProtocolException [OMS sent an invalid response: "BACKOFF::180000"]

At least someone in Oracle has some humour when it comes to this.

The Solution

Now I dug around a lot more and finally managed to get to the conclusion. It was actually a two fold problem. The first agent was simply blocked. After finding a way to unblock it, it worked happily.

The second agent was a bit more trouble. I unblocked it as well from the agent page in OEM, which failed. As it turned out the agent was shut down. And it didn't start either:

[oracle@rac11203node2 12.1.0.1.0]$ emctl start agent

Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0

Starting agent ............. failed.

Target Metadata Loader failed at Startup

Consult the log files in: /u01/app/oracle/product/agent_inst/sysman/log

I checked the logs and found this interesting bit of information:

2011-10-24 21:35:21,387 [1:3305B9] INFO - Plugin oracle.sysman.oh is now active

2011-10-24 21:35:21,393 [1:3305B9] INFO - Plugin oracle.sysman.db is now active

2011-10-24 21:35:21,396 [1:3305B9] WARN - Agent failed to Startup for Target Metadata Loader in step 2

oracle.sysman.gcagent.metadata.MetadataLoadingException: The targets.xml file is empty

at oracle.sysman.gcagent.metadata.MetadataManager$Loader.validateMetadataFile(MetadataManager.java:799)

at oracle.sysman.gcagent.metadata.MetadataManager$RegistryLoader.processMDFile(MetadataManager.java:1733)

at oracle.sysman.gcagent.metadata.MetadataManager$RegistryLoader.readRegistry(MetadataManager.java:1695)

at oracle.sysman.gcagent.metadata.MetadataManager$RegistryLoader.load(MetadataManager.java:1641)

at oracle.sysman.gcagent.metadata.MetadataManager.load(MetadataManager.java:282)

at oracle.sysman.gcagent.metadata.MetadataManager.runStartupStep(MetadataManager.java:450)

at oracle.sysman.gcagent.metadata.MetadataManager.tmNotifier(MetadataManager.java:337)

at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.invokeNotifier(TMComponentSvc.java:876)

at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.invokeInitializationStep(TMComponentSvc.java:959)

at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.doInitializationStep(TMComponentSvc.java:800)

at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.notifierDriver(TMComponentSvc.java:740)

at oracle.sysman.gcagent.tmmain.TMMain.startup(TMMain.java:215)

at oracle.sysman.gcagent.tmmain.TMMain.agentMain(TMMain.java:458)

at oracle.sysman.gcagent.tmmain.TMMain.main(TMMain.java:447)

2011-10-24 21:35:21,397 [1:3305B9] INFO - Agent exiting with exit code 55

2011-10-24 21:35:21,398 [31:F9C26A76:Shutdown] INFO - *jetty*: Shutdown hook executing

2011-10-24 21:35:21,399 [31:F9C26A76] INFO - *jetty*: Graceful shutdown SslSelectChannelConnector@0.0.0.0:3872

2011-10-24 21:35:21,399 [31:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@14d964af@14d964af/emd/lifecycle/main,null

I yet have to find the reason for the empty targets.xml file but sure enough it existed with 0 byes length.

Simple enough I thought, all I need to do is run agentca to repopulate the file. Unfortunately I couldn't find it.

1 2	`[oracle@rac11203node2 emd]$ find /u01/app/oracle/product/ -name "agentca*"` `[oracle@rac11203node2 emd]$`

This was a bit of a let down. Then I decided to create a new targets.xml file and try a resynchronisation of the agent.This is a well hidden menu item so I dedided to show it here:

The only element that went into targets.xml was "<targets />". This was sufficient to start the agent, which is a requirement for the resynchronisation to succeed. I was quite amazed that this succeeded, but it did:

1 2	`[oracle@rac11203node2 emd]$ find /u01/app/oracle/product/ -name "agentca*"` `[oracle@rac11203node2 emd]$`

This was very encouraging, and both agents are now working properly.

With metta
Quang Phan

Install EM12c

http://www.snapdba.com/2013/07/em12c-upgrade-from-12-1-0-2-to-12-1-0-3/

--

With metta
Quang Phan

How to open a PDF file from terminal?

http://askubuntu.com/questions/43264/how-to-open-a-pdf-file-from-terminal

--

With metta
Quang Phan

How to find db version after upgrading or patching

SQL> show parameters compatibility

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_v2_compatibility               boolean     FALSE
SQL> show parameters compat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.0.0
plsql_v2_compatibility               boolean     FALSE

SQL> col action_time for a30
SQL> col comments for a60
SQL> set linesize 120
SQL> select action_time, comments from registry$history order by 1 desc,2;

--

With metta
Quang Phan

Tuesday, May 20, 2014

How to clone database

How To Clone An Existing RDBMS Installation Using OUI [ID 300062.1]
How to Manually Backup - Restore or Clone a Database to Another Node [ID 562556.1]
Clone a Database With RMAN Without Connecting To Target Database [ID 732624.1]
Clone/Refresh Non-prod database from production [ID 1338233.1]

--

With metta
Quang Phan

Monday, May 19, 2014

Fwd: Oracle vmware virtual box

Start install Oracle to Windows from here

http://www.oracle.com/technetwork/server-storage/virtualbox/downloads/index.html?ssSourceSiteId=otnjp

With metta
Quang Phan

Migrating (and Upgrading!) Your EM12c Repository Database

07 April 2014

This week I migrated our EM12c repository database to a new server as part of its promotion to production status. Just to make it a little more exciting, the migration also involved an in-flight upgrade from 11.2.0.3 to 11.2.0.4. Much of this post is directly inspired by Martin Bach's post on the same subject. I ran into a few other snags that weren't mentioned so I thought it would be worthwhile to document the experience here for your benefit.

I'm assuming you have all the software installed (and patched to the latest PSU, right?). Alright then, let's begin!

Stop OMS

We want to make sure there are no more changes coming, and nothing needs to access the repository database, so be sure to stop all OMS instances:

$ emctl stop oms -all

Backup PFILE

We need to get the pfile for the current repo and copy it into place on new host:

SQL> create pfile='/mnt/emrepo_backup/initemrepo.ora' from spfile;

I use /mnt/emrepo_backup here because that is the directory that I'll be backing the database up to and copying to the new host after. If you create your pfile somewhere else, be sure to copy it to the new host under $ORACLE_HOME/dbs/

Backup Repo Database

Next we backup the repo database. Here's a snippet from my ksh script that I used:

#!/bin/ksh

BACKUPDIR=/mnt/emrepo_backup

LOGFILE=backup_emrepo.log

mkdir -p $BACKUPDIR

rman log=$LOGFILE <<EOF

connect target /

set echo on

run {

allocate channel c1 device type disk format '$BACKUPDIR/%U';

allocate channel c2 device type disk format '$BACKUPDIR/%U';

allocate channel c3 device type disk format '$BACKUPDIR/%U';

allocate channel c4 device type disk format '$BACKUPDIR/%U';

backup as compressed backupset database

include current controlfile

plus archivelog;

}

EOF

When the backup is finished, review the RMAN log and make note of which backup piece contains the controlfile backup. We'll need to refer to it by name as part of the restore process.

If your backup directory is an NFS mount, then you can simply unmount it from here and mount it to the new server. Otherwise, be sure to copy the files there after the backup is complete, for example:

$ scp -r /mnt/emrepo_backup newhost:/path/to/emrepo_backup

After this, it should be safe to shutdown the old repository database.

$ sqlplus / as sysdba

SQL> shutdown immediate

If you use Oracle Restart:

$ srvctl stop database -d emrepo

$ srvctl disable database -d emrepo

Prepare New Host for Repo DB

Now we need to set things up on the new host for the emrepo DB.

Create oratab Entry

First let's create an entry in /etc/oratab for this DB under the new 11.2.0.4 home. For example:

emrepo:/oracle/app/product/11.2.0.4:N

Edit PFILE and Create SPFILE

Then let's copy that parameter file into place.

$ . oraenv

ORACLE_SID = [oracle] ? emrepo

The Oracle base has been set to /oracle/app

$ cd $ORACLE_HOME/dbs/

$ cp /mnt/emrepo_backup/initemrepo.ora .

Now edit that file and make sure you update the parameters that require updating. In my case, I'm using Oracle Managed Files (OMF) so I set db_create_file_dest and db_create_online_log_dest_1. I also set db_recovery_file_dest for the FRA. I then set the control_files parameter to specify where I want the control file(s) restored to from the backup when I get to that point.

Now, Martin Bach noted in his blog post that he did not have to specify a db_file_name_convert or log_file_name_convert. I was having some difficulty during the restore phase, and added these parameters out of pure speculation. They didn't help the problem, but I left them in for the duration of my process. I only mention this as an FYI if you end up comparing your settings to mine.

Once you have all your parameters set as desired, create the SPFILE:

$ sqlplus / as sysdba

SQL> create spfile from pfile;

Now, let us restore ourselves the database.

Restore Repo DB on New Host

The restore was done largely as part of a ksh script, which I'll reference snippets of here. Let's start by defining some variables:

BACKUPDIR=/mnt/emrepo_backup
DESTDIR=/oracle/app/oradata/data/EMREPO

Restore Controlfile and Mount Database

From the script, we call RMAN to start the instance in nomount mode, restore the controlfile from the specified backuppiece and mount the database:

rman log=$LOGFILE <<EOF
connect target /

set echo on

startup force nomount;

restore controlfile from '$BACKUPDIR/1abcd123_1_1';
alter database mount;

catalog start with '$BACKUPDIR' noprompt;

EOF

We end by cataloging the backup files, as you can see.

Generate SET NEWNAME Script

Here I dip into sqlplus to generate an script for RMAN to call SET NEWNAME for each of the datafiles. Without this, RMAN would try to restore the datafiles to their old paths on the original host. Here I set them for the path that OMF will use:

sqlplus -s /nolog <<EOF

connect / as sysdba

set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.rman
select 'set newname for datafile ' || FILE# || ' to ''' || '$DESTDIR/datafile/' || substr(name,instr(name,'/',-1)+1) || ''';' from v\$datafile;
select 'set newname for tempfile ' || FILE# || ' to ''' || '$DESTDIR/tempfile/' || substr(name,instr(name,'/',-1)+1) || ''';' from v\$tempfile;
spool off

EOF

Restore & Recover Database

Now we're ready to restore the database and perform recovery. Again, we call RMAN and run this:

run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
@rename_datafiles.rman
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

At this point we're done with the restore and recovery. Normally I would OPEN RESETLOGS, but remember that we're restoring this to an 11.2.0.4 home, so we still need to UPGRADE the database!

Open and Upgrade Database

First we still call OPEN RESETLOGS, but with the UPGRADE option. This replaces the "STARTUP UPGRADE" command you would find in the manual upgrade instructions.

$ sqlplus / as sysdba
SQL> alter database open resetlogs upgrade;

Now we follow the rest of the manual upgrade instructions, I'll just post the commands here, but you should definitely review the documentation:

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL> spool upgrade.log
SQL> @catupgrd.sql

-- Start database again
SQL> startup;

-- Check status of components, some will be fixed by utlrp.sql
SQL> @utlu112s.sql

-- Rebuild everything
SQL> @catuppst.sql
SQL> @utlrp.sql

-- Confirm everything is OK now
SQL> SELECT count(*) FROM dba_invalid_objects;
SQL> SELECT distinct object_name FROM dba_invalid_objects;
SQL> @utlu112s.sql

The utlu112s.sql should now report all components as VALID. If not, you'll want to refer to the upgrade documentation for troubleshooting.

At the point the database is upgraded and open. Make sure you have a listener running and that the new database is registered. The only thing left is the tell your OMS servers to look for the repository database in its new location.

Update OMS Repository Settings

First we need to start just the administration server:

$ emctl start oms -admin_only

This is necessary if you used the "-all" option when stopping OMS earlier. If you did not use "-all" then the admin server should still be running.

Now, update the store_repos_details setting in the OMS configuration:

$ emctl config oms -store_repos_details -repos_port 1521 \

-repos_sid emrepo -repos_host newhost.mydomain.com \

-repos_user sysman -repos_pwd xxx

Repeat this step for all your OMS servers (emctl should remind you to do so when changing the config). Then on each, completely shutdown and restart OMS:

$ emctl stop oms -all
$ emctl start oms

And that should be it! Don't forget to drop/delete the database from the original server when you're comfortable doing so.