Month: September 2011

ORA-00257

Error:

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

Reason:

File recovery area is above specified size.

Action:

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

SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string C:oracleproduct10.2.0/flash_recovery_area
db_recovery_file_dest_size big integer 2G

Check what the value for db_recovery_file_dest_size.

Increase the Flash Recovery Area

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

http://itnewscast.com/database/error-ora-00257-archiver-error-connect-internal-only-until-freed

http://oraworks.wordpress.com/2011/08/08/ora-00257-archiver-error-connect-internal-only-until-freed/

Start database along with Windows startup

Sometimes starting the Windows Oracle services starts the database automatically. You can check this in Windows registry as mentioned below.

Open the registry with regedit.

HKEY_LOCAL_MACHINESOFTWAREORACLEoracle_home_name.
There will be a key called:
ORA_SID_AUTOSTART. SID is your database SID.

This key should be set to TRUE. If you want to start the database automatically once the service is started.

If you want to manually start the database set ORA_SID_AUTOSTART to FALSE. The service will start but not the database.

There is also an ORA_SID_SHUTDOWN which you want to be TRUE so that if the server is shut down the service will shutdown the database automatically.

For Deleting a service

you can use
> oradim or sc delete

User Managed Hot Backup

To use hot backups, the database should operate in ARCHIVELOG mode.

1. Check whether the database is in ARCHIVELOG mode or not

SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
————
ARCHIVELOG

If not, place the database in archive log as follows, (assuming your database is up and running)

SQL> alter database close;
SQLPLUS>alter database archivelog;
SQL>alter database open;

Display online log sequence

SQL> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:oracleproduct10.1.0db_2RDBMS
Oldest online log sequence 169
Next log sequence to archive 171
Current log sequence 171

2. Files that require to backup found using the following query ie. backup of data files , online redo log files ,control files, spfile.

SQL> SELECT NAME “FILE NEED BACKUP” FROM V$DATAFILE
2 UNION ALL
3 SELECT NAME FROM V$CONTROLFILE
4 UNION ALL
5 SELECT MEMBER FROM V$LOGFILE
6 UNION ALL
7 SELECT VALUE FROM V$PARAMETER WHERE NAME=’SPFILE’;

FILE NEED BACKUP
——————————————————————————–

D:CRMDATASYSTEM.DBF
D:CRMDATASYSAUX.DBF
D:CRMDATAUNDOTBS.DBF
D:CRMDATACRM.DBF
D:CRMCONTROLCRM_CTL1.CTL
D:CRMCONTROLCRM_CTL2.CTL
D:CRMCONTROLTICKET_CTL3.CTL
D:CRMREDOR1.LOG
D:CRMREDOR2.LOG
D:CRMREDOR3.LOG

10 rows selected.

SQL> SELECT S.STATUS, S.TABLESPACE_NAME “Tablespace”, D.FILE_NAME “Datafile”
2 FROM DBA_TABLESPACES S, DBA_DATA_FILES D
3 WHERE S.TABLESPACE_NAME = D.TABLESPACE_NAME;

STATUS Tablespace Datafile
———— —————- ———-

ONLINE SYSTEM
D:CRMDATASYSTEM.DBF

ONLINE SYSAUX
D:CRMDATASYSAUX.DBF

ONLINE UNDOTBS
D:CRMDATAUNDOTBS.DBF

STATUS Tablespace Datafile
———— —————- ———–

ONLINE CRM
D:CRMDATACRM.DBF

Force a log switch, so that Oracle will create an archived log file

SQL> ALTER SYSTEM SWITCH LOGFILE;

Backup control file

SQL> ALTER DATABASE BACKUP CONTROLFILE TO filespec;

Connect / as sysdba;

Welcome to dbpost !

Oracle database related posts appear in my blog as I meet new tasks and issues as a DBA.  Its only an effort to document my work log  in simple words,  as I go adventurous on Oracle databases and related technologies.

All posts are written based on different articles, books, discussions and more on web. And in almost posts,  I had tried enough to provide source/reference links.

Please don’t try these solutions directly on production database based on any of my post,  give a  shot in your test environment initially..!

Oracle Data Pump

Data Pump Export and Import

Data Pump takes the old export and import utilities one step further, you can have total control over the job running (stop it, pause it, check it, restart it). Data pump is a server side technology and it can transfer large amounts of data very quickly using parallel streams to achieve maximum throughput, they can be 15-45% faster than the older import/export utilities.

Advantages using data pump are

  • ability to estimate jobs times
  • ability to restart failed jobs
  • perform fine-grained object selection
  • monitor running jobs
  • directly load a database from a remote instance via the network
  • remapping capabilities
  • improved performance using parallel executions

A couple of notes is that you cannot export to a tape device only to disk, and the import will only work with version of oracle 10.1 or greater. Also remember that the expdp and impdp are command line tools and run from within the Operating System.

Data Pump Uses

You can use data pump for the following

  • migrating databases
  • copying databases
  • transferring oracle databases between different operating systems
  • backing up important tables before you change them
  • moving database objects from one tablespace to another
  • transporting tablespace’s between databases
  • reorganizing fragmented table data
  • extracting the DDL for tables and other objects such as stored procedures and packages

Data Pump components

Data pump technology consists of three major components

  • dbms_datapump – the main engine for driving data dictionary metadata loading and unloading
  • dbms_metadata – used to extract the appropriate metadata
  • command-line – expdp and impdp are the import/export equivalents

Examples:

Export

expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

Import

impdp system/oracle DIRECTORY=dmpdir DUMPFILE=scott.dmp
Source/References: