Pages

Popular Posts

Friday, April 25, 2008

How to bring Oracle Database Back from the Dead

How to bring Oracle Database Back from the Dead or how to open an Oracle database with missing (or deleted, or lost) archive logs.

Here is the "typical" scenario you need to open/recover oracle database but you do not have the archive log files - that pretty much renders your Oracle database useless except you do know that that the data is still there and there MUST be a way to open the database without the archive logs and “reset” logfiles…

Here is how to open Oracle Database without archive logs:

1. Shutdown your database.
2. Set the following parameter in your init.ora files (you might need to create pfile from spfile)

_allow_resetlogs_corruption=true
3. Mount your database and issues “alter database open resetlogs”
4. The database will attempt to open but will crash.
5. Edit your init.ora file and and perform the following:

Remove _allow_resetlogs_corruption=true entry
Add undo_management=manual
6. Mount your database
7. Recover database using “recover database command”
8. Open your database – “miracle” your database will open. “alter database open” do not user “alter database open resetlogs”

BUT (It’s a big BUT) the database is not ready yet

Your UNDO tablespace is still in manual mode and the original UNDO tablespace is still corrupted.

Here is how to fix that:

1. Create new undo tablespace i.e UNDOTBS2
2. Set UNDOTBS2 as default undo tablespace
3. Remove undo_management=manual from init.ora
4. Bounce your database


Follow below step to drop your “old” tablespace:

1 – Identify the bad segment -

select
segment_name,
status
from
dba_rollback_segs
where
tablespace_name='undotbs_corrupt'
and
status = ‘NEEDS RECOVERY’;


SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU22$ NEEDS RECOVERY

2. Bounce the instance with the hidden parameter “_offline_rollback_segments”, specifying the bad segment name:

_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU22$


3. Bounce database, nuke the corrupt segment and tablespace:
SQL> drop rollback segment "_SYSSMU22$";
Rollback segment dropped.

SQL > drop tablespace undotbs including contents and datafiles;
Tablespace dropped.
Now you are done …

Thursday, April 24, 2008

Use FNDCPASS utility to change Oracle Apps passwords

FNDCPASS is an EBS tool to change passwords of database schema's within the Oracle EBS. For example, you can change the APPS password using FNDCPASS, but also any other schema in the EBS database. FNDCPASS can also be used to change the password of an application user (like sysadmin).

To change the APPS password use...
FNDCPASS apps/*** 0 Y system/***** SYSTEM APPLSYS [new_password]

To change any other schema...
FNDCPASS apps/**** 0 Y system/***** ORACLE GL [new_password]

To change the password of a application user
FNDCPASS apps/*** 0 Y system/****** USER SYSADMIN [new_password]

When changing the password of all schemas in the database, you have a lot off FNDCPASS to do...there are almost 200 schemas in the EBS database that need to be changed. Default the password is schema name, so gl/gl and ap/ap...

When installing patch 4676589 (11i.ATG_PF.H Rollup 4) a new feature is added to FNDCPASS. Now you can use the ALLORACLE functionality to change all the schema passwords in one FNDCPASS.

Here is what I did to use the new FNDCPASS feature...

1. install AD: Patch 11i.AD.I.4 (patch 4712852)
2. install patch 5452096
Purging timing information for prior sessions.
sqlplus -s APPS/***** @/appl/prodappl/ad/11.5.0/admin/sql/adtpurge.sql 10 1000
Spawned Process 17504
Done purging timing information for prior sessions.
AutoPatch is complete.
AutoPatch may have written informational messages to the file/appl/prodappl/admin/prod/log/u5452096.lgi
Errors and warnings are listed in the log file/appl/prodappl/admin/prod/log/u5452096.log
and in other log files in the same directory.
3. run the Technology Stack Validation Utility
[oracle@ebs2 bin]$ ./txkprepatchcheck.pl -script=ValidateRollup -outfile=$APPLTMP/txkValidateRollup.html -appspass=apps
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
***STDOUT /appl/prodcomn/rgf/prod_ebs2/TXK/txkValidateRollup_Mon_Jan_8_stdout.log
Reportfile /appl/prodcomn/temp/txkValidateRollup.html generated successfully.
4. run autoconfig
5. apply patch 4676589 (11i.ATG_PF.H Rollup 4, Applications Technology Family)
6. After the install
7. apply patch 3865683 (AD: Release 11.5.10 Products Name Patch)
8. apply patch 4583125 (Oracle XML Parser for Java) see note 271148.1

Verify if the upgrade has been successful..
cd $JAVA_TOP
[oracle@ebs2 java]$ unzip -l appsborg.zip grep 9.0.4
0 04-19-03 02:10 .xdkjava_version_9.0.4.0.0_production
[oracle@ebs2 java]$
if there is an xdkjava_version_9.0.4.0.0_production entry, then XML parser is installed.
9. run autoconfig
10. disable maintenance mode (via adadmin)
Change Maintenance Mode
----------------------------------------
Maintenance Mode is currently: [Enabled].
Maintenance mode should normally be enabled when patchingOracle Applications and disabled when users are logged onto the system. See the Oracle Applications MaintenanceUtilities manual for more information about maintenance mode.
Please select an option:
1. Enable Maintenance Mode
2. Disable Maintenance Mode
3. Return to Main Menu

Enter your choice [3] : 2
sqlplus -s &un_apps/***** @/appl/prodappl/ad/11.5.0/patch/115/sql/adsetmmd.sql DISABLE
Successfully disabled Maintenance Mode.

Now try the new FNDCPASS function..

[oracle@ebs2 prod_ebs2]$ FNDCPASS apps/apps 0 Y system/manager ALLORACLE WELCOME
Log filename : L2726002.log
Report filename : O2726002.out
[oracle@ebs2 prod_ebs2]$
[oracle@ebs2 prod_ebs2]$ sqlplus apps/apps
SQL*Plus: Release 8.0.6.0.0 - Production on Mon Jan 15 08:50:39 2007
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn gl/welcome
Connected.
SQL> conn ap/welcome
Connected.
SQL>

Tuesday, April 01, 2008

Oracle Roles and Users Permissions

Users Mapped to Roles and System Privs:

select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('%&enter_username%')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
System privileges to roles and

System Privs to Users and Roles
select
lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
(
/* THE PRIVILEGES */
select
null p,
name c
from
system_privilege_map
where
name like upper('%&enter_privliege%')
/* THE ROLES TO ROLES RELATIONS */
union
select
granted_role p,
grantee c
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
privilege p,
grantee c
from
dba_sys_privs
)
start with p is null
connect by p = prior c;

Object Privs:

select
case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
end
from
(
/* THE OBJECTS */
select
null p1,
null p2,
object_name obj,
owner own,
object_type typ
from
dba_objects
where
owner not in
('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN','MDSYS','ORDSYS','XDB', 'WKSYS', 'EXFSYS',
'OLAPSYS', 'DBSNMP', 'DMSYS','CTXSYS','WK_TEST', 'ORDPLUGINS', 'OUTLN')
and object_type not in ('SYNONYM', 'INDEX')
/* THE OBJECT TO PRIVILEGE RELATIONS */
union
select
table_name p1,
owner p2,
grantee,
grantee,
privilege
from
dba_tab_privs
/* THE ROLES TO ROLES/USERS RELATIONS */
union
select
granted_role p1,
granted_role p2,
grantee,
grantee,
null
from
dba_role_privs
)
start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;