Guess what, RMAN can handle lost datafiles even if you do not have a backup.
The conditions:
1. The control file knows about the datafile, that is, the user backed up the control file after datafile creation, but the datafile itself is not backed up.
2. If the datafile record is in the control file, then RESTORE creates the datafile in the original location or in a user-specified location (for example, with SET NEWNAME). The RECOVER command can then apply the necessary logs to the datafile.
3. The control file does not have the datafile record, that is, the user did not back up the control file after datafile creation. During recovery, the database will detect the missing datafile and report it to RMAN, which will create a new datafile and continue recovery by applying the remaining logs. If the datafile was created in a parent incarnation, it will be created during restore or recover as appropriate.
Example:
1. Make a full database backup of your ARCHIVELOG mode database.
RMAN> BACKUP DATABASE TAG="FULL";
2. You create a tablespace "TEST" containing a single datafile called /orcl/test01.dbf.
3. create a table and populate some data.
4. Archive all the active online redo logs.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
5. Delete the file /orcl/test01.dbf.
6. Take the tablespace with the missing datafile offline.
SQL> ALTER TABLESPACE TEST OFFLINE IMMEDIATE;
7. Restore the tablespace even without backup.
RMAN> RESTORE TABLESPACE "TEST";
8. Recover tablespace.
RMAN> RECOVER TABLESPACE "TEST";
9. Bring the recovered tablespace online.
SQL> ALTER TABLESPACE TEST ONLINE;
10. Verify the contents of the tablespace.
Wednesday, February 4, 2009
Tuesday, January 13, 2009
Disabling OS-Authentication
On the server where that host the database, edit the file $ORACLE_HOME/network/admin/sqlnet.ora
Set SQLNET.AUTHENTICATION_SERVICES=(NONE)
The second way will be remove oracle OS user from dba group or ORA_DBA on Windows.
Set SQLNET.AUTHENTICATION_SERVICES=(NONE)
The second way will be remove oracle OS user from dba group or ORA_DBA on Windows.
Auto statistics collection in 10gR1 and 10gR2
We all know that Oracle Database 10g introduces the new automatic optimizer-statistics collection feature. Oracle automatically creates a database job, GATHER_STATS_JOB, and Oracle Scheduler automatically schedules the job to run during the maintenance window.
The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.
But do you know there is something different between Oracle Database 10gR1 and 10gR2 ?
In 10gR1, the GATHER_STATS_JOB continues until it finishes, even if it exceeds the allocated time for the maintenance window.
http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10752/stats.htm#40674
In 10gR2, the GATHER_STATS_JOB stops when the maintenance window closes.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i41282
The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.
But do you know there is something different between Oracle Database 10gR1 and 10gR2 ?
In 10gR1, the GATHER_STATS_JOB continues until it finishes, even if it exceeds the allocated time for the maintenance window.
http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10752/stats.htm#40674
In 10gR2, the GATHER_STATS_JOB stops when the maintenance window closes.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i41282
Friday, January 2, 2009
Customize 10g sqlplus prompt
Edit the file $ORACLE_HOME/sqlplus/admin/glogin.sql and add in line as below.
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
When perform sqlplus,
[localhost.localdomain]oracle:> sqlplus user1/user1@ORCL
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jan 3 15:27:23 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
user1@ORCL>
This setting can be overwritten if you have login.sql file in the directory where you run sqlplus.
Eg. On my windows client, I have D:\> login.sql and the contents of the file is as below:
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _DATE> "
When I run sqlplus from D:\ I get output below.
D:\>sqlplus user1/user1@ORCL
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 3 15:31:38 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
user1@ORCL 03-JAN-09>
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
When perform sqlplus,
[localhost.localdomain]oracle:> sqlplus user1/user1@ORCL
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jan 3 15:27:23 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
user1@ORCL>
This setting can be overwritten if you have login.sql file in the directory where you run sqlplus.
Eg. On my windows client, I have D:\> login.sql and the contents of the file is as below:
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _DATE> "
When I run sqlplus from D:\ I get output below.
D:\>sqlplus user1/user1@ORCL
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 3 15:31:38 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
user1@ORCL 03-JAN-09>
Thursday, December 11, 2008
Unset streams DML handler
To unset DML handler in a streams environment, simply point "user_procedure" to NULL.
Eg.
BEGIN DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name => '',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => false,
user_procedure => NULL ,
apply_database_link => NULL,
apply_name => NULL);
END;
/
Eg.
BEGIN DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name => '
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => false,
user_procedure => NULL ,
apply_database_link => NULL,
apply_name => NULL);
END;
/
Monday, December 1, 2008
How to re-synchronize the streams replicated objects online
Refer to My Oracle Support(Metalink), Note:749036.1 publish on 23-NOV-2008.
In case some reader couldn't access Metalink, refer below.
Applies to: Oracle Server - Enterprise Edition - Version: 10.2 to 11.1
Information in this document applies to any platform.
Goal: In-cases we need to re-synchronize the data between the replicating sites, like for example in cases of errors like ORA-01403 / ORA-1422 reported by the apply process because data is out of sync, and resynchronizing manually is not feasible.
Here are the steps we need to do to re-synchronize the data between the source and target site in the streams environment, without the need to stop the activity on the replicated objects on the source site. .
Solution
In here we assume that we have one SOURCE and one TARGET and streams replication is configured between them. For more complicated environments, some modifications/additions may be needed on these steps.
Here is what we need to do:
1. Stop the capture and apply processes:
-- Stop the capture process:
exec dbms_capture_adm.stop_capture('');
-- Make sure its stopped:
select capture_name, status from dba_capture;
-- Stop the apply process:
exec dbms_apply_adm.stop_apply('');
-- Make sure its stopped:
select apply_name, status from dba_apply;
2. Purge the Capture and apply queue tables:
- As we will re-synch; The messages in both source queue that has not been propagated yet, and messages in the apply queue that has not been applied yet, can both be purged.
You can do this step of purging the capture and apply queue if and only if you are doing complete resync of all the streams replication objects. If you are doing synchronization for some of the objects, then do not attempt to purge any of the queues or the other objects will get out of sync
- There will be no problem if you skipped that step, the apply process will still bypass all those messages, we just do that incase we got alot of messages that may take long time to be propagated and skipped by the apply process.
-- Stop the propagation:
exec dbms_propagation_adm.stop_propagation('');
select propagation_name, status from dba_propagation;
-- To purge perform the following for both the capture and apply queue tables:
DECLARE
options dbms_aqadm.aq$_purge_options_t;
BEGIN
options.delivery_mode:=DBMS_AQADM.BUFFERED; DBMS_AQADM.PURGE_QUEUE_TABLE('',NULL,options);
END;
/
-- check that the messages has been successfully pruged for capture and apply queue:
SELECT QUEUE_SCHEMA, QUEUE_NAME, (NUM_MSGS - SPILL_MSGS) MEM_MSG, SPILL_MSGS, NUM_MSGS FROM V$BUFFERED_QUEUES where queue_name='';
-- Start the propagation again:
exec dbms_propagation_adm.start_propagation(''); select propagation_name, status from dba_propagation;
3. Export the source schema:
-- For example we are replicating schema "MARS", so we should do the following:
exp system/oracle owner=mars file=mars.dump log=mars.log object_consistent=Y
*Object_consistent must be set to Y, so the imported data would be consistent with the source.
4) Delete the apply errors:
As we will re-synchronize, we can delete the errors that are in the error_queue of the apply process.
To do so, we can execute either DBMS_APPLY_ADM.DELETE_ERROR or DBMS_APPLY_ADM.DELETE_ALL_ERRORS at the apply site.
You need to make sure that you are not deleting any needed transaction, specially if you are not synchronizing all the replicated for that apply process. If you are synchronizing all the data you can execute directly DBMS_APPLY_ADM.DELETE_ALL_ERRORS.
-- Check the errors in the error queue:
select apply_name, LOCAL_TRANSACTION_ID, ERROR_MESSAGE from dba_apply_error;
-- To delete specific transaction:
exec DBMS_APPLY_ADM.DELETE_ERROR('');
-- To delete all errors:
exec DBMS_APPLY_ADM.DELETE_ALL_ERRORS('');
If you need to check the contents of the transactions before deleting check the procedures in documentation:
> Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-04 > 22 Monitoring Streams Apply Processes
> Displaying Detailed Information About Apply Errors
5. Import the source schema to target:
imp system/oracle file=mars.dump full=y ignore=y STREAMS_INSTANTIATION=Y
When doing STREAMS_INSTANTIATION=Y and having the export done with object_consistent=Y, the instantiation SCN for the apply will be modified to the SCN at the time the export was taken, and this will insure that data at the target is consistent with data at the source.
In the example above of exp/imp we are assuming that we are replicating schema MARS, and we are trying to synchronize it, but we may also exp/imp specific tables, we do not have to synchronize the whole schema when we are doing schema level streams.
Note:
- After doing the import, we are now sure that the data is now consistent, and the apply process will only apply changes starting from the SCN after the time of the export.
- If the exp/imp will be done using datapump, then we will not need to set any parameters, as the imported objects will be automatically imported with the SCN of the source at point in time where the export was taken, as long as there is an existing apply process on the apply site.
6. Start capture and apply:
-- Start the apply process:
exec dbms_apply_adm.start_apply('');
-- Make sure apply is started
select apply_name, status from dba_apply;
-- Start the capture process:
exec dbms_capture_adm.start_capture('');
-- Make sure capture is started
select capture_name, status from dba_capture;
In case some reader couldn't access Metalink, refer below.
Applies to: Oracle Server - Enterprise Edition - Version: 10.2 to 11.1
Information in this document applies to any platform.
Goal: In-cases we need to re-synchronize the data between the replicating sites, like for example in cases of errors like ORA-01403 / ORA-1422 reported by the apply process because data is out of sync, and resynchronizing manually is not feasible.
Here are the steps we need to do to re-synchronize the data between the source and target site in the streams environment, without the need to stop the activity on the replicated objects on the source site. .
Solution
In here we assume that we have one SOURCE and one TARGET and streams replication is configured between them. For more complicated environments, some modifications/additions may be needed on these steps.
Here is what we need to do:
1. Stop the capture and apply processes:
-- Stop the capture process:
exec dbms_capture_adm.stop_capture('
-- Make sure its stopped:
select capture_name, status from dba_capture;
-- Stop the apply process:
exec dbms_apply_adm.stop_apply('
-- Make sure its stopped:
select apply_name, status from dba_apply;
2. Purge the Capture and apply queue tables:
- As we will re-synch; The messages in both source queue that has not been propagated yet, and messages in the apply queue that has not been applied yet, can both be purged.
You can do this step of purging the capture and apply queue if and only if you are doing complete resync of all the streams replication objects. If you are doing synchronization for some of the objects, then do not attempt to purge any of the queues or the other objects will get out of sync
- There will be no problem if you skipped that step, the apply process will still bypass all those messages, we just do that incase we got alot of messages that may take long time to be propagated and skipped by the apply process.
-- Stop the propagation:
exec dbms_propagation_adm.stop_propagation('
select propagation_name, status from dba_propagation;
-- To purge perform the following for both the capture and apply queue tables:
DECLARE
options dbms_aqadm.aq$_purge_options_t;
BEGIN
options.delivery_mode:=DBMS_AQADM.BUFFERED; DBMS_AQADM.PURGE_QUEUE_TABLE('
END;
/
-- check that the messages has been successfully pruged for capture and apply queue:
SELECT QUEUE_SCHEMA, QUEUE_NAME, (NUM_MSGS - SPILL_MSGS) MEM_MSG, SPILL_MSGS, NUM_MSGS FROM V$BUFFERED_QUEUES where queue_name='
-- Start the propagation again:
exec dbms_propagation_adm.start_propagation('
3. Export the source schema:
-- For example we are replicating schema "MARS", so we should do the following:
exp system/oracle owner=mars file=mars.dump log=mars.log object_consistent=Y
*Object_consistent must be set to Y, so the imported data would be consistent with the source.
4) Delete the apply errors:
As we will re-synchronize, we can delete the errors that are in the error_queue of the apply process.
To do so, we can execute either DBMS_APPLY_ADM.DELETE_ERROR or DBMS_APPLY_ADM.DELETE_ALL_ERRORS at the apply site.
You need to make sure that you are not deleting any needed transaction, specially if you are not synchronizing all the replicated for that apply process. If you are synchronizing all the data you can execute directly DBMS_APPLY_ADM.DELETE_ALL_ERRORS.
-- Check the errors in the error queue:
select apply_name, LOCAL_TRANSACTION_ID, ERROR_MESSAGE from dba_apply_error;
-- To delete specific transaction:
exec DBMS_APPLY_ADM.DELETE_ERROR('
-- To delete all errors:
exec DBMS_APPLY_ADM.DELETE_ALL_ERRORS('
If you need to check the contents of the transactions before deleting check the procedures in documentation:
> Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-04 > 22 Monitoring Streams Apply Processes
> Displaying Detailed Information About Apply Errors
5. Import the source schema to target:
imp system/oracle file=mars.dump full=y ignore=y STREAMS_INSTANTIATION=Y
When doing STREAMS_INSTANTIATION=Y and having the export done with object_consistent=Y, the instantiation SCN for the apply will be modified to the SCN at the time the export was taken, and this will insure that data at the target is consistent with data at the source.
In the example above of exp/imp we are assuming that we are replicating schema MARS, and we are trying to synchronize it, but we may also exp/imp specific tables, we do not have to synchronize the whole schema when we are doing schema level streams.
Note:
- After doing the import, we are now sure that the data is now consistent, and the apply process will only apply changes starting from the SCN after the time of the export.
- If the exp/imp will be done using datapump, then we will not need to set any parameters, as the imported objects will be automatically imported with the SCN of the source at point in time where the export was taken, as long as there is an existing apply process on the apply site.
6. Start capture and apply:
-- Start the apply process:
exec dbms_apply_adm.start_apply('
-- Make sure apply is started
select apply_name, status from dba_apply;
-- Start the capture process:
exec dbms_capture_adm.start_capture('
-- Make sure capture is started
select capture_name, status from dba_capture;
Monday, November 24, 2008
sqlplus give error /usr/lib/hpux64/dld.so: Unable to find library 'libclntsh.so.10.1'
When other user in HP-UX run sqlplus and system give following error:
/usr/lib/hpux64/dld.so: Unable to find library 'libclntsh.so.10.1'
Solution:chmod -R 755 $ORACLE_HOME
/usr/lib/hpux64/dld.so: Unable to find library 'libclntsh.so.10.1'
Solution:chmod -R 755 $ORACLE_HOME
Subscribe to:
Posts (Atom)