DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, April 30, 2018

I have faced ORA-01118: Cannot add any more data file limit exceeded.

When the Database is created the db_file parameter in the initialization file is set to a limit. You can shutdown the database and reset these up to the MAX_DATAFILE as specified in database creation. The default for MAXDATAFILES is 30. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.The simplest way to recreate the controlfile to change the ‘hard’ value MAXDATAFILES

I have followed below steps:

ALTER DATABASE BHACKUP CONTROLFILE TO TRACE;

Then go to UDUMP destination pick it up and modify the value of MAXDATAFILES

SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
sql> @(name of edited file);


Steps for tuning Redo logs and Checkpoints (Contention, Waits, Number/Duration of Checkpoints)

1). Redolog Buffer Contention
-----------------------------
SELECT SUBSTR(name,1,20) "Name",gets,misses,immediate_gets,immediate_misses
  FROM v$latch
 WHERE name in ('redo allocation', 'redo copy');

Name                       GETS     MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------------------- ---------- ---------- -------------- ----------------
redo allocation     277'446'780  2'534'627              0                0
redo copy                33'818     27'694    357'613'861          150'511

MISSES/GETS (must be < 1%)

Redo allocation: (2'534'627 / 277'446'780) * 100 = 0.91 %
Redo Copy:       (27'694 / 33'818) * 100 = 81.8 %

IMMEDIATE_MISSES/(IMMEDIATE_GETS+IMMEDIATE_MISSES) (must be < 1%)

Redo Copy: 150'511/(150'511+357'613'861) = 0.04 %

2). Waits on Redo Log Buffer
----------------------------
SELECT name,value
 FROM v$sysstat
 WHERE name = 'redo log space requests';

The value of 'redo log space requests' reflects the number
of times a user process waits for space in the redo log buffer.
Optimal is if the value is near 0 (Oracle Manual says this ...)

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo log space requests                                               22641

4). Number of Checkpoints per hour
----------------------------------
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
spool show_logswitches.lst

ttitle left 'Redolog File Status from V$LOG' skip 2

select group#, sequence#,
       Members, archived, status, first_time
  from v$log;

ttitle left 'Number of Logswitches per Hour' skip 2

select to_char(first_time,'YYYY.MM.DD') day,
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'00',1,0)),'99') "00",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'01',1,0)),'99') "01",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'02',1,0)),'99') "02",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'03',1,0)),'99') "03",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'04',1,0)),'99') "04",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'05',1,0)),'99') "05",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'06',1,0)),'99') "06",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'07',1,0)),'99') "07",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'08',1,0)),'99') "08",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'09',1,0)),'99') "09",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'10',1,0)),'99') "10",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'11',1,0)),'99') "11",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'12',1,0)),'99') "12",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'13',1,0)),'99') "13",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'14',1,0)),'99') "14",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'15',1,0)),'99') "15",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'16',1,0)),'99') "16",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'17',1,0)),'99') "17",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'18',1,0)),'99') "18",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'19',1,0)),'99') "19",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'20',1,0)),'99') "20",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'21',1,0)),'99') "21",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'22',1,0)),'99') "22",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'23',1,0)),'99') "23"
  from v$log_history
 group by to_char(first_time,'YYYY.MM.DD')
/
spool off;


DAY   00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23
----- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
07/07   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   2   0   0   0   0   0   0   0   0
07/08   0   0   0   0   0   0   0   0   0   0   0   5   0   4   1   0   1   0   0   0   0   0   0   0
07/12   0   0   0   0   0   0   0   0   0   0   1   1   0   1   1   0   0   0   0   0   0   0   0   0
07/13   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
07/14   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   1   1   1   0   0
07/15   1   0   0   0   0   0   0   0   0   0   0   0   2   1   0   0   1   2   2   0   0   0   0   0
07/16   0   0  10  10  15  11   5   0   0   0   0   0   2   5   5   4   5   7   6   6   7   4   4   4
07/17   2   2   1   3   4   6   9  10  11  11  12  12  11  11  12  11  11  12  12   9   9  10  12   9
07/18  12   9  10  10   8   8   9  10   9   8   9  10  10  11  10  11  10  10  11  10  11   9  10  10
07/19   9   3   1   1   0   0   4   6   7   7   4   5  11  10   5   4   5   7   6   8   7   5   5   3
07/20   1   1   8  10   7   5   4   5   4   5   7   7   9   7   9   9   7   9  10  11  12  11  12   9
07/21   9  10  10  10  12  10   7   8   9   8   9  10  11  11  11   8  10  10  12   7   6   7   7   7
07/22   8   7   9  10   8   6   7   8   8   8   9   9   9  10   9   9   9   9   9   9  10   7   6   7
07/23   5   5   7   7   7   2   3   3   4   5   6   5   5   4   3   3   4   4   6   6   5   9   8   5
07/24   4   4   5   4   7   6   5   8   8  11  11  11   

log_checkpoint_interval = 900'000'000  (OK, must be greather than Redolog-File)
log_checkpoint_timeout  = 1200 (Set it to 0, so time-based checkpoints are disabled)

5). Time needed to write a checkpoint
-------------------------------------
Beginning database checkpoint by background
Mon Aug  2 16:37:36 1999
Thread 1 advanced to log sequence 2860
  Current log# 4 seq# 2860 mem# 0: /data/ota/db1/OTASICAP/redo/redoOTASICAP04.log
Mon Aug  2 16:43:31 1999
Completed database checkpoint by background

==> 6 Minutes

Mon Aug  2 16:45:15 1999
Beginning database checkpoint by background
Mon Aug  2 16:45:15 1999
Thread 1 advanced to log sequence 2861
  Current log# 5 seq# 2861 mem# 0: /data/ota/db1/OTASICAP/redo/redoOTASICAP05.log
Mon Aug  2 16:50:29 1999
Completed database checkpoint by background

==> 5.5 Minutes

Mon Aug  2 16:51:50 1999
Beginning database checkpoint by background
Mon Aug  2 16:51:51 1999
Thread 1 advanced to log sequence 2862
  Current log# 6 seq# 2862 mem# 0: /data/ota/db1/OTASICAP/redo/redoOTASICAP06.log
Mon Aug  2 16:56:44 1999
Completed database checkpoint by background

==> 5.5 Minutes

Wednesday, April 25, 2018

RMAN duplicate database having multiple directories to assign to BACKUP LOCATION


Applies To


 By default RMAN picks one backup location to do duplicate with clause 'BACKUP LOCATION' .

Many times user don't have much space in single location ,hence, the backup piece distributed
to different location and at same time want to use 'BACKUP LOCATION' feature for duplicate.

How to perform the RMAN duplication using the BACKUP LOCATION clause when the RMAN backuppieces are stored on several mount points or directories?


Solution

An Enhancement request is already in place:  'Unpublished'  Bug 12846424 : RMAN DUPLICATE BACKUP LOCATION TO ACCEPT MULTIPLE DIRECTORY PATH LOCATIONS


Workaround:

Create soft link for the rest location in the location we providing in the clause 'BACKUP LOCATION'.

Please perform the following steps, as shown here.

1. Create the softlink on all of the individual RMAN backuppieces required for the RMAN duplicate, as shown in the example here. You may change the '/tmp/rmanbkup' on a different directory where you want to create those softlinks.

mkdir /tmp/rmanbkup/
ln –s /u02/oracle/level0_backup_21042018 /tmp/rmanbkup/bkp3
ln –s /u03/backup /tmp/rmanbkup/bkp1
ln –s /u04/backup /tmp/rmanbkup/bkp2



Now use /tmp/rmanbkup/ as your backup location in your restore script.

RMAN>  catalog start with '/tmp/rmanbkup';




Done.!

Tuesday, April 24, 2018

CRS-2800: Cannot start resource 'ora.ORA_DATA.dg' as it is already in the INTERMEDIATE state on server 

ISSUE:-

SQL> startup;

ORA-39510: CRS error performing start on instance

CRS-2800: Cannot start resource 'ora.TESTDB.dg' as it is already in the INTERMEDIATE state on server


]$ srvctl start database -d TESTDB

PRCR-1079 : Failed to start resource ora.TESTDB.db

CRS-2800: Cannot start resource ‘ora.TESTDB.dg’ as it is already in the INTERMEDIATE state on server 'Doyen25'

CRS-2632: There are no more servers to try to place resource ‘ora.TESTDB.db’ on that would satisfy its placement policy

oracle@Doyen25]$ crsctl stat res -t ora.TESTDB.db
ONLINE INTERMEDIATE TESTDB01 CHECK TIMED OUT
ONLINE ONLINE TESTDB02

Root Cause:


This may be an intermediate connectivity disturbance



Solution:



1) If you have root access you can re-check the resources.

[oracle@Doyen25]$ crsctl check resource ora.TESTDB.db
crsctl status resource ora.TESTDB.db
NAME=ora.TESTDB.db
TYPE=ora.database.type
TARGET=INTERMEDIATE TESTDB01 CHECK TIMED OUT
STATE=OFFLINE


]#crsctl start resource ora.TESTDB.db
CRS-2672: Attempting to start 'ora.TESTDB.db' on 'TESTDB01'
CRS-2676: Start of 'ora.TESTDB.db' on 'TESTDB01' succeeded


[oracle@Doyen25]$ crsctl stat res -t

ora.TESTDB.db

ONLINE ONLINE TESTDB01

ONLINE ONLINE TESTDB02



[oracle@Doyen25] # srvctl status database -d TESTDB

Instance TESTDB01 is running on node Doyen25

Instance TESTDB02 is running on node Doyen26



                                                          (OR)

2)

[oracle@Doyen25] $ srvctl remove database -d TESTDB
               
SQL> Startup;

[oracle@Doyen25] $ srvctl add database -d TESTDB -o <ORACLE_HOME>
               
[oracle@Doyen25] $ srvctl status database -d TESTDB




Wednesday, April 18, 2018

Please move GGUSER to its own tablespace

Please move GGUSER to its own tablespace

 SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:GGUSER

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.



declare
*
ERROR at line 1:
ORA-20783:
ORA-20783:
Oracle GoldenGate DDL Replication setup:
*** Please move GGUSER to its own tablespace
ORA-06512: at line 34


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL>
I check the default table of GGUSER


SQL> select username, default_tablespace from dba_users where username='GGUSER';
USERNAME                       DEFAULT_TABLESPACE           
------------------------------ ------------------------------
GGUSER                      GG_TBS     

Then I have check if any other user is using GG_TBS.

 SQL> select USERNAME from dba_users where default_tablespace='GG_TBS';

USERNAME                     
------------------------------
GGUSER
TESTUSER

So i change the TESTUSER default tablespace to other DATA.


SQL> alter user TESTUSER default tablespace DATA;

User altered.

so now no other user is using GG_TBS

SQL> select USERNAME from dba_users where default_tablespace='GG_TBS';

USERNAME
------------------------------
GGUSER

SQL>

now, executed again the @ddl_setup.sql and it worked.


SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:GGUSER

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.











Using GGUSER as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGUSER

CLEAR_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

CREATE_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

TRACE_PUT_LINE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

INITIAL_SETUP STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED

STAYMETADATA IN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF

DDL TRIGGER SQL TRACING
------------------------------------------------------------------------------------------------------------------------
0

DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/optware/oracle/diag/rdbms/cxxxnm2s/CXXXNM2S/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL>

OGG-00529 DDL Replication is enabled but table gguser.GGS_DDL_HIST is not found. Please check DDL installation in the database.

Configure Goldengate DDL Replication

Prerequisite Setup
1. Navigate to the directory where the Oracle Goldengate software is installed.
2.Connect to the Oracle database as sysdba.
   sqlplus sys/password as sysdba
3.For DDL synchronization setup, run the marker_setup.sql script. Provide OGG_USER 
   schema name, when prompted.
4.Here the OGG_USER is the name of the database user, assigned to support DDL 
   replication feature in Oracle Goldengate


SQL> @marker_setup

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:gguser


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGUSER

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.
SQL>

SQL> @ddl_setup

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:gguser

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.







WARNING: Tablespace GGUSER does not have AUTOEXTEND enabled.



Using GGUSER as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGUSER

CLEAR_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

CREATE_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

TRACE_PUT_LINE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

INITIAL_SETUP STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED

STAYMETADATA IN TRIGGER
-----------------------------------
OFF

DDL TRIGGER SQL TRACING
-----------------------------------
0

DDL TRIGGER TRACE LEVEL
-----------------------------------
NONE

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/product/12.1.0.2/db_1/rdbms/log/ggs_ddl_trace.log

Analyzing installation status...


VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL> grant dba to gguser;

Grant succeeded.

SQL> @ddl_enable

Trigger altered.