select to generate create syntax

For a sample script to create tablespaces based on a simple select check out:

http://www.nsa.no/222/script-genereate-create-bigfile-tablespace-statements

Oracle Enterprise Manager Agent Installation Issues (OEMAII)

Product: Oracle Enterprise Manager Agent
Version: 10.1.*
10.2.*
(Last version checked is 10.2.0.3)

Agent installation can be hard. I have lots of problems installing the mass deployment agent, and are now only using the GUI installation found in ..agent/runInstaller included in the zip-file.



Problem

You are seeing this error message:

The agentTZRegion value in /u01/app/oracle/product/10.2.0/agent10g/sysman/config/emd.properties is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting has not changed since the last start of the agent.
If you modified the timezone setting in the environment, please stop the agent and exectute 'emctl resetTZ agent' and also execute the script mgmt_target.set_agent_tzrgn(, ) to get the value propagated to repository. Consult the log files in: /u01/app/oracle/product/10.2.0/agent10g/sysman/log

Solution


Edit $AGENT_HOME/sysman/config/emd.properties and set timezone to GMT (bottom of the file)

agentTZRegion=GMT

Restart the agent (emctl start agent), do not use emctl resetTZ agent!!


Problem

You are having problems with host discovery in Enterprise Manager Grid Control.

You might see this in the logfiles:

“The config assistant was running into problems because of an PERL5LIB setting
@ in the system environment. Removing this setting and rerunning the install
@ fixes the issue. “

Solution

Check if the OS user has the PERL5LIB environment set. If it does, unset it!!


Problem

Installation was broken or did not complete, agent is not started and is not abel to upload.

Solution

Sometimes there is a problem with securing the agent or with the first upload to OMS. It
often helps to do a clearstate of the agent.

Clearstate

Stop the agenten and remove these files from $AGENT_HOME:

$ rm -r sysman/emd/state/*
$ rm -r sysman/emd/upload/*
$ rm sysman/emd/agntstmp.txt
$ rm sysman/emd/lastupld.xml
$ rm sysman/emd/protocol.ini

Start the agent.

Run:
emctl clearstate agent
emctl upload agent


Problem

The agent is not discovering all targets or discovers old targets that where deleted.

Solution

Discovery in grid control checks targets.xml file from the agent and all Sqlnet files like
listener.ora,tnsnames.ora. It also checks /etc/oratab. Check these files for errors or old
information.

Problem

You get this error messages when discovering a target:

"...java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource ORA-06512:
at "SYSMAN.EM_TARGET", line 1681 ORA-06512: at "SYSMAN.EM_TARGET", line 1926 ORA-06512: at
"SYSMAN.MGMT_TARGET",line 2834 ORA-06512: at line 1 - ORA-00060: deadlock detected while
waiting for resource ORA-06512:

Solution

The installation might have used an ip address instead of hostname.
You can solve this by setting this environment variable before installing:

ORACLE_HOSTNAME=hostname


Problem

Agent will not start after boot.


Solution

Check the existence of these files:
/etc/rc.d/rc3.d/S99gcstartup
/etc/rc.d/rc5.d/S99gcstartup

Check the file:
$AGENT_HOME/install/unix/scripts/agentstup

If the file has "user=null" change this to "user=oracle". (user for agent installation)

Problem

I cant install the agent, several reinstalls have done a mess with the repository

Solution

1.Remove agent and associated targets from within Grid Control.
2.Uninstall agent.
3.Log into the repository DB as sysman, query the MGMT_TARGETS_DELETE table
for the GUID of the removed agent. Use the GUID from the MGMT_TARGETS_DELETE table to identify
the corresponding targets and delete
them from the MGMT_EMD_PING, MGMT_EMD_PING_CHECK, and the MGMT_TARGETS_DELETE tables.
4. Reinstall the agent


Problem

AGENT do not autodicsover duplicated databases

Solution

1. Edit $ORACLE_HOME/sysman/emd/targets.xml
change the values for MachineName, OracleHome, Port,SID etc.,
Set the dbsnmp password in clean text and change ENCRYPTED="FALSE"
and bounce the agent.

or,

emctl config agent addtarget


2. Use GC itself

Targets > Databases > Add Button
Select appropriate host and Continue. Auto discovery will occur.


Problem

Problems with uploading agent

Solution

If the agent is successfully installed, up and running and you still get error messages
when running emctl upload agent, try to do a new secure of the agent:

emctl secure agent

I have experienced several times that some agents need the manual secure job to be abel to
work after install.



Some emctl examples

emctl config agent addtarget /home/oracle/labs/asm1_target_RDBB.xml
emctl secure agent welcome (welcome er passordet til oms)
emctl clearstate agent (after this you need to do emctl upload agent)
emctl upload agent
emctl secure status agent

Qlogic HP driver in an Active/Active Setting

If you have an IO bound system you might want to try to use dual fibercards in an active/active setting. This is usually done by adding some SAN software like EMC powerpath or Securepath or Multipath. If you are using standard Qlogic cards you might be abel to set this up in an active/active configuration as well. This way we should be abel to push twice as much IO through each node. Please see this article by HP:

http://h71028.www7.hp.com/ERC/downloads/4AA0-8102ENW.pdf

1. Install HP Proliant Support Pack (PSP)

2. Modify your /etc/modprobe.conf:
…

options qla2xxx ql2xmaxqdepth=16 qlport_down_retry=30 ql2xloginretrycount=30 ql2xfailover=1 ql2xlbType=2 ql2xautorestore=0x20

…

3. Create a new initrd

/opt/hp/src/hp_qla2x00src/make_initrd

4. Edit you driver (/etc/hp_qla2x00.conf)

qdepth = 16
port_down_retry_count = 30
login_retry_count = 30
failover = 1
load_balancing = 2
auto_restore = 0x20

5. Boot the server/servers

The parameter ql2xlbType defines which method of load balancing you want to use:
0 (None) - Expose luns on the first active path and make them the preferred path or the first active optimize path and make them the preferred path (storages: MSA A/A and EVA A/A).

1 (Static load balancing) - Distribute and expose the LUNs across the active optimize port(s) or active un-optimize port(s) and HBA(s).

2 (Least outstanding I/O) - send command to the path with the lowest I/O count.
3 (Least Service time) - Send request to the path with the shortest execution time. Default: 0

- Endret /etc/hp_qla2x00.conf:
qdepth = 16
port_down_retry_count = 30
login_retry_count = 30
failover = 1
load_balancing = 2
auto_restore = 0x20

Problem setting up Recovery Catalog in Oracle Enterprise Manager Grid Control

One of my customers discovered a problem on a freshly installed 10.2.0.3 database and 10.2.0.3 Grid Control. After some metalink searching, I found that it was a bug. Seems to be related to both 10.2.0.2 and 10.2.0.3. The error message is as follows:

"RMAN-06035: wrong version of recover.bsq, expecting 10.2.0.0, found 10.2.0.3"

There is an easy workaround:

Edit the recovery.bsq file in the AGENT_HOME (ORACLE_HOME for the OEM Agent). Modify the line:

library '10.2.0.3'

with

library '10.2.0.0'

References Bug ID: 5648668

Dataguard Syntax Short

This little list has proven to be quite usefull for me. Escpesially since they made the commands so loooong. Its all there in the manual, but with a lot more stuff around it ;).

Be Aware! The lines break like the wave Maveric in this blog!

Physical standby database commands

Goal
Mount a standby database
Command
SQL> alter database mount standby database;

Goal
Start Redo Apply (Normal Recovery Process)
Command
SQL> alter database recover managed standby database disconnect;

Goal
Start Real Time Apply
Command
SQL> alter database recover managed standby database using current logfile disconnect;

Goal
Stop Apply (Redo or Real Time)
Command
SQL> alter database recover managed standby database cancel;

Goal
Run the recovery process in paralell (Number of CPU*2)
Command
alter database recover managed standby database using current logfile disconnect paralell 16;

Logical standby database commands

Goal
Start SQL Apply
Command
SQL> alter database start logical standby apply;

Goal
Start SQL Apply with Real Time Apply
Command
SQL> alter database start logical standby apply immediate;

Goal
Stop SQL Apply
Command
SQL> alter database stop logical standby apply;

Switchover

Goal
Check if you database is ready to switch over
Command
SQL> select switchover_status from v$database;
Expected result
SWITCHOVER_STATUS
-----------------
TO PRIMARY



Other commands

Goal
List information about archivelogs (I just had to put it in here, cause its cute)
Command
SQL> archive log list;

Going to ASM...

Converting non-ASM databases to ASM is quite easy...if you know what to do ;).

Actually I had to convert a non-ASM standby database to a two instance RAC ASM-database as part of a catastropical test. Why doing it this way, is really a good question. I will just say I'm trying to convince my customer to use Dataguard as the product is intended. No luck yet ;).

Here we go...

The tool you really need to know is RMAN! You don't get anywhere without RMAN working with ASM. This is not entirely true, but that's another story.

So basically you have a backup set of your non-ASM database. The files are typically stored on "/u02/oradata/SID/..." and so on... You ned RMAN to restore these files to ASM instead of "/u02/oradata/SID/...". To do this I use something called "set newname". The syntax is:

set newname for datafile 1 to '+DATA'

The reference "+DATA" is your diskgroup in ASM.

So, at some point you need to know how many datafiles you have, and probably how many tempfiles you have. You will typically have to write a little script like this:

connect / as sysdba
whenever sqlerror exit sql.sqlcode
set space 0
set pages 0
set heading off
set echo off
set trimspool on
set verify off
set feedback off
spool rman_newname_restore.rcv
select 'configure DEVICE TYPE DISK parallelism 4;' from dual;
select 'run' from dual;
select '{' from dual;
select 'set newname for datafile ' file# ' to ''' name '.dbf'';' from
v$datafile;
select 'restore database;' from dual;
select '}' from dual;
spool off;

Then you use the rman_newname_restore.rcv for the restore script. Rman will then restore your files to the new location set by newname.

There might be better solutions than mine, so I'm looking forward to reading your replays.