Fix broken objects

From: http://forums.oracle.com/forums/thread.jspa?threadID=1319667&tstart=0

As sys user run:

@?/rdbms/admin/utlrp.sql

-- A check
col OBJECT_NAME for a30
select owner, object_name, object_type from dba_objects where status = 'INVALID';


UK Post

http://www.doogal.co.uk/UKPostcodes.php?Search=E1

mysql and phpmyadmin and char issue

Below is the trick that fixes the charter issue when importing data via phpmyadmin.

SET NAMES 'utf8';


RMAN Disaster recovery, on different host

Disaster recovery (or clone db) using RMAN on different host.

- Basic version: This will restore .dbf, .ctl & .log to original location and assumes the rman backup in are in same location.
- Advance version: Marked as optional steps, restore all db files to different location and rman backup are in different location.

Summary of RMAN Disaster recovery

1. Export the SID
2. Create the folders [?dump, oradata, arch]
3. Startup nomount
4. Restore spfile
5. (optional) Change the .ctl + archive location, using pfile
6. Restore control file
7. Force mount the database
8. (optional) Catalog the backup file
9. (optional) Rman 'set newname' for restore file
10. restore database;
11. (optional) rename datafiles
12. recover database;
13. alter database open resetlogs;
14. Take a new backup of database


1. Export the SID

$ export ORACLE_SID=PRDB1

2. Create the folders [?dump, oradata, arch]

mkdir /u01/oracle/admin/PRDB1
mkdir /u01/oracle/admin/PRDB1/adump
mkdir /u01/oracle/admin/PRDB1/bdump
mkdir /u01/oracle/admin/PRDB1/cdump
mkdir /u01/oracle/admin/PRDB1/udump

mkdir /u01/oracle/oradata/PRDB1
mkdir /u11/oracle/oradata/PRDB1
mkdir /u21/oracle/oradata/PRDB1
mkdir /u31/oracle/arch_PRDB1


3. Startup nomount

$ rman target /

RMAN> startup nomount;

-- Will give you error, and say can't open initPRDB1.ora


4. Restore spfile

RMAN> restore spfile from '/mnt/restore/PRDB1_cf_c-1795524320-20100511-00';


5. (optional) Change the .ctl + archive location, using pfile

$ cd $ORACLE_HOME/dbs
$ strings spfilePRDB1.ora > initPRDB1.ora
$ mv spfilePRDB1.ora xxspfilePRDB1.oraxx
RMAN> shutdown immediate;
RMAN> startup nomount;

-- Modify the *.control_files and *.log_arc parameters (e.g. all go to /u01)


6. Restore control file

RMAN> restore controlfile from '/mnt/restore/PRDB1_cf_c-1795524320-20100511-00';


7. Force mount the database

RMAN> startup force mount;


8. (optional) Catalog the backup file

RMAN> catalog start with '/mnt/restore';


9. (optional) RMAN 'set newname' for restore file, restore to different location, needs to be done before the restore command.

set line 150
set pagesize 100
col NEW_FILE_NAME for a100
select 'set newname for datafile ' || FILE_ID || ' to ' || ''''|| FILE_NAME || ''';'
as NEW_FILE_NAME from dba_data_files;

select 'set newname for tempfile ' || FILE_ID || ' to ' || ''''|| FILE_NAME || ''';'
as NEW_FILE_NAME from dba_temp_files;


run {
set newname for datafile 1 to '/u31/oracle/oradata/PRDB1/system01.dbf';
set newname for datafile 2 to '/u31/oracle/oradata/PRDB1/undotbs01.dbf';
...
set newname for datafile 9 to '/u31/oracle/oradata/PRDB1/PRDB1_BLOB_DATA_02.dbf';
set newname for datafile 10 to '/u31/oracle/oradata/PRDB1
...
set newname for datafile 19 to '/u31/oracle/oradata/PRDB1/PRDB1_BLOB_DATA_11.dbf';

set newname for tempfile 1 to '/u31/oracle/oradata/PRDB1/temp01.dbf';

restore database;
}


10. Restore database

RMAN> restore database;


11. (optional) rename datafiles, Use below to restore to different location, needs to be done before the recover command.

set line 150
set pagesize 100
col RENAME_SQL for 150
-- data files
select 'alter database rename file '''|| NAME || ''' to ' || ''''|| NAME || ''';'
as RENAME_SQL from v$datafile;

-- redo logs
select 'alter database rename file '''|| NAME || ''' to ' || ''''|| NAME || ''';'
as RENAME_SQL from v$tempfile;

-- temp files
select 'alter database rename file ''' || lf.MEMBER || ''' to ''' || lf.MEMBER || ''';'
as RENAME_SQL from v$logfile lf, v$log l where l.group# = lf.group#;


-- Run above output via SqlPLus session
...
...
alter database rename file '/u02/oracle/oradata/PRDB1/redo02.log'
to '/u01/oracle/oradata/PRDB1/redo02.log';
alter database rename file '/u31/oracle/oradata/PRDB1/redo03.log'
to '/u01/oracle/oradata/PRDB1/redo03.log';


12. Recover database

RMAN> recover database;

-- Will give error since can't find 'unable to find archive log'


13. Open database, reset log

RMAN> alter database open resetlogs;


14. Take a new backup of database

RMAN> backup database;




Reference
=========
Below sites came useful in doing this (Backup and Recovery Reference, chap RESET DATABASE)
http://users.telenet.be/oraguy.be/make_a_clone_of_your_database_1.htm
http://www.oracle-base.com/articles/9i/DuplicateDatabaseUsingRMAN9i.php
http://kamranagayev.wordpress.com/2010/03/29/rman-video-tutorial-series-performing-disaster-recovery-with-rman/
http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtroub004.htm

10g optimizer

Another day the 10.2.0.1.0 face issues with 10g optimizer

Issue:
Same Query is not slow

Fix:
Put '/*+ RULE */ hints, even thought RULE is no longer supported, this has trully fixed.

Also note same db in office, one verrion 10.2.0.1

Update in parallel mode

Change overview and monitoring

alter table mytable parallel 5;

-- As system:
select pid, status, server_name from v$px_process;


My request for change:

Hi,

Since we are upgrading to v3 for many of our customers, so far Customer_A and Customer_2 and Customer_Big in progress.
One thing clearly stood out was that the script "rel_2_0_4_field_answers_upgrade.sql" took around 1hr for Customer_B, 6hr for Customer_A and a massive 12hr for Customer_Big.

I would like to propose a change to this script (hopefully it will speed up the script without changing any biz-logic), due my lack of knowledge of the application and data model, I am not 100% sure if my change impacts any business logic.

Can someone please take this proposal forward and see if we can go ahead with this, extensive testing required.

Since yesterday, I have done two test runs with Customer_Big (slim db from last Friday, note 7.5 million rows on field_answers), both showed similar results.

Old script took: 4 hour (or 3hr 55min)
New script took: 30 minutes

Please allocate a developer to look into this suggestion, ideally the one who wrote the original script.
For your information this script was our biggest blocker for the Customer_Big migration.

Proposed New Script (using parallel execution):

ALTER TABLE "FIELD_ANSWERS" ADD "ID" VARCHAR2(20);
ALTER TABLE "FIELD_ANSWERS" ADD "TYPE" VARCHAR2(10);

alter table field_answers parallel 5;

UPDATE field_answers set
id='FN' || lpad(keygen('FN'),8,'0'),
type=decode(fk_app_profile,null,decode(fk_hire_profile,null,decode(fk_requestid,null,decode(fk_profile,null,'SAVED_PS','P'),'R'),'PSH'),'PSA')
WHERE id is null;

alter table field_answers parallel 1;


Current Live Script:

ALTER TABLE "FIELD_ANSWERS" ADD "ID" VARCHAR2(20);
ALTER TABLE "FIELD_ANSWERS" ADD "TYPE" VARCHAR2(10);

DECLARE
CURSOR c1 IS SELECT rowid, decode(fk_app_profile,null,decode(fk_hire_profile,null,decode(fk_requestid,null,decode(fk_profile,null,'SAVED_PS','P'),'R'),'PSH'),'PSA') as fatype FROM field_answers where id is null;
faid varchar2(10);

BEGIN
FOR i IN c1 LOOP
BEGIN
faid:='FN' || lpad(keygen('FN'),8,'0');
EXECUTE IMMEDIATE 'UPDATE field_answers set id=''' || faid || ''', type=''' || i.fatype || ''' WHERE rowid = ''' || i.rowid || '''';
END;
END LOOP;

END;
/


WebLogic app to deploy

Run below command to create the war file (nidb-app.war):

jar cvf nidb-app.war test_app.jsp test_db.jsp test_driver.jsp includes/UTF-8.inc WEB-INF/web.xml

WEB-INF/web.xml
< ?xml version="1.0" encoding="UTF-8"?>
< !DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
<>
<>nidb_test
<>niDB Test App

<>
<>test_app.jsp
< /welcome-file-list>

< /web-app>



includes/UTF-8.inc
<% response.setHeader("Content-Type", "text/html; charset=UTF-8"); response.setContentType("text/html; charset=UTF-8"); request.setCharacterEncoding("UTF-8"); %>