awk: Find & Replace

1. Single file - Replaces Oracle with Naikh
awk '{gsub("Oracle","Naikh",$0); print > FILENAME }' *.txt

2. Multiple Files - Replaces Oracle with Naikh
awk '{gsub("Oracle","Naikh",$0); print > FILENAME }' *.txt

3. All the file which contains a pattern
awk '{gsub("Oracle","Naikh",$0); print > FILENAME }' `find . exec grep -l "Oracle" {} \;

Note: Below command will not work
find . exec grep -l "Oracle" {} \; awk '{gsub("Oracle","Naikh",$0); print > FILENAME }'

Transportable TBS - Part 2

Assumptions
* Tablespace name: TTBS
* Source platform: Linux (32 bit)
* Target platform: Solaris (64 bit)

To Identify the endian format
select d.platform_name, endian_format
from v$transfortable_platform tp, v$database d
where tp.platform_name=d.platform_name;

List of all platform and respective endian format
select * from v$transfortable_platform
order by platform _id;


Source Machine

1. check tablespace is transprtable
a. execute this procedure
exec dbms_tts.transport_set_check=('ttbs',true);

b. check for any violations
select * from transport_set_violations;

2. Make tablespace read only
alter tablspace ttbs read only;

3. Identify the datafiles
select file_name
from dba_data_files
where tablespace_name='TTBS';

4. Export tablespace's metadata
expdp directory=dump1
dumpfile=expdp_ttbs.dmp
transportable_tablespace=ttbs
logfile=expdp_ttbs.log
job_name=j1

5. Copy datafile and export dump file to target machine
cp /home/oracle/dba01/ttbs1.dbf /temp/ttbs1.dbf cp /home/oracle/dpump1/expdp_ttbs.dmp /temp/expdp_ttbs.dmp

6. Make the tablespace read write
alter tablespace ttbs read write;

Target Machine

1. Convert the datafile
rman> convert datafile '/temp/ttbs1.dbf'
to platform 'Solaris [tm] (64-bit)'
from platform 'Linux (32-bit)'
db_file_name_convert="/temp/","/home/oracle/testdb/ttbs1.dbf";

2. Import metadata
impdp directory=dump2
dumpfile=expdp_ttbs.dmp
transport_datafiles=/home/oracle/testdb/ttbs1.dbf
logfile=impdp_ttbs.log
job_name=j2

3. Make tablespace read write
alter tablespace ttbs read write;

Transportable TBS - Part 1

Assumptions
* Tablespace name=TTBS
* Same platform (source and target)

Source Machine
1. check tablespace is transprtable
a. execute this procedure
exec dbms_tts.transport_set_check=('ttbs',true);

b. check for any violations
select * from transport_set_violations;

2. Make tablespace read only
alter tablspace ttbs read only;

3. Identify the datafiles
select file_name
from dba_data_files
where tablespace_name='TTBS';

4. Export tablespace's metadata
expdp directory=dump1
dumpfile=expdp_ttbs.dmp
transportable_tablespace=ttbs
logfile=expdp_ttbs.log
job_name=j1

5. Copy datafile and export dump file to new location cp /home/oracle/dba01/ttbs1.dbf /temp/ttbs1.dbf cp /home/oracle/dpump1/expdp_ttbs.dmp /temp/expdp_ttbs.dmp

6. Make the tablespace read write
alter tablespace ttbs read write;

Target Machine

1. Import metadata
impdp directory=dump2
dumpfile=expdp_ttbs.dmp
transport_datafiles=/temp/ttbs1.dbf
logfile=impdp_ttbs.log
job_name=j2

2. Make tablespace read write
alter tablespace ttbs read write;

Useful SQL statements

1. To create a script to copy all the datafiles to new location:

select 'cp 'name' /newpath/'substr(name,instr(name,'/',-1,1)+1))' &'

from v$database;

2. To create a script to copy all the datafiles, controlfiles and redolog files to new location:

select 'cp 'name'/new_path/'
substr(name,instr(name,'/',-1,1)+1))' &'
from (
select name from v$datafile
union all
select name from v$controlfile
union all
select member from v$logfile
)

3. To get the sql statements to Rename all datafiles and redolog files:

select 'alter database rename file '''name ''' to
''/new_path/'substr(name,instr(name,'/',-1,1)+1)''';'
from (
select name from v$datafile
union all
select member from v$logfile
)

4. Copy datafile

select 'cp ' file_name

decode(substr(file_name,1,instr(file_name,'/',-1,1)),

'/Old_path1/','/New_path1/',

'/Old_path2/','/New_path2/'

)substr(file_name,instr(file_name,'/',-1,1)+1)

' &' file_name

from dba_data_files;

5. Copy redolog file

select 'cp ' member ' '

decode(substr(member,1,instr(member,'/',-1,1)),
'/Old_path1/','/New_path1/',
'/Old_path2/','/New_path2/'
)substr(member,instr(member,'/',-1,1)+1)' &' member

from v$logfile;

6. Rename data file

select ' Alter database rename file '''file_name ''' to '''

decode(substr(file_name,1,instr(file_name,'/',-1,1)),
'/Old_path1/','/New_path1/',
'/Old_path2/','/New_path2/'
)substr(file_name,instr(file_name,'/',-1,1)+1) ' ;' file_name
from dba_data_files;

7. Rename redolog file

select 'alter database rename file ''' member ''' to '''
decode(substr(member,1,instr(member,'/',-1,1)),

'/Old_path1/','/New_path1/'

,'/Old_path2/','/New_path2/')

substr(member,instr(member,'/',-1,1)+1) ''' ;' member
from v$logfile;

8. Datafile Offline drop

select 'alter database datafile 'file_id' offline drop;'

from dba_data_files;

Managing Standby Database

Start the standby database

startup nomount;

alter database mount standby database;

alter database recover managed standby database disconnect;


Shutdown the standby database

alter database recover managed standby database cancel;

shutdown immediate;


Registering the archive logs manualy

alter database register or replace logfile '/opt/oracle/archive/oradb_1_1234_48540.arc';


Opening standby database in READ ONLY mode

alter database recover managed standby database cancel;

alter database open read only;


From READ ONLY mode to Managed recovery
a. terminate all the active user sessions on the standby database

b.
alter database recover managed standby database disconnect;