Export / Import : Export / Import are the utilities provided by Oracle to take logical backups online.Logical backup contains the backup of table,schema,tablespace, index or in short all the objects…
Export Import ( expdp/Impdp) : Real Time Cases
Export / Import :
Export / Import is the utilities provided by Oracle to take logical backups online.Logical backup contains the backup of table,schema,tablespace, index or in short all the objects.Unlike RMAN ,it takes backup at object level whereas RMAN takes the backup at block level.
Earlier before 10g ,Export /Import has utilities as exp/imp whereas from 10g onwards , oracle has introduced its advanced version as Data pump Expdp/impdp. There are few differences in both the version which is quite obvious that expdp/impdp(Latest) must have advanced capabilities and agile.
Today we gonna see some real time examples where we would take the backup on different case to case.
There are two basic prerequisites for expdp / impdp :
1. Database directory should be created and user(the one who triggers the expdp/impdp) has to have Read/Write permission on that directory.
eg :
SQL> create or replace directory d1 as ‘/u01/app/d1’;
Directory created.
2. User should have exp_full_database and imp_full_database system privilege.
SQL> GRANT EXP_FULL_DATABASE TO USER;
SQL> GRANT IMP_FULL_DATABASE TO USER;
Real Time cases Export :
1. Take the estimate of export which doesn’t take actual export instead provides the output BLOCKS to be processed for Storage planning.This is really a good practice to examine the storage that would be needed for actual export.
Keyword: estimate_only
[oracle@station35 ~]$ export ORACLE_SID=prod1
[oracle@station35 ~]$ expdp system/oracle schemas=HR estimate_only=y
Export: Release 10.2.0.1.0 – Production on Thursday, 31 January, 2013 14:12:12
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/******** schemas=HR estimate_only=y
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated “HR”.”DEPT” 64 KB
. estimated “HR”.”EMP” 64 KB
. estimated “HR”.”SALGRADE” 64 KB
. estimated “HR”.”BONUS” 0 KB
Total estimation using BLOCKS method: 192 KB
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 14:12:27
2. When you wish to take export of single schema.
expdp system/saturn08 dumpfile=EXPDP_TEST directory=TEST_EXPDP schemas=EXPDP_TEST
3. When you wish to take export of multiple schemas.
expdp system/saturn08 dumpfile=EXPDP_TEST directory=TEST_EXPDP schemas=HR,SCOTT
4. When you wish to split dumpfile creation across two directories due less space on each directory location but summing both them can accommodate the output dump file.
eg :
Directory Creation :
SQL> create or replace directory expdp1 as ‘/u01/app/dp1’;
Directory created.
SQL> create or replace directory expdp2 as ‘/u01/app/dp2’;
Directory created.
Actual Export :
oracle@station35 ~]$ expdp system/oracle dumpfile=d1:HR1%U.dmp,d2:HR2%U.dmp schemas=HR
Export: Release 10.2.0.1.0 – Production on Thursday, 31 January, 2013 14:26:58
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/******** dumpfile=expdp1:HR1%U.dmp,expdp2:HR2%U.dmp schemas=HR
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “HR”.”DEPT” 5.656 KB 4 rows
. . exported “HR”.”EMP” 7.820 KB 14 rows
. . exported “HR”.”SALGRADE” 5.585 KB 5 rows
. . exported “HR”.”BONUS” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/dp1/HR101.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 14:27:13
5. When you wish to take export of only few objects or tables.
Keyword: INCLUDE
eg :
[oracle@olx45 ~]$ expdp system/oracle directory=expdp1 dumpfile=HR.dmp schemas=HR INCLUDE=table:\”IN\(\’EMP\’,\’DEPT\’\)\”
Export: Release 10.2.0.1.0 – Production on Thursday, 31 January, 2013 14:51:04
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/******** directory=expdp1 dumpfile=HR.dmp schemas=HR INCLUDE=table:”IN(‘EMP’,’DEPT’)”
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “HR”.”DEPT” 5.656 KB 4 rows
. . exported “HR”.”EMP” 7.820 KB 14 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/expdp1/HR.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 14:51:09
5. When you wish to take export of only few objects or tables.
Keyword: EXCLUDE
expdp system/saturn08 dumpfile=TEST_EXPDP:EXPDP_TEST1 tables=EXPDP_TEST.TEST_EXP1 exclude=INDEX:\”LIKE\’\%\’\” logfile=export1.log
6. Export Till Restore_point
SQL> create restore point TEST_EXP_rest
2 ;
Restore point created.
SQL> select scn,name from v$restore_point;
SCN NAME
———- ——————————————————————————————————————————–
97383033 TEST_EXP_REST
SQL> insert into EXPDP_TEST.TEST_EXP1 values(‘jjj’,000);
1 row created.
SQL> select * from EXPDP_TEST.TEST_EXP1;
NAME ID
———- ———-
aaa 111
bbb 222
ccc 333
ddd 444
eee 555
fff 666
ggg 777
hhh 888
iii 999
jjj 0
10 rows selected.
expdp system/saturn08 dumpfile=TEST_EXPDP:EXPDP_TEST1 tables=EXPDP_TEST.TEST_EXP1 job_name=exp_job Flashback_scn=97383033
After Import :
SQL> select * from IMPDP_TEST.TEST_EXP1;
NAME ID
———- ———-
aaa 111
bbb 222
ccc 333
ddd 444
eee 555
fff 666
ggg 777
hhh 888
iii 999
9 rows selected.
SQL>
7. When you wish to take export in the manner that each dumpfile should have maxsize limitation.In Such case ,we can pass keyword as Filesize that would limit the dumpfile size creation.
Keyword: FILESIZE
[oracle@olx45 ~]$ expdp system/oracle directory=expdp1 dumpfile=DB.dmp FULL=y FILESIZE=32m LOGFILE=DB.log
Export: Release 10.2.0.1.0 – Production on Thursday, 31 January, 2013 15:19:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “SYSTEM”.”SYS_EXPORT_FULL_03″: system/******** directory=expdp1 dumpfile=DB.dmp FULL=y filesize=33m LOGFILE=DB.log
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32.12 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
————————————-O/p Trim ————————————-
. . exported “SYSTEM”.”SQLPLUS_PRODUCT_PROFILE” 0 KB 0 rows
. . exported “TSMSYS”.”SRS$” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_FULL_03″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_03 is:
/u01/app/expdp1/DB.dmp
Job “SYSTEM”.”SYS_EXPORT_FULL_03″ successfully completed at 15:20:17
8. When you wish to take the export with parallel processing in order to speed up the operation.There is Parallel keyword needs to pass which could take the values upto 2*Cpu_Count(database parameter).
Keyword: PARALLEL
eg :
expdp system/saturn08 Directory =TEST_EXPDP dumpfile=EXPDP_TEST2%u tables=EXPDP_TEST.TEST_EXP1 job_name=exp_job estimate=blocks parallel=5
O/p :
=======
Dump file set for SYSTEM.EXP_JOB is:
/oracle/dbawork/TEST_EXPdpd/EXPDP_TEST201.dmp
/oracle/dbawork/TEST_EXPdpd/EXPDP_TEST202.dmp
9. Export through datapump API.
declare
handle number;
begin
handle := dbms_datapump.open(‘EXPORT’,’SCHEMA’);
dbms_datapump.add_file(handle,’HR3.DMP’,’DP’);
dbms_datapump.metadata_filter(handle,’SCHEMA_EXPR’,’= ”HR”’);
dbms_datapump.set_parallel(handle,4);
dbms_datapump.start_job(handle);
dbms_datapump.detach(handle);
end;
/
10. When you wish to take export two tables from two different schemas.
Keyword : TABLES
Tables that need to export : HR.DEPT,VISH.FINANCE
eg .
expdp system/oracle directory=expdp1 dumpfile=DB.dmp tables=HR.DEPT,VISH.FINANCE
Database Creation using silent mode of Database Configuration Assistant (DBCA)
Oracle Provides lots of alternatives to create database among which there is a very popular and handy tool called database Configuration Assistant (DBCA).DBCA can run in either GUI or silent mode a…
Source: Database Creation using silent mode of Database Configuration Assistant (DBCA)
Physical Standby Database Configuration: Standalone
Physical Standby Database (DR) Configuration: Standalone:
Primary Server : OLX495
Primary Database : RAZZ
Secondary Server : olx494
Secondary Database : RAZZDR
- There are some basic prerequisites parameters need to be set at primary database as follows :
ON OLX495 : Primary
[oracle@olx495 dbawork]$ export ORACLE_SID=RAZZ
[oracle@olx495 dbawork]$ sqlplus sys/saturn08@RAZZ as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 17 21:13:32 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the OLAP, Data Mining and Real Application Testing options
SQL> show parameter db_n
NAME TYPE VALUE
———————————— ———– ——————————
db_name string RAZZ
SQL> show parameter db_unique
NAME TYPE VALUE
———————————— ———– ——————————
db_unique_name string RAZZ
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(RAZZ,RAZZDR)’ scope=both;
System altered.
SQL> show parameter control_files
NAME TYPE VALUE
———————————— ———– ——————————
control_files string /oracle/dbawork/RAZZ_TEST/cont
rol/RAZZ/control01.ctl, /oracl
e/dbawork/RAZZ_TEST/control/RA
ZZ/control02.ctl
SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=/oracle/dbawork/RAZZ_TEST/archive_logs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RAZZ’ scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=RAZZDR LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RAZZDR’ scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
System altered.
SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
System altered.
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
System altered.
SQL> show parameter fal_
NAME TYPE VALUE
———————————— ———– ——————————
fal_client string RAZZ
fal_server string RAZZDR
SQL>
SQL>exit
2 : Take Full backup of Primary database :
[oracle@olx495 backup]$ rman target /
Recovery Manager: Release 11.2.0.3.0 – Production on Mon Feb 17 21:38:30 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RAZZ (DBID=824804085)
RMAN> run
2> {
3> allocate channel t1 device type disk format ‘/oracle/dbawork/RAZZ_TEST/backup/df_%U.bak’;
4> allocate channel t2 device type disk format ‘/oracle/dbawork/RAZZ_TEST/backup/df_%U.bak’;
5> backup database;
6> release channel t1;
7> release channel t2;
8> }
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=145 device type=DISK
allocated channel: t2
channel t2: SID=40 device type=DISK
Starting backup at 17-FEB-14
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00002 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/sysaux01.dbf
channel t1: starting piece 1 at 17-FEB-14
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/system01.dbf
input datafile file number=00004 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/users01.dbf
channel t2: starting piece 1 at 17-FEB-14
channel t1: finished piece 1 at 17-FEB-14
piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4bp0skq1_1_1.bak tag=TAG20140217T214017 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:25
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/example01.dbf
input datafile file number=00003 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/undotbs01.dbf
input datafile file number=00006 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/razz01.dbf
input datafile file number=00007 name=/oracle/dbawork/RAZZ_TEST/oradata/RAZZ/TEST1.dbf
channel t1: starting piece 1 at 17-FEB-14
channel t2: finished piece 1 at 17-FEB-14
piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4cp0skq1_1_1.bak tag=TAG20140217T214017 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:25
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
including current control file in backup set
channel t2: starting piece 1 at 17-FEB-14
channel t1: finished piece 1 at 17-FEB-14
piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4dp0skqq_1_1.bak tag=TAG20140217T214017 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:02
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel t1: starting piece 1 at 17-FEB-14
channel t2: finished piece 1 at 17-FEB-14
piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4ep0skqq_1_1.bak tag=TAG20140217T214017 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:01
channel t1: finished piece 1 at 17-FEB-14
piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4fp0skqs_1_1.bak tag=TAG20140217T214017 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:00
Finished backup at 17-FEB-14
released channel: t1
released channel: t2
RMAN> exit
Recovery Manager complete.
- Create Controlfile and pfile for Standby Database :
[oracle@olx495 backup]$ sqlplus sys/saturn08@RAZZ as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 17 21:41:02 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the OLAP, Data Mining and Real Application Testing options
SQL> alter database create standby controlfile as ‘/oracle/dbawork/RAZZ_TEST/control/RAZZ/RAZZDR_control.ctl’;
Database altered.
SQL> create pfile from spfile;
File created.
- : Points to Do :
- Create password file in both the databases (password must be same), you can copy the password file from prod to DR and rename it in DR site.
after creation of password file, put the tns entries in both the servers.check the connection in both the servers.
Eg :
orapwd file=orapwRAZZ.ora password=saturn08
or
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle entries=5
- Transfer all backup pieces,controlfile and pfile on olx494(Secondary).
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
- Change parameter on init file according to Standby database :
on Glaolx494:
*.db_name=’RAZZ’
*.db_unique_name=’RAZZDR’
*.log_archive_config=’DG_CONFIG=(RAZZDR,RAZZ)’
*.log_archive_dest_1=’LOCATION=/oracle/dbawork/RAZZ_TEST/archive_logs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RAZZDR’
*.log_archive_dest_2=’SERVICE=RAZZ LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RAZZ’
*.fal_client=’RAZZDR’
*.fal_server=’RAZZ’
- Start DR Database as follows :
[oracle@olx494 dbs]$ export ORACLE_SID=RAZZDR
[oracle@olx494 dbs]$ sqlplus “/as sysdba”
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 17 22:30:24 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=’initRAZZDR.ora’ mount;
ORACLE instance started.
Total System Global Area 939495424 bytes
Fixed Size 2233960 bytes
Variable Size 578816408 bytes
Database Buffers 352321536 bytes
Redo Buffers 6123520 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
Create password file in both the databases (password must be same), you can coppy the password file from prod to DR and rename it in DR site.
after creation of password file, put the tns entries in both the servers.check the connection in both the servers.
orapwd file=orapwRAZZ.ora password=saturn08
or
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle entries=5
- Check Connection tns entries :
[oracle@olx494 dbs]$ sqlplus sys/saturn08@RAZZDR as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 17 22:30:59 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
SQL> conn sys/saturn08@RAZZ as sysdba
Connected.
SQL> conn sys/saturn08@RAZZDR as sysdba
Connected.
SQL>exit
- Restore Database using backup pieces :
[oracle@olx494 backup]$ rman target /
Recovery Manager: Release 11.2.0.3.0 – Production on Mon Feb 17 22:41:48 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RAZZ (DBID=824804085, not open)
RMAN> catalog start with ‘/oracle/dbawork/RAZZ_TEST/backup/’;
using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/dbawork/RAZZ_TEST/backup/
no files found to be unknown to the database
RMAN> restore database;
Starting restore at 2014-02-17 22:42:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=216 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/users01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/dbawork/RAZZ_TEST/backup/df_4cp0skq1_1_1.bak
channel ORA_DISK_1: piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4cp0skq1_1_1.bak tag=TAG20140217T214017
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/dbawork/RAZZ_TEST/backup/df_4bp0skq1_1_1.bak
channel ORA_DISK_1: piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4bp0skq1_1_1.bak tag=TAG20140217T214017
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/razz01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /oracle/dbawork/RAZZ_TEST/oradata/RAZZ/TEST1.dbf
channel ORA_DISK_1: reading from backup piece /oracle/dbawork/RAZZ_TEST/backup/df_4dp0skqq_1_1.bak
channel ORA_DISK_1: piece handle=/oracle/dbawork/RAZZ_TEST/backup/df_4dp0skqq_1_1.bak tag=TAG20140217T214017
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 2014-02-17 22:42:45
RMAN> exit
Recovery Manager complete.
[oracle@olx494 backup]$ sqlplus sys/saturn08@RAZZDR as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 17 22:43:07 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/dbawork/RAZZ_TEST/archive_logs
Oldest online log sequence 279
Next log sequence to archive 0
Current log sequence 280
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/dbawork/RAZZ_TEST/archive_logs
Oldest online log sequence 279
Next log sequence to archive 0
Current log sequence 280
Switch logfiles manually on RAZZ:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/dbawork/RAZZ_TEST/archive_logs
Oldest online log sequence 278
Next log sequence to archive 280
Current log sequence 280
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/dbawork/RAZZ_TEST/archive_logs
Oldest online log sequence 284
Next log sequence to archive 286
Current log sequence 286
Now Check logs status on RAZZDR :
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/dbawork/RAZZ_TEST/archive_logs
Oldest online log sequence 284
Next log sequence to archive 0
Current log sequence 286
SQL>
Change Database Characterset
How to change Database Characterset:
There are sometimes when there is a need to change the database charcaterset. This can be done but it requires restart of database in order to make it effective. I have tested this on 11g. Please test it on your sandbox before implementing it on Prod.
Current value: WE8MSWIN1252
Target value: TH8TISASCII
[oracle@olx495 archive_logs]$ export ORACLES_SID=TALLYOAT1
[oracle@olx495 archive_logs]$
[oracle@olx495 archive_logs]$
[oracle@olx495 archive_logs]$
[oracle@olx495 archive_logs]$ sqlplus “/as sysdba”
SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 26 00:33:55 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the OLAP, Data Mining and Real Application Testing options
SQL> SELECT *
FROM V$NLS_PARAMETERS
WHERE PARAMETER = ‘NLS_CHARACTERSET’; 2 3
PARAMETER
—————————————————————-
VALUE
—————————————————————-
NLS_CHARACTERSET
WE8MSWIN1252
SQL>
UPDATE PROPS$ SET VALUE$ = ‘TH8TISASCII’ WHERE NAME = ‘NLS_CHARACTERSET’;SQL>
1 row updated.
SQL> SELECT *
FROM V$NLS_PARAMETERS
WHERE PARAMETER = ‘NLS_CHARACTERSET’; 2 3
PARAMETER
—————————————————————-
VALUE
—————————————————————-
NLS_CHARACTERSET
WE8MSWIN1252
SQL> commit;
Commit complete.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2238099456 bytes
Fixed Size 2230312 bytes
Variable Size 603981784 bytes
Database Buffers 1627389952 bytes
Redo Buffers 4497408 bytes
Database mounted.
Database opened.
SQL> SELECT *
FROM V$NLS_PARAMETERS
WHERE PARAMETER = ‘NLS_CHARACTERSET’; 2 3
PARAMETER
—————————————————————-
VALUE
—————————————————————-
NLS_CHARACTERSET
TH8TISASCII
SQL>
ASM Instance Setup on Standalone Machine-Silent Method
This is the shortest method to create asm instance
Source: ASM Instance Setup on Standalone Machine-Silent Method
ASM Instance Setup on Standalone Machine-Silent Method
Once you installed grid software and all the Disk are formatted from raw disk by System admin. Then you could proceed to create asm instance and map those Disk(s) with ASM instance. There are multiple ways to setup ASM instance however here we are going to see the silent method using asmca. Below is the command needs to run after setting grid home.
Run Below command from bin directory of grid home where asmca binaries are present.Once asmca completes successfully ,ASM instance gets created ,DISK(s) gets mounted and ASM instances gets started.
[oracle@db-test-01 bin]$ export ORACLE_HOME=/u01/grid/oracle/product/11.2.0/grid_1
[oracle@db-test-01 bin]$ cd $ORACLE_HOME/bin
[oracle@db-test-01 bin]$ ./asmca -silent -configureASM \ -sysAsmPassword Password \ -asmsnmpPassword Password \ -diskGroupName DATA \ -diskList ‘/dev/oracleasm/disks/DATA01’ \ -redundancy EXTERNAL \ -au_size 1 \ -diskString ‘/dev/oracleasm/disks/*’
ASM created and started successfully.
Disk Group DATA created successfully.
RMAN backup based duplication with Clone and Source Database on same server.
This Article will illustrate – RMAN backup based duplication with Clone and Source Database on same server.
Note : Oracle has below two type of technique to duplicate database :
- Using Backup Based. : Oracle Duplicates the database using recent backup of source database.
- Active database : Oracle Duplicates database on fly from Source to target.This could hamper network congestion if bandwidth is low while performing across tow servers.This Technique is recommended when database size is low and sufficient bandwidth available.
Here we are going to show the duplicate using First Technique i.e. Using Backup Based.
Details about Databases :
Server : olx495
Source Database : SRCDB
Destination database : CLONEDB
Create Password File :
[oracle@olx495 dbs]$orapwd file=$ORACLE_HOME/dbs/orapwdCLONEDB password=saturn08 entries=10
Configure listener : Make Changes into Listener
[oracle@olx495 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = olx495)(PORT = 1521)))
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = CLONEDB.world)))
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = SRCDB.world)))
)
)
and
(SID_DESC =
(SID_NAME = CLONEDB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC =
(SID_NAME = SRCDB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
Configure TNSNAMES.ora file :
CLONEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = glbolx495)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLONEDB)
)
)
SRCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = glbolx495)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SRCDB)
)
)
As we are duplicating database on same server so in order to avoid datafile/logfile naming conflict,we need to set DB_FILE_NAME_CONVERT & LOG_FILE_NAME_CONVERT.
Create Pfile :
[oracle@olx495 dbs]$ vi initCLONEDB.ora
*.db_name=’CLONEDB’
*.control_files=’/oracle/dbawork/CLONEDB/control/control01.ctl’,’/oracle/dbawork/CLONEDB/control/control02.ctl’,’/oracle/dbawork/CLONEDB/control/control03.ctl’
*.DB_FILE_NAME_CONVERT=(/oracle/dbawork/SRCDB/datafile/,/oracle/dbawork/CLONEDB/datafile/)
*.LOG_FILE_NAME_CONVERT=(/oracle/dbawork/SRCDB/logfile/,/oracle/dbawork/CLONEDB/logfile/)
Connect to Clone database :
[oracle@olx495 dbs]$ export ORACLE_SID=CLONEDB
[oracle@olx495 dbs]$ sqlplus “/as sysdba”
SQL*Plus: Release 11.2.0.3.0 Production on Sun Nov 17 05:56:01 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=’$ORACLE_HOME/dbs/initCLONEDB.ora’;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2227944 bytes
Variable Size 738197784 bytes
Database Buffers 503316480 bytes
Redo Buffers 8921088 bytes
SQL>Exit
Enter into RMAN to start Cloning :
[oracle@olx495 ~]$ export ORACLE_SID=CLONEDB
[oracle@olx495 ~]$ rman target sys/saturn08@SRCDB auxiliary /
Recovery Manager: Release 11.2.0.3.0 – Production on Sun Nov 17 05:57:54 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SRCDB (DBID=465520759)
connected to auxiliary database: CLONEDB (not mounted)
RMAN> duplicate target database to CLONEDB nofilenamecheck;
Starting Duplicate Db at 17-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
sql clone “create spfile from memory”;
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1252663296 bytes
Fixed Size 2227944 bytes
Variable Size 754975000 bytes
Database Buffers 486539264 bytes
Redo Buffers 8921088 bytes
contents of Memory Script:
{
sql clone “alter system set db_name =
”SRCDB” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”CLONEDB” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ”SRCDB” comment= ”Modified by RMAN duplicate” scope=spfile
sql statement: alter system set db_unique_name = ”CLONEDB” comment= ”Modified by RMAN duplicate” scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1252663296 bytes
Fixed Size 2227944 bytes
Variable Size 754975000 bytes
Database Buffers 486539264 bytes
Redo Buffers 8921088 bytes
Starting restore at 17-NOV-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oracle/dbawork/SRCDB/backup/df_08op5ie2_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/oracle/dbawork/SRCDB/backup/df_08op5ie2_1_1.bkp tag=TAG20131117T044435
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/dbawork/CLONEDB/control/control01.ctl
output file name=/oracle/dbawork/CLONEDB/control/control02.ctl
output file name=/oracle/dbawork/CLONEDB/control/control03.ctl
Finished restore at 17-NOV-13
database mounted
contents of Memory Script:
{
set until scn 1090325;
set newname for datafile 1 to
“/oracle/dbawork/CLONEDB/datafile/system01.dbf”;
set newname for datafile 2 to
“/oracle/dbawork/CLONEDB/datafile/sysaux01.dbf”;
set newname for datafile 3 to
“/oracle/dbawork/CLONEDB/datafile/undotbs01.dbf”;
set newname for datafile 4 to
“/oracle/dbawork/CLONEDB/datafile/users01.dbf”;
set newname for datafile 5 to
“/oracle/dbawork/CLONEDB/datafile/example01.dbf”;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 17-NOV-13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oracle/dbawork/CLONEDB/datafile/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oracle/dbawork/CLONEDB/datafile/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/dbawork/CLONEDB/datafile/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oracle/dbawork/CLONEDB/datafile/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oracle/dbawork/CLONEDB/datafile/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oracle/dbawork/SRCDB/backup/df_07op5idj_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/oracle/dbawork/SRCDB/backup/df_07op5idj_1_1.bkp tag=TAG20131117T044435
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 17-NOV-13
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=831707968 file name=/oracle/dbawork/CLONEDB/datafile/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=831707968 file name=/oracle/dbawork/CLONEDB/datafile/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=831707968 file name=/oracle/dbawork/CLONEDB/datafile/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=831707968 file name=/oracle/dbawork/CLONEDB/datafile/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=831707968 file name=/oracle/dbawork/CLONEDB/datafile/example01.dbf
contents of Memory Script:
{
set until scn 1090325;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 17-NOV-13
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 8 is already on disk as file /oracle/dbawork/SRCDB/arch/1_8_831680323.dbf
archived log file name=/oracle/dbawork/SRCDB/arch/1_8_831680323.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-NOV-13
Oracle instance started
Total System Global Area 1252663296 bytes
Fixed Size 2227944 bytes
Variable Size 754975000 bytes
Database Buffers 486539264 bytes
Redo Buffers 8921088 bytes
contents of Memory Script:
{
sql clone “alter system set db_name =
”CLONEDB” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ”CLONEDB” comment= ”Reset to original value by RMAN” scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1252663296 bytes
Fixed Size 2227944 bytes
Variable Size 754975000 bytes
Database Buffers 486539264 bytes
Redo Buffers 8921088 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “CLONEDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/oracle/dbawork/CLONEDB/logfile/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/oracle/dbawork/CLONEDB/logfile/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/oracle/dbawork/CLONEDB/logfile/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/oracle/dbawork/CLONEDB/datafile/system01.dbf’
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
“/u01/app/oracle/oradata/SRCDB/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/oracle/dbawork/CLONEDB/datafile/sysaux01.dbf”,
“/oracle/dbawork/CLONEDB/datafile/undotbs01.dbf”,
“/oracle/dbawork/CLONEDB/datafile/users01.dbf”,
“/oracle/dbawork/CLONEDB/datafile/example01.dbf”;
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/SRCDB/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/oracle/dbawork/CLONEDB/datafile/sysaux01.dbf RECID=1 STAMP=831707978
cataloged datafile copy
datafile copy file name=/oracle/dbawork/CLONEDB/datafile/undotbs01.dbf RECID=2 STAMP=831707978
cataloged datafile copy
datafile copy file name=/oracle/dbawork/CLONEDB/datafile/users01.dbf RECID=3 STAMP=831707978
cataloged datafile copy
datafile copy file name=/oracle/dbawork/CLONEDB/datafile/example01.dbf RECID=4 STAMP=831707978
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=831707978 file name=/oracle/dbawork/CLONEDB/datafile/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=831707978 file name=/oracle/dbawork/CLONEDB/datafile/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=831707978 file name=/oracle/dbawork/CLONEDB/datafile/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=831707978 file name=/oracle/dbawork/CLONEDB/datafile/example01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 17-NOV-13
RMAN>Exit
[oracle@olx495 dbs]$ export ORACLE_SID=CLONEDB
[oracle@olx495 dbs]$ sqlplus “/as sysdba”
SQL*Plus: Release 11.2.0.3.0 Production on Sun Nov 17 06:00:44 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the OLAP, Data Mining and Real Application Testing options
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
—————- ————
CLONEDB OPEN
SQL>
Hurray ,The Database has been cloned with very less effort as compared to Manual Backup Restore.
Online Recovery of Lost datafile
Database is online and datafile is damaged then prepare the database for recovery by making sure it is open and taking the datafile in offline mode the recover it using recent backup.here the whole database would be online except the damaged datafile. So all the objects associated on that datafile won’t be accessible till it’s get recovered completely.
I have removed one datafile (u01/app/oracle/oradata/TEST1/ts_corrupt01.dbf) to test this scenario…..
here we go…..
SQL> select FILE#,STATUS,NAME,ONLINE_TIME,FIRST_NONLOGGED_SCN,CHECKPOINT_CHANGE# from v$datafile;
FILE# STATUS
—– ——-
NAME
———————————————————————————————————————————————————————————————-
ONLINE_TI FIRST_NONLOGGED_SCN CHECKPOINT_CHANGE#
——— ——————- ——————
1 SYSTEM
/u01/app/oracle/oradata/TEST1/system01.dbf
19-NOV-13 0 5395071
2 ONLINE
/u01/app/oracle/oradata/TEST1/sysaux01.dbf
19-NOV-13 0 5395071
3 ONLINE
/u01/app/oracle/oradata/TEST1/undotbs01.dbf
19-NOV-13 0 5395071
4 ONLINE
/u01/app/oracle/oradata/TEST1/users01.dbf
19-NOV-13 0 5395071
5 ONLINE
/u01/app/oracle/oradata/TEST1/example01.dbf
19-NOV-13 0 5395071
6 ONLINE
/u01/app/oracle/oradata/TEST1/sqlldr.dbf
19-NOV-13 0 5395071
8 ONLINE
/u01/app/oracle/oradata/TEST1/ts_corrupt01.dbf
19-NOV-13 0 5395071
7 rows selected.
Recovery Manager complete.
[oracle@olx143 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 – Production on Tue Nov 19 23:43:20 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST4 (DBID=1012901700)
RMAN> sql ‘alter database datafile 8 offline’;
using target database control file instead of recovery catalog
sql statement: alter database datafile 8 offline.
SQL> select FILE#,STATUS,NAME,ONLINE_TIME,FIRST_NONLOGGED_SCN,CHECKPOINT_CHANGE# from v$datafile;
FILE# STATUS
—– ——-
NAME
———————————————————————————————————————————————————————————————-
ONLINE_TI FIRST_NONLOGGED_SCN CHECKPOINT_CHANGE#
——— ——————- ——————
1 SYSTEM
/u01/app/oracle/oradata/TEST1/system01.dbf
19-NOV-13 0 5395071
2 ONLINE
/u01/app/oracle/oradata/TEST1/sysaux01.dbf
19-NOV-13 0 5395071
3 ONLINE
/u01/app/oracle/oradata/TEST1/undotbs01.dbf
19-NOV-13 0 5395071
4 ONLINE
/u01/app/oracle/oradata/TEST1/users01.dbf
19-NOV-13 0 5395071
5 ONLINE
/u01/app/oracle/oradata/TEST1/example01.dbf
19-NOV-13 0 5395071
6 ONLINE
/u01/app/oracle/oradata/TEST1/sqlldr.dbf
19-NOV-13 0 5395071
8 RECOVER
/u01/app/oracle/oradata/TEST1/ts_corrupt01.dbf
19-NOV-13 0 5395071
RMAN> restore datafile 8;
Starting restore at 19-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/TEST1/ts_corrupt01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/dbawork/BACKUP_TEST4/gdopcrfj_1_1.BUS
channel ORA_DISK_1: piece handle=/oracle/dbawork/BACKUP_TEST4/gdopcrfj_1_1.BUS tag=TAG20131119T230210
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 19-NOV-13
RMAN> recover datafile 8;
Starting recover at 19-NOV-13
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-NOV-13
RMAN> sql ‘alter database datafile 8 online’;
sql statement: alter database datafile 8 online
RMAN>exit
SQL> select FILE#,STATUS,NAME,ONLINE_TIME,FIRST_NONLOGGED_SCN,CHECKPOINT_CHANGE# from v$datafile;
FILE# STATUS
—– ——-
NAME
———————————————————————————————————————————————————————————————-
ONLINE_TI FIRST_NONLOGGED_SCN CHECKPOINT_CHANGE#
——— ——————- ——————
1 SYSTEM
/u01/app/oracle/oradata/TEST1/system01.dbf
19-NOV-13 0 5395071
2 ONLINE
/u01/app/oracle/oradata/TEST1/sysaux01.dbf
19-NOV-13 0 5395071
3 ONLINE
/u01/app/oracle/oradata/TEST1/undotbs01.dbf
19-NOV-13 0 5395071
4 ONLINE
/u01/app/oracle/oradata/TEST1/users01.dbf
19-NOV-13 0 5395071
5 ONLINE
/u01/app/oracle/oradata/TEST1/example01.dbf
19-NOV-13 0 5395071
6 ONLINE
/u01/app/oracle/oradata/TEST1/sqlldr.dbf
19-NOV-13 0 5395071
8 ONLINE
/u01/app/oracle/oradata/TEST1/ts_corrupt01.dbf
19-NOV-13 0 5396194
7 rows selected.
SQL> select * from v$recover_file;
no rows selected
SQL> alter system checkpoint;
System altered.
SQL> select FILE#,STATUS,NAME,ONLINE_TIME,FIRST_NONLOGGED_SCN,CHECKPOINT_CHANGE# from v$datafile;
FILE# STATUS
—– ——-
NAME
———————————————————————————————————————————————————————————————-
ONLINE_TI FIRST_NONLOGGED_SCN CHECKPOINT_CHANGE#
——— ——————- ——————
1 SYSTEM
/u01/app/oracle/oradata/TEST1/system01.dbf
19-NOV-13 0 5396253
2 ONLINE
/u01/app/oracle/oradata/TEST1/sysaux01.dbf
19-NOV-13 0 5396253
3 ONLINE
/u01/app/oracle/oradata/TEST1/undotbs01.dbf
19-NOV-13 0 5396253
4 ONLINE
/u01/app/oracle/oradata/TEST1/users01.dbf
19-NOV-13 0 5396253
5 ONLINE
/u01/app/oracle/oradata/TEST1/example01.dbf
19-NOV-13 0 5396253
6 ONLINE
/u01/app/oracle/oradata/TEST1/sqlldr.dbf
19-NOV-13 0 5396253
8 ONLINE
/u01/app/oracle/oradata/TEST1/ts_corrupt01.dbf
19-NOV-13 0 5396253
7 rows selected.
SQL>
/* If damaged datafile is preventing you to open database and that datafile is not necessary then to open database with all remaining datafile, you can make that particular datafile offline at mount state of database and then alter database to open safely with all remaining datafile. */
Managing Tablespace/datafiles/tables (Tweaks)
Create Tablespace :
===================
CREATE TABLESPACE test01
DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 100M
UNIFORM;
/* Default Uniform size will take 1Mb and Alternative to uniqform is AUTOALLOCATE */
CREATE TABLESPACE test01
DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 100M
UNIFORM SIZE 5M;
CREATE TABLESPACE test01
DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 100M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
CREATE TABLESPACE test01 DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
/* Alternative to EXTENT is LOCAL */
Drop Tablespace :
=================
DROP TABLESPACE test01; /* This will drop the tablespace only if it is empty */
DROP TABLESPACE test01 INCLUDING CONTENTS;
/* This will drop the tablespace even if it is not empty. But the datafiles will not be deleted you have to use operating system command to delete the files. But if you include datafiles keyword then, the associated datafiles will also be deleted from the disk. */
DROP TABLESPACE test01 INCLUDING CONTENTS AND DATAFILES;
/* For reverential integrity constraints to drop */
===============================================
DROP TABLESPACE test01 CASCADE CONSTRAINTS;
Add datafile :
=============
ALTER TABLESPACE test01 ADD DATAFILE ‘/finance10/app/oracle/finance/test01.dbf’ SIZE 1000M;
Resize datafile:
==============
ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.dbf’ RESIZE 500m;
Take Offline/Online Datafile :
==============================
ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.dbf’ OFFLINE;
ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.dbf’ ONLINE;
/* Note: You can’t take individual datafiles offline it the database is running in NOARCHIVELOG mode. If the datafile has become corrupt or missing when the database is running in NOARCHIVELOG mode then you can only drop it by giving the following command:
ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.dbf’ OFFLINE DROP;
*/
Autoextent :
==========
ALTER TABLESPACE data01
ADD DATAFILE ‘/finance10/oradata/data01.dbf’ SIZE 200M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 1000M;
Rename Tablespace :
====================
SQL> ALTER TABLESPACE test01 RENAME TO test02;
Tablespace altered.
SQL>
You can rename both permanent and temporary tablespaces, but there are a few restrictions:
• You can’t rename the System and Sysaux tablespaces.
• The tablespace being renamed must have all its data files online.
• If the tablespace is read-only, renaming it doesn’t update the file headers of its data files.
Rename Datafile :
================
Sometimes, you may need to rename a data file. The process for this is straightforward:
1. Take the data file offline by taking its tablespace offline. Use the following command:
SQL> ALTER TABLESPACE test01 OFFLINE NORMAL;
Tablespace altered.
SQL>
2. Rename the file using an operating system utility such as cp or mv in UNIX, or copy in
Windows.
$ cp /u01/app/oracle/test01.dbf /u02/app/oracle/test01.dbf
3. Rename the data file in the database by using the following command:
SQL> ALTER TABLESPACE test01
2 RENAME DATAFILE
3 ‘/u01/app/oracle/test01.dbf’
4 TO
5* ‘/u02/app/oracle/test01.dbf’;
Tablespace altered.
SQL>
Read or Read write mode:
===================
SQL> ALTER TABLESPACE test01 READ ONLY;
If you want to make this read-only tablespace writable again, you can use the following
command:
SQL> ALTER TABLESPACE test01 READ WRITE;
Oflline /Online : Except System, all can be turn to offline.
=========================================
ALTER TABLESPACE index_01 OFFLINE NORMAL;
ALTER TABLESPACE index_01 ONLINE;
Temporary tabelspace :
=================
CREATE TEMPORARY TABLESPACE temp_demo TEMPFILE ‘temp01.dbf’ SIZE 500M AUTOEXTEND ON;
CREATE TEMPORARY TABLESPACE temp_demo TEMPFILE ‘temp01.dbf’ SIZE 500M extent management local uniform size 5M;
The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The AUTOALLOCATE clause is not allowed for temporary tablespaces like as for user tablespaces.
Resize Temporaray tablepace:
=====================
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/prod1/temp01.dbf’ RESIZE 50M;
Drop Temp Tablespace:
==================
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/prod1/temp01.dbf’ DROP INCLUDING DATAFILES;
Shrink Temporary Tablespace :
=============================
SQL> alter tablespace temp shrink space;
SQL> alter tablespace temp shrink space keep 100m;
SQL> alter tablespace temp shrink tempfile ‘/u01/app/oracle/oradata/or11/temp01.dbf’;
SQL> alter tablespace temp shrink space tempfile ‘/u01/app/oracle/oradata/prod1/temp02.dbf’ keep 100m;
Imp views:
==========
V$SORT_SEGMENT and V$TEMPSEG_USAGE
Temporary Tablespace Groups:
=======================
When you assign the first temporary tablespace to a tablespace group, you automatically create the temporary tablespace group.
desc dba_tablespace_groups
You can also use the DBA_USERS view to find out which temporary tablespaces or temporary
tablespace groups are assigned to each user….
CREATE TEMPORARY TABLESPACE temp01 TEMPFILE ‘/u01/oracle/oradata/temp01_01.dbf’ SIZE 500M TABLESPACE GROUP tmpgrp1;
ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp1
The preceding statement will cause Oracle to create a new group named tmpgrp1,
No group:
========
SQL> CREATE TEMPORARY TABLESPACE temp02
TEMPFILE ‘/u01/oracle/oradata/temp02_01.dbf’ SIZE 500M
TABLESPACE GROUP ”;
SQL> CREATE TEMPORARY TABLESPACE temp03
TEMPFILE ‘/u01/oracle/oradata/temp03_01.dbf’ SIZE 500M;
Tablespace created.
Setting a Group as the Default Temporary Tablespace for the Database:
===================================================
SQL> ALTER TABLESPACE DEFAULT TEMPORARY TABLESPACE tmpgrp1;
Using Temporary Tablespace Groups When You Create and Alter Users :
=================================================================
When you create new users, you can assign them to a temporary tablespace group instead of to the usual single temporary tablespace. Here’s an example:
SQL> CREATE USER salapati IDENTIFIED BY sammyy1
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE tmpgrp1;
User created.
SQL>
Bigfile Tablespaces:
==============
Creating Bigfile Tablespaces:
CREATE BIGFILE TABLESPACE bigtbs_01 DATAFILE ‘/u01/oracle/data/bigtbs_01.dbf’ SIZE 100GB;
Changing the Default Tablespace Type:
============================
SQL> ALTER TABLESPACE SET DEFAULT BIGFILE TABLESPACE;
Altering a Bigfile Tablespace:
=====================
SQL> ALTER TABLESPACE bigtbs RESIZE 120G;
SQL> ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
deallocate unused space from table:
==================================
SQL> ALTER TABLE persons DEALLOCATE UNUSED;
To encrypt any colum of table : just use keyword as “ENCRYPT”.
=============================================
ssn NUMBER(9) ENCRYPT,where ssn is the column name.
Adding a Column to a Table:
====================
ALTER TABLE emp ADD (retired char(1));
Dropping a Column from a Table:
========================
SQL> ALTER TABLE emp DROP (retired);
Table altered.
For lagrge tables, can set the column as unused that will not be visible to table,views or constraints:
==============================================================
SQL> ALTER TABLE emp SET UNUSED (hiredate, mgr);
so during maintainence window we can drop all unused coloumns :
SQL> ALTER TABLE emp DROP UNUSED COLUMNS;
Renaming a Table Column:
========================
SQL> ALTER TABLE emp RENAME COLUMN retired TO non_active;
Renaming a Table:
=============
SQL> ALTER TABLE emp RENAME TO emp;
Removing All the Data from a Table:
==========================
TRUNCATE TABLE test; : DDL Comand => No Rollback
DELETE * FROM TABLE; : DML Command => Can Rollback
Creating a New Table with the CTAS Option:
=================================
SQL> CREATE TABLE emp_new AS SELECT * FROM emp;
Table created.
PARALLEL and NOLOGGING to speed up data loading into table:
=================================================
SQL> CREATE TABLE employee_new AS SELECT * FROM employees PARALLEL DEGREE 4,NOLOGGING;
Table created.
For more speed move table from one tablespace to other tablespace:
================================================
SQL> ALTER TABLE employee MOVE new_tablespace;
/* When you move a table, the ROWIDs of the rows change, thus making the indexes on the table unusable. You must either re-create the indexes or rebuild them after you move the table. */
Dropping Tables:
===============
SQL> drop table TEST1.EMP_BONUS_BKP1;
Table dropped.
Elapsed: 00:00:00.02
SQL> select count(*) from TEST1.EMP_BONUS_BKP1;
select count(*) from TEST1.EMP_BONUS_BKP1
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.00
SQL> flashback table TEST1.EMP_BONUS_BKP1 to before drop;
Flashback complete.
Elapsed: 00:00:00.01
SQL> select count(*) from TEST1.EMP_BONUS_BKP1;
COUNT(*)
———-
2
Elapsed: 00:00:00.02
SQL> drop table TEST1.EMP_BONUS_BKP1 purge;
Table dropped.
Elapsed: 00:00:00.87
SQL> flashback table TEST1.EMP_BONUS_BKP1 to before drop;
flashback table TEST1.EMP_BONUS_BKP1 to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
Elapsed: 00:00:00.01
SQL>
Read-Only Tables :
==================
In earlier releases of the Oracle database, you could have read-only tablespaces but not readonly
tables. One of the more useful but simple to implement new features in Oracle Database
11g is the read-only table feature. Simply specify the clause read only in the alter table statement
as shown in the following example, where we first create a normal read-write table and
then convert that into a read-only table:
SQL> create table test1 (name varchar2(30));
Table created.
SQL> alter table test1 read only;
Table altered.
SQL>
The new column READ_ONLY in the DBA_TABLES view shows whether a column has readonly
status. You can return a read-only table to a read-write status by using the read write
clause in the alter table statement, as shown in the following example:
SQL> alter table test1 read write;
Table altered.
SQL>
Oracle Database with Vishal 