Borndba

It’s convinent to post blog via email. After struggling for a while, got the plugin postie to do this functionalities. Which mean more posting from me going forward.

convert this post to pdf.

Set the login info, otherwise you will give session expired error.

./emcli setup -url=https://source_hostname:7799/em  -username=sysman

./emcli export_report -title="Database Size" -owner="SYSMAN" -output_file="db_size.xml"
Report "Database Size", owned by "SYSMAN", has been exported from the repository.
Exported reports may not contain all information from original report.  Repository specific information such as targets, administrator access, and schedules are not exported.  Imported reports should be edited after import to supply all necessary report parameters.

In target

./emcli setup -url=https://target_hostname:7799/em  -username=sysman

./emcli import_report -files="db_size.xml"
File "db_size.xml" has been imported into the repository.
Imported reports should be edited after import to supply all necessary report parameters.

convert this post to pdf.

./emcli setup -url=https://source_hostname:7799/em -username=sysman

If you don’t issue the above command, you will get error like session expired. get the name and target type from below query

 SELECT TEMPLATE_NAME,TARGET_TYPE FROM sysman.MGMT_TEMPLATES ;

./emcli export_template -name="Common_Database_Template" -arget_type="oracle_database" -output_file=Common_Database_Template.out

./emcli export_template -name="Common_Sybase_Template" -target_type="sybase_ase" -output_file=Common_Sybase_Template.out

./emcli export_template -name="Common_Clusterdb_template" -target_type="rac_database" -output_file=Common_Clusterdb_template.out

./emcli export_template -name="Common_ASM_template" -target_type="osm_instance" -output_file=Common_ASM_template.out

./emcli export_template -name="Common_Agent_template" -target_type="oracle_emd" -output_file=Common_Agent_template.out

./emcli export_template -name="Common_listener_Template" -target_type="oracle_listener" -output_file=Common_listener_Template.out

./emcli export_template -name="Common_Host_template" -target_type="host" -output_file=Common_Host_template.out

To import

./emcli setup -url=http://target_hostname:7788/em -username=sysman

Make sure that if the oms is secured use https, if not use http in -url. If you want to import as different user you can change the -username to respective users.

 ./emcli import_template -files="Common_Database_Template.out;Common_Sybase_Template.out;
Common_Clusterdb_template.out;Common_ASM_template.out;
Common_Agent_template.out;Common_listener_Template.out;
Common_Host_template.out"

sybase template will fail if sybase plugin is not installed. If there is already a name with exisiting template, that template will not be added.

convert this post to pdf.

By mistake if you have used wrong port while configuring SLB, the oms will not come up.

./emctl secure oms -sysman_pwd xyz123 -reg_pwd abc123 -host hostname -secure_port 443 -slb_port 443 -slb_console_port 443

You will be getting below error while starting the oms.

./emctl start oms
Oracle Enterprise Manager 11g Release 1 Grid Control 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting WebTier…
WebTier Could Not Be Started
Error Occurred: WebTier Could Not Be Started
Please check /u01/app/oracle/Middleware/oms11g/em/EMGC_OMS2/sysman/log/emctl.log for error details

when you check the log, basically the apache will not start. You can see the error in /u01/app/oracle/Middleware/oms11g/WebTierIH1/diagnostics/logs/OHS/ohs1/ohs1.log

The error will look like

[pid: 9633918] [tid: 1] [user: oracle] [VirtualHost: main] (13)Permission denied:  make_sock: could not bind to address [::]:443
[pid: 9633918] [tid: 1] [user: oracle] [VirtualHost: main]  no listening sockets available, shutting down

3918] [tid: 1] [user: oracle] [VirtualHost: main]  Unable to open logs

you will have to change the port back in

/u01/app/oracle/Middleware/oms11g/WebTierIH1/config/OHS/ohs1/httpd_em.conf

note there will be a backup file automatically taken whenever any change to above file. Overwrite the correct copy with incorrect one.

convert this post to pdf.

There is easier way of  pointing agent to new oms in 11g.

Go the $AGENT_HOME/bin and execute just one command,

$ ./emctl switchOMS https://oms_hostname:1159/em/upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
SwitchOMS succeeded.

If you have agent registration passwd enabled, do the below steps,

$./emctl stop agent

$./emctl secure agent ‘pwd’

$./emctl start agent

Before 11g, you will have to follow steps given metalink#413228.1

convert this post to pdf.

In case if you have lost agent registration pwd and want to create new one. Go to setup=> registration pwd and remove the existing one.

then in the OMS server execute below command,

/emctl secure setpwd
Oracle Enterprise Manager 11g Release 1 Grid Control 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root Password :
Enter New Agent Registration Password :
Registration Password added successfully.

root password above is sysman password

convert this post to pdf.

It’s specific to AIX environment. While doing oms installation during the agent configuration stage it failed. Even a fresh installation of agent in oms host will fail with similar error. You will not be able to stop or start the agent.

It’s a aix bug, metalink#1298284.1 talks about it. There is a workaround provided in that note.

Workaround provided by Oracle:

1. export $ORACLE_HOME=<agent home>/sysman/lib
2. cd <agent home>/sysman/lib
3. backup env_emagent.mk
4. Modify env_emagent.mk:
   Search “LIB_JVM_LINK =”
   Remove the “-blazy” option from LIB_JVM_LINK definition
   Save the file
5. /usr/sbin/slibclean

5.1. export OBJECT_MODE=32 ==> Pls note this step is missing in the oracle document

6. make -f ins_emagent.mk agent

convert this post to pdf.

We have 2 node cluster running in 11gR1 crs and asm. We have a requirement to upgrade to 11gR2 grid infrastrcuture. As we know grid infrastructure is one home for crs and asm unlike 11gR1. Also in case of upgrade if the crs and asm of 11gR1 was installed in oracle account, you can install the new grid infrastructure only on oracle account.  Where as for new install you can have grid infrastructure in grid account and your DBs in oracle accounts.

What we tried is a rolling upgrade, since the steps are not clear we didn’t actually test the application after upgrading one node. But guess what outlined here will give you a clue and may help you to do rolling upgrade.

There are totalling 7 zip files for linux, download to a folder and unzip each one of it. At the end of unzip you will have folder like grid,database,client etc.,

grid folder will have installables for grid infrastrucutre,

set the display and run the runInstaller. Some reason our environment we used  -ignoreInternaldriver flag with runInstaller.

skip the software updates,

Select the ‘Upgrade Oracle Grid Infrastructure or Oracle Automatic Storage Management”

Though you select one node, software will be pushed to all the nodes in that cluster. You can choose to run rootupgrade.sh or not later. Since you selected one node asm upgrade has to be done manually.

Next screen will ask for scan ip info and the port. Port give which ever required.

Make sure everything it is oinstall group  or whatever relevant in your environment.

Next

Next

 Next you will get the summary tab. If everything fine select Next click ‘Install’.

End of this it will ask to run rootupgrade.sh. You can run one node at a time. At the end of ‘rootupgrade.sh’ it will bring the crs in new home and ASM with older version. Pls node only if you run rootupgrade.sh the asm will come up.

“Configuration assistants have not been executed by the installer, since only a subset of nodes was selected for the upgrade. Run rootupgrade.sh on the rest of the nodes and then proceed to execute the configuration assistants.

“/u01/app/11.2.0/grid/cfgtoollogs/configToolAllCommands” script contains all commands that the configuration assistants will complete. This file may be used to run the configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before you can run the script.”

Note you will have run rootupgrade.sh in all the node and comeback and run “/u01/app/11.2.0/grid/cfgtoollogs/configToolAllCommands”. This is because you have selected only one node during the upgrade. configToolAllCommands is a script which call runconfig.sh

Set the environment variable to new grid infrastructure and start the asmca for upgrading the +asm. If you have specified asmca_rolling_upgrade=true, it will upgrade asm in each node in rolling fashion.

This is the stage we are not sure what to do. We ran the rootupgrade.sh in both the node and started asmca with asmca_rolling_upgrade=true. asmca_rolling_upgrade needs to be set as environment variable. When you set this environment variable, it does the asm upgrade in both the node.

I belive after rootupgrade.sh and asmca on one node could have brought the application up on one node.

When you set the environment variable pointing to new grid and run asmca from $ORACLE_HOME/bin/asmca you will see the screen like below.

convert this post to pdf.

You don’t need tns or ldap entry to connect to your db from your desktop. 10g uses service name and 11g uses sid.

C:\Documents and Settings>tnsping hostname:1141/emrep

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 – Production on 24-FEB-2

011 18:42:59

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:

c:\tns\sqlnet.ora

Used EZCONNECT adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=emrep))(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxx)(PORT=1141)))

OK (440 msec)

C:\Documents and Settings\>sqlplus dbsnmp/*******@hostname:1141/emrep

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 24 18:44:30 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

hostname

SQL> show parameter db_name

NAME TYPE

———————————— ——————————–

VALUE

——————————

db_name string

emrep

to work you need below entry in your sqlnet.ora,

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

convert this post to pdf.

If you want top 200 sql in your report
dbms_workload_repository.modify_snapshot_settings (topnsql=> 200)

If you want a particular sql even if it’s not in your top list.
dbms_workload_repository.add_colored_sql(:sql_id)

trace one or more sql id
alter session set events ‘sql_trace [sql: sql_id1 |sql_id2]‘

I know about the last one.

convert this post to pdf.

Pls see this interesting note,

http://blogs.oracle.com/optimizer/2011/02/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex.html

convert this post to pdf.

If one of your data file is corrupted in Production or in Standby, you can pick that data file from other location and restore.
We don’t have to recreate the standby db or restore the DB. Please find the steps we followed..

SQL> select * from v$database_block_corruption;

FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
36     289469          1                  0 CORRUPT
75      96029          1                  0 CORRUPT

Primary:

RMAN> backup as copy datafile 36 format ‘/ora_dr_backup/rman/drdb/data36.dbf’;

RMAN>backup as copy datafile 75 format ‘/ora_dr_backup/rman/drdb/data75.dbf’;

ftp Standby

SQL> alter database datafile 36 offline ;   If your standby datafile is corrupted use ‘drop’ option
SQL> alter database datafile 75 offline ;  If your standby datafile is corrupted use ‘drop’ option

RMAN> catalog datafilecopy  ‘/ora_dr_backup/rman/drdb/data36.dbf’;

RMAN> catalog datafilecopy ‘/ora_dr_backup/rman/drdb/data75.dbf’;

RMAN> list copy of datafile 36; ==> Check above mountpoint is listed

RMAN> list copy of datafile 75; ==> Check above mountpoint is listed

RMAN> restore datafile 36;

SQL> alter database datafile 36 online;

RMAN> recover datafile 36;

RMAN> restore datafile 75;

SQL> alter database datafile 75 online;

RMAN> recover datafile 75;

convert this post to pdf.

Got lot of insight on various process in DG environment.

http://forums.oracle.com/forums/thread.jspa?messageID=4090175

convert this post to pdf.

New 11gR2 feature. Seems interesting. Can it be a alternate to active-passive cluster or Dataguard? May not be for DG, as DG protects you in cases like corruption.

http://download.oracle.com/docs/cd/E11882_01/rac.112/e16795/onenode.htm

convert this post to pdf.

Sometime you may want to run the rda.sh has nohup. But it take lot of input. You can create the setup.cfg using

./rda.sh -S

Again you will have to input the password if you are collecting via system.

./rda.sh -A system

enter the system password. In the same session

nohup ./rda.sh &

password what you set is valid only that session. You can run nohup from anothe r session.

you can see in the setup.cfg, newly added content like below.

#B.Multi-run collection indicator
S999END_MRC=0
#T.Password for ‘SYSTEM’
SQL_PASSWORD_SYSTEM=’(<WDR8F%S93(`’

convert this post to pdf.

If you have used repvfy utility in grid control, pls see the sql generated under emdiag/log location. Out of repvfy a sql file generated and content of those sql’s are pretty useful.

Also on other note if you want to diagonise one target, you can use below command.

./repvfy -usr SYSMAN -tns emrep -pwd dump target -name -type oracle_database

convert this post to pdf.

If you have administered Grid Control, you would have known two types of corrective actions.

1. Corrective Action Script
2. Agent Response Script

What it does is you can invoke a remediation script based on an action. Suppose you want to add a datafile if the tablespace usage is nearing 95%, you can write a script and keep in the target server. Configure this script in metrics and policy setting of tablespace usage metrics. So whenever any tablespace usage crossing 95%, the script stored in the target server will be invoked.

The advantage is that it avoid human interference,errors and enhance the effective usage of DBA.

Consider if you have 1000s of targets and each targets you need to add remediation scripts to the metrics, it’s going to take lot of time.
What if i have option to invoke the Agent response script via sqlplus. It’s going to give me better control. I can write a package, which will poll the metrics tables and invoke the scripts based on varying conditions. This is not possible to implement in metrics and policy tab.

Opened a SR with oracle and justification like below is given,
1. We have 1000s of targets for most of which we need to enable response action. Manually updating each target is time consuming. We can’t have template as the scripts getting invoked will have different name based on db name.
2. There are different groups of targets for which invoking response actions script will be based on certain scenarios. If response action can be invoked manually, then we can put our conditions and invoke accordingly.
3. We will have control on one place. Modifying something will be quicker instead of going to each target’s metric & policy and changing.

We have filed enhancement request filed with oracle.

ER IS : Bug 10214800 – INVOKING ” RESPONSE ACTION” VIA SQLPLUS

convert this post to pdf.

If we could simulate the performance issue in sqlplus, consider we can fix the issue most of the time. Incase if you need oracle supports help in a query, you can build the test case like below.

SQL> DECLARE tc clob;
  2  BEGIN
  3  DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(
  4  DIRECTORY=>'DATA_PUMP_DIR',
  5  SQL_ID=>'6bcwxgty705p8',
  6  TESTCASE=>tc);
  7  END;
  8  /

PL/SQL procedure successfully completed.

You need to supply sql_id and directory name. You can zip this and sent it to support. If you give exportdata to true, expdp will include table data.

This is tested in 11gR2.

convert this post to pdf.

impdp will do a direct load import. This is one way to confirm

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';

SUM(BYTES/1024/1024)
--------------------
            176.1875

SQL> truncate table t;

Table truncated.

SQL> alter table t COMPRESS FOR OLTP ;

Table altered.

Note: compress for all operation is deprecated in 11gR2 instead it will be compress for oltp

SQL> host impdp directory=data_pump_dir dumpfile=xyz.dmp tables='test.t' table_e
xists_action=append

Import: Release 11.2.0.1.0 - Production on Thu Oct 14 21:30:33 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA directory=data_pump_d
ir dumpfile=xyz.dmp tables='test.t' table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "TEST"."T" exists. Data will be appended to existing table but
all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T"                                  149.2 MB 1689984 rows
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 21:31:03

Let us check the size now.

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';

SUM(BYTES/1024/1024)
--------------------
             72.1875

From 176mb the size is reduced to 72mb. Not let us try the direct load after truncating the table,

SQL> alter table t compress for DIRECT_LOAD OPERATIONS;

Table altered.

SQL> host impdp directory=data_pump_dir dumpfile=xyz.dmp tables='test.t' table_e
xists_action=append status=2 

Import: Release 11.2.0.1.0 - Production on Thu Oct 14 21:32:28 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA directory=data_pump_d
ir dumpfile=xyz.dmp tables='test.t' table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "TEST"."T" exists. Data will be appended to existing table but
all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 21:43:55

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';

SUM(BYTES/1024/1024)
--------------------
             72.1875

Now the size has reduced from 176mb

SQL> alter table t nocompress;

Table altered.

SQL> host impdp directory=data_pump_dir dumpfile=xyz.dmp tables='test.t' table_e
xists_action=append status=2 

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';

SUM(BYTES/1024/1024)
--------------------
            176.1875

Without compression it’s 176mb.

convert this post to pdf.

If your DB is applied with patch#8198906, then beware that you may run into corruption when extending the table manually.
This patch is obselete now. You have to uninstall and install 9711859.

If your shop is managing 1000s of DBs then how to find out on which installation this patch is applied.

If these DBs are managed in OEM, then you can use below query

select * from  mgmt_inv_patch_fixed_bug  where bug_number=8198906;

convert this post to pdf.

In one of our application, we have a daily partition on few critical tables. After data load, we gather statitics. Stats gathering should complete in 2 hrs. 2hrs is pretty high due to memory usage on the server. But that’s different issue which we are working parallely.
We had a issue, where the stats gathering job failed. Stats was scheduled to complete 9am.Since the stats failed BO reports started going slow. Business can’t afford to wait 2hrs for stats gathering.
As a quick fix we took previous day stats and applied to today’s partition.

If subpartition exist and if you are trying to copy partition statistics, we will be getting error message like below,

ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 17130
ORA-06512: at line 2

If subpartition exist, run the stats gathering first and followed by partition copy stats

exec dbms_stats.copy_table_stats(user, 'TAB1', srcpartname=>'P_20100927_S', dstpartname=>'P_20100928_S');

Followed by partition stats, to which you will get the error. Which you can ignore.

exec dbms_stats.copy_table_stats(user, 'TAB1', srcpartname=>'P_20100927', dstpartname=>'P_20100928');
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 17130
ORA-06512: at line 2

Bug#8866627 might fix it has it has fix for many dbms_stats related bugs.

convert this post to pdf.

Many of our sessions are waiting on ‘cursor: pin S wait on X’ in one our DB. Connecting to DB via Toad,sql developer is hanging.

Most of the sessions from toad,sql developer is waiting on, as the query all_objects during the startup.

SELECT 'X' FROM SYS.ALL_OBJECTS WHERE    OBJECT_NAME = :1 AND OWNER = :2 AND
OBJECT_TYPE = 'TABLE'

Some kind of contention around all_objects.

To find the blocking session,

SELECT sid,last_call_et,p2raw
,to_number(substr(to_char(rawtohex(p2raw)), 1, 8), 'XXXXXXXX') sid "Blocking Sid"
FROM v$session
WHERE event = 'cursor: pin S wait on X'
order by last_call_et;

Get the record having max last_call_et and take the correspond “Blocking Sid”. Killing the “Blocking Sid” has cleared the DB issue. Most likely this case killing sessions will take time, so kill -9 the os process.

Blocking Sid in our case is waiting on sql_id 1j4husc7c0k5u

To get the query,

SQL> select * from table(dbms_xplan.display_awr('1j4husc7c0k5u'));

SQL_ID 1j4husc7c0k5u
--------------------
select i.obj#, i.rowcnt, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey,i.clufac, i.blevel,
i.analyzetime, i.samplesize, decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),
i.flags, ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist where
i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#

Plan hash value: 1414749717

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |       |       |     4 (100)|          |
|   1 |  SORT ORDER BY                |                   |     4 |   400 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER          |                   |     4 |   400 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS CLUSTER       | IND$              |     4 |   192 |     2   (0)| 00:00:01 |
|   4 |     INDEX UNIQUE SCAN         | I_OBJ#            |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| IND_STATS$        |     1 |    52 |     1   (0)| 00:00:01 |
|   6 |     INDEX UNIQUE SCAN         | I_IND_STATS$_OBJ# |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

May be auto stats gathering happening on that time creating contention in IND$. This behaviour is documented in note#6011045.8

convert this post to pdf.

Refer to below link for manually creating and evolving the baseline plan.

http://www.oracle-base.com/articles/11g/SqlPlanManagement_11gR1.php

In the example given in the url above, after adding index we need to manually evolve. This helps in accepting the right plan.

But what happens if i drop the index.

SQL> drop index spm_test_tab_idx;

Index dropped.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE TRACE
SQL> SELECT description
2  FROM   spm_test_tab
3  WHERE  id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462

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

--

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
|

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

--

|   0 | SELECT STATEMENT  |              |     1 |    25 |    13   (0)| 00:00:01

|

|*  1 |  TABLE ACCESS FULL| SPM_TEST_TAB |     1 |    25 |    13   (0)| 00:00:01

|

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

--

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=99)

Note
-----
- SQL plan baseline "SQL_PLAN_7qxjk7bch8h5tb65c37c8" used for this statement ==> Old base line is taken automatically

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
47  consistent gets
0  physical reads
0  redo size
441  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

Let me try to create that index back and see what happens,

SQL> CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT description
2  FROM   spm_test_tab
3  WHERE  id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 3121206333

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

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

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%

CPU)| Time     |

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

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

|   0 | SELECT STATEMENT            |                  |     1 |    25 |     2
(0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SPM_TEST_TAB     |     1 |    25 |     2
(0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |     1 |       |     1
(0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=99)

Note
-----
- SQL plan baseline "SQL_PLAN_7qxjk7bch8h5ted3324c0" used for this statement  ==> Goes for the best plan

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
448  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

Note: I have not evolved 2nd time after putting back the index.

Need to check how the auto Evolving of baseline works.

convert this post to pdf.

 We did see our mview going slow. When we traced the session we found our job is waiting for a while in sumdelta$.

if automic_refresh=false due to direct load operation sumdelta$ don’t purge. There are some bug registered for this.

This is the workaround.

CREATE MATERIALIZED VIEW dummy_mview
TABLESPACE tbs_data
NOLOGGING
refresh complete
start with sysdate
next (sysdate+(1/1440))
WITH ROWID
AS select sysdate,col1 from mview mv10 WHERE 1=2 ;

In the select add “sysdate” so that refresh is assumed as complex and refresh every time.

exec DBMS_MVIEW.REFRESH('DUMMY_MVIEW','C');

select count(*) from sys.sumdelta$ where tableobj# in (select obj# from sys.obj$ where name=upper('DUMMY_MVIEW');

the count of this query should reduce to zero.

convert this post to pdf.

I generally create the profile via oem. The below link explains how to create via command line,

http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php

convert this post to pdf.

We have a DB which connects to other DB via LDAP connection. We had an issue where db link to another db server is slow.

I have limited expertise on LDAP. This is how i solved the issue.

select * from dual@db_link;

it’s slow first time. In the same session if we run it again, it’s fast.

Opened a SR.Got all the trace files but no update.

I enabled tnsping trace using below command

#TNSPING.TRACE_LEVEL = 16
#TNSPING.TRACE_DIRECTORY=/tmp

It didn’t have time info. So enabled the client and server level trace.

#trace_level_server=16
#trace_level_client=16
#trace_directory_server=/tmp
#trace_directory_client=/tmp
#trace_file_client=cli1
#trace_file_server=srv1
#trace_unique_client=true

in the trace

[04-AUG-2010 05:20:19:035] nnflilc:  Opening sync conn to server1.domain.com:123
[04-AUG-2010 05:21:33:947] nnflilc:   ldap_open failed ld:0

almost a minute it waits on server1, then it goes to server 2 and connects

[04-AUG-2010 05:21:33:954] nnflilc:  Opening sync conn to server2.domain.com:123
[04-AUG-2010 05:21:33:979] nnflalc: entry
[04-AUG-2010 05:21:34:009] nnflalc:     bind call returns 0

This gives the clue that server1 oid is having issue. So we changed the ldap.ora look up

DIRECTORY_SERVERS= (server1.domain.com:123:636,server2.domain.com:123:636)

to

DIRECTORY_SERVERS= (server2.domain.com:123:636,server1.domain.com:123:636)

command to start and stop oid.

oidmon start
oidctl server=oidldapd1 instance=1 configset=1 start
oidctl server=oidrepld1 instance=1 flags="-h server1 -p 389" start

stop

oidctl server=oidldapd1 instance=1 configset=1 stop;sleep 10
oidctl server=oidrepld1 instance=1 stop;sleep 10
oidmon stop

convert this post to pdf.

Create a sql based user defined metrics with below script. SQL query output will be 2 colums with metrics value as number.

select tablespace_name,
100-(round(((mb_free+mb_total-mb_curr)/mb_total)*100,0)) adj_pct_free
from
(
select d.tablespace_name,
nvl(sum(f.bytes)/1024/1024, 0) mb_free,
sum(d.bytes)/1024/1024 mb_curr,
case when sum(d.maxbytes)>0 then sum(d.maxbytes)/1024/1024
else  sum(d.bytes)/1024/1024 end mb_total
from dba_data_files d left outer join dba_free_space f
on (f.tablespace_name = d.tablespace_name)
group by d.tablespace_name
) used_free
where tablespace_name not like '%UNDO%';

convert this post to pdf.

In sqlt.zip there is a script called coe_xfr_sql_profile.sql which will help in generating the sql_profile from other_xml of v$sql_plan and output in executable format. This helps in moving the plan across the environment and also before creating profile helps you in editing the hints.

SQL> @/tmp/coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: bcmu3by7zdb03

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 2415307779

Values passed:
~~~~~~~~~~~~~
SQL_ID         : "bcmu3by7zdb03"
PLAN_HASH_VALUE: "2415307779"

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

convert this post to pdf.

New diagonistic tool is introduced in 11g. Using which we can gather all the information relevant to an issue and upload to oracle or investigate by yourself.

adrci is executable in $ORACLE_HOME/bin location

$ adrci

ADRCI: Release 11.1.0.7.0 - Production on Mon Jul 26 04:08:33 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ADR base = "/opt/apps/oracle"
adrci>

adrci> show home
ADR Homes:
diag/rdbms/db1
diag/rdbms/db2
diag/rdbms/db3
diag/rdbms/db4
diag/tnslsnr/hostname/listener_bor3rep

adrci> set homepath diag/rdbms/db1

adrci> show problem ==> this will report the problem recently poped up in the db.

*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
3                    ORA 7445 [_memset()+120]                                    32093                2010-07-25 18:01:06.962092 -04:00
4                    ORA 7445 [_doprnt()+44]                                     32077                2010-07-25 18:01:03.642031 -04:00
2                    ORA 4030                                                    32270                2010-07-25 18:01:00.837604 -04:00
1                    ORA 7445 [ioc_pin_shared_executable_object()+952]           28842                2010-06-05 22:39:34.757161 -04:00
4 rows fetched

If you want to diagonise 4030 error, then

adrci> ips pack problem 2 in /tmp
Generated package 3 in file /tmp/ORA4030_20100726041042_COM_1.zip, mode complete

Upload the trace file to oracle or use it for investigation. Has all the information relevant to 4030 error.

convert this post to pdf.

When the primary archive location fills, there is an option of writing to alternate archivelog location. These parameter needs to be set.

log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN  ALTERNATE=LOG_ARCHIVE_DEST_2'
log_archive_dest_2='LOCATION=/other_destination_for_archiving'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'

You could use the following to revert back after failover:

alter system set  log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2  = alternate;

But make note of below restriction when setting this.Metalink#369120.1

” The REOPEN attribute takes precedence over the ALTERNATE attribute. The alternate destination is
used only if one of the following is true:
- The NOREOPEN attribute is specified.
- A value of zero (0) is specified for the REOPEN attribute.
- A nonzero REOPEN attribute and a nonzero MAX_FAILURE count have been exceeded. ”

It’s preferable to set NOREOPEN to the default location and enable this option.

convert this post to pdf.

We had  an outage, where pga eating up all the memory crashing the vcs. We temporarily solved the issue by moving to shared server and reducing the pga size.

Came across some of the suggestion to restrict pga,

1. Private_sga in MTS

2. 10261 event

10261, 00000, "Limit the size of the PGA heap"
// *Cause:    the limit is one kilobyte times the level of the event. If the
//            pga grows bigger than this signal an internal error.

to set a session to 100mb

useful note is found in

http://dioncho.wordpress.com/2010/06/14/rapid-pga-size-increase/

for setting multiple events in spfile, you can use below command
ALTER SYSTEM SET event='10261 trace name context forever, level 15000000','600 trace name heapdump, level 0x20000001' COMMENT='pga issue' SCOPE=SPFILE;

for resetting use,





2:


ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ;




Note: Have tried setting 10261 event in shared server db, but process didn't crash once reached the limit.

3. DBMS_SESSION.FREE_UNUSED_USER_MEMORY

convert this post to pdf.

We got an requirement to schedule a script after BD6+1. Means next day to BD6, which can be a holiday. But incase between BD1 to BD6 federal holiday falls, it needs to be moved to next day.

Create schedule for all the holidays,

begin
dbms_scheduler.create_schedule( schedule_name => 'NYD_FRI',
repeat_interval => 'FREQ=YEARLY;BYDATE=1231;BYDAY=FRI',
comments => 'Friday alternative for New Year''s Day');
dbms_scheduler.create_schedule( schedule_name => 'NYD_MON',
repeat_interval => 'FREQ=YEARLY;BYDATE=0102;BYDAY=MON',
comments => 'Monday alternative for New Year''s Day');
dbms_scheduler.create_schedule( schedule_name => 'NewYearsDay',
repeat_interval => 'FREQ=YEARLY;BYDATE=0101;BYDAY=MON,TUE,WED,THU,FRI;'
|| 'INCLUDE=NYD_FRI,NYD_MON', comments => 'New Year''s Day');
dbms_scheduler.create_schedule( schedule_name => 'MartinLutherKing',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=JAN;BYDAY=3MON',
comments => 'Martin Luther King Day');
dbms_scheduler.create_schedule( schedule_name => 'PresidentsDay',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=FEB;BYDAY=3MON',
comments => 'President''s Day');
dbms_scheduler.create_schedule( schedule_name => 'MemorialDay',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=MAY;BYDAY=-1MON',
comments => 'Memorial Day');
dbms_scheduler.create_schedule( schedule_name => 'ID_FRI',
repeat_interval => 'FREQ=YEARLY;BYDATE=0703;BYDAY=FRI',
comments => 'Friday alternative for Independence Day');
dbms_scheduler.create_schedule( schedule_name => 'ID_MON',
repeat_interval => 'FREQ=YEARLY;BYDATE=0705;BYDAY=MON',
comments => 'Monday alternative for Independence Day');
dbms_scheduler.create_schedule( schedule_name => 'IndependenceDay',
repeat_interval => 'FREQ=YEARLY;BYDATE=0704;BYDAY=MON,TUE,WED,THU,FRI;'
|| 'INCLUDE=ID_FRI,ID_MON', comments => 'Independence Day');
dbms_scheduler.create_schedule( schedule_name => 'LaborDay',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=SEP;BYDAY=1MON',
comments => 'Labor Day');
dbms_scheduler.create_schedule( schedule_name => 'ColumbusDay',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=OCT;BYDAY=2MON',
comments => 'Columbus Day');
dbms_scheduler.create_schedule( schedule_name => 'VD_FRI',
repeat_interval => 'FREQ=YEARLY;BYDATE=1110;BYDAY=FRI',
comments => 'Friday alternative for Veterans Day');
dbms_scheduler.create_schedule( schedule_name => 'VD_MON',
repeat_interval => 'FREQ=YEARLY;BYDATE=1112;BYDAY=MON',
comments => 'Monday alternative for Veterans Day');
dbms_scheduler.create_schedule( schedule_name => 'VeteransDay',
repeat_interval => 'FREQ=YEARLY;BYDATE=1111;BYDAY=MON,TUE,WED,THU,FRI;'
|| 'INCLUDE=VD_FRI,VD_MON', comments => 'Veterans Day');
dbms_scheduler.create_schedule( schedule_name => 'Thanksgiving',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=NOV;BYDAY=4THU',
comments => 'Thanksgiving');
dbms_scheduler.create_schedule( schedule_name => 'XMS_FRI',
repeat_interval => 'FREQ=YEARLY;BYDATE=1224;BYDAY=FRI',
comments => 'Friday alternative for Christmas');
dbms_scheduler.create_schedule( schedule_name => 'XMS_MON',
repeat_interval => 'FREQ=YEARLY;BYDATE=1226;BYDAY=MON',
comments => 'Monday alternative for Christmas');
dbms_scheduler.create_schedule( schedule_name => 'Christmas',
repeat_interval => 'FREQ=YEARLY;BYDATE=1225;BYDAY=MON,TUE,WED,THU,FRI;'
|| 'INCLUDE=XMS_FRI,XMS_MON', comments => 'Christmas');
end;
/

– create a schedule which clubs all the schedule created above.

begin
dbms_scheduler.create_schedule( schedule_name => 'FederalHolidays',
repeat_interval => 'NewYearsDay,MartinLutherKing,PresidentsDay,' ||
'MemorialDay,IndependenceDay,LaborDay,ColumbusDay,VeteransDay,' ||
'Thanksgiving,Christmas', comments => 'Federal Holidays');
end;
/

– Job which needs to be scheduled

create or replace procedure stats_run_after_BD6 as
begin

---- some job;
end;
/

— Create a job, note the “EXCLUDE” and “BYSETPOS” option

BEGIN
dbms_scheduler.create_job(
job_name => 'Stats_Run_After_BD6+1'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin stats_run_after_BD6; end; '
,repeat_interval => 'FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; byhour=14;byminute=30;bysecond=0; EXCLUDE=FederalHolidays; BYSETPOS=7'
,enabled => TRUE
,comments => 'Run Stats Gather next day of BD6');
END;
/

What are all the dates this job is going to execute. We can use the below procedure.

create or replace procedure print_dates
is
nr_of_dates  pls_integer :=30;
start_date timestamp with time zone := sysdate;
date_after timestamp with time zone := start_date - interval '1' second;
next_execution_date timestamp with time zone;
correct_execution_date timestamp with time zone;

begin
for i in 1 .. nr_of_dates
loop
dbms_scheduler.evaluate_calendar_string
('FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; byhour=14;byminute=30;bysecond=0; EXCLUDE=FederalHolidays; BYSETPOS=6', start_date, date_after, next_execution_date);

correct_execution_date := next_execution_date + interval '1' day;
DBMS_OUTPUT.PUT_LINE(correct_execution_date);

date_after := next_execution_date;
end loop;
end;
/

sometime the BD6+ 1 date will be weekend. If you want to execute in the weekend you can add “interval ’1′ day”,

correct_execution_date := next_execution_date + interval ’1′ day;

else remove that.

convert this post to pdf.

When one of the db got upgraded from 10202 to 10205, we found out one of the query is very slow. Didn’t taken the export of stats before upgrade. This is one way we forced a profile to this query.

declare
pln_sql_id varchar2(20) :='fathgmks3vb1g';
pln_plan_hash_value number := 3396554601;
orig_sql_id varchar2(20) := 'fathgmks3vb1g';
new_prof_name varchar2(20) := 'SQL_PROFILE_1';
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin

select extractvalue(value(d), '/hint') as outline_hints
bulk collect into ar_profile_hints
from xmltable('/*/outline_data/hint'
passing (select xmltype(other_xml) as xmlval from dba_hist_sql_plan
where sql_id = pln_sql_id and plan_hash_value = pln_plan_hash_value
and other_xml is not null)) d;

select sql_text into cl_sql_text
from dba_hist_sqltext where sql_id = orig_sql_id;

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
name => new_prof_name,
force_match => true);

end;
/

The below script can be used to see what’s the access path for a hash_value. This can be seen when you create outln and query ol_hint$

select
substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
hash_value = 2378699969
and child_number = 0
and other_xml is not null
)
) d;

You need to drop any sql_profile created before forcing a plan.

exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_014a4a1e346a8004', ignore => TRUE);

convert this post to pdf.

Steps for export and importing the db stats. This helps when you change the stats gathering approach.

exec dbms_stats.create_stat_table(ownname => ‘SYS’, stattab => ‘db_stat’, tblspace => ‘SYSTEM’)

exec dbms_stats.export_database_stats(stattab => ‘db_stat’,statown => ‘SYS’)

exec dbms_stats.import_database_stats(stattab => ‘db_stat’,statown => ‘SYS’)

convert this post to pdf.

Recently while applying incremental backup in DG running under ASM, got into issue due the rman backup taken with %U.

May be should have used %N%f.dbf this helps in giving both tablespace name and absolute file_id. This information is documented in

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta033.htm#sthref531

convert this post to pdf.

Recently upgraded the OMS from 10204 to 10205.
As part of we want to upgrade the agent to 10205 from lower version.
We have around 150 targets. So upgrading each is very difficult. Also fresh install requires to install base version apply 10205 patch, psu patch and other fixes. Doing in each server is difficult.

Instead, we installed 10205 with up to date patch in a sand box. Took tar and moved to servers where we need to patch.

After untaring,
./runInstaller -clone -forceClone ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=agent10205 -noconfig -silent
this will do the proper clone of the agent applying all the bugs
incase if you get complient like home already exist you can try,

./runInstaller -detachHome ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=agent10204 -noconfig -silent

once done

go to $AGENT_HOME/sysman/config
vi emd.properties
check and apply correct port to emd_url,repository_url,emdWallet
once done
secure the agent and start the agent

after which run
$ORACLE_HOME/bin/agentca -f

if its RAC run the below command. For Rac DB agent needs to be installed in both the server. Make sure the directory structures are same.
$ORACLE_HOME/bin/agentca -f -n “cluster1,cluster2″

Due to nic setting we might have issue in RAC. That case

“ListenOnAllNICs=false” in emd.properties if it’s cluster environment and getting http error port already used errorcheck note 443524.1

convert this post to pdf.

After a while did the DG creation using rman. Referred note#789370.1

take init.ora of both primary and standby backup

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format ‘/oradata/orabackup01/Primary/rman/%U’;
backup archivelog all format ‘/oradata/orabackup01/Primary/rman/%U’;
backup current controlfile for standby format ‘/oradata/orabackup01/Primary/rman/%U’;
}

alter system switch logfile;

scp the files to standby server. Use similar location.

on secondary

$ rman target sys/syspwd@Primary_dg auxiliary /

Recovery Manager: Release 10.2.0.2.0 – Production on Tue May 18 04:12:27 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: Primary (DBID=863765789)
connected to auxiliary database: standby (not mounted)

RMAN> duplicate target database for standby dorecover;

Starting Duplicate Db at 18-MAY-2010 04:12:51
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=155 devtype=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: sid=154 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 6.5 (2008052301)
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: sid=153 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Veritas NetBackup for Oracle – Release 6.5 (2008052301)

contents of Memory Script:
{
set until scn 8978424429104;
restore clone standby controlfile;
sql clone ‘alter database mount standby database’;
}
executing Memory Script

executing command: SET until clause

Starting restore at 18-MAY-2010 04:25:19
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata/orabackup01/standby/rman/jolduq5h_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata/orabackup01/standby/rman/jolduq5h_1_1 tag=TAG20100518T011321
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:18
output filename=+DISK_GROUP1/standby/controlfile/control01_standby.ctl
Finished restore at 18-MAY-2010 04:25:40

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
released channel: ORA_AUX_DISK_2
released channel: ORA_AUX_SBT_TAPE_1
released channel: ORA_AUX_SBT_TAPE_2

contents of Memory Script:
{
set until scn 8978424429104;
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 12 to new;
set newname for clone datafile 13 to new;
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to +DISK_GROUP1 in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18-MAY-2010 04:26:02
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=153 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=154 devtype=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: sid=155 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 6.5 (2008052301)
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: sid=156 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Veritas NetBackup for Oracle – Release 6.5 (2008052301)

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DISK_GROUP1
restoring datafile 00005 to +DISK_GROUP1
restoring datafile 00007 to +DISK_GROUP1
restoring datafile 00008 to +DISK_GROUP1
restoring datafile 00009 to +DISK_GROUP1
restoring datafile 00010 to +DISK_GROUP1
restoring datafile 00013 to +DISK_GROUP1
channel ORA_AUX_DISK_1: reading from backup piece /oradata/orabackup01/standby/rman/jkldupnn_1_1
channel ORA_AUX_DISK_2: starting datafile backupset restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DISK_GROUP1
restoring datafile 00003 to +DISK_GROUP1
restoring datafile 00004 to +DISK_GROUP1
restoring datafile 00006 to +DISK_GROUP1
restoring datafile 00011 to +DISK_GROUP1
restoring datafile 00012 to +DISK_GROUP1
channel ORA_AUX_DISK_2: reading from backup piece /oradata/orabackup01/standby/rman/jjldupnm_1_1
channel ORA_AUX_DISK_2: restored backup piece 1
piece handle=/oradata/orabackup01/standby/rman/jjldupnm_1_1 tag=TAG20100518T010558
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:06:15
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata/orabackup01/standby/rman/jkldupnn_1_1 tag=TAG20100518T010558
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:31
Finished restore at 18-MAY-2010 04:32:41

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=41 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/system.413.719295981
datafile 2 switched to datafile copy
input datafile copy recid=42 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/undo1.407.719295981
datafile 3 switched to datafile copy
input datafile copy recid=43 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/sysaux.345.719295971
datafile 4 switched to datafile copy
input datafile copy recid=44 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/users.416.719295983
datafile 5 switched to datafile copy
input datafile copy recid=45 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/ts_aud_data_purge.405.719295981
datafile 6 switched to datafile copy
input datafile copy recid=46 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/tspan_data.351.719295975
datafile 7 switched to datafile copy
input datafile copy recid=47 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/tspan_index.410.719295979
datafile 8 switched to datafile copy
input datafile copy recid=48 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/fuego_data.474.719295975
datafile 9 switched to datafile copy
input datafile copy recid=49 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/fuego_index.368.719295977
datafile 10 switched to datafile copy
input datafile copy recid=50 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/adw_data.342.719295971
datafile 11 switched to datafile copy
input datafile copy recid=51 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/adw_index.409.719295981
datafile 12 switched to datafile copy
input datafile copy recid=52 stamp=719296363 filename=+DISK_GROUP1/standby/datafile/reserve.411.719295979
datafile 13 switched to datafile copy
input datafile copy recid=53 stamp=719296363 filename=+DISK_GROUP1/standby/datafile/streams_tbs.406.719295983

contents of Memory Script:
{
set until scn 8978424429104;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 18-MAY-2010 04:32:45
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2

starting media recovery

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘+DISK_GROUP1/standby/datafile/system.413.719295981′

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/18/2010 04:33:00
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 203315 lowscn 8978424428500 found to restore
RMAN-06025: no backup of log thread 1 seq 203314 lowscn 8978424427332 found to restore
RMAN-06025: no backup of log thread 1 seq 203313 lowscn 8978424419381 found to restore
RMAN-06025: no backup of log thread 1 seq 203312 lowscn 8978255748027 found to restore

exit from rman. Ignore the above error time being.

go to primary

alter system set log_archive_dest_state_2 = enable ;

go to standby

alter database recover managed standby database disconnect from session;

This should catch up the standby with the missing arch reported above.

execute the below command in standby, you will get the status.

select sequence#,applied from v$archived_log order by sequence#;

convert this post to pdf.

My OMS is in 10204. It’s very slow, one vital reason is we are running low on OS resource. We are planning to move to much higher configuration.

These are some of the changes which helped is putting the below entry in opmn.xml at $OMS_HOME/opmn/conf

===========

convert this post to pdf.

My OMS installed in very poor configuration. Can’t take any load. I applied a template by mistake for 750 target in a shot. My OMS crashed. It didnt’ came up. I tried bouncing, no luck. But i see some activity in my trace file.
Looks like it’s applying or doing something.
Ok, how long it is going to take before my OMS back up. Since my OMS crashed i can’t see how many pending jobs. Here i used below query to figure out how much pending and how much completed.

SELECT target_type “Target Type”,
target_name “Destination Target”,
template_name “Last Applied Template”,
copy_type “Apply Option”,
execution_status “Status”
FROM
(
SELECT mts.template_name template_name,
mt.target_name target_name,
mt.target_type target_type,
mtc.copy_type copy_type,
DECODE(muodt.execution_status,
1, ‘PENDING’,
5, ‘COMPLETE’,
2, ‘EXECUTING’,
3, ‘ABORTED’,
4, ‘FAILED’) execution_status,
mtc.created_date,
RANK () OVER (PARTITION BY mt.target_name,mt.target_type ORDER BY mtc.created_date
DESC ) rk
FROM mgmt_update_operations_details muodt,
mgmt_update_operations_data muoda,
mgmt_update_template_data_map mutdm,
mgmt_template_copies mtc,
mgmt_targets mt,
mgmt_templates mts
WHERE muodt.operation_guid = muoda.operation_guid
AND muoda.data_set_guid = mutdm.data_set_guid
AND muoda.data_set_type = 4
AND mutdm.template_copy_guid = mtc.template_copy_guid
AND mt.target_guid = muodt.destination_target_guid
AND mtc.template_guid = mts.template_guid
)
WHERE rk <=1
and template_name ='Prod - Database - Template' ==> my template name
ORDER BY 1,3,2 ;

convert this post to pdf.

I am trying to remove agent from OEM. It’s running for quite a while and no result seen. If your case is like this, you can use below command to remove.

exec mgmt_admin.cleanup_agent(‘hostname:1830′);

if you want to delete any target from command line use

exec mgmt_admin.delete_target(‘target_name’,'oracle_database’);

followed by

commit;

convert this post to pdf.

This i have seen in one of the forums. Which is something we are looking for sometime.

Not tried yet, but planning to implement soon once we migrate to much robust oem server.

export ORACLE_HOME=${AGENT_HOME}

Stop the agent:
${ORACLE_HOME}/bin/emctl stop agent

Save the file database.xmlp :
cp -p $ORACLE_HOME/sysman/admin/default_collection/database.xmlp $ORACLE_HOME/sysman/admin/default_collection/database.xmlp.orig

search for pattern <CollectionItem NAME=”tbspAllocation”>  or whatever and change the frequency to your requirement

<CollectionItem NAME=”tbspAllocation”>
<ValidIf>
<CategoryProp NAME=”VersionCategory” CHOICES=”pre8;8i;8iR2;9i;9iR2;10gR1;10gR2;10gR203;11gR1;11gR2″/>
</ValidIf>

<Schedule>
<IntervalSchedule INTERVAL=”7″ TIME_UNIT=”Day”/>
</Schedule>

<MetricColl NAME=”tbspAllocation” />
</CollectionItem>

Change the value of INTERVAL from 7 to 1 then save & quit.

Restart the agent :
$ORACLE_HOME}/bin/emctl start agent

The update will be shown in the Grid Control within 24H.

convert this post to pdf.

Our managment decided to give OEM access to development DBA. We were worried that development DBA might share the password to developer. There are 100s of developer in our environment. If everyone login to the OEM, this would create night mare. Since OEM is administration tool, we would prefer to be used by DBA.
How to protect development DBA sharing their pwd with developer. We tried something similar to sessions_per_user profile. Which didn’t work, as it is only for oracle user and not for OEM user.
Other approach is enable auditing on OEM user and if the login is coming from more than one ip, alert the dba.
By default, ip addr and hostname will not be registered in mgmt$audit_log. To enable it follow the metalink note#457382.1

exec mgmt_audit_admin.set_audit(0, null, 0);
shutdown the oms,opmn,agent
update
$OMS_HOME/Apache/Apache/conf/httpd.conf

make these 2 entries
UseWebCacheIp On
HostnameLookups On

restart the OMS.
After which each login in insert into mgmt_user_sessions table along with hostname and ipaddr. If a userid is used in more than one ipaddr, we can easily track.
select EM_USER,
             EM_USER_TYPE,
             EM_USER_HOST_NAME,
             OMS_HOST_NAME ,
             IP_ADDRESS
   from  MGMT_USER_SESSION
order by LOGIN_TIME desc;

But problem is this table might keep growing.
To do the purge,
Stop oms
exec mgmt_audit_admin.set_audit(0,null,2)
exec mgmt_audit_admin.audit_purge(’11-Jan-10′); — Give sysdate+1 to remove all the data

enable auditing
exec mgmt_audit_admin.set_audit(0, null, 0);
start OMS.

convert this post to pdf.

we have a requirement to alert for backup failures. Connecting to each rman repository or db to get this information is difficult. We have OEM, and this how we have implemented. We created UDM and applied to necessary targets.

Monitor DB fullbackup.

select count(*) from (select rownum no,x.status,x.end_time from (select status,end_time from v$rman_status where end_time is not null and object_type=’DB FULL‘ and end_time > sysdate-1 order by end_time desc) x) y where y.no=1 and y.status = ‘COMPLETED’

Monitor DB incr,

select count(*) from (select rownum no,x.status,x.end_time from (select status,end_time from v$rman_status where end_time is not null and object_type=’DB INCR‘ and end_time > sysdate-1 order by end_time desc) x) y where y.no=1 and y.status = ‘COMPLETED’

Archivelog backup.

select count(*) from (select rownum no,x.status,x.end_time from (select status,end_time from v$rman_status where end_time is not null and object_type=’ARCHIVELOG’ and end_time > sysdate-1 order by end_time desc) x) y where y.no=1 and y.status =’COMPLETED’

Other similar query which i came across is

select (sysdate-min(t))*24 from
(
  select max(b.CHECKPOINT_TIME) t
  from v$backup_datafile b, v$tablespace ts, v$datafile f
  where INCLUDED_IN_DATABASE_BACKUP='YES'
  and f.file#=b.file#
  and f.ts#=ts.ts#
  group by f.file#
)

for arch log monitoring,

select (sysdate-max(NEXT_TIME))*24 from v$BACKUP_REDOLOG

convert this post to pdf.

if you have used OEM, then you should have used sql tuning advisor. Sometime if the plan is very large you will not be able to implement the profile. You will get message like,
“There was a problem creating the SQL Profile setString can only process strings of less than 32766 chararacters”

Instead of implementing via OEM, use the below script to force the profile. But remember the tuning name what you have generated.

begin
dbms_output.put_line(
dbms_sqltune.accept_sql_profile(
task_name => 'sql_tuning_xyz',
name => 'manual_forcing',
replace => true,
force_match => true
)
);
end;
/

convert this post to pdf.

Read couple of interesting topic on proxy login in oracle.

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_19.shtml

http://jonathanlewis.wordpress.com/2006/12/18/proxy-users/

convert this post to pdf.

We have an requirement, where we need to purge portion of data from huge table. After much discussion, we have decided go to like
1. create temp table
2. populate only relevant data from main table to temp table
3. drop main table.
4. rename temp table to main table and recreate missing indexes.

there are various approach to do this. But instead we have gone with something like split the big table in to logical split using rowid and with each logical split populate the temp table parallel.

create table scuser.tab1_bk as select * from scuser.tab1 where 1=2;

alter table scuser.tab1_bk nologging;

truncate table scuser.tab1_bk;

Drop table split_rowid purge;

CREATE TABLE split_rowid as select distinct grp,
first_value(relative_fno) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) lo_fno,
first_value(block_id ) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) lo_block,
last_value(relative_fno) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) hi_fno,
last_value(block_id+blocks-1) over
(partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) hi_block,
sum(blocks) over (partition by grp) sum_blocks
from (
select relative_fno,
block_id,
blocks,
trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
(sum(blocks) over ()/&chunk) ) grp
from dba_extents
where segment_name = ('TAB1')
and owner = '&owner' order by block_id
)
/

Create or replace procedure purge_clock(p_no number,lo_rowid rowid,hi_rowid rowid) as
type array is table of scuser.tab1_bk%rowtype;
l_data array;
cursor c is select /+* first_rows */ * from tab1 a where (exists
(select 1 from probsummarym1
where probsummarym1.numberprgn = a.key_char
and a.type = 'problem')
or a.datetime_last_upd > (sysdate-3)
or type <> 'problem')
and substr(name,0,7) <> 'JPASRPT' and rowid between lo_rowid and hi_rowid;;

lv_cnt number:=0;
lv_cnt1 number:=0;
BEGIN
open c;
loop
fetch c bulk collect into l_data LIMIT 10000;
lv_cnt:=lv_cnt+1;
lv_cnt1 := lv_cnt*10000;
dbms_application_info.set_client_info(p_no||' of Clock:'||lv_cnt1);
forall i in 1 .. l_data.count
Insert into scuser.tab1_bk values l_data(i);
COMMIT;
exit when c%notfound;
END LOOP;
CLOSE c;
commit;
END;
/

alter system set job_queue_processes=20;

set serveroutput on;
Declare
cursor c1 is
select rownum no,
dbms_rowid.rowid_create(1,
(select data_object_id from dba_objects where object_name='tab1' and object_type='TABLE'),lo_fno,lo_block,0) lo_rowid,
dbms_rowid.rowid_create(1,
(select data_object_id from dba_objects where object_name='tab1' and object_type='TABLE'),hi_fno,hi_block,32767) hi_rowid
from split_rowid order by no;

lv_job number;
begin
for i in c1 loop
dbms_job.submit(lv_job,'purge_clock('||i.no||','||chr(39)||i.lo_rowid||chr(39)||','||chr(39)||i.hi_rowid||chr(39)||');');
dbms_output.put_line('Job#'||lv_job);
commit;
end loop;
commit;
end;
/

convert this post to pdf.

If you have hundreds of DB it will be usefull to have all the tns entry in your desktop. So for small task you don’t have to get into server. This will be saving lot of times. There are various way to create tns entry. The approach what i have used is from OEM.
If your shop is monitored by OEM, then you can use below script to generate tns entry and put it in your tns_admin location.
This procedure needs be compiled in sysman schema.

create or replace procedure rpt_tns_generation as
cursor c1 is
select distinct a.target_guid from mgmt_target_properties a,mgmt_targets b
where a.target_guid = b.target_guid
and b.target_type=’oracle_database’;

lv_sid varchar2(30);
lv_host varchar2(100);
lv_port number;
lv_service_name varchar2(100);
begin
for i in c1 loop
select property_value
into lv_sid
from mgmt_target_properties
where property_name=’SID’
and target_guid=i.target_guid;

select property_value
into lv_host
from mgmt_target_properties
where property_name=’MachineName’
and target_guid=i.target_guid;

select property_value
into lv_port
from mgmt_target_properties
where property_name=’Port’
and target_guid=i.target_guid;

dbms_output.put_line(lv_sid||’='||chr(10)||’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=’||lv_host||’)(PORT=’||lv_port||’)))(CONNECT_DATA=(sid=’||lv_sid||’)))’);

end loop;

exception
when others then
dbms_output.put_line(‘error for ‘||sqlerrm);
end;
/

convert this post to pdf.

If your shop using OEM as monitoring tool, then you should be aware of this. If not try it out. Create a group and put all the DB under that group. If you get into the group you will see the launch dashboard. That will invoke the console. This will be one place where you get all the information. Critical,warning, target down, target unknow information. There is a option to customize also.
In our shop we have given a extra TFT monitor to each DBA and he moves this console to this extra monitor. After implementing this there was tremendous progress in our monitoring ability. We are on top of all the alerts now. If budget permits you can go for a wall mounted LCD

convert this post to pdf.

This tool needs to be installed in OMS server preferreably in OMS_HOME. Create a directory called “emdiag” to store these binaries.
You can follow the note 421053.1 and 421499.1 for this.
Once the installation is done you can get the debug details for all the target monitored using

./repvfy -usr SYSMAN -pwd  -tns emrep test -level 9 -detail

If you want to fix some of the debug issues you need to get the number from detail*.log or verify*.log in $EMDIAG_HOME/log location
These files will ahve info like,

001. Agents without a monitored host target: 1
101. Active Agents with clock-skew problems: 8
105. Agents not uploading any data: 6
106. Agents with improper ping status (5440008): 4
110. Agents not marked as potentially down (6314624): 4

so to fix 001 you need to run

$ ./repvfy -tns emrep verify blackouts -test 1 -fix

Please enter the SYSMAN password: 

-- --------------------------------------------------------------------- --
-- EMDIAG: 2009.0711     Repository: 10.2.0.4.0     06-Aug-2009 09:04:50 --
---------------------------------------------------------------------------
-- -- -- - Running in FIX mode: Data updated for all fixed tests - -- -- --
-- --------------------------------------------------------------------- --
verifyBLACKOUTS

These are some of the other module which can be fixed.

$ repvfy verify availability -test 100 -fix

$ repvfy verify targets -test 709 -fix
$ repvfy verify targets -test 705 -fix
$ repvfy verify targets -test 703 -fix
$ repvfy verify targets -test 702 -fix
$ repvfy verify targets -test 701 -fix
$ repvfy verify targets -test 206 -fix
$ repvfy verify targets -test 130 -fix
$ repvfy verify targets -test 122 -fix
$ repvfy verify targets -test 106 -fix

$ repvfy verify repository -test 706 -fix

$ repvfy verify policies -test 702 -fix
$ repvfy verify policies -test 700 -fix
$ repvfy verify policies -test 103 -fix
$ repvfy verify metrics -test 199 -fix
$ repvfy verify policies -test 101 -fix

$ repvfy verify metrics -test 705 -fix
$ repvfy verify ecm -test 799 -fix
$ repvfy verify metrics -test 703 -fix
$ repvfy verify metrics -test 110 -fix
$ repvfy verify targets -test 189 -fix

$ repvfy verify loaders -test 700 -fix

./repvfy -usr SYSMAN -tns emrep -pwd
 -module TARGETS -level 9 -detail
./repvfy -usr SYSMAN -tns emrep -pwd
 dump target -name  -type oracle_database

convert this post to pdf.

In many shops year on year a capacity planning will be done to find out how much space to be added. If you want to find out what are the mountpoint to which storage as to be added, you can get those information from OEM. Our client shop has more than 1000 targets. No way there to generate report from oem to find out what are the mountpoint reaching threshold.
If you know the sysman pwd, you can use below query to find out potential mountpoint to which you need to add space.

select b.host_name,b.target_name,a.key_value,a.message,a.collection_timestamp from MGMT_CURRENT_SEVERITY a,mgmt_targets b
where a.target_guid= b.target_guid and
(a.message_nlsid like 'host_file%' or a.message_nlsid like 'DiskGroup%' or a.message_nlsid like 'archFull%')
order by b.host_name asc,b.target_name asc,a.key_value asc,a.collection_timestamp desc

convert this post to pdf.

A client mailed us with a query, which crashes with 4031 error. It’s a complex query has Nl and Hash joins. While diagonising i found that the problem lies with the below two line of where condition.

AND  ( (GL_MONTH_ACTV_BAL.BUS_UNIT_ID)=SAP_BUS_UNIT_MO_ASIS.BUS_UNIT_ID and SAP_BUS_UNIT_MO_ASIS.BUS_UNIT_CD_TYPE in (‘XYZ123′ ,’ABC’)  )
 AND  ( (GL_MONTH_ACTV_BAL.GLAS_BUS_UNIT_ASIS_ID)=GLAS_BUSINESS_UNIT.BUS_UNIT_ID   AND GLAS_BUSINESS_UNIT.BUS_UNIT_CD_TYPE IN (‘ABC123′ ,’ABC’)  )

When i change the query to

AND  ( (to_number(GL_MONTH_ACTV_BAL.BUS_UNIT_ID))=SAP_BUS_UNIT_MO_ASIS.BUS_UNIT_ID and SAP_BUS_UNIT_MO_ASIS.BUS_UNIT_CD_TYPE in (‘XYZ123′ ,’ABC’)  )
 AND  ( (to_number(GL_MONTH_ACTV_BAL.GLAS_BUS_UNIT_ASIS_ID))=GLAS_BUSINESS_UNIT.BUS_UNIT_ID   AND GLAS_BUSINESS_UNIT.BUS_UNIT_CD_TYPE IN (‘ABC123′ ,’ABC’)  )

It worked. The funny thing here is the column GL_MONTH_ACTV_BAL.BUS_UNIT_ID is  of number datatype.  We were not able to even get the explain plan user “Set autot trace exp”, as the query crashes with 4031 error.

On error and trial of this query, we found that ‘star_transformation_enabled’ is the culprit. It’s pure luck i found this one. I have to change this value from true to false.

I can’t set this at db level or session level, as it might have impact on other queries. That’s where we used /*+opt_param*/ hint

SELECT /*+ OPT_PARAM(‘star_transformation_enabled’,'false’) */

convert this post to pdf.

From 10g there is a change in No_Invalidate parameter of dbms_stats. Default value of No_Invalidate in 10g is dbms_stats.Auto_Invalidate. Means the cursor will remain valid for 5 hrs after the stats gathering. This is to avoid spike in cpu and othe resources due to hard parsing. 5 hrs time limit is governed by underscore parameter “_optimizer_invalidation_period”

convert this post to pdf.

It’s common missing the arch log file needed for standby recovery, there by standby going out of sync. One way before 10g is rebuilding the standby from scratch.

From 10g, you can create a incremental backup from primary and apply the incremental to standby site.

For Source and Target in ASM, use metalink note

Steps to perform for Rolling forward a standby database using RMAN incremental backup when primary and standby are in ASM filesystem [ID 836986.1]

BACKUP FROM SOURCE:

~~~~~~~~~~~~~~~~~~~

get the SCN from checkpoint_change#@v$datafile_header  of standby database. You can also take the current_scn@v$database. But v$database value comes from controlfile and if the controlfile value is recent than datafile, you may not be successful.

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 329412309 DATABASE FORMAT ‘/local/eastus/orabackup00/rman/incr1_%U’;

if you want compressed backup

BACKUP AS COMPRESSED backupset DEVICE TYPE DISK INCREMENTAL FROM SCN 8977054477894  DATABASE FORMAT  ‘/local/eastus/orabackup00/rman/incr1_%U’;

Starting backup at 06-MAY-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=422 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00015 name=/local/eastus/oradata01/eastusp1/eastusp1rdgdbo_data01.dbf

input datafile fno=00002 name=/local/eastus/oradata03/eastusp1/eastusp1undotbs01.dbf

input datafile fno=00010 name=/local/eastus/oradata05/eastusp1/eastusp1audit_data01.dbf

input datafile fno=00014 name=/local/eastus/oradata05/eastusp1/eastusp1_sysaux_01.dbf

input datafile fno=00013 name=/local/eastus/oradata05/eastusp1/eastusp1statspack_data.dbf

input datafile fno=00008 name=/local/eastus/oradata05/eastusp1/eastusp1xdb01.dbf

input datafile fno=00004 name=/local/eastus/oradata05/eastusp1/eastusp1drsys01.dbf

input datafile fno=00009 name=/local/eastus/oradata05/eastusp1/netiq_data01.dbf

input datafile fno=00003 name=/local/eastus/oradata05/eastusp1/eastusp1cwmlite01.dbf

input datafile fno=00005 name=/local/eastus/oradata05/eastusp1/eastusp1odm01.dbf

input datafile fno=00001 name=/local/eastus/oradata00/eastusp1/eastusp1system01.dbf

input datafile fno=00006 name=/local/eastus/oradata00/eastusp1/eastusp1tools01.dbf

input datafile fno=00007 name=/local/eastus/oradata00/eastusp1/eastusp1users01.dbf

input datafile fno=00012 name=/local/eastus/oradata02/eastusp1/eastusp1east4_idx01.dbf

input datafile fno=00017 name=/local/eastus/oradata02/eastusp1/eastusp1rdgdbo_idx01.dbf

input datafile fno=00011 name=/local/eastus/oradata01/eastusp1/eastusp1east4_data01.dbf

input datafile fno=00016 name=/local/eastus/oradata01/eastusp1/eastusp1ASI_Data01.dbf

channel ORA_DISK_1: starting piece 1 at 06-MAY-09

channel ORA_DISK_1: finished piece 1 at 06-MAY-09

piece handle=/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1 tag=TAG20090506T160952 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:07:05

Finished backup at 06-MAY-09

TRANSFER THE INCREMENTAL BACKUP TO TARGET

Catalog the incremental backup. This help rman to indentify the new backup.

RMAN> CATALOG START WITH ‘/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1′;

searching for all files that match the pattern /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

List of Files Unknown to the Database

=====================================

File Name: /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files…

cataloging done

List of Cataloged Files

=======================

File Name: /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

Recover with redo clause

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 06-MAY-09

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /local/eastus/oradata00/eastusp1/eastusp1system01.dbf

destination for restore of datafile 00002: /local/eastus/oradata03/eastusp1/eastusp1undotbs01.dbf

destination for restore of datafile 00003: /local/eastus/oradata05/eastusp1/eastusp1cwmlite01.dbf

destination for restore of datafile 00004: /local/eastus/oradata05/eastusp1/eastusp1drsys01.dbf

destination for restore of datafile 00005: /local/eastus/oradata05/eastusp1/eastusp1odm01.dbf

destination for restore of datafile 00006: /local/eastus/oradata00/eastusp1/eastusp1tools01.dbf

destination for restore of datafile 00007: /local/eastus/oradata00/eastusp1/eastusp1users01.dbf

destination for restore of datafile 00008: /local/eastus/oradata05/eastusp1/eastusp1xdb01.dbf

destination for restore of datafile 00009: /local/eastus/oradata05/eastusp1/netiq_data01.dbf

destination for restore of datafile 00010: /local/eastus/oradata05/eastusp1/eastusp1audit_data01.dbf

destination for restore of datafile 00011: /local/eastus/oradata01/eastusp1/eastusp1east4_data01.dbf

destination for restore of datafile 00012: /local/eastus/oradata02/eastusp1/eastusp1east4_idx01.dbf

destination for restore of datafile 00013: /local/eastus/oradata05/eastusp1/eastusp1statspack_data.dbf

destination for restore of datafile 00014: /local/eastus/oradata05/eastusp1/eastusp1_sysaux_01.dbf

destination for restore of datafile 00015: /local/eastus/oradata01/eastusp1/eastusp1rdgdbo_data01.dbf

destination for restore of datafile 00016: /local/eastus/oradata01/eastusp1/eastusp1ASI_Data01.dbf

destination for restore of datafile 00017: /local/eastus/oradata02/eastusp1/eastusp1rdgdbo_idx01.dbf

channel ORA_DISK_1: reading from backup piece /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1 tag=TAG20090506T160952

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Finished recover at 06-MAY-09

RMAN>

You can check the checkpoint_change#@datafile_header, it should have increased and should be equal to the point you started the incremental backup.

Controlfile of standby may not be updated after this excercise. Recreate the standby controlfile from primary again.

convert this post to pdf.

########

– You can copy below script to notepad and change “SOURCE” to your SOURCE DB and “TARGET” to your TARGET DB
########

Execute in both source and target:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

connect SYS/password as SYSDBA

EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
drop user strmadmin cascade;

    create user STRMADMIN identified by STRMADMIN123;
    GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
    execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
    alter system set aq_tm_processes=1;
   
    connect STRMADMIN/STRMADMIN123

    BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
      queue_table => 'STREAMS_QUEUE_TABLE',
      queue_name => 'STREAMS_QUEUE',
      queue_user => 'STRMADMIN');
    END;
    /

create db link from source to target and vice versa

Test using

select * from global_name@db_link;

verify it is returning correct value
Steps to be carried out at the Destination Database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Add apply rules for the Schema at the destination database :

connect STRMADMIN/STRMADMIN123
 BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name => 'SCOTT',
    streams_type => 'APPLY ',
    streams_name => 'STRMADMIN_APPLY',
    queue_name => 'STRMADMIN.STREAMS_QUEUE',
    include_dml => true,
     include_ddl => true,
    source_database => 'SOURCE');
 END;
 /
  BEGIN
      DBMS_APPLY_ADM.ALTER_APPLY(
       apply_name => 'STRMADMIN_APPLY',
       apply_user => 'SCOTT'
              );
 END;
 /

  BEGIN
 DBMS_APPLY_ADM.SET_PARAMETER(
   APPLY_NAME => 'STRMADMIN_APPLY',
   PARAMETER => 'disable_on_error',
   VALUE => 'N');
  END;
/
 

DECLARE
 v_started number;
BEGIN
 SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
 FROM DBA_APPLY WHERE APPLY_NAME = 'STRMADMIN_APPLY';
 if (v_started = 0) then
   DBMS_APPLY_ADM.START_APPLY(apply_name  => 'STRMADMIN_APPLY');
 end if;
END;
/

 
Steps to be carried out at the Source Database SOURCE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 Connect to STRMADMIN schema at source and execute below steps

connect STRMADMIN/STRMADMIN123

 BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name => 'SCOTT',
    streams_type => 'CAPTURE',
    streams_name => 'STREAM_CAPTURE',
    queue_name => 'STRMADMIN.STREAMS_QUEUE',
    include_dml => true,
   include_ddl => true,
   inclusion_rule => true,
    source_database => 'SOURCE');
 END;
 /

     BEGIN
 DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
     schema_name => 'SCOTT',
     streams_name => 'STREAM_PROPAGATE',
     source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
     destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@TARGET',
     include_dml => true,
     include_ddl => true,
     source_database => 'SOURCE');
 END;
 /

Steps to replicate the data to destination:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

First load the dictionary information to redo log file, otherwise you might get “WAITING FOR DICTIONARY REDO” during the capture process.

exec DBMS_CAPTURE_ADM.BUILD();

get the scn of the logfile which contains dictionary information.

select  FIRST_CHANGE#,name from v$archived_log where dictionary_begin='YES';

note the first_change# from above query. Make sure the arch logfile is available in disk from the filename got from name column of above query.

Take the datapump export from source

expdp userid/password dumpfile=<filename> directory=<directory_name>  flashback_scn=<first_change#>

Note the flashback_scn option

import the datapump to target

impdp userid/password dumpfile=<filename> directory=<directory_name> streams_configuration=n

note the streams_configuration=n in impdp

go to source database

exec  DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name  => 'STREAM_CAPTURE',force=>true);

You can either give force=>true or leave it, based on your requirement.

execute dbms_capture_adm.drop_capture ('STREAM_CAPTURE');

execute DBMS_CAPTURE_ADM.CREATE_CAPTURE(queue_name => 'STRMADMIN.STREAMS_QUEUE', capture_name => 'STREAM_CAPTURE', use_database_link=>TRUE,start_scn=><first_change#>, first_scn=><first_change#>);

note the start_scn and first_scn, this value is taking from v$archived_log.

      begin
       dbms_capture_adm.set_parameter('STREAM_CAPTURE','_SGA_SIZE','2000');
       dbms_capture_adm.set_parameter('STREAM_CAPTURE','_CHECKPOINT_FREQUENCY','1000');
       dbms_capture_adm.set_parameter('STREAM_CAPTURE','TRACE_LEVEL','2');
       dbms_capture_adm.set_parameter('STREAM_CAPTURE','PARALLELISM','20');
       DBMS_CAPTURE_ADM.START_CAPTURE(capture_name  => 'STREAM_CAPTURE');
     end;
/

when you set TRACE_LEVEL parameters, incase if the capture process fails you can trace more information to debug.

Go to destination and execute

exec  DBMS_APPLY_ADM.START_APPLY(apply_name  => 'STRMADMIN_APPLY');

Make changes in primary and see whether it is getting replicated in secondary. You can create heart beat table.

heart beat table can be like

create table scott.streams_heartbeat(source_name varchar2(30),Change_dt Date);

insert into scott.streams_heartbeat('<SOURCE_DB_NAME>',SYSDATE);

COMMIT;

Update heart beat table every minute using dbms_job/scheduler or even crontab.

Execute the below statement,

select state from v$streams_capture

you should see “CAPTURING CHANGES”

convert this post to pdf.

Capture process might stop sometime due to space issue in sysaux tablespace. Checkpoint information is stored in logmnr_restart_ckpt$ for quicker restart. From 10.2 onwards oracle has enabled automatic purging using the parameter CHECKPOINT_RETENTION_TIME. By default, checkpoint information is stored for last 60 days.
In some environment 60 days could be very long.This is one reason why sysaux tablespace can grow fast.
You can change the default by setting below parameter,

exec dbms_capture_adm.alter_capture(capture_name =>,CHECKPOINT_RETENTION_TIME=> 7);

Also you can bring the hwm of the table using below command,

alter table system.LOGMNR_RESTART_CKPT$ enable row movement;
alter table system.LOGMNR_RESTART_CKPT$ shrink space ;
alter table system.LOGMNR_RESTART_CKPT$ disable row movement;

You can also shrink the primary key index on this table.

Also you can force manual checkpoint using “_checkpoint_force”=’Y’.

convert this post to pdf.

If you are managing logical standby, it’s going out of sync is common. If number of log applied is very huge, and you find transaction on one table consuming lot of time in applying.

Then skip the transaction on table as below

execute dbms_logstdby.skip('DML','SCHEMA_NAME','TABLE_NAME');

Once the remaining log file applied and standby is in sync with primary, you have instantiate the skipped table again.

One option to do is create a db_link to primary and instantiate using,

EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE(schema_name => <SCHEMA_NAME>, table_name => <TABLE_NAME>, dblink =><DB LINK NAME>);

There might be cases where the table you are trying to instantiate is very huge. That case you can use expdp to instantiate the table as below,

A. Stop SQL apply

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

B. put in a skip rule(to keep others from making changes to the table on the Logical)

EXECUTE DBMS_LOGSTDBY.SKIP('DML','TEST,'AAA');

C1.Drop and recreate the table test.aaa

drop table test.aaa;
create table test.aaa(num1 number, text1 varchar2(50));

C2. then get the datapump data from the Primary

On the Logical:

SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;

expdp system/oracle tables=test.aaa dumpfile=expaaa.dmp flashback_scn=<scn#>

Note: Flashback scn will be restart_scn which you get from v$logstdby_progress.

4. then import the datapump data to the Logical

impdp system/oracle dumpfile=expaaa.dmp tables=test.aaa table_exists_actions=append

5. then unskip the skip rule for the table

EXECUTE DBMS_LOGSTDBY.UNSKIP(’DML’,'TEST’,’AAA’);

analyze table test.aaa compute statistics or use dbms_stats to gather statistics.

6. then start sql apply

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY ;

If you like to use exp/imp for instantiatiion follow Note 271455.1 – Synchronizing tables in a Logical Standby Database

convert this post to pdf.

If  you are in 10203 rac db and if you see crsd.bin as top consumer apply the CRS Bundle patch 3 #7117233. Till you apply the patch you can have a crontab scheduled to clean *.log in CRS_HOME/log/<hostname>/client 

convert this post to pdf.

When there is heavy load on production, logical standby will not be able to keep up the pace. This is because it has to mine the redo log convert to sql statement and apply to standby db. If there is huge gap, the apply process get struck.
Incase if you are able to know in advance about huge data load, you can set below parameters.

alter database stop logical standby apply;
execute dbms_logstdby.apply_set('MAX_SERVERS',60);
execute dbms_logstdby.apply_set('MAX_SGA',2000);
execute dbms_logstdby.apply_set('APPLY_SERVERS',52);
execute dbms_logstdby.apply_set('PREPARE_SERVERS',5);
execute dbms_logstdby.apply_set ('PRESERVE_COMMIT_ORDER','FALSE');
execute dbms_logstdby.apply_set('_MAX_LOG_LOOKBACK',1);
execute dbms_logstdby.apply_set('_EAGER_SIZE',1000);
alter database start logical standby apply;

convert this post to pdf.

Capture process in one of our client db was hanging with above message. While investigating we found out that some of the arch files need for capture is missing from os, it’s not in tape also. Only option left is to rebuild the streams. Since client is not worried about all the data being in target db, we proposed the alternate option. By which we drop the existing capture process and create a new capture process.

This is what needs to be done,

select queue_name,rule_set_name,queue_owner,rule_set_owner,negative_rule_set_owner, negative_rule_set_name from dba_capture where capture_name =’STREAM_CAPTURE’;

Note the output of above query.
Stop the capture,
exec DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => ‘STREAM_CAPTURE’,force=>true);

Stop the apply in target,
exec DBMS_APPLY_ADM.STOP_APPLY(apply_name => ‘STRMADMIN_APPLY’);

Drop the existing capture in source,
execute dbms_capture_adm.drop_capture (‘STREAM_CAPTURE’);

Recreate the Capture in source,
execute DBMS_CAPTURE_ADM.CREATE_CAPTURE(queue_name => ‘STRMADMIN.STREAMS_QUEUE’, capture_name => ‘STREAM_CAPTURE’, use_database_link=>TRUE,rule_set_name => ‘STRMADMIN.RULESET$_244′,start_scn=>null, first_scn=>null);

Some of the values feed to above query could be feeded to CREATE_CAPTURE procedure. One important thing is note is start_scn and first_scn. If you know the scn from which to start and sure that arch file is there in disk, you can feed the start_scn and first_scn value. Otherwise set it as null.
Null value will take the current scn.

If you are feeding scn and arch file is not there, then capture process will stop with “Waiting for Dictionary Redo” message.

The above approach fixed the problem and streams started to run.

convert this post to pdf.

One of the excercise we do every year in our shop is to request business to add more storage. This year we are following a different approach. We are going to stop monitoring tablespace usage in OEM and concentrate only on used and available space on each mount point. To do that, we are putting all the tablespace in autoextend on, except temp and undo.
Once we have removed tablepsace usage monitoring in OEM, we did the shrink of each datafile to remove extra allocated space. So at any point of time tablespace usage will look 100%, with the exception that autoextend is on. Currently we have done this excercise in dev environment, we got a amazing savings for around 4TB savings.
Advantage:
Effective use of storage. Each tablespace grow only when there is space pressure.
Extra ticket generated for tablespace usage alert can be avoided.
Disadvt:
OS mountpoint should be monitored very closely. If any alert, needs to be acted immediatly. As in some cases we need to purchase extra storage.

Ok, this works only for non undo and temp tablespace. We have put the undo and temp in autoextend off mode. So, we need to monitor these two tablespace. While monitoring these two tablespace, we need to consider expired,unexpired extents. We created two user managed metric alert in OEM for these two tablespace. Below is the script which we have used.

Metric Name Temp Usage
Metric Type NUMBER
SQL Query Output Two Columns
SQL Query select * from (select z.tablespace_name||’ Used %’,z.Perc_used from (select x.tablespace_name,trunc((v_usage_gb/v_total_gb)*100,2) Perc_Used from (select tablespace_name,trunc(sum(bytes/1024/1024/1024),2) v_total_gb from dba_temp_files group by tablespace_name) x, (select tablespace,trunc(sum(blocks*8192/1024/1024/1024),2) v_usage_gb from v$sort_usage group by tablespace) y where x.tablespace_name=y.tablespace order by Perc_used DESC ) z where rownum = 1 union all select ’0′,0 from dual ) x1 where rownum = 1

Metric Name Undo Usage
Metric Type NUMBER
SQL Query Output Two Columns
SQL Query select y.tablespace_name,trunc((y.total_gb/z.grand_total_gb)*100,2) Perc_Used from (select x.tablespace_name, trunc(x.total_bytes/1024/1024/1024,2) Total_Gb from (select tablespace_name, (nvl(sum(bytes),0)) total_bytes from dba_undo_extents where status in (‘ACTIVE’,'UNEXPIRED’) group by tablespace_name order by 2 desc) x where rownum=1) y, (select tablespace_name,sum(bytes/1024/1024/1024) grand_total_gb from dba_data_files group by tablespace_name) z where y.tablespace_name = z.tablespace_name

convert this post to pdf.

This is one feature which i use very often.  Even after killing the session using alter system command, the oracle process still hangs in the os.  In such scenario without getting into oracle account we can kill the os process. Please note you need to sysdba privs.

sqlplus "/as sysdba"
oradebug setospid spid@v$process;
oradebug short_stack;
oradebug event immediate crash;
oradebug short_stack;
This will kill the associated os process. This is undocumented so all the warning holds good.