Dba Doc

Verify Flashback Size, Used, Reclaimable and Flashback Occupants

2021-01-01T11:13:00.009-08:00

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;

2020-12-31T06:27:00.001-08:00

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

2020-12-28T14:25:00.001-08:00

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

2010-03-19T08:54:00.001-07:00

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

Open the database with RESETLOGS

SQL> alter database open resetlogs;

Database altered.

Verify DBID updated

SQL> select dbid from v$database;

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

AIX

2010-03-03T08:08:00.001-08:00

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

ORA-38760: This database instance fai...

2009-09-28T02:17:00.001-07:00

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

2009-09-07T01:31:00.001-07:00

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

2009-08-17T06:55:00.001-07:00

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 )

2009-08-17T06:40:00.001-07:00

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

2009-08-06T06:06:00.001-07:00

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

2009-06-22T03:48:00.001-07:00

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

2009-06-22T03:39:00.001-07:00

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

2009-06-22T02:20:00.001-07:00

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)

2009-06-15T10:21:00.001-07:00

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

2009-06-15T07:12:00.000-07:00

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

2009-06-15T07:11:00.000-07:00

SQL Server Scripts

Table Space Usage

AIX Commands

2009-06-15T07:07:00.000-07:00

AIX Commands

oslevel

AIX OS Level

2009-06-15T07:06:00.001-07:00

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]

2009-05-25T04:29:00.001-07:00

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...

2009-05-25T04:15:00.001-07:00

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...

2009-04-11T17:04:00.001-07:00

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;

DOS COMMANDS

2008-07-03T04:43:00.000-07:00

DOS COMMANDS

AT : Schedule tasks to be performed at a specified time and date.

Delete a Service in Windows using SC ( Service Control)

AT - Schedule tasks to be performed at a specified time and date

2008-07-03T04:35:00.000-07:00

AT

Enables users to schedule tasks to be performed at a specified time and date.

C:Documents and Settings>at /?

The AT command schedules commands and programs to run on a computer at
a specified time and date. The Schedule service must be running to use
the AT command. ( Note : Use "net start" to verifty the service is running)

AT [\computername] [ [id] [/DELETE] | /DELETE [/YES]]
AT [\computername] time [/INTERACTIVE]
[ /EVERY:date[,...] | /NEXT:date[,...]] "command"

\computername Specifies a remote computer. Commands are scheduled on the
local computer if this parameter is omitted.
id Is an identification number assigned to a scheduled
command.
/delete Cancels a scheduled command. If id is omitted, all the
scheduled commands on the computer are canceled.
/yes Used with cancel all jobs command when no further
confirmation is desired.
time Specifies the time when command is to run.
/interactive Allows the job to interact with the desktop of the user
who is logged on at the time the job runs.
/every:date[,...] Runs the command on each specified day(s) of the week or
month. If date is omitted, the current day of the month
is assumed.
/next:date[,...] Runs the specified command on the next occurrence of the
day (for example, next Thursday). If date is omitted, the
current day of the month is assumed.
"command" Is the Windows NT command, or batch program to be run.

Examples

At 22:00 every night run a batch script called rman_backup.bat

C:Documents and SettingsAdministrator>AT 22:00 /every:M,T,W,Th,F,S,Su cmd /c "L:Backuprman_backup.bat"

Added a new job with job ID = 1

Running at on its own gives a detailed list of all jobs available

C:Documents and SettingsAdministrator>AT
Status ID Day Time Command Line
-------------------------------------------------------------------------------
1 Each M T W Th F S Su 16:55 PM cmd /c "C:Documents and SettingsAdministratorhello.BAT"

To delete a scheduled job

C:Documents and SettingsAdministrator>AT 1 /DELETE

To check the status of a job

C:Documents and SettingsAdministrator>AT 1

Task ID: 1
Status: OK
Schedule: Today
Time of day: 16:55 PM
Interactive: No
Command: C:Documents and SettingsAdministratorhello.BAT

AIX Procedures

2008-07-01T08:16:00.000-07:00

AIX Procedures

NTP Setup AIX Client Side

AIX lsconf

NTP Setup AIX Client Side

2008-07-01T08:09:00.000-07:00

NTP Setup AIX Client Side

Within any production environment it is advisable to implement an ntp server to
synchronize the date and time of all you server to one central clock. To configure
this on and AIX ( Client Side) , perform the following:

The client configuration for ntp is defined in the configuration file /etc/ntp.conf
and should resemble the following , where server point to the ip address of the ntp
server

root#> more xntp.conf

server 10.101.50.6
driftfile /etc/ntp.drift
tracefile /etc/ntp.trace

# To start the xntpd daemon, enter:

startsrc -s xntpd

# To stop the xntpd daemon, enter:

stopsrc -s xntpd

to configure xntpd to start up at boot time

Edit the /etc/rc.tcpip file, and near the bottom uncomment the
'start xntpd' line. This will ensure ntp client restart on reboot.

e.g.

root#> stopsrc -s xntpd
0513-044 The /usr/sbin/xntpd Subsystem was requested to stop.
root#> ps -ef | grep xntpd
root 499964 581830 0 12:33:58 pts/1 0:00 grep xntpd
root#> startsrc -s xntpd
0513-059 The xntpd Subsystem has been started. Subsystem PID is 499966.
root#> ps -ef | grep xntpd
root 499966 209062 0 12:34:10 - 0:00 /usr/sbin/xntpd
root 532568 581830 0 12:34:13 pts/1 0:00 grep xntpd
root#> date
Sun May 4 12:34:16 BST 200

Note : The /etc/rc.tcpip startup script which defines the TCP/IP daemons is called at boot time by
/etc/inittab

i.e.

root#> grep tcpip inittab

rcptcpip:23456789:wait:/etc/rc.tcpip > /dev/console 2>&1 # Start TCP/IP daemons