Oracle Dataguard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
this is step by step to configure ODG on ASM configuration (copy this artikel to notepad or editor to easy read ):
– 2 virtual machine(DBSERVER and DBSERVERDRC) with OEL 5.5 and Oracle database 10gR2 (ASM Storage : +DATA and +FRA).
+DATA for database file and +FRA for recovery area.
– Database with SID : “blackowl” are configured on DBSERVER and at DBSERVERDRC just software oracle only
Quite useful, I had the case where I needed to provide a CSV file to an external party from our Oracle 10gdatabase. No problem, there are loads of CSV export tools out there. But it then transpired that the CSV’s that I was generating were using quote (“) marks to identify fields e.g. “field1″,”field2″ and so on…
When I ran the script in something like SQL Developer it would spool the file and also spool the query as well into the file no matter what I specified. Along with this it also wouldn’t recognise usage of variables in SQL Developer. Basically what I’m saying here is write the query in something like SQL Developer by all means but for writting and debugging a CSV spooler etc… just save the hassle and go straight to SQL*Plus.
First of all lets start with how to spool the CSV file without leading line breaks, no quote marks etc… the below is the SQL script to run, we’ll need to save this in order to run it later.
set echo off
set termout off
set feedback off
set linesize 100
set pagesize 0
set sqlprompt ”
set trimspool on
set show off
select * from mas_pasien;
The following are the errors seen during tablespace creation in an Oracle DB with ASM.
NOTIFICATION rcu: oracle.sysman.assistants.rcu.backend.action.JDBCAction::perform: Performing Inline Action Command: CREATE TABLESPACE “RRWEBC_IAS_WEBCENTER” EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO DATAFILE ‘+DATA’ SIZE 250M AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED
2010-02-09 23:27:35.187 ERROR rcu: oracle.sysman.assistants.rcu.backend.action.AbstractAction::handleNonIgnorableError: Received Non-Ignorable Error: ORA-01119: error in creating database file ‘+DATA’
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup space exhausted
The related disk group could have run out of space, due to more number of tablespaces, in this case there were 84. Firstly look for space, and appropriately cleanup some of them or just new disks.
select NAME, OS_MB, TOTAL_MB, FREE_MB from v$asm_disk;
drop tablespace <NAME> including contents and datafiles;
To Increase Disk Space:
alter diskgroup DISK1 rebalance power 11;
alter diskgroup DISK2 add disk ‘path’;
alter diskgroup test_dg add disk ‘/dev/hdb10′,’/dev/hdb11’;
Moving datafiles from file system to ASM can be done in different ways, one of them using the RMAN copy command.
These are the steps:
1. Check where to build a new file system based tablespace:
[oracle@rac1 ~]$ cd /u01/oradata/racdb
[oracle@rac1 ~]$ df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
31457280 17540576 13819976 56% /u01
2. Connect to sqlplus and create a new tablespace
[oracle@rac1 racdb]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Tue Jun 19 06:07:50 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> create tablespace trtst datafile ‘/u01/oradata/racdb/trtst01.dbf’ size 150M;
Whilst it is not very common to move from ASM to filesystem I needed to prepare a regression plan for the migration of a 450 datafile database in the event of problems during the migration or shortly afterwards.
The example below is from a small database called TEST and I used filestore in /app/oracle whereas normally I would have a /oradata/SID/ filesystem
The details below should be enough to get someone through the migration and the sql scripts can be modified to be more generic if necessary but it is not too hard to use them as they are now.
List the datafiles and we will add a second datafile to the users tablespace to provide an example of an issue that can occur.
SYS@TEST SQL>select file_name from dba_data_files; +DATA/test/datafile/undotbs1.301.697649965
Some tablespaces may have multiple datafiles which would end up with the same name using the code above. However as a quick circumvention I manually identified those tablespaces using this sql and then edited the new data file names
select tablespace_name,count(file_name) from dba_data_files group by tablespace_name ORDER BY 2