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

Script to recursively delete Oracle Log Files under Windows

Script to recursively delete Oracle Log Files under Windows

Set the number of days you want to keep data for with the following variable

dtmDate = Date - 20

Specify top level dir for your bdump , adump , cdump etc

Set Thefolder = objFSO.GetFolder("S:\ADMIN\DB_SID")

It will do a recursive search of all folders e.g. bdump , adump , cdump , udump
under the top level folder

Specify file types you wish to delete here

If UCase(objFSO.GetExtensionName (AFile)) = "TRC" Then   

            Killfile (Afile)
        ElseIf UCase(objFSO.GetExtensionName (AFile)) = "TMP" Then
            Killfile (Afile)
        ElseIf UCase(objFSO.GetExtensionName (AFile)) = "ERR" Then

            Killfile (Afile)
        ElseIf Ucase(objFSO.GetExtensionName (AFile)) = "DMP" Then
            Killfile (Afile)
        ElseIf Ucase(objFSO.GetExtensionName (AFile)) = "TRW" Then

            Killfile (Afile)
        End If

Here is the actual script , cut and paste the script with the relevant change into a text file
and rename it to a file name with a .vbs extension e.g. delete_oracle_log.vbs
You can schedule the script to run periodically using windows scheduler

'==========================================================================

'==========================================================================

'Current Date minus 20 days
Dim sMsg
dtmDate = Date - 20

strTargetDate = ConvDate(dtmDate)

FolderCount = 0
DeletedCount = 0

'strComputer = "."
'Set objWMIService = GetObject("winmgmts:\\"& strComputer & "\root\cimv2")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objWSH = CreateObject("WScript.Shell")

'folder dialog Box
Const WINDOW_HANDLE = 0
Const OPTIONS = 0

Set Thefolder = objFSO.GetFolder("J:\ADMIN\DB_SID")
StartTime = Now
WorkWithSubFolders Thefolder
EndTime = Now
sMsg = "Deleted: " & DeletedCount & " File(s) from " & FolderCount & " Directory(s)!!" & _
VbCrLf&VbCrLf & "Script Started: " & vbTab&StartTime & _
VbCrLf & "Script Ended: " & vbTab&EndTime
objWSH.LogEvent 0, sMsg

'Credit for this sub goes to S. Hussain Akbar
Sub WorkWithSubFolders (AFolder)
Dim MoreFolders, TempFolder
FolderCount = FolderCount + 1

CheckExt AFolder
Set Morefolders = AFolder.Subfolders
For Each TempFolder In MoreFolders
WorkWithSubFolders (TempFolder)
Next

End Sub

Sub CheckExt (AFolder)
Dim Afile, TheFiles

On Error Resume Next
Set TheFiles = AFolder.Files
For Each Afile In TheFiles
If UCase(objFSO.GetExtensionName (AFile)) = "TRC" Then
Killfile (Afile)
ElseIf UCase(objFSO.GetExtensionName (AFile)) = "TMP" Then
Killfile (Afile)
ElseIf UCase(objFSO.GetExtensionName (AFile)) = "ERR" Then
Killfile (Afile)
ElseIf Ucase(objFSO.GetExtensionName (AFile)) = "DMP" Then
Killfile (Afile)
ElseIf Ucase(objFSO.GetExtensionName (AFile)) = "TRW" Then
Killfile (Afile)
End If
Next
If (DeletedCount > 0) And (DeletedCount Mod 10 = 0) Then
sMsg = "Have worked so far with " & FolderCount & " folders." & VbCrLf&VbCrLf
sMsg = sMsg & "And have deleted " & DeletedCount & " files."
objWSH.LogEvent 1, sMsg
End If
End Sub

Sub Killfile (AFile)
On Error Resume Next
strDate = ConvDate(Afile.DatelastModified)
If strDate < strTargetDate Then
objFSO.DeleteFile (AFile)
DeletedCount = DeletedCount + 1
End If
End Sub

Function ConvDate (sDate) 'Converts MM/DD/YYYY HH:MM:SS to string YYYYMMDD
strModifyDay = day(sDate)
If len(strModifyDay) < 2 Then
strModifyDay = "0" & strModifyDay
End If
strModifyMonth = Month(sDate)
If len(strModifyMonth) < 2 Then
strModifyMonth = "0" & strModifyMonth
End If
strModifyYear = Year(sDate)
ConvDate = strModifyYear & strModifyMonth & strModifyDay

End Function

ORA-31633: unable to create master table ( expdp )

ORA-31633: unable to create master table ( expdp )

Example

K:\Partion\expdmp>expdp USER_PROD/USER_PROD@PROD_DB parfile=ExportParam.txt
 
Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 12 August, 2009 14:09:57
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCHEMA_PROD.TABLE1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-00955: name is already used by an existing object

Details

When performing and export using data pump you receive the error as above due to the
fact that the exp was previously executed and not completed due to the fact the it was
canceled or stopped for some reason. As a result the new expdp job has the same name
as the old expdp job.

Solution

Verify that the table associated with the expdp job exists:

SQL> select table_name from dba_tables where table_name like '%TABLE1%';

 

TABLE_NAME                                                                     

------------------------------                                                 

TABLE1 

Identified that data pump job exists and the status of the job is NOT RUNNING

SQL>SELECT owner_name, job_name, operation, job_mode, state, attached_sessions

FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;

Drop the table                                                          

SQL> drop table SCHEMA_PROD.TABLE1;

 
Table dropped.
 

Re Run the export

Error 12514

Error 12514 attaching to destination LOG_ARCHIVE_DEST_X standby host

Example


Alert log shows the following when trying ship archive logs to Physical Standby Site:

    Error 12514 received logging on to the standby
    Fri Jul 31 09:33:44 2009
    Errors in file i:\admin\semdb\bdump\databasedb2_lns3_154572.trc:
    ORA-12514: Message 12514 not found; No message file for product=RDBMS, facility=
    LGWR: Error 12514 creating archivelog file 'databasedr'
    LNS: Failed to archive log 22 thread 2 sequence 47481 (12514)
    Fri Jul 31 09:33:46 2009
    Error 12514 received logging on to the standby

Details

Trace file from primary database server is unable to connect to physical standby database:

FAL[server]: Selected inactive destination; resetting temporarily
Redo shipping client performing standby login
OCIServerAttach failed -1
.. Detailed OCI error val is 12514 and errmsg is 'ORA-12514: Message 12514 not found; No message file for product=RDBMS, facility=ORA
*** 2009-07-31 09:33:49.490 61080 kcrr.c
Error 12514 received logging on to the standby
Error 12514 connecting to destination LOG_ARCHIVE_DEST_4 standby host 'databasedr'
Error 12514 attaching to destination LOG_ARCHIVE_DEST_4 standby host 'databasedr'
ORA-12514: Message 12514 not found; No message file for product=RDBMS, facility=ORA
*** 2009-07-31 09:33:49.505 59323 kcrr.c
kcrrfail: dest:4 err:12514 force:0 blast:1
kcrrwkx: unknown error:12514

Solution

On standby server check what services are registered by the listener

    > lsnrctl status

Check the SERVICE_NAME in the CONNECT data for the tnsnames.ora file on standby database server.

How to identify Duplicates in a table using SQL PLUS

How to identify Duplicates in a table using SQL PLUS 

SELECT COUNT(*), COLUMN_A, COLUMN_B
FROM TABLE_B
GROUP BY COLUMN_A, COLUMN_B
HAVING COUNT(*)>1;

ORA-01618

ORA-01618: redo thread 2 is not enabled - cannot mount

Example

SQL> startup

ORACLE instance started.
 
Total System Global Area 4731174912 bytes
Fixed Size                  2061264 bytes
Variable Size             838863920 bytes
Database Buffers         3875536896 bytes
Redo Buffers               14712832 bytes
ORA-01618: redo thread 2 is not enabled - cannot mount

 

Details:

The above error will be displayed during the startup of a node in a rac cluster 

in the event that a redo thread is not enabled. You need to enable the thread 

instance prior to adding a redo log thread which is created using the 

            ALTER DATABASE ADD LOGFILE THREAD [NUMBER]

The respective instance threads can be identified from the init parameter files

below e.g.:

RACNODE1.THREAD=1  # RACNODE1 instance uses Thread 1
RACNODE2.THREAD=2  # RACNODE2 instance uses Thread 2

Solution:

Enabled the Thread on the active node as follows:

 

SQL> alter database enable public thread 2;
 
SQL> select thread#, enabled from v$thread;
 
   THREAD# ENABLED
---------- --------
         1 PUBLIC
         2 PUBLIC

Restart the node which displays the ORA error

ORA-29707

ORA-29707: inconsistent value string for initialization parameter string with other instances

Example:

SQL> startup

ORA-29707: inconsistent value 2 for initialization parameter cluster_database_in

stances with other instances

SQL>

SQL>

Details:

In the event that you startup a node on a RAC cluster and you receive an ORA-29707 as above 

check "cluster_database" parameter on all nodes in the rac cluster.

SQL> show parameter cluster
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string

Note: There is no need to set cluster_database_instances

Solution:

Set cluster_database parameter to true on all nodes of the cluster

SQL> alter system set cluster_database=true;

Delete a Service in Windows using SC (Service Control)

Delete a Service in Windows using SC ( Service Control)

C:\Documents and Settings\user>sc query| FIND "TEST"

SERVICE_NAME: OracleServiceTEST
DISPLAY_NAME: OracleServiceTEST

C:\Documents and Settings\user>sc stop OracleServiceTEST

SERVICE_NAME: OracleServiceTEST
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 3  STOP_PENDING
                                (STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x1
        WAIT_HINT          : 0x15f90

C:\Documents and Settings\user>sc stop OracleServiceTEST

[SC] ControlService FAILED 1062:

The service has not been started.

C:\Documents and Settings\user>sc DELETE OracleServiceTEST

[SC] DeleteService SUCCESS

C:\Documents and Settings\user>sc query| FIND "TEST"


C:\Documents and Settings\user>



Ref:
DESCRIPTION:
        SC is a command line program used for communicating with the
        NT Service Controller and services.
USAGE:
        sc <server> [command] [service name] <option1> <option2>...

        The option <server> has the form "\\ServerName"
        Further help on commands can be obtained by typing: "sc [command]"
        Commands:
          query-----------Queries the status for a service, or
                          enumerates the status for types of services.
          queryex---------Queries the extended status for a service, or
                          enumerates the status for types of services.
          start-----------Starts a service.
          pause-----------Sends a PAUSE control request to a service.
          interrogate-----Sends an INTERROGATE control request to a service.
          continue--------Sends a CONTINUE control request to a service.
          stop------------Sends a STOP request to a service.
          config----------Changes the configuration of a service (persistant).
          description-----Changes the description of a service.
          failure---------Changes the actions taken by a service upon failure.
          sidtype---------Changes the service SID type of a service.
          qc--------------Queries the configuration information for a service.
          qdescription----Queries the description for a service.
          qfailure--------Queries the actions taken by a service upon failure.
          qsidtype--------Queries the service SID type of a service.
          delete----------Deletes a service (from the registry).
          create----------Creates a service. (adds it to the registry).
          control---------Sends a control to a service.
          sdshow----------Displays a service's security descriptor.
          sdset-----------Sets a service's security descriptor.
          showsid---------Displays the service SID string corresponding to an ar
bitrary name.
          GetDisplayName--Gets the DisplayName for a service.
          GetKeyName------Gets the ServiceKeyName for a service.
          EnumDepend------Enumerates Service Dependencies.

        The following commands don't require a service name:
        sc <server> <command> <option>
          boot------------(ok | bad) Indicates whether the last boot should
                          be saved as the last-known-good boot configuration
          Lock------------Locks the Service Database
          QueryLock-------Queries the LockStatus for the SCManager Database
EXAMPLE:
        sc start MyService

Would you like to see help for the QUERY and QUERYEX commands? [ y | n ]: y
QUERY and QUERYEX OPTIONS :
        If the query command is followed by a service name, the status
        for that service is returned.  Further options do not apply in
        this case.  If the query command is followed by nothing or one of
        the options listed below, the services are enumerated.
    type=    Type of services to enumerate (driver, service, all)
             (default = service)
    state=   State of services to enumerate (inactive, all)
             (default = active)
    bufsize= The size (in bytes) of the enumeration buffer
             (default = 4096)
    ri=      The resume index number at which to begin the enumeration
             (default = 0)
    group=   Service group to enumerate
             (default = all groups)
SYNTAX EXAMPLES
sc query                - Enumerates status for active services & drivers
sc query messenger      - Displays status for the messenger service
sc queryex messenger    - Displays extended status for the messenger service
sc query type= driver   - Enumerates only active drivers
sc query type= service  - Enumerates only Win32 services
sc query state= all     - Enumerates all services & drivers
sc query bufsize= 50    - Enumerates with a 50 byte buffer.
sc query ri= 14         - Enumerates with resume index = 14
sc queryex group= ""    - Enumerates active services not in a group
sc query type= service type= interact - Enumerates all interactive services
sc query type= driver group= NDIS     - Enumerates all NDIS drivers

SQL Server Table Space Usage

Table Space Usage

Calculates space usage for all tables in a particular Database , Support 2000 and 2005:

BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
, schemaname VARCHAR(500) collate database_default
);
DECLARE @temp_table TABLE (
tablename sysname
, row_count INT
, reserved VARCHAR(50) collate database_default
, data VARCHAR(50) collate database_default
, index_size VARCHAR(50) collate database_default
, unused VARCHAR(50) collate database_default
);

INSERT INTO @tab1
SELECT t1.name
, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');

-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END

FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT t1.*
, t2.schemaname
FROM @temp_table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,tablename;
END try
BEGIN catch
SELECT -100 AS l1
, ERROR_NUMBER() AS tablename
, ERROR_SEVERITY() AS row_count
, ERROR_STATE() AS reserved
, ERROR_MESSAGE() AS data
, 1 AS index_size, 1 AS unused, 1 AS schemaname
END catch

SQL Server Scripts

SQL Server Scripts

Table Space Usage

AIX Commands

AIX Commands

oslevel

AIX OS Level

OS Level

To display AIX version

# oslevel

To display AIX version and patch level. 

# oslevel -r

To display list of known service packs on a system

# oslevel -q -s

To display man page on oslevel 

# man oslevel

ALTER SESSION SET CURRENT_SCHEMA = [ USER NAME]

ALTER SESSION SET CURRENT_SCHEMA = [ USER NAME]


The ALTER SESSION SET CURRENT_SCHEMA = [USER NAME] allows you to switch your current session to that schema user.
Usually you will log in as sys or system to execute the above. In short this gives you the option to act as a schema user
without knowing the schema user password. The settings persists for the duration of the
session or until you issue another
ALTER SESSION SET CURRENT_SCHEMA statement.

e.g.

ALTER SESSION SET CURRENT_SCHEMA = OE

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIAT...

ORA-06512: at “SYS.DBMS_LOGSTBY”, LINE 577

Example


SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE( SCHEMA_NAME => 'OE', TABLE_NAME => 'MF_RESOURCES_SN', dblink => '[DBLINK]');
BEGIN DBMS_LOGSTDBY.INSTANTIATE_TABLE( SCHEMA_NAME => 'OE', TABLE_NAME => 'ORDERS', dblink => 'OE_LINK'); END;

*

ERROR at line 1:

ORA-31631: privileges are required
ORA-06512: at "SYS.DBMS_LOGSTDBY", line 577
ORA-06512: at line 1

Detail

The above error can arise when you are attempting to refresh an object in your logical standby database using
EXECUTE
DBMS_LOGSTDBY.INSTANTIATE_TABLE

In the event that you receive the above error please grant the appropriate permissions affected schema
before re-syncing the object
 
SQL> GRANT LOGSTDBY_ADMINISTRATOR TO [USER];

Once complete revoke LOGSTDBY_ADMINISTRATOR for that schema.

 
Solution

In the event an object within the logical standby database server gets out of sync with the primary database server
you can re-sync the object within the schema as follows. This can be the case with Materialized Views which are not supported
as part of the logical apply for logical standby database servers for Oracle 10g
 
   1. Stop Logical Apply

SQL > ALTER DATABASE STOP LOGICAL STANDBY APPLY;

   2. Create a DB Link from Reporting DB server to Primary DB server with respect to the affected schema.

CREATE [PUBLIC] DATABASE LINK <link_name>
CONNECT TO <user_name>
IDENTIFIED BY <password>
USING '<service_name>';

e.g.

CREATE DATABASE LINK OE_LINK
CONNECT TO OE
IDENTIFIED BY XXXX
USING 'ORDERDB';

   3. Grant “DBA” and “LOGSTDBY_ADMINISTRATOR” role to the affected schema

e.g.

SQL> GRANT DBA TO OE;
SQL> GRANT LOGSTDBY_ADMINISTRATOR TO OE;

   4. Re-sync the object using DBMS_LOGSTDBY.INSTANTIATE_TABLE as follows

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE( SCHEMA_NAME => '<schema>', TABLE_NAME => '<table>', dblink => '<dblink>');


e.g.
 

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE( SCHEMA_NAME => 'OE', TABLE_NAME => 'ORDERS', dblink => 'OE_LINK');

 
PL/SQL procedure successfully completed.

 
Once complete revoke LOGSTDBY_ADMINISTRATOR for that schema.

ORA-12520: TNS:listener could not fin...

ORA-12520: TNS:listener could not find available handler for requested
                       type of server

Example:

/home/oracle/tns_admin$ sqlplus system/<password>@DATABASE

    SQL*Plus: Release 10.2.0.2.0 - Production

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

    Enter password:
    ERROR:
    ORA-12520: TNS:listener could not find available handler for
    requested type of server


Details:

None of the known and available service handlers for requested
type of server (dedicated or shared) are appropriate for the
client connection.

Solution:


Run "lsnrctl services" to ensure that the instance(s) have
registered with the listener and that the appropriate handlers are
accepting connections.

Configure the instances local_listener parameter to point at your listener:

    alter system set local_listener=
      '(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))' scope=spfile;

If you have an entry in your tnsnames.ora that points to the listener,
use the listener name instead:

    alter system set local_listener='<listener_name>' scope=spfile;