Recover from loss of the password file

--set REMOTE_LOGIN_PASSWORDFILE=NONE
--then re-create it and reset REMOTE_LOGIN_PASSWORDFILE to EXECLUSIVE again

Recovering from a Read-Only Tablespace

--If no change sicne the time made it read-only, just restore the datafiles will do
--However, if changes was made after you backup the tablespace. All the redo logs are quired since the moment are required . (SCN basis)

Mview log is not purged

Today during analyze the growing trend of one tablespace and noticed that the biggest segment is a materialized view log. What a big size ! 7Gb , while the master table is only 21Mb.

After do some research, suspected this could be an orphan mview log from previous upgrade.

Key tables to lookup for investigation are:
master_owner.mlog$_master_table (snaptiem$$)
sys.slog$ (snaptime and snapid)
dba_regestered_mviews(mview_id)
dba_rgroups, dba_refresh_children( for those using refresh group)

Q:When mivew log is purged?
A: Only when all regiested mviews are refreshed, i.e check that all sys.slog$.snaptime are equal or before master_owner.mlog$_master_table.snaptime$$


From this practice, I also learned that if the refresh method is complete, no mview log is required.

How to check locality of partition table's index

select LOCALITY from dba_part_indexes where index_name='';

LOCALITY indicates whether this partitioned index is LOCAL or GLOBAL.

Error Message: "Total lru latches configured: 1; latches required for buffer pools = 2"

This occurred in one 8i database during failover to another sever with less CPUs.

Solution

1.This is due to CPU changed from 4 to 2 after permanently switch to another server, which affects db_block_lru_latches (default is CPU/2).
2.comment out init parameter buffer_pool_keep and alter affected table's buffer pool to DEFAULT.

Recovering from loss of an Index Tablespace

Detail steps:
1. alter tablespace INDEX offline immediate
--IMMEDIATE keyword instructs Oracle to take the tablespace offline with attempting to flush any dirty buffers from the tablespace to disk.

2. Prepare the DDL from dictionary for recreation, before drop the damaged tablespace
2.1 Either by check the dicionary : dba_ind_columns
2.2 Or dbms_metadata.get_ddl
2.3 Or use database pump
a) expdp hr/hr directory=dp_dir dumpfile=ind.dmp include=index;
b) then export to a text file
impdp hr/hr directory=dp_dir dumpfile=ind.dmp sqlfile=ind.sql
3. Drop the tablespace and delete its datafiles
--drop tablespace including contents and datafiles;
4. Create a new tablespace
5. Re-create the indexes. To speed up creation, using nologging and parallelism.

recovering from loss of an Online Redo Log File

Method 1:drop the damaged member and add a replacement member
1. drop the member
-- alter database drop logfile member '';
2. delete the file from disk physically
3. add a new member
-- alter database add logfile member '' to group ;


Method 2: clear the group (simple and devoid of any chance of error)
--This works only when no disk problem and the group has been archived
e.g
alter database clear logfile group 1;