Sunday, October 23, 2011

Basic Rman (Recovery manager) Guide

Today we are gonna talk about Oracle Rman ( Recovery Manager) This tools from oracle makes backup and restore database looks easily , If you lose you data it will be problem so oracle provide solution for this issue
Rman is Command line tools that gives you lot of benefits for backup
such as backup database, control file , archive log , create scripts ... etc

you can connect to the following types of databases.

Target database : 
RMAN connects you to the target database with the SYSDBA privilege. If you do not have this privilege, then the connection fails.
Recovery catalog database: 
This database is optional, you can also use RMAN with the default NOCATALOG option.
Auxiliary database :
You can connect to a standby database, duplicate database, or auxiliary instance (standby instance or tablespace point-in-time recovery instance.

Connecting to the Target Database
rman target /



 Information About your Schema And display datafiles Currently In the target database .


RMAN>REPORT SCHEMA;


To list the backup sets and image copies that you have created, run the list command as follows:



 RMAN> LIST BACKUP;


To list image copies, run the following command:
RMAN> LIST COPY;

 Show Rman Confurgation :
RMAN>Show all ;

List All the backup has been taken by Rman :
RMAN>list backupset ;

Validate Your backup that has been taken  By Rman , to check there's no corrputed file in it :
RMAN>validate backupset

Take anote that I mention Rman basic command in earlier Blog , Please take a look at it .
And ENJOY .

Thank you
Osama Mustafa

Friday, October 21, 2011

Oracle Stream Stop Replication without any error

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 :
COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10
COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 99999
COLUMN NAME HEADING 'Required|Archived Redo Log|File Name' FORMAT A40

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)