Database.fi

Just a real quick one, since I need to do it right away…

EM CC 12c installation blew up and need to re-create, since here are the instructions to remove an existing OEM CC (or OEM GC) repository of a failed installation using the following command:

$/sysman/admin/emdrep/bin/RepManager -dbUser sys -dbPassword -dbRole sysdba -reposName sysman -reposPassword -action dropall [-mwHome ] [-mwOraHome ]


For Oracle Enterprise Manager Database Control (single instance) repository use this command:
emca -deconfig dbcontrol db -repos drop -cluster -SYS_PWD sys1 -SYSMAN_PWD SYS_PASSWORD


HTH,
Pasi

Hello, it’s been a while since last post. Too busy

Here is just a small example of how to perform analysis on a specific time period. Of course with AWR it would be so simple, but if you do not have access to AWR (Standard Edition Oracle for example), you just have to make do with Statspack.

Let’s say that we have a problem which occurs late at night at customer site.
It would be helpful to see what happens between 21 and 23 at night.

Of course we can just make statspack do snaps every 5 minutes and then turn it off later:
BEGIN
DBMS_SCHEDULER.CREATE_JOB ( job_name => 'STATSPACK_COLLECTION',
job_type => 'STORED_PROCEDURE',
job_action => 'statspack.snap',
repeat_interval => 'FREQ=MINUTELY; BYMINUTE=00,05,10,15,20,25,30,35,40,45,50,55',
enabled => TRUE,
comments => 'Statspack Collection'
);
END;
/

But it is way too easy to forget to turn it off and also you would allocate space from perfstat for no good reason. You’d probably end up filling up perfstat tablespace / quota.

The elegant way is to run snaps only for the time period you are interested in.
For that, we need to add a schedule to dbms_scheduler, create a program and tie them together as a job.

We need to create a schedule:
- Starts at 21:00
- Ends at 23:00
- When the schedule is active, Oracle must run statspack.snap every 5 minutes

Fine. How do we do that?

We need to create a schedule like this:

-- run every 5 minute from 21 PM to 23 PM
begin
dbms_scheduler.create_schedule(
schedule_name => 'INTERVAL_EVERY_5_MINUTES_2H',
start_date => trunc(sysdate)+21/24,
repeat_interval => 'freq=MINUTELY;interval=5',
end_date => trunc(sysdate)+23/24,
comments => 'Today from 21 to 23 PM, 5 minute interval');
end;
/


Also we need a program (Oracle scheduler term) to run statspack.snap:
-- Program to run statspack.snap
begin
dbms_scheduler.create_program
(program_name=> 'STATSPACK_SNAP',
program_type=> 'STORED_PROCEDURE',
program_action=> 'statspack.snap',
enabled=>true,
comments=>'Procedure to create statspack snap.'
);
end;
/



begin
-- Create job which will run with the new schedule and program.
dbms_scheduler.create_job
(job_name => 'STATSPACK_COLLECTION',
program_name=> 'STATSPACK_SNAP',
schedule_name=>'INTERVAL_EVERY_5_MINUTES_2H',
enabled=>true,
auto_drop=>false,
comments=>'Run statspack every 5 minutes from 21 to 23 PM today');
end;
/


That should do it.
It will run statspack collection from 21 to 23 PM with 5 minute interval.

Tomorrow you’ll be able to create spreport.sql (statspack reports) from that time period and dig deeper into the problem.

Hope this helps,
Pasi

What is really great about expdp & impdp is that you can export and import to new schemas very easily. That needed some work earlier with export and import.

Let’s say that I want to import into HR_NEW schema instead of HR.

We just add a remap_schema into the import command: remap_schema=HR:HR_NEW

This will actually even create the new user (schema)!

C:\Temp>impdp system/oracle dumpfile=hr.dmp directory=dmpdir schemas=hr logfile=
hr_imp.log remap_schema=HR:HR_NEW

Import: Release 11.2.0.2.0 – Beta on Ma Touko 16 11:42:28 2011

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

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 – Beta
Master table “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_01″: system/******** dumpfile=hr.dmp direc
tory=dmpdir schemas=hr logfile=hr_imp.log remap_schema=HR:HR_NEW
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “HR_NEW”.”COUNTRIES” 6.367 KB 25 rows
. . imported “HR_NEW”.”DEPARTMENTS” 7.007 KB 27 rows
. . imported “HR_NEW”.”EMPLOYEES” 16.80 KB 107 rows
. . imported “HR_NEW”.”JOB_HISTORY” 7.054 KB 10 rows
. . imported “HR_NEW”.”JOBS” 6.992 KB 19 rows
. . imported “HR_NEW”.”LOCATIONS” 8.273 KB 23 rows
. . imported “HR_NEW”.”REGIONS” 5.476 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at 11:42:47

The new user is still locked:
SQL> connect hr_new/hr_new
ERROR:
ORA-28000: the account is locked

Unlock:
C:\Temp>sqlplus system/oracle

SQL*Plus: Release 11.2.0.2.0 Beta on Ma Touko 16 11:44:30 2011

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

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 – Beta

SQL> alter user hr_new account unlock;

User altered.

SQL> alter user hr_new identified by hr_new;

User altered.

SQL> connect hr_new/hr_new
Connected.

SQL> select count(*) as amount, object_type
2 from user_objects
3 group by object_type;

AMOUNT OBJECT_TYPE
———- ——————-
3 SEQUENCE
2 PROCEDURE
2 TRIGGER
7 TABLE
19 INDEX
1 VIEW

6 rows selected.

Sometimes you need to also remap_tablespaces with … remap_tablespace. What a tool!

That was of course possible with exp & imp. But since it was not supported I remember setting quotas to 0 and changing default tablespaces to be able to import to different tablespace..

Hope this helps,
Pasi

Just a quick example of expdp and impdp.

First, check that you have the OS directory like /temp or c:\temp.

Since this example was made in Windows world, we use c\temp.

Log in and create an Oracle Directory:

C:\Temp>sqlplus system/oracle

Directory created.


You may have a suitable Oracle Directory already, you can check it from DBA_DIRECTORIES:

SQL> SELECT directory_name, directory_path FROM dba_directories ;

DMPDIR
c:\temp


All right, we have what we need.

Let’s say that we need to export HR without knowing HR user’s password.

From OS level:

C:\Temp>expdp system/oracle dumpfile=hr.dmp directory=dmpdir schemas=hr logfile=
hr_exp.log

16.05.2011 11:30 .
16.05.2011 11:30 ..
16.05.2011 11:32 503 808 HR.DMP
16.05.2011 11:32 2 395 hr_exp.log


Import is very simple.
expdp / impdp is somewhat easier to use than exp/imp, since most of the times you can use the same parameters while exporting and importing.

Basically I changed EXPDP => IMPDP and the name of the logfile.

Impdp command:
impdp system/oracle dumpfile=hr.dmp directory=dmpdir schemas=hr logfile=hr_imp.log

What is really great about expdp & impdp is that you can export and import to new schemas very easily. That needed some work earlier with export and import.

I wrote another entry about that, just click here to see it.

Just a quick one.

If you need to check what is the running database character set…

SELECT * FROM NLS_DATABASE_PARAMETERS;
SELECT value$ FROM sys.props$ WHERE name = ‘NLS_CHARACTERSET’ ;

HTH,
Pasi

Apparently it came out on first of April 2011.

So far I’ve installed it on two boxes, no glitches at all.

More on this later.. Need to study it more.
SQL> select status, instance_name from v$instance;

SQL>

BR,
Pasi

I was just recently asked to diagnose and re-install Oracle Internet Directory and I thought it might be a good idea to document the installation steps – so here goes.

We will use the latest versions available at the time of writing this (22th March 2011).

Installation manual:

http://download.oracle.com/docs/cd/E17904_01/install.1111/e12002/instps2001.htm

Basic steps:
1. Install Oracle
2. Install OID (and FMW control and ODSM)

Oracle installation is quite trivial, so let’s focus on the OID installation.
- Just remember to use the AL32UTF8 character set on the database!

You need to download:
- Oracle WebLogic Server 10.3.4.
- Oracle Identity Management 11.1.1.2.0 & 11.1.1.3.0

Actual installation:

1. Install WLS 10.3.4
- Run the installation .bin
* In 64 bit environments use: JAVA_HOME/bin/java -jar wls1034_generic.jar
* You need JDK 1.6 or later
- Create a new FMW home
- Register for security updates..
- Typical or Custom
- Change or accept the installation directories (df -h …)
- Summary => Next
- Installation…

2. Install OID 11.1.1.2.0
- unzip ../ofm_idm_linux_11.1.1.2.0_32_disk1_1of1.zip …
- ./runInstaller
- Install Software – DO NOT CONFIGURE!
- Use SAME MIDDLEWARE HOME as WLS above!
- Oracle Home Directory: This will be the directory name under Middleware Home
- Installation …
- Run root script: /middleware_home_directory/oracle_home_dir/oracleRoot.sh
- Save Summary.

3. Install OID 11.1.1.3.0 Patch Set
- unzip ../ofm_idm_linux_11.1.1.3.0_32_disk1_1of1.zip …
- ./runInstaller
- Install Software
- Use same homes !
- Next, next
- Root script
- Save Summary

OID Configuration with FMW Control and ODSM:

1. Configuration

/middleware_home_directory/oracle_home_dir/bin/config.sh
- Installer starts
- Create new domain
=> FMW Control is being configured to manage OID here
* User Name: WLS Admin user details
* Domain name
- Installation location
* Weblogic Server Directory
* Oracle Instance location, new “ASInstance” (Not actual Oracle Instance)
* Oracle Instance Name, new “ASInstance” (Not actual Oracle Instance)
- De-select others than Oracle Internet Directory
=> We will configure only that
- Auto configuration ports normally OK, you can select them if you want
- Create Schema
* Create ODS Database Schema
* Connect string, for example: myserver:1521:orcl
* SYS
* Sys_password
- OID Passwords
* ODS Schema password & confirm (all directory content)
* ODSSM Schema password & confirm (OID statistics and DIP schema)
- OID information
* Realm, for example: dc=us,dc=oracle,dc=com
* Admin user: orcladmin
* Admin password: …
- Install
- Save Summary
* Note: Weblogic Console ie: http://myhost.us.oracle.com:7001/console

Verify installation:
- …home/bin/opmnctl status -l
- Alive:
* OVD
* oidldapd
* oidldapd
* oidmon => LDAP port, LDAPS port
* EMAGENT
- ldapsearch -p LDAP_port -b “” -s base “objectclass=*” orcldirectoryversion
=> orcldirectoryversion=OID 11.1.1.3.0

Open Enterprise Manager Fusion Middleware Control 11g
* For example: http://myhost.us.oracle.com:7001/em
- Find oid1 in FMW Control
- Verify version number in FMW Control

Open Oracle Directory Services Manager
* For example: http://myhost.us.oracle.com:7005/odsm
- Connect to a directory
* OID – directory name
* User Name: cn=orcladmin
* password
- Verify OID version

After you’re done installing and configuring the OID itself, you can proceed to netca to configure the destination databases “tnsnames.ora”.
That will update sqlnet.ora and ldap.ora

Examples
LDAP.ORA:
DEFAULT_ADMIN_CONTEXT = “ou=ora,dc=company,dc=com”
DIRECTORY_SERVERS = (ldap1.company.com:389, ldap2.company.com:389)
DIRECTORY_SERVER_TYPE = OID

Oracle can “officially” only use OID or AD as LDAP servers.
The type can be OID or AD. The multiple servers are for redundancy; it will not try each one in turn. Then in SQLNET.ORA:

NAMES.DIRECTORY_PATH=(LDAP, TNSNAMES)

The means try LDAP first, then try TNSNAMES.ORA, then give up.

If you want to use a third-party LDAP server, Oracle has a product called Virtual Directory that will act as a proxy between them.

Note:
- OID is a standalone system component (no run time dependency on WLS)
- FMW Control and ODSM are J2EE apps running in WLS. These components are used for managing OID via GUI interface.

Links section

Complete tutorial:

http://www.oracle.com/technetwork/middleware/id-mgmt/overview/ods11g-use-cases-084303.html

http://download.oracle.com/otndocs/products/oid/11113-oid-fresh-installation/11113oidfreshinstallation_viewlet_swf.html

http://www.oracle.com/technetwork/middleware/id-mgmt/overview/oracleauthenticationservices-100184.html

Concepts:

http://onlineappsdba.com/index.php/2009/07/28/oracle-fusion-middleware-11g-concepts-for-apps-dbas/

Download:

http://www.oracle.com/technetwork/middleware/downloads/fmw-11-download-092893.html

http://www.oracle.com/technetwork/middleware/weblogic/downloads/index.html

http://download.oracle.com/otndocs/products/oid/11113-oid-fresh-installation/11113oidfreshinstallation_viewlet_swf.html

http://www.oracle.com/technetwork/testcontent/index-092280.html

LDAP configuration:

http://serverfault.com/questions/192359/oracle-replacing-tnsnames-ora-with-ldap-lookup

http://laurentschneider.com/wordpress/2006/10/migration-of-tnsnamesora-to-ldap-sun-java-system-directory-server.html

Tnsnames.ora information from OpenLDAP:

http://oracle-cookies.blogspot.com/2007/01/get-tnsnamesora-from-openldap.html

Orafaq:

http://www.orafaq.com/wiki/Oracle_Internet_Directory

OID basics:

http://onlineappsdba.com/index.php/2006/12/04/oracle-internet-directory-oid/

HTML DB did not impress me much (huh?) – but I have to say I am pretty impressed with Oracle Apex.

It is one of the quickest ways to make db-aware web solutions. That combined with the fact that is uses Oracle is just great.

Well, anyway – I was asked to upgrade the APEX to the latest version (4.02 while writing this).
Here is a quick guide:
1. Download apex installation package
2. Unzip it somewhere like c:\oraclexe\apex or /var/oraclexe/apex
3. Open shell or cmd and CD there, “sqlplus / as sysdba” from that directory
4. @apexins SYSAUX SYSAUX TEMP /i/
5. ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
5.1. ALTER USER APEX_PUBLIC_USER IDENTIFIED BY new_password

If your images are not displayed correctly, you may need to run also:
@apxldimg c:\oraclexe\

In order to change the admin account password, run: @apxxepwd.sql

BR,
Pasi

When I found out about virtual columns I just had to try it.
Just a simple example for our familiar employees table.

First I made a copy of the employees:
Create table duunarit as select * from employees;


SQL> desc duunarit
Name Null? Type
----------------------------------------- -------- ---------------------

EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)


I would like to add a salgrade column to the table with some simple reasoning based on salary:
when salary is less than 3000, print ‘Poor you’
when salary is 3000 – 5000, print ‘Lucky you’
when it is above 5000, print ‘You bastard’

So here is my column definition:

alter table duunarit add
(salgrade varchar2(20)
generated always as
(
case
when salary < 3000 then 'Poor you'
when salary < 5000 then 'Lucky you'
else 'You bastard'
end
)virtual
);

Table altered.


Let's try it!

SQL> l
1* select last_name, salary, salgrade from duunarit

LAST_NAME SALARY SALGRADE
------------------------- ---------- --------------------
Sciarra 7700 You bastard
Urman 7800 You bastard
Popp 6900 You bastard
Raphaely 11000 You bastard
Khoo 3100 Lucky you
Baida 2900 Poor you
Tobias 2800 Poor you
Himuro 2600 Poor you
Colmenares 2500 Poor you
Weiss 8000 You bastard
Fripp 8200 You bastard


This looks great functionality!

I was asked to provide instructions on how to install and configure Statspack into Oracle so here goes.

The statspack scripts can be found from under this directory structure:
$Oracle_home/rdbms/admin


The installation needs to be run as SYS user.

Basic installation is done simply by running this script:
@?/rdbms/admin/spcreate.sql


This installs the Statspack engine & tables to database.

You can (if you want) automate the snap creation with this script:
@?/rdbms/admin/spauto.sql

This script takes snaps every hour starting from the next hour.

You can modify this script to make another kind of scheduling.
For example if you want to take snaps every half an hour, you need to modify:
"sysdate+1/24" => "sysdate+1/48"

dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

=>
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'HH'), 'trunc(SYSDATE+1/48,''HH'')', TRUE, :instno);


If you need to ie. change the currently running job interval, you need to find out the job number first.
Connect as dba user to Oracle and run this query:

select job, what, interval from dba_jobs;

JOB
----------
WHAT
-----------------------------
INTERVAL
-----------------------------
21
statspack.snap;
trunc(SYSDATE+1/24,'HH')

So the statspack.snap is run every hour and job number is 21. Fine.
Let’s say that we need to run it every half an hour.
We need to change the interval to: 1/48.

You can update the job interval like this. You can set is as the job owner with this syntax:
exec dbms_job.interval(21,'TRUNC(SYSDATE+1/48,''HH'')');

PL/SQL procedure successfully completed.


Or as another user with DBA privileges:
SQL> show user
USER is "SYS"
SQL> exec dbms_ijob.interval(21,'TRUNC(SYSDATE+1/48,''HH'')');

PL/SQL procedure successfully completed.


Please note: there are two single quotes ‘ around HH, not one double-quote “.

Also you could remove the job with:
exec dbms_job.remove(21);

and re-create it with:

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'HH'), 'trunc(SYSDATE+1/48,''HH'')', TRUE, :instno);
commit;
end;
/


You should try that the job runs now.

Run as the job owner:
SQL> exec dbms_job.run(21);

PL/SQL procedure successfully completed.

Run as a DBA user:
SQL> exec dbms_ijob.run(21);

PL/SQL procedure successfully completed.

You can check when the next scheduled job run is:

SQL> select job, what, interval, to_char(next_date, 'DD.MM.YYYY HH24:MI:SS') from dba_jobs where job=21;

JOB
----------
WHAT
-------------------------------------
INTERVAL
-------------------------------------
TO_CHAR(NEXT_DATE,'
-------------------
21
statspack.snap;
TRUNC(SYSDATE+1/48,'HH')
29.03.2010 11:00:00

Statspack was introduced in Oracle 9i and it is still an usable tool in current Oracle version of 11.2.
AWR is superior technology compared to Statspack, but for many purposes Statspack still holds its place.