I am Md.Samiuzzaman Khan (Tamim) work as Technical Consultant having 5+ years of experience in Oracle Database Administration and Development, Unix/Linux administration, System Design and Integration, Search Engine Optimization (SEO), Mobile Content Management System (SMPP and Web Services), Java and .Net (C#) base Application Development also in Project Management(Agile/Scrum).

RAC Setup Diagram

November 20, 2011 Leave a comment

Categories: Oracle10g

ERROR: ORA-00257: archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.alter database flashback on

There is two possible way to solution.
1. without increasing DB_RECOVERY_FILE_DEST_SIZE.
2. by increasing DB_RECOVERY_FILE_DEST_SIZE.

Without increasing DB_RECOVERY_FILE_DEST_SIZE.
1. Check whether the database is in archive log mode and automatic archiving is enabled.

 SQL> archive log list;
 Database log mode              Archive Mode
 Automatic archival             Enabled
 Archive destination            USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence     29
 Next log sequence to archive   31
 Current log sequence           31

2. If archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by:

SQL> show parameter db_recovery_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

Check what the value for db_recovery_file_dest_size.

3. Find the space used in flash recovery area by using following SQL:

col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999
col round(space_used/1048576) heading “Space Used (MB)” format 99999
col name format a40
select name, round(space_limit/1048576) As space_limit,round(space_used/1048576) As space_used
from v$RECOVERY_FILE_DEST;

4. if SPACE_USED is equal to SPACE_LIMIT of db_recovery_file_dest, move the archive logs to different destination.

5. Archive all the log files

SQL> alter system archive log all;

6. Just switch the logs to verify

 SQL> alter system switch logfile;

7. DB_RECOVERY_FILE_DEST_SIZE is to delete (archive log) files from DB_RECOVERY_FILE_DEST if you are sure you have backups and the archived logs are no longer necessary.

 
 $rman target /
 RMAN>delete archivelog until time 'SYSDATE-1';
 or,
 RMAN>delete archivelog all;

By increasing DB_RECOVERY_FILE_DEST_SIZE.
1. See the path of flash recovery area.

 SQL> show parameter db_recovery_file_dest;

2. Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;
Sytem Altered.

CreateFile error 32 when trying set file time

March 24, 2011 Leave a comment

Today I am going to install Oracle 10g Client and ODAC11gR2 (Oracle Data Provider .Net) in Microsoft Windows XP Professional Service Pack 2. I have got following error message in console.

CreateFile error 32 when trying set file time

After searching I have found that this error message for my Antivirus. In my case the anti virus is Kaspersky. After disable my antivirus I am able to install Oracle Client with out any Warning or Error message

ORA-00439: feature not enabled: Flashback Database

March 13, 2011 1 comment

Some feature is not enabled in oracle database standard edition, as a result we can not use flashback feature in Oracle Database if you are use in standard edition of oracle.

Following Error Message show in Oracle 10g standard edition

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

Version of Oracle

SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Features are available in standard edition

SQL> SELECT Parameter,Value FROM V$OPTION Where Value = 'TRUE';
PARAMETER                                                        VALUE                                                           
----------------------------------------------------------------------
Objects                                                          TRUE                                                            
Connection multiplexing                                          TRUE                                                            
Connection pooling                                               TRUE                                                            
Database queuing                                                 TRUE                                                            
Incremental backup and recovery                                  TRUE                                                            
Instead-of triggers                                              TRUE                                                            
Parallel load                                                    TRUE                                                            
Proxy authentication/authorization                               TRUE                                                            
Plan Stability                                                   TRUE                                                            
Transparent Application Failover                                 TRUE                                                            
Sample Scan                                                      TRUE                                                            
Java                                                             TRUE                                                            
OLAP Window Functions                                            TRUE

So flashback database is disabled in standard edition or in standard one  edition. To be able to flashback on you have to use enterprise edition.

ORA-01775: looping chain of synonyms

March 12, 2011 1 comment
Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself.
Action: Change one synonym definition so that it applies to a base table or view and retry the operation.

For example, the following definitions are circular: CREATE SYNONYM s1 for s2;
CREATE SYNONYM s2 for s3
CREATE SYNONYM s3 for s1
or
CREATE SYNONYM s4 for s4

Check your synonyms with:
select * from all_synonyms where synonym_name = ‘name’ or table_name = ‘name’;

SELECT table_owner, table_name, db_link
FROM dba_synonyms
WHERE owner = 'PUBLIC'
AND synonym_name = <<synonym name>>

select owner, synonym_name, connect_by_iscycle CYCLE
from dba_synonyms
where connect_by_iscycle > 0
connect by nocycle prior table_name = synonym_name
and prior table_owner = owner
union
select 'PUBLIC', synonym_name, 1
from dba_synonyms
where owner = 'PUBLIC'
and table_name = synonym_name
and (table_name, table_owner) not in (select object_name, owner from dba_objects
where object_type != 'SYNONYM')

RemoteOperationException:ERROR:Wrong Password for User

March 12, 2011 Leave a comment

Problem in Oracle 10g database on Windows 2000/2003 Professional/Enterprise error message  “RemoteOperationException:ERROR:Wrong Password for User

Solutions

1. go to Control Panel -> Administrative Tools.

2. Click on Local Security Policy.

3. Local Policies, User Rights Assignment.

4. Double click, ‘Log on as a batch job‘.

5. Add your local user that you are supplying for the Host Credentials.

 

 

Listener Commands

March 1, 2011 Leave a comment
current_listener  
Syntax : set current_listener <listener_name>
LSNRCTL> set current_listener LISTENER
LSNRCTL> show current_listener
displaymode 
Syntax : set displaymode <RAW | COMPAT | NORMAL | VEBOSE>
LSNRCTL> show displaymode
Service display mode is NORMAL
LSNRCTL> set displaymode VERBOSE
inbound_connect_timeout 
Syntax : set inbound_connect_timeout <value>
LSNRCTL> set inound_connect_timeout 0
log_directory     
Syntax : set log_directory <directory_path>
LSNRCTL> set log_directory c:\listner_log
log_file    
Syntax : set log_file <file_name>
LSNRCTL> set log_file listner.log
log_status  
Syntax : set log_status < ON | OFF>
LSNRCTL> set log_status ON
password    
Syntax : set password
LSNRCTL> set password
Password:
rawmode     
Syntax : set rawmode <ON | OFF>
LSNRCTL> set rawmode OFF
save_config_on_stop     
Syntax : set save_config_on_stop <ON | OFF>
LSNRCTL> set save_config_on_stop ON
startup_waittime  
Syntax : set startup_waittime <value>
LSNRCTL> set startup_waittime 0
trc_directory     
Syntax : set trc_directory <directory path>
LSNRCTL> set trc_directory c:\listner_log
trc_file    
Syntax : set trc_level <file_name>
LSNRCTL> set trc_file listener.trc
trc_file    
Syntax : set trc_level <file_name>
LSNRCTL> set trc_file listener.trc
trc_level set trc_level <value>
Value

Description

0 – 1

off

2 – 3

err

4 – 5

user

6 – 14

admin

15

dev

16 – 99

support

LSNRCTL> set trc_level 0

Services view the current listener services

lsnrctl services
Start the listener  lsnrctl start
Stop the listener  lsnrctl stop
Show status lsnrctl status
Listener version lsnrctl version