Verify Flashback Size, Used, Reclaimable and Flashback Occupants

 Verify Flashback Size, Used, Reclaimable and Flashback Occupants

-- Size, Used, Reclaimable 

SELECT

  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 

  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 

  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,

  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED

FROM

  V$RECOVERY_FILE_DEST A,

  V$FLASH_RECOVERY_AREA_USAGE B

GROUP BY

  SPACE_LIMIT, 

  SPACE_USED , 

  SPACE_RECLAIMABLE ;

-- Flashback Occupants

set linesize 120

set pagesize 100

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

 How to restore RAC Thread archive logs using commvault libobk library 

rman 

connect target sys/******@db_name

connect catalog rmancatalogowner/********@rcat12c

Connect to RMAN 

run

{

  allocate channel ch1 DEVICE TYPE SBT

  PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144";

  restore archivelog from sequence  37807 thread 2 until sequence 37864 thread 2;

  release channel ch1;

}

 To Restore Thread 2 using commvault SBT_LIBRARY

 

 /opt/commvault/Base/libobk.so

Purge Unified Audit Trail

 

As the sys or system user 

select  count(*) from unified_audit_trail

/

BEGIN

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

use_last_arch_timestamp  =>  FALSE);

END;

/

select  count(*) from unified_audit_trail

/

DBNEWID

Procedure to Change Database DBID

Start up the database in mount mode

SQL> startup mount

ORACLE instance started.

Total System Global Area 4731174912 bytes
Fixed Size                  2061264 bytes
Variable Size             687868976 bytes
Database Buffers         4026531840 bytes
Redo Buffers               14712832 bytes

Database mounted.

SQL> quit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Invoke DBNEWID to generate a new dbid

D:\oracle>nid target=sys/xxxxxxr@db
DBNEWID: Release 10.2.0.3.0 - Production on Fri Mar 19 14:45:18 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to database DB (DBID=2131079061)
Connected to server version 10.2.0
Control Files in database:

    K:\ DB\ORA_REDO\CONTROL01.CTL
    H:\DB\SYS\ORA_SYS\CONTROL02.CTL
    J:\DB\ORA_REDO\CONTROL03.CTL

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

Proceeding with operation

Changing database ID from 2991078061 to 618795265
    Control File K:\DB\ORA_REDO\CONTROL01.CTL - modified
    Control File H:\DB\SYS\ORA_SYS\CONTROL02.CTL - modified
    Control File J:\DB\ORA_REDO\CONTROL03.CTL - modified
    Datafile ……..DBF - dbid changed
    Datafile ……..DBF - dbid changed
    Datafile ……..DBF - dbid changed
    Control File K:\DB\ORA_REDO\CONTROL01.CTL - dbid changed
    Control File H:\DB\SYS\ORA_SYS\CONTROL02.CTL - dbid changed
    Control File J:\DB\ORA_REDO\CONTROL03.CTL - dbid changed
    Instance shut down

Database ID for database DB changed to 618795265.
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.

Start up the database in mount mode

SQL> startup mount

ORACLE instance started.
Total System Global Area 4731174912 bytes
Fixed Size                  2061264 bytes
Variable Size             687868976 bytes
Database Buffers         4026531840 bytes
Redo Buffers               14712832 bytes
Database mounted.

Open the database with RESETLOGS

SQL> alter database open resetlogs;

Database altered.

Verify DBID updated

SQL> select dbid from v$database;

      DBID
----------
618795265

AIX

AIX lsconf

Usage of lsconf in AIX return

Serial Number for AIX
Number of Processors AIX
System Model AIX
Memory AIX
Number of internal disks AIX

# lsconf | egrep "(Serial Number|Number Of Processors|System Model|Memory Size|disk)"    

ORA-38760: This database instance fai...

ORA-38760: This database instance failed to turn on flashback database

Example

Database unable to startup due to the following:

SQL> startup

ORACLE instance started.

Total System Global Area 6744440832 bytes
Fixed Size                  2065120 bytes
Variable Size            1124076832 bytes
Database Buffers         5603590144 bytes
Redo Buffers               14708736 bytes
Database mounted.

ORA-38760: This database instance failed to turn on flashback database

Solution

The above can be resolved by turning off flash back

Turn off flashback.

SQL> startup mount;
SQL> alter database flashback off;
SQL> alter database open;

To re-enable the flashback again.

SQL> Shutdown immediate;
SQL> Startup mount;
SQL> alter database flashback on;
SQL> alter database open;

 
In the event that the database does not open i.e.

SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

Check if you have a restore point ,

SQL> select name from v$restore_point;

Drop the restore point

SQL> drop restore point <restore_point>;

Restore point dropped.

SQL> alter database open;

Database altered.

ORA-01031: RMAN Duplicate Fails for ASM file system

ORA-01031: RMAN Duplicate Fails for ASM file system

Example

channel ORA_AUX_DISK_1: reading from backup piece K:\BACKUP\DAILY\27KN1ASE_1_1
ORA-19870: error reading backup piece K:\BACKUP\DAILY\27KN1ASE_1_1
ORA-19504: failed to create file "+DATA/ora_index/lineind_7.dbf"
ORA-17502: ksfdcre:3 Failed to create file +DATA/ora_index/lineind_7.dbf
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15055: Message 15055 not found; No message file for product=RDBMS, facility=
ORA-01031: insufficient privilege

Detail

 

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

RMAN duplicate fails with ORA-01031 on 10G Windows Platform with ASM

Solution

sqlnet.ora parameter

SQLNET.AUTHENTICATION_SERVICES = (NTS)  needs to be unset

Also check that OS user you are logged in as is a member of the ora_dba group:

    net localgroup ora_dba oracle /add