Find the Archivelog names by using the SCN

During database recovery, you may have a SCN number and need to know the archivelog names. Here is the SQL for the answer:

column first_change# format 9,999,999,999
column next_change# format 9,999,999,999

alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where 35297312527 between first_change# and next_change#;

If you see 'D' in the STATUS column, the archive log has been deleted from the disk. You may need to restore it from the tape.

SEQUENCE# number usually shows up on the archivelog name. You can use RMAN command to restore them:

restore archivelog from logseq=45164 until logseq=45179;

Or, use commands to check the backup status:

list backup of archivelog all completed after 'SYSDATE - 21';
list backup of archivelog from logseq=45164 until logseq=45179;

RMAN "delete noprompt obsolete;"

The "delete obsolete" or "delete noprompt obsolete" will delete the archivelog files past the retention from disk and will also delete any backups on disk or tape.

allocate channel for maintenance type 'SBT_TAPE';
delete noprompt obsolete;
release channel;

will list two parts

. The first part lists the obsolete backups and copies (including archive logs).
. The 2nd part confirms what have been really deleted on archive logs and backup piece, with statement "Deleted xx objects".

When flash recovery area (FRA) is used, Oracle will automatically remove archive logs when space pressure is seen in the FRA. "delete obsolete" command will not remove obsolete archivelog files from the FRA, and so it only reports the obsolete archive logs without "Deleted xx objects" under them.

After a osboleted backpiece has been deleted by the command, the LIST command will not be able to see it any more. For example, I see follwoings on backup piece 635938 in the log:

Backup Set 635930 12-SEP-08
Backup Piece 635938 12-SEP-08 vcjqcgn8_1_1

deleted backup piece
backup piece handle=vcjqcgn8_1_1 recid=2021 stamp=665207528

Now, LIST on them returns errors:

RMAN> list backupset 635930;
RMAN-00571: ==================================
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571:===================================
RMAN-03002: failure of list command at 09/12/2008 15:32:05
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20215: backup set not found
RMAN-06159: error while looking up backup set

RMAN> list backuppiece 635938;
RMAN-00571: ==================================
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ==================================
RMAN-03002: failure of list command at 09/12/2008 15:32:34
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20260: backup piece not found in the recovery catalog
RMAN-06092: error while looking up backup piece

You can use "report obsolete" to find the obsolete backups. If you want to find what will become obsolete in next backup run, use "report obsolete redundancy 6" if the retention redundancy is 7.

Command line to compile invalid objects

You can have a script to compile invalid 11i database objects, and put the script in cron.

1. Script

#!/bin/ksh
APPSPASS="pwd1"
SYSTEMPASS="pwd2"

. /path/to/EBSSITE_servername.env

echo ${APPSPASS}
sqlplus -s APPS @${AD_TOP}/admin/sql/adutlrcmp.pls APPLSYS ${APPSPASS} APPS ${APPSPASS} ${SYSTEMPASS} 8 0 NONE FALSE

2. Log will look like:

Enter password:
Arguments are:
AOL_schema = APPLSYS, AOL_password = *****,Schema_to_compile = APPS, Schema_to_compile_pw = *****,SYSTEM_password = *****, Total_workers = 8, Logical_worker_num = 0
Object_type_to_not_compile = NONE
Use_stored_dependencies = FALSE

Connected.
Running utl_recomp.recomp_parallel(8), if it exists
OWNER NUM_INVALID
------------------ -----------
BOLINF 3
APPS 1
Elapsed: 00:00:00.68
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
STATUS_MESSAGE
------------------------------------------------------------------------------
Running UTL_RECOMP.RECOMP_PARALLEL...
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.65
STATUS_MESSAGE
------------------------------------------------------------------------------
Successfully ran UTL_RECOMP.RECOMP_PARALLEL
no rows selected
Elapsed: 00:00:00.28
Done running utl_recomp.recomp_parallel
Commit complete.
Elapsed: 00:00:00.00

3. Cron line (like below)
0 * * * * /path/scriptname.sh > /path/to_log.log 2>&1 &

UPDATE: ${AD_TOP}/admin/sql/adutlrcmp.pls does not exist in R12.2. Use adadmin to compile APPS schema.

Find the target on a host for Grid Control

One database disappeared from Grid Control. I executed "agentca -d" on the target server to re-run the agent configuration assistant and look for new targets. It did bring the database back to Grid Control.

/u01/app/oracle/product/agent10g/bin ORA$ ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.1.0
OMS Version : 10.2.0.1.0
Protocol Version : 10.2.0.0.0
Agent Home : /u01/app/oracle/product/agent10g
Agent binaries : /u01/app/oracle/product/agent10g
Agent Process ID : 585838
Parent Process ID : 569446
Agent URL : http://dssqa:3872/emd/main/
Repository URL : http://dssgrid:4889/em/upload/
Started at : 2008-08-15 19:44:26
Started by user : oracle
Last Reload : 2008-08-16 01:26:09
Last successful upload : 2008-08-16 13:42:01
Total Megabytes of XML files uploaded so far : 94.10
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 25.83%
Last successful heartbeat to OMS : 2008-08-16 13:41:51
---------------------------------------------------------------
Agent is Running and Ready

/u01/app/oracle/product/agent10g/bin ORA$ ./agentca -d
Stopping the agent using /u01/app/oracle/product/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
Running agentca using /u01/app/oracle/product/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/u01/app/oracle/product/agent10g
ACTION=Configure MODE=Perform
RESPONSE_FILE=/u01/app/oracle/product/agent10g/response_file
RERUN=TRUE
INV_PTR_LOC=/etc/oraInst.loc

Perform - mode is starting for action: Configure
Perform - mode finished for action: Configure

You can see the log file: /u01/app/oracle/product/agent10g/cfgtoollogs/oui/confi gActions2008-08-16_01-43-06-PM.log

/u01/app/oracle/product/agent10g/bin ORA$ ls -al agentca
-rwxr-xr-x 1 oracle oinstall 657 Oct 19 2007 agentca

"alter profile ..." could shut down entire 11i applications

Co-worker misunderstood the concept of database password management when tried to set the length of 11i Application password expiration, and ran two lines as sysdba:

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 1;
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 2;

That action expired APPS password, and even SYSTEM password:
SQL> conn system/xxx@tns
ERROR:ORA-28002: the password will expire within 0 days
Connected.

and so the entire Oracle EBS instance went down.

To fix the issue, tried following steps:

1. Run the below sql's as sysdba to change the profile back.

1) ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME unlimited;
2) ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;

2. Then try to connect as apps user in SQL*Plus. If it is requesting for a password change, cancel it.

3. Use normal approach of changing the apps password.
1) Stop MT services
2) Change APPS password using FNDCPASS
3) Change the password in wdbsvr.app and cgicmd.dat
4) Execute cmclean.sql connected to the database as APPS
5) Start MT services.

But, FNDCPASS keep getting error:
$ FNDCPASS apps/xxxxxx 0 Y system/xxxxxx SYSTEM APPLSYS xxxxxxx
APP-FND-01564: ORACLE error 28001 in AFPCOA
Cause: AFPCOA failed due to ORA-28001: the password has expired

Even, after APPS account is unlocked by SQL*Plus,
$ FNDCPASS apps/*** 0 Y system/*** system APPLSYS ***
fails with these error messages in logfile:
FNDCPASS was not able to decrypt password for ANONYMOUS during applsys password change.
FNDCPASS was not able to decrypt password for AUTOINSTALL during applsys password change.
FNDCPASS was not able to decrypt password for CONCURRENT MANAGER during applsys password change.
... 500 lines generated.

Metalink note 459601.1 suggests to change password manually for 500 users, which doesn't sound like a good solution.

Finally, we have to refresh the instance by using another instance.