Reading a CSV file becomes more complicated when it includes quoation marks with combination of commas inside a column. To overcome that issues we can use string shunting method which realy works in certain cases.
Following excercise will help you to extract a CSV file by shunting every data item after a read from the string. You must know which columns are provided with quotation makrs.
Step 1: Create a CSV file
create a csv file using any text editor and it should be saved with extension like filename.csv
copy and paste following text in the text file and save it.
1233222, JACK, "123 UNKNOWN STREET, UNKNOWN TOWN", 12-DEC-2020, "THE CUSTOMER IS TAGGED AS VIP CUSTOMER", 200000000
1233222, TOM, "321 UNKNOWN STREET, UNKNOWN TOWN", 12-MAR-2010, "", 7000
1233222, JERRY, "551 UNKNOWN STREET, UNKNOWN TOWN", 12-OCT-2009, "THE ACCOUNT HOLDER MAY APPLY FOR LOAN, AS THE CUSTOMER IS AN INVESTOR", 150000
Step 2: Create a database directory
C:\ mkdir csvfiles
C:\SQLPLUS / AS SYSDBA
SQL> DROP DIRECTORY CSV_FILES;
SQL> CREATE DIRECTORY CSV_FILES AS 'C:\csvfiles';
SQL> GRANT read, write ON DIRECTORY CSV_FILES TO HR ;
Step 3: Create and compile following procedure in HR user
SQL> CONNECT HR/HR@ORCL
CREATE OR REPLACE PROCEDURE READ_WRITE_CSV (
P_FILE_LOCATION IN VARCHAR2,
P_FILENAME IN VARCHAR2,
P_READ_WRITE_MODE IN VARCHAR2
) as
V_EXTRACT_FILE UTL_FILE.FILE_TYPE;
V_EXTRACT_LOCATION VARCHAR2(300);
V_READ_WRITE_MODE VARCHAR2(1);
V_LENGTH number;
V_FILE_LINE varchar2(4000);
account_id varchar2(500);
customer_name varchar2(500);
customer_address varchar2(500);
account_open_date varchar2(500);
comments varchar2(500);
opening_balance varchar2(500);
BEGIN
V_EXTRACT_FILE := UTL_FILE.fopen(P_FILE_LOCATION,P_FILENAME,P_READ_WRITE_MODE);
--=================
-- string format
--=================
-- account_id, customer name, customer address, account open date, comments, opening balance
LOOP
utl_file.get_line (V_EXTRACT_FILE,V_FILE_LINE);
-- account id
V_LENGTH := (INSTR(V_FILE_LINE , ',', 1, 1)) ;
account_id := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
--customer name
V_LENGTH := (INSTR(V_FILE_LINE , ',', 1, 1)) ;
customer_name := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
--customer address
V_LENGTH := (INSTR(V_FILE_LINE , '"', 1, 2))+1 ;
customer_address := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
--account open date
V_LENGTH := (INSTR(V_FILE_LINE , ',', 1, 1)) ;
account_open_date := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
--comments
V_LENGTH := (INSTR(V_FILE_LINE , '"', 1, 2))+1 ;
comments := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
--openning balance
opening_balance := substr(V_FILE_LINE , 1 ) ;
dbms_output.put_line(customer_name ||'-'||
customer_address ||'-'||
account_open_date ||'-'||
comments ||'-'||
opening_balance
);
end loop;
EXCEPTION
WHEN NO_DATA_FOUND THEN
COMMIT;
UTL_FILE.fclose(V_EXTRACT_FILE);
dbms_output.put_line('OK') ;
WHEN UTL_FILE.INVALID_PATH THEN
dbms_output.put_line('NOT OK - INVALID PATH') ;
WHEN OTHERS THEN
UTL_FILE.fclose(V_EXTRACT_FILE);
DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
END;
/
SQ>
Step 4: Execute procedure to read CSV file
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE READ_WRITE_CSV ('CSV_FILES', 'customer.csv', 'R' ) ;
Wednesday, 15 December 2010
Thursday, 4 November 2010
CREATING STANDBY (DISASTER RECOVERY) DATABASE WITHOUT USING DATAGUARD – by GMA
CREATING STANDBY (DISASTER RECOVERY) DATABASE WITHOUT USING DATAGUARD – by Ghulam Abbasi
This article is focussed to provide basic understanding on how to create an standby (Disaster Recovery) database. In this article we are not using the dataguard therefore we have to go through few additional steps. The advantage of using dataguard is that you can create standby databases for primary databases already configured to use FRA (Flash Recovery Area). Also dataguard provide a great feature to automate your synchronization process and manage primary and standby databases with more control and flexibility.
My next article will cover the issue of how to synchronize archive log files automatically using some database tables and unix scripts.
The Standby database is always created in read only mode. Though you can switch it back to normal database at any time, bear in mind that standby database is always managed with statement ‘read only’. Suppose open the standby database we use following statement:
SQL> alter database open read only;
If you issued following statement instead, the database will switch to normal (read / write) mode and you cannot switch it back to standby mode again.
SQL> alter database open;
1. Configuring init.ora file (orcl primary database)
1.1 Remove FRA feature to allow recognised archive log file name (while not using dataguard).
Note: You don’t need this step if using dataguard for synchronizing archive log files
[oracle@orcl_primary]$ sqlplus / as sysdba
SQL> alter system reset db_recovery_file_dest_size scope=SPFILE sid='*';
System altered.
SQL> alter system reset db_recovery_file_dest scope=SPFILE sid='*';
System altered.
SQL> exit
[oracle@orcl_primary]$
1.2 Now set a new path where archive logs should be created by database.
- Create a new directory for archive logs
[oracle@orcl_primary]$ cd $ORACLE_HOME/db_1/
[oracle@orcl_primary]$ mkdir archivelogs_orcl
[oracle@orcl_primary]$ chmod 775 archivelogs_orcl
- Add/ Change Set the parameters log_archive_dest and log_archive_format in init.ora file.
[oracle@orcl_primary]$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET log_archive_dest = '/app/oracle/product/10.2.0/db_1/archivelogs_orcl' scope=SPFILE sid='*' ;
SQL> ALTER SYSTEM SET log_archive_format='%t_%s_%r.arc' ;
SQL> ALTER SYSTEM SET
db_file_name_convert='/oradata/ORCL/','/oradata/ORCL_STDBY/';
- Now restart primary database to let new settings in init.ora take effect.
SQL> shutdown immediate;
SQL> startup;
SQL> create pfile from spfile;
File created.
NOTE: The new SPFILE / PFILE are normally created at following path in unix environment (10g only).
Please note that :
- spfile is created by default as ‘spfile’+’instance name’+.’ora’.
- pfile is created by default as ‘init’+’instance name’+.’ora’.
Remember, we need to move initORCL.ora form following location to standby server later in section 3.
$ORACLE_HOME/db_1/dbs/
2. Create backup for Standby Database (orcl primary database)
2.1 Create a separate directory to store standby backup. This backup set will be
moved to standby database.
- Create a new directory to store backup for standby database.
[oracle@orcl_primary]$ cd $ORACLE_HOME/db_1/
[oracle@orcl_primary]$ mkdir backup_standby
[oracle@orcl_primary]$ chmod 775 backup_standby
2.2 Take an standby backup of the primary database now.
- Logon to primary database target using RMAN and run following script to backup in standby mode.
[oracle@orcl_primary]$ rman
RMAN> connect target /
RMAN> sql 'alter system archive log current';
run {
allocate channel d01 device type disk format
'/app/oracle/product/10.2.0/db_1/backup_standby/%d_%t_%s_%U.dbf';
backup as compressed backupset database include current controlfile for
standby plus archivelog;
}
3. Move Required files to Standby Database
Please note that this article is tested on two databases (primary and standby) where:
- Same software version was installed on both servers (i.e. 10g).
- Similiar oracle home and directory structure was used on both servers.
- The Primary database was running in archive log mode.
- Primary host name is set to orcl_primary
- Standby host name is set to orcl_standby
- Instance name is used as ‘orcl’ for both the primary and standby databases.
3.1 Move init.ora (initORCL.ora) file from primary database to standby database.
Copy initORCL.ora from ORACLE_HOME/db_1/dbs/ directory on primary server to the same directory on sandby database (i-e. ORACLE_HOME/db_1/dbs/).
3.2 Move standby backup set from primary database to standby database.
Note: You need to create directory backup_standby under $ORACLE_HOME/dbs/ on standby server before copying backup files.
Copy all files from $ORACLE_HOME/db_1/backup_standby/ directory on primary server to the same directory on sandby server (i-e. $ORACLE_HOME/db_1/backup_standby/).
4. Create TNSNAMES entry on standby database pointing to primary server.
TNSNAMES.ORA file normally exists at $ORACLE_HOME/db_1/network/admin/
Edit this file and add following lines at the end of this file.
Note: Host name should be same as your primary host name. In this case the primary hostname is set as orcl_primary. Also you must be able to ping the primary database from standby database. You may use following command to make sure you primary database is accessible.
$ ping orcl_primary
[oracle@orcl_standby]$ vi ORACLE_HOME/db_1/network/admin/tnsname.ora
ORCL_PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl_primary)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
:wq
[oracle@orcl_standby]$
5. Restore standby database
Before restoring the standby database we need to delete any existing control files and datafiles from standby database locations.
5.1 Remove all datafiles and control files from all locations of standby database.
[oracle@orcl_standby]$ cd /oradata/datafile/
[oracle@orcl_standby]$ rm *
[oracle@orcl_standby]$ cd /oradata/controlfile/
[oracle@orcl_standby]$ rm *
5.2 Shutdown and start database with new spfile in nomount mode.
[oracle@orcl_standby]$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount pfile='/datadr/app/oracle/product/10.2.0/db_1/dbs/initORCL.ora';
SQL> create spfile from pfile='/datadr/app/oracle/product/10.2.0/Db_1/dbs/initORCL.ora';
File created.
SQL> exit
5.3 Shutdown and start database with new spfile in nomount mode.
[oracle@orcl_standby]$ rman
RMAN>
connect target sys/password@ORCL;
connect auxiliary /;
run {
allocate channel d01 device type disk;
allocate auxiliary channel aux01 device type disk;
duplicate target database for standby
dorecover;
}
Note: There are three types of connection RMAN normally deals with. The database connection, the Catalogue DB and the Standby database connection. The RMAN statement ‘connect auxiliary /;’ is used to connect with standby database. This connection uses TNS connection entry (ORCL_PRIMARY) we just configured in step 4 of this article. This connectivity validates to control file sequence number, the log file sequence number and some other necessary checks.
RMAN> exit
[oracle@orcl_standby]$ sqlplus / as sysdba
SQL> shutdown;
SQL> Startup nomount;
SQL> alter database mount standby database;
SQL> alter database open read only;
SQL> alter system register;
SQL> exit
The database is now restored on DR (standby) server.
6. Applying archive logs to the standby database
6.1 Create new archive log files on primary database.
Here we will force the primary database to create some new archive logs.
Connect to primary database first:
[oracle@orcl_primary]$ sqlplus / as sysdba
SQL>alter system archive log current;
Note: Repeat this statement for few times to create more log files. As a result some new archive log files with be created at following location on primary database.
/app/oracle/product/10.2.0/db_1/archivelogs_orcl/
6.2 Move new archive log files to DR (Standby) database.
Copy the new arc files created as result of step 6.1 to the same location of DR (standby database).
/app/oracle/product/10.2.0/db_1/archivelogs_orcl/
6.3 Apply new archive log files to the DR (Standby) Database.
SQL> set autorecovery on;
SQL> recover standby database;
SQL> alter database open read only;
SQL> exit
$
Above recovery will fail if any active sessions are made to DR database. I would write following script to automate recovery. The script can be set in crontab to run every night.
#!/bin/sh
ORACLE_SID='orcl'
export ORACLE_SID
sqlplus / as sysdba << EOF
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database open read only;
set autorecovery on;
recover standby database;
alter database open read only;
exit;
EOF
exit 0
Now both the databases are synchronised upto this point of time. Use following statement to check current log file sequence number on both databases. Both databases must show you the same last sequence number in following query.
[oracle@orcl_primary]$ sqlplus / as sysdba
SQL> select * from V_$LOGHIST order by SEQUENCE#;
[oracle@orcl_standby]$ sqlplus / as sysdba
SQL> select * from V_$LOGHIST order by SEQUENCE#;
GMA
Saturday, 9 October 2010
Simple Backup and Recovery Example / Cloning a database (Oracle10g)
I will discuss very simple but most crucial activity performed by every DBA as his / her routine. This scenario will be applied when your database is crashed and your physical machine is not available. Definitely you need to arrange same database on another machine with up-to-date database.
Following per-requisites must be met before going further with this exercise.
Step 1: Backup the Live database
If your database is already configured to perform full backup every night then please proceed to next section.
Create a backup directory
$ cd /data/app/oracle/product/10.2.0/db_1/dbs/
$ mkdir backup
$ chmod 775 backup
$ rman
RMAN> connect target /
connected to target database: ORCL (DBID=1258382971)
RMAN> run {
allocate channel d01 device type disk
format '/data/app/oracle/product/10.2.0/db_1/dbs/backup/%d_%t_%s_%U.dbf';
set limit channel d01 kbytes 1024000;
backup as compressed backupset database include current controlfile plus archivelog;
}
using target database control file instead of recovery catalog
allocated channel: d01
channel d01: sid=136 devtype=DISK
Starting backup at 08-OCT-10
current log archived
channel d01: starting compressed archive log backupset
channel d01: specifying archive log(s) in backup set
..
..
..
tag=TAG20101008T221834 comment=NONE
channel d01: backup set complete, elapsed time: 00:00:02
Finished backup at 08-OCT-10
released channel: d01
RMAN>exit
Note: Assume this backup was taken at 01:00 A.M,
Before restoring backup to target server we assume that our database was crashed in following patters
Step 2: Recover database on another machine.
Finally, connect to rman target and recover the database.
$ rman
RMAN> connect target /
connected to target database: ORCL (DBID=1258382971)
RMAN> run {
$
$ sqlplus / as sysdba
SQL>Alter database open;
Check the last committed transactions to verify restore operation.
SQL> select * from test_backup_tr;
DATE_IN
---------
09-OCT-10
09-OCT-10
09-OCT-10
SQL>
Following per-requisites must be met before going further with this exercise.
1. Live database will be called as primary database here.
2. Target database / server will be one where you want to recover your backed up database.
3. Primary database is installed with autobackup. The full backup is performed every night.
4. Same software version (Oracle 10g) is installed both the primary and target database / machine.
5. Same directory structure is available on both the primary and target database.
6. Database is running in archive log mode
Step 1: Backup the Live database
If your database is already configured to perform full backup every night then please proceed to next section.
Create a backup directory
$ ORACLE_HOME=/data/app/oracle/product/10.2.0/db_1;export ORACLE_HOME
$ ORACLE_BASE=/data/app/oracle/;export ORACLE_BASE
$ PATH=$ORACLE_HOME/bin:$PATH;export PATH
$ ORACLE_SID='orcl';export ORACLE_SID
$ cd /data/app/oracle/product/10.2.0/db_1/dbs/
$ mkdir backup
$ chmod 775 backup
$ rman
RMAN> connect target /
connected to target database: ORCL (DBID=1258382971)
RMAN> run {
allocate channel d01 device type disk
format '/data/app/oracle/product/10.2.0/db_1/dbs/backup/%d_%t_%s_%U.dbf';
set limit channel d01 kbytes 1024000;
backup as compressed backupset database include current controlfile plus archivelog;
}
using target database control file instead of recovery catalog
allocated channel: d01
channel d01: sid=136 devtype=DISK
Starting backup at 08-OCT-10
current log archived
channel d01: starting compressed archive log backupset
channel d01: specifying archive log(s) in backup set
..
..
..
tag=TAG20101008T221834 comment=NONE
channel d01: backup set complete, elapsed time: 00:00:02
Finished backup at 08-OCT-10
released channel: d01
RMAN>exit
Note: Assume this backup was taken at 01:00 A.M,
Before restoring backup to target server we assume that our database was crashed in following patters
· Nightly full database backup taken at 01:00 A.M
· Users performed some transactions on database at 11:00 A.M
Note : To prove the recovery upto last committed transation, please do the following
on live database after step 1.
SQL> Create table test_backup_tr (transaction_dttm date);
SQL> Insert into test_backup_tr values(sysdate);
SQL> Commit;
· Database crashed at 11:35 A.M
Once you got full backup of the database, you can restore it on any other machine which has suitable/ required hardware installed in hand. It is important that the target / recovery server has same version of oracle software installed alongwith the same directory structure (for simplicity).
The recovery process requires following files to be moved to Recovery Server
1. A good copy of control file.
Run following query to find location(s) of control file(s).
SQL> select name from v$controlfile;
2. Latest copy redo log files
Run following query to find location(s) of redo log files.
SQL> select member from v$logfile;
3. Archive logs.
Run following statement at sql prompt to find location(s) of Archive Logs.
SQL> show parameter log_archive_dest
log_archive_dest string /data/oradata/ORCLLIV_ARC
4. Full database backup files
Remember we did mention backup location as following.
/data/app/oracle/product/10.2.0/db_1/dbs/backup/
5. Latest copy of spfile
In 10g following is the default location of spfile
/data/app/oracle/product/10.2.0/db_1/dbs/
Please make sure you have good copies of all the above mentioned files to recover the target / recovery server. If any of those files are missing and you think you have physically lost them, learn to maintain them on multiple places (multiplexing) and find alternative ways to recover database with old backups. In case of any missing files, this process will not guarantee the last committed transactions to be recovered.
Step 3: Restore database on target / recovery serverSetup environment for the database instance
$ ORACLE_BASE=/data/app/oracle/;export ORACLE_BASE
$ PATH=$ORACLE_HOME/bin:$PATH;export PATH
$ ORACLE_SID='orcl';export ORACLE_SID
First shutdown recovery instance (if running) with abort option.
$ sqlplus / as sysdba
SQL> Shutdown abort;
SQL> exit
Now delete any existing datafile / controlfiles from database location
$ cd /data/oradata/controlfile/
$ rm *
$
$ cd /data/oradata/datafile/
$ rm *
$
Copy the recovery files taken from live database and move them to there same locations on recovery / target database.
Please note that directory structure may be different on your database, in this case please make necessary changes in the directories mentioned below
Move the new controlfile copy(s) to $ /data/oradata/controlfile/
Move the new redo logfiles to to $ /data/oradata/redo/
Move archive log files to /data/oradata/ORCLLIV_ARC
Move spfile to /data/app/oracle/product/10.2.0/db_1/dbs/
Move database backup piece files to /data/app/oracle/product/10.2.0/db_1/dbs/backup/
Connect to sqlplus to startup instance in mount position.
SQL> Startup mount;
SQL> exit
Finally, connect to rman target and recover the database.
$ rman
RMAN> connect target /
connected to target database: ORCL (DBID=1258382971)
RMAN> run {
restore database;
Recover database;
}
RMAN>exit$
$ sqlplus / as sysdba
SQL>Alter database open;
Check the last committed transactions to verify restore operation.
SQL> select * from test_backup_tr;
DATE_IN
---------
09-OCT-10
09-OCT-10
09-OCT-10
SQL>
Subscribe to:
Posts (Atom)