Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for August, 2010

Listener Config in Target.xml file in Grid Control

Posted by Hendry chinnapparaj on August 23, 2010

Listener Config in Target.xml file in Grid Control

oracle@drou009n1 emd>pwd

/u01/oracle/agent10g/sysman/emd

oracle@drou009n1 emd>cat targets.xml

<Targets AGENT_TOKEN=”82267db0b7e27a46ce0e716dba8255e185355d32″>

<Target TYPE=”oracle_emd”/>

<Target TYPE=”host”/>

<Target TYPE=”oracle_listener”>

<Property VALUE=”10.777.61.69″/>

<Property VALUE=”1621″/>

<Property VALUE=”/u01/oracle/product/10.2/db_1/network/admin”/>

<Property VALUE=”mpsoa1_fc_dg”/>

<Property VALUE=”/u01/oracle/product/10.2/db_1″/>

</Target>

<Target TYPE=”oracle_listener”>

<Property VALUE=”10.777.61.70″/>

<Property VALUE=”1521″/>

<Property VALUE=”/u01/oracle/product/10.2/db_1/network/admin”/>

<Property VALUE=”mpsoa1_public”/>

<Property VALUE=”/u01/oracle/product/10.2/db_1″/>

</Target>

<Target TYPE=”oracle_database”>

<Property VALUE=”/u01/oracle/product/10.2/db_1″/>

<Property VALUE=”sys”/>

<Property VALUE=”10.777.61.69″/>

<Property VALUE=”1621″/>

<Property VALUE=”mpsoa1″/>

<Property VALUE=”mpsoa1_fc”/>

<Property VALUE=”0dd23bd9a4b3bedbc61e26cf4dfd4f6e” ENCRYPTED=”TRUE”/>

<Property VALUE=”SYSDBA”/>

</Target>

</Targets>

Cd /u01/oracle/agent10g/bin

./emctl reload agent

./emctl status agent

Posted in EM10g Grid Control | Leave a Comment »

ORA-28001: the password has expired Error-Code:28001 in Apex URL

Posted by Hendry chinnapparaj on August 18, 2010

Problem

————

You try to access the APEX 3.2 Application URL, fails with

Database Log In Failed

TNS is unable to connect to destination. Invalid TNS address supplied or destination is not listening. This error can also occur because of underlying network transport problems.

Verify that the TNS name in the connectstring entry of the DAD for this URL is valid and the database listener is running.

ORA-28001: the password has expired Error-Code:28001 Error TimeStamp:Wed, 18 Aug 2010 00:48:12 GMT

Diagnosis

————

And you check the database and confirm the account APEX_PUBLIC_USER is expired

Solution

———-

Go to the Server where Apex apps http server is running and get the password

———

devu025>pwd

/u01/oracle/product/apex/Apache/modplsql/conf

View dads.conf

PlsqlPathAliasProcedure portal.wwpth_api_alias.process_download

</Location>

<Location /pls/apex>

SetHandler pls_handler

Order deny,allow

Allow from all

AllowOverride None

PlsqlDatabaseUsername APEX_PUBLIC_USER

PlsqlDatabasePassword hello

PlsqlDatabaseConnectString devu024:1521:ofsad1.greatwest.com ServiceNameFormat

PlsqlDefaultPage apex

PlsqlDocumentTablename wwv_flow_file_objects$

PlsqlDocumentPath docs

PlsqlDocumentProcedure wwv_flow_file_mgr.process_download

PlsqlAuthenticationMode Basic

———-

Here the APEX_PUBLIC_USER password is “admin”

Now connect to the apex oracle database and alter the user and change the password as it is

SQL> alter user APEX_PUBLIC_USER identified by hello;

User altered.

Now you can access the URL again

cheers

Posted in Oracle Application Express 3.2 | Leave a Comment »

Restore Tablespace

Posted by Hendry chinnapparaj on August 3, 2010

RESTORE TESTING FOR READ ONLY TABLESPACE
——————————————————————-

oracle@uatu009 backup>./oracle_main.ksh fixtest DDBOBK01
ORACLE_HOME = [/u01/oracle] ? /u01/oracle/product/10.2/db_1
RMAN> 2> 3> 4> 5> 6> 7>

Shutdown immediate

oracle@uatu009 fixtest>mv CB004_01.DBF CB004_01.DBF.ORI
oracle@uatu009 fixtest>mv CB004_02.DBF CB004_02.DBF.ORI

QL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  2085392 bytes
Variable Size             176164336 bytes
Database Buffers          352321536 bytes
Redo Buffers                6299648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 29 – see DBWR trace file
ORA-01110: data file 29: ‘/u02/oradata/fixtest/CB004_02.DBF’

SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      fixtest

Recovery Manager: Release 10.2.0.4.0 – Production on Tue Aug 3 12:24:36 2010

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

connected to target database: fixtest (DBID=258344671, not open)
connected to recovery catalog database

RMAN> restore tablespace CB004;

Starting restore at 03-08-2010 12:26:17
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=539 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=538 devtype=DISK

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00029 to /u02/oradata/fixtest/CB004_02.DBF
channel ORA_SBT_TAPE_1: reading from backup piece 1clkdlo6_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=1clkdlo6_1_1 tag=TAG20100803T111327
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:08
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00030 to /u02/oradata/fixtest/CB004_01.DBF
channel ORA_SBT_TAPE_1: reading from backup piece 1glkdlrc_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=1glkdlrc_1_1 tag=TAG20100803T111327
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:08
Finished restore at 03-08-2010 12:26:49

-rw-r—–    1 oracle   dba         8396800 Aug  3 12:26 CB004_02.DBF
-rw-r—–    1 oracle   dba         8396800 Aug  3 12:26 CB004_01.DBF

SQL> show parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      fixtest
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 29 needs media recovery
ORA-01110: data file 29: ‘/u02/oradata/fixtest/CB004_02.DBF’

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
——— ———-
fixtest   READ WRITE

RESTORE TESTING FOR READ WRITE TABLESPACE

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

oracle@uatu009 fixtest>pwd
/u02/oradata/fixtest
oracle@uatu009 fixtest>ls -lrt users01.dbf
-rw-r—–    1 oracle   dba         5251072 Aug  3 12:32 users01.dbf

oracle@uatu009 fixtest>mv users01.dbf users01.dbf.ori

SQL> startup
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  2085392 bytes
Variable Size             176164336 bytes
Database Buffers          352321536 bytes
Redo Buffers                6299648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u02/oradata/fixtest/users01.dbf’

Recovery Manager: Release 10.2.0.4.0 – Production on Tue Aug 3 12:37:16 2010

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

connected to target database: fixtest (DBID=258344671, not open)
connected to recovery catalog database

RMAN> restore tablespace users;

Starting restore at 03-08-2010 12:37:32
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=539 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=538 devtype=DISK

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u02/oradata/fixtest/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0tlkdlbh_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=0tlkdlbh_1_1 tag=TAG20100803T111327
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:07
Finished restore at 03-08-2010 12:37:51

RMAN> recover tablespace users;

Starting recover at 03-08-2010 12:38:29
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 03-08-2010 12:38:32

SQL> alter database open;

Database altered.

Posted in RMAN10G | Leave a Comment »

change dbid for fixtest database

Posted by Hendry chinnapparaj on August 3, 2010

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      fixtest

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

/u01/oracle/.kshrc[19]: hostname:  not found

ORACLE instance started.

Total System Global Area  536870912 bytes

Fixed Size                  2085392 bytes

Variable Size             171970032 bytes

Database Buffers          356515840 bytes

Redo Buffers                6299648 bytes

Database mounted.

SQL> !

oracle@uatu009 oracle>nid target=sys/fixtest@fixtest

DBNEWID: Release 10.2.0.4.0 – Production on Tue Aug 3 09:18:19 2010

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

Connected to database fixtest (DBID=803403107)

Connected to server version 10.2.0

Control Files in database:

/u02/oradata/fixtest/control01.ctl

/u02/oradata/fixtest/control02.ctl

/u02/oradata/fixtest/control03.ctl

Change database ID of database fixtest? (Y/[N]) => Y

Datafile /u02/oradata/fixtest/bw_audit01.dbf – dbid changed

Datafile /u02/oradata/fixtest/soe.dbf – dbid changed

Datafile /u02/oradata/fixtest/soeindex.dbf – dbid changed

Datafile /u02/oradata/fixtest/temp01.dbf – dbid changed

Control File /u02/oradata/fixtest/control01.ctl – dbid changed

Control File /u02/oradata/fixtest/control02.ctl – dbid changed

Control File /u02/oradata/fixtest/control03.ctl – dbid changed

Instance shut down

Database ID for database fixtest changed to 258344671.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database ID.

DBNEWID – Completed succesfully.

oracle@uatu009 oracle>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 3 09:20:52 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount

/u01/oracle/.kshrc[19]: hostname:  not found

ORACLE instance started.

Total System Global Area  536870912 bytes

Fixed Size                  2085392 bytes

Variable Size             171970032 bytes

Database Buffers          356515840 bytes

Redo Buffers                6299648 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select dbid from v$database;

DBID

———-

258344671

Posted in Admin | Leave a Comment »