parameter – Technology Geek

Check Your Database Size

Posted on June 8, 2012December 23, 2018 by Osama Mustafa in Scripts

Datafiles :

select sum(bytes)/1048576 “DATAFILES_SIZE_MB” from dba_data_files;

Tempfiles:

select sum(bytes)/1048576 “TEMPFILES_SIZE_MB” from dba_temp_files;

Your redo logs can also use up a large amount of disk space – especially if your database has more than the minimum number of 2 redo log groups. (You may also have several members within each group).

Redologs:

select sum(bytes)/1048576 “REDOLOGS_SIZE_MB” from v$log;

The database obviously needs controlfiles to record information such as which datafiles belong to the database. If your CONTROL_FILE_RECORD_KEEP_TIME is set to a large value, then your controlfiles can become quite large.

Controlfiles:

select round(sum(block_size*file_size_blks)/1048576,2) “CONTROLFILESIZE_MB” from v$controlfile;

From 10g onwards, flashback database is not enabled by default, but if it is, then this area can grow rapidly over time.

Flash Recovery Area:

select * from v$recovery_file_dest;
select * from v$flash_recovery_area_usage;

These views will show sizing details and free space available.

Note: If your backups are held outside of the flash recovery area, then you’ll also need to allow space for these. This will depend on your backup strategy and backup retention policy. (Export/datapump export dumpfiles also need to be planned for).
If you are using RMAN incremental backups and have block change tracking enabled, then include this file:

Block change tracking file

select filename, nvl(bytes/1048576,0) “BLOCK_CT_SIZE_MB” from v$block_change_tracking;

Files referenced by database directories or the utl_file_dir parameter:

Your application may read from, or write to external files via database directories or the utl_file_dir parameter.

Other examples of using external directories are for

(a) External tables –

select a.owner||’.’||a.table_name||’ stored in directory ‘||b.directory_path “EXTERNAL_TABLES”

from dba_external_locations a, dba_directories b

where a.directory_owner=b.owner

and a.directory_name=b.directory_name;

(b) If you are storing multiple versions of the same tablespace within a file group repository. (i.e. tablespace versioning).

select a.tablespace_name, a.version, a.file_group_owner, a.file_group_name,

b.file_name, b.file_directory

from dba_file_group_tablespaces a, dba_file_group_files b

where a.file_group_owner=b.file_group_owner

and a.file_group_name=b.file_group_name;

Thank you
osama Mustafa

ORA-00020: maximum number of processes (%s) exceeded

Posted on May 18, 2012December 23, 2018 by Osama Mustafa in Database

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     150

SQL> select count(*) from v$process;

  COUNT(*)
----------
       149

SQL> alter system set processes=300 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     300

Thank you
Osama mustafa

Oracle Stream Stop Replication without any error

Posted on October 21, 2011December 23, 2018 by Osama Mustafa in Database

I faces the most strange issue that you will ever seen , Everything was works fine without any problem but suddenly without any error Replication on database stopped Why ?

No one was know , I try everything that you could be imagine , i try to register the archive log manually :

ALTER DATABASE REGISTER LOGICAL LOGFILE
‘Archivelog-name’ FOR ‘Capture-name’;

But nothing works, after that i start trace Capture process maybe it will give me hints with following produce:

1. Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)

2. Enable tracing:

sqlplus /nolog
connect / as sysdba
alter system set events ‘26700 trace name context forever, level 6’;
alter system set events ‘1349 trace name context forever , level 1024’;
exit

sqlplus strmadmin/passwd (streams admin)

exec dbms_capture_adm.set_parameter(‘yourcapturename’,’trace_level’,’127′);
exec dbms_capture_adm.start_capture(‘yourcapturename’);

Wait for about 30 minutes an check whether capture is or not progressing..

3. To turn off capture tracing:

sqlplus strmadmin/passwd (streams admin)

Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)
exec dbms_capture_adm.set_parameter(‘yourcapturename’,’trace_level’,null);
exit

sqlplus /nolog
connect / as sysdba

alter system set events ‘26700 trace name context off’;
alter system set events ‘1349 trace name context off’;
exit

Yes Nothing works , No error , its make me crazy

Tried checkpoint force, stop/start capture.

tracing the capture process:

1. Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)

exec dbms_capture_adm.set_parameter(‘yourcapturename‘,’trace_level’,’127′);

exec dbms_capture_adm.start_capture(‘yourcapturename‘);
“set trace off after 10-15 mins”:

2. To turn off capture tracing:
exec dbms_capture_adm.set_parameter(‘yourcapturename‘,’trace_level’,null);

Yea Nothing Works too

Then I start check if there’s some missing Archivelogs By :

select NAME,SEQUENCE#,STATUS,DELETED from V$ARCHIVED_LOG

Information about archive logs that needed by Oracle Stream :

SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME AND
r.NEXT_SCN >= c.REQUIRED_CHECKPOINT_SCN
order by 3;

and using this query could help sometimes to give you information TXN about apply process :

select apply_name, xidusn||’.’||xidslt||’.’||xidsqn txn_id, first_scn, first_message_create_time, message_count, spill_creation_time from dba_apply_SPILL_TXN;

Output : (Usually The last one)

APPLY_FROM_MARKA               9.27.637276                         5600212722474 17-OCT-11            25    18-OCT-11
APPLY_FROM_MARKA               4.23.369085                         5600212761449 17-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               56.17.156892                        5600242177417 18-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               4.40.369226                        5600243099471 18-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               24.21.419884                         5600247380679 18-OCT-11            15      19-OCT-11
APPLY_FROM_MARKA               14.20.166840                         5600278453686 19-OCT-11             1       19-OCT-11

After Check The TXN we need to Run the Stream to ignore the required TXN :

1-exec dbms_apply_adm.stop_apply(‘Apply-process-name’);

2-exec dbms_apply_adm.set_parameter(‘Apply-process-name’,’_ignore_transaction’,’TXN-Number’);

3-exec sys.purge_spill_txn(‘Apply-process-name’,’TXN-Number’);

4-exec dbms_apply_adm.start_apply(‘Apply-process-name’);

5-exec dbms_apply_adm.set_parameter(‘Apply-process-name’,’_ignore_transaction’,null);

Capture Site:

1-execute dbms_capture_adm.stop_capture(‘capture-Process-name’);

2-execute dbms_capture_adm.set_parameter(‘capture-Process-name’,’_ignore_transaction’,’TXN-Number’);

3-execute dbms_capture_adm.start_capture(‘capture-Process-name’);

But Nothing works too .

But I remember something Stream got hidden parameter _SGA_Size :

Capture needs to be configured to have more space.

The default amount of space for capture / log miner activity is 10

Steps :

exec dbms_capture_adm.set_parameter(‘Capture-procees-name’,’_SGA_SIZE’,’50’);

exec dbms_capture_adm.start_capture(‘Capture-procees-name’);

The above changes space for named capture process : ‘STRMADMIN_CAPTURE’ from 10M -> 50M

Thank God Its works again .

Note that i write every steps maybe something will be useful for you .

Some metalink note will be useful too :

Troubleshooting Long-Running Transactions in Oracle Streams (Doc ID 783927.1)

LogMiner out-of-memory (Doc ID 336705.1)