Many years ago ,when I was a child ..
One of my customers had a very old system solaris 5.6 and oralce 8.0.5.0.0
Our aim was to move data to a newer IBM P5 server ,So the only way seems exp/imp .
When I tried to get a full export dump file size exceed 2gb limit and export terminated.
When searched metalink I learned that this old version was not supporting files bigger then 2 gb.
multiple export files and filesize parameters were not avaliable in this version.So a trciky way came handy.You can check metalink note
Subject: SCRIPT TO EXPORT USING UNIX PIPES Doc ID: 1014083.102
Simply
1- create a named pipe
2- use compress command in background to work when pipe is invoked
3- start export utulity using pipe as dump destination
4- You now have a compressed file , can overcome 2 gb filesize limit.!!
cd /VAT13/ExpImp/
/usr/sbin/mknod /VAT13/ExpImp/FullExpNP.dmp p ## named pipe creation
nohup compress < /VAT13/ExpImp/FullExpNP.dmp > /VAT13/ExpImp/FullExpNP`date '+%d%m%y'`.dmp.Z &
nohup exp sys/oracle8 full=y FILE=/VAT13/ExpImp/FullExpNP.dmp log=/VAT13/ExpImp/hede.log 2> /VAT13/ExpImp/FullExpNP`date '+%d%m%y'`.log&
Take care you will not a file named /VAT13/ExpImp/FullExpNP.dmp it is only a pipe,
you will have dump files in format /VAT13/ExpImp/FullExpNP06032009.dmp
And pipe's size will not get bigger.
DONE.
2009/03/06
2009/02/21
Export without typing password
Sometimes cronjobs help us to automate routine tasks like export.
But these text based script files can include passwords which is an uncool sutiation.
One solution is to add cronjobs to dba groups' users crontabs. guess what :-) oracle
Here is an example without typing passwords.
exp \'/ as sysdba\' full=y direct=y file=fullnopwd.dmp log=fullnopwd.log
But these text based script files can include passwords which is an uncool sutiation.
One solution is to add cronjobs to dba groups' users crontabs. guess what :-) oracle
Here is an example without typing passwords.
exp \'/ as sysdba\' full=y direct=y file=fullnopwd.dmp log=fullnopwd.log
2009/01/28
How To Change SID of a 10g Database ?
In this article I will change SID by using file copy method;
A remote host or same host will be used as destination host.
there are alternate ways like using rman ; read backup and recovery guide for further information;
I prefer this method for databases using filesystems as datafile storage area; because if rman is used a backup and restore operation will be required.
For Changing SID a tool NID can be prefered.
startup mount ;
nid TARGET=sys "/ as sysdba " DBNAME=SID9 SETNAME=YES
SETNAME=YES parameter keeps db id not changed, so if a dataguard was set it can continue applying .
it is simple and easy to use but a few times I needed deep dive :)
========================================================
CURRENT SID= SID8
new SID= SID9
10 backup control file to trace (user dump destination ); we will re-create it;
a new create control file script will be created in path "user_dump_dest"
SQL> alter database backup controlfile to trace;
SQL> show parameter user_dump_dest;
[oracle@tabya ~]$ cd /oracle/admin/SID8/udump
-rw-r----- 1 oracle dba 6.5K 2009-01-31 13:44 sid8_ora_4059.trc --> this is the file created ..!!
[oracle@tabya udump]$ cp sid8_ora_4059.trc /home/oracle/createControl_pre.txt
20 cold copy; shutdown listener and instance.
copy all datafiles;tempfiles;redolog files,
to get the list of files;
select name from v$datafile; --copy all of them
select member from v$logfile; -- recommended to copy all of them
select name from v$controlfile; -- do not copy them we will cretae them.
select name from v$tempfile; -- do not copy them we will create them
cp -R /oracle/oradata/SID8 /oracle/oradata/SID9
or
mv /oracle/oradata/SID8 /oracle/oradata/SID9
30 Change , edit user enviorement;
some evn variables need to be set like ORACLE_HOME,ORACLE_BASE,ORACLE_SID
you can modify .profile or create a new profile let'say .profile.SID2
cp .bash_profile .profile.SID9
vi .profile.SID9
ORACLE_SID=SID8; export ORACLE_SID ------>> ORACLE_SID=SID9; export ORACLE_SID
40 init file spfile or pfile.
parameter file must be valid before re-creating control file..
create a pfile from source spfile; change the SID to new name.
note: you can use
cd $ORACLE_HOME/dbs
cat spfileSID8.ora > pfileEditSID9.ora
vi pfileEditSID9.ora
#### DEFAULT FILE
*.audit_file_dest='/oracle/admin/SID8/adump'
*.background_dump_dest='/oracle/admin/SID8/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oracle/oradata/SID8/control01.ctl','/oracle/oradata/SID8/control02.ctl','/oracle/oradata/SID8/control03.ctl'
*.core_dump_dest='/oracle/admin/SID8/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='SID8'
*.db_recovery_file_dest='/oracle/fra'
*.db_recovery_file_dest_size=21118320640
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SID8XDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=83886080
*.processes=70
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=82
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/SID8/udump'
#### CHANGE THESE PARAMETER
*.db_name='SID9'
*.audit_file_dest='/oracle/admin/SID9/adump'
*.background_dump_dest='/oracle/admin/SID9/bdump'
*.core_dump_dest='/oracle/admin/SID9/cdump'
*.user_dump_dest='/oracle/admin/SID9/udump'
*.control_files='/oracle/oradata/SID9/control01.ctl','/oracle/oradata/SID9/control02.ctl','/oracle/oradata/SID9/control03.ctl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SID9XDB)'
##
*.compatible='10.2.0.3.0'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest='/oracle/fra'
*.db_recovery_file_dest_size=21118320640
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=83886080
*.processes=70
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=82
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
50 I will edit file created in step 10 by changing and setting same parameters.
This file will include 2 sets ;
Set #1. NORESETLOGS case
Set #2. RESETLOGS case
I will use RESETLOGS case because we will have clean shutdown and no need to recovery.
set SID to new in enviorement;
change datafile paths no newly copied destination
cp /home/oracle/createControl_pre.txt /home/oracle/createControl_ok.txt
vi /home/oracle/createControl_ok.txt
60 listener.ora ; tnsnames.ora
optional you can configure listener to register it or leave it to database itself.
70 create abcu dump destinations change in pfileEdit
create dump destinations; you must have changed them in pfile.. important.
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump
## CREATE DUMP FOLDERS
mkdir -p /oracle/admin/SID9/adump
mkdir -p /oracle/admin/SID9/bdump
mkdir -p /oracle/admin/SID9/cdump
mkdir -p /oracle/admin/SID9/udump
80 create passwd file for new instance;
after correctly setting env variables you can just create a password file with the command as oracle user.
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=10
90 After editing "/home/oracle/createControl_ok.txt" you will open db in nomount mode;
create controlfile
cd $ORACLE_HOME/dbs
sqlplus / as sysdba
startup nomount pfile=pfileEditSID9.ora;
@/home/oracle/createControl_ok.txt -->> failed.
@/home/oracle/createControl_ok2.txt -->> succeeded
CREATE CONTROLFILE REUSE DATABASE "SID8" RESETLOGS ARCHIVELOG -->> createControl_ok.txt ..!! WARNING
CREATE CONTROLFILE REUSE SET DATABASE "SID9" RESETLOGS ARCHIVELOG -->> createControl_ok2.txt --> Control file created.
..!! WARNING
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name SID8 in file header does not match given name of SID9
ORA-01110: data file 1: '/oracle/oradata/SID9/system01.dbf'
====================================
in create controlfile script there is a "reuse" statement set it to "set" ; then db name is changed ;SID8 to SID9
datafile has its SID embeded but can be changed during create controlfile. with SET
====================================
CREATE CONTROLFILE REUSE SET DATABASE "SID9" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/SID9/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/SID9/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/SID9/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/SID9/system01.dbf',
'/oracle/oradata/SID9/undotbs01.dbf',
'/oracle/oradata/SID9/sysaux01.dbf',
'/oracle/oradata/SID9/users01.dbf',
'/oracle/oradata/SID9/example01.dbf'
CHARACTER SET WE8ISO8859P9
;
100 add tempfile to temp tablespace
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/SID9/temp01.dbf'SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
110 create spfile and rebounce db
create spfile from pfile=''
create spfile from pfile='/oracle/product/db/10.2.0/dbs/pfileEditSID9.ora';
shutdown immediate;
exit
rm pfileEditSID9.ora
sqlplus / as sysdba
startup;
That was all;
Thank You
Tamer ÖNEM
A remote host or same host will be used as destination host.
there are alternate ways like using rman ; read backup and recovery guide for further information;
I prefer this method for databases using filesystems as datafile storage area; because if rman is used a backup and restore operation will be required.
For Changing SID a tool NID can be prefered.
startup mount ;
nid TARGET=sys "/ as sysdba " DBNAME=SID9 SETNAME=YES
SETNAME=YES parameter keeps db id not changed, so if a dataguard was set it can continue applying .
it is simple and easy to use but a few times I needed deep dive :)
========================================================
CURRENT SID= SID8
new SID= SID9
10 backup control file to trace (user dump destination ); we will re-create it;
a new create control file script will be created in path "user_dump_dest"
SQL> alter database backup controlfile to trace;
SQL> show parameter user_dump_dest;
[oracle@tabya ~]$ cd /oracle/admin/SID8/udump
-rw-r----- 1 oracle dba 6.5K 2009-01-31 13:44 sid8_ora_4059.trc --> this is the file created ..!!
[oracle@tabya udump]$ cp sid8_ora_4059.trc /home/oracle/createControl_pre.txt
20 cold copy; shutdown listener and instance.
copy all datafiles;tempfiles;redolog files,
to get the list of files;
select name from v$datafile; --copy all of them
select member from v$logfile; -- recommended to copy all of them
select name from v$controlfile; -- do not copy them we will cretae them.
select name from v$tempfile; -- do not copy them we will create them
cp -R /oracle/oradata/SID8 /oracle/oradata/SID9
or
mv /oracle/oradata/SID8 /oracle/oradata/SID9
30 Change , edit user enviorement;
some evn variables need to be set like ORACLE_HOME,ORACLE_BASE,ORACLE_SID
you can modify .profile or create a new profile let'say .profile.SID2
cp .bash_profile .profile.SID9
vi .profile.SID9
ORACLE_SID=SID8; export ORACLE_SID ------>> ORACLE_SID=SID9; export ORACLE_SID
40 init file spfile or pfile.
parameter file must be valid before re-creating control file..
create a pfile from source spfile; change the SID to new name.
note: you can use
cd $ORACLE_HOME/dbs
cat spfileSID8.ora > pfileEditSID9.ora
vi pfileEditSID9.ora
#### DEFAULT FILE
*.audit_file_dest='/oracle/admin/SID8/adump'
*.background_dump_dest='/oracle/admin/SID8/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oracle/oradata/SID8/control01.ctl','/oracle/oradata/SID8/control02.ctl','/oracle/oradata/SID8/control03.ctl'
*.core_dump_dest='/oracle/admin/SID8/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='SID8'
*.db_recovery_file_dest='/oracle/fra'
*.db_recovery_file_dest_size=21118320640
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SID8XDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=83886080
*.processes=70
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=82
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/SID8/udump'
#### CHANGE THESE PARAMETER
*.db_name='SID9'
*.audit_file_dest='/oracle/admin/SID9/adump'
*.background_dump_dest='/oracle/admin/SID9/bdump'
*.core_dump_dest='/oracle/admin/SID9/cdump'
*.user_dump_dest='/oracle/admin/SID9/udump'
*.control_files='/oracle/oradata/SID9/control01.ctl','/oracle/oradata/SID9/control02.ctl','/oracle/oradata/SID9/control03.ctl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SID9XDB)'
##
*.compatible='10.2.0.3.0'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest='/oracle/fra'
*.db_recovery_file_dest_size=21118320640
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=83886080
*.processes=70
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=82
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
50 I will edit file created in step 10 by changing and setting same parameters.
This file will include 2 sets ;
Set #1. NORESETLOGS case
Set #2. RESETLOGS case
I will use RESETLOGS case because we will have clean shutdown and no need to recovery.
set SID to new in enviorement;
change datafile paths no newly copied destination
cp /home/oracle/createControl_pre.txt /home/oracle/createControl_ok.txt
vi /home/oracle/createControl_ok.txt
60 listener.ora ; tnsnames.ora
optional you can configure listener to register it or leave it to database itself.
70 create abcu dump destinations change in pfileEdit
create dump destinations; you must have changed them in pfile.. important.
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump
## CREATE DUMP FOLDERS
mkdir -p /oracle/admin/SID9/adump
mkdir -p /oracle/admin/SID9/bdump
mkdir -p /oracle/admin/SID9/cdump
mkdir -p /oracle/admin/SID9/udump
80 create passwd file for new instance;
after correctly setting env variables you can just create a password file with the command as oracle user.
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=10
90 After editing "/home/oracle/createControl_ok.txt" you will open db in nomount mode;
create controlfile
cd $ORACLE_HOME/dbs
sqlplus / as sysdba
startup nomount pfile=pfileEditSID9.ora;
@/home/oracle/createControl_ok.txt -->> failed.
@/home/oracle/createControl_ok2.txt -->> succeeded
CREATE CONTROLFILE REUSE DATABASE "SID8" RESETLOGS ARCHIVELOG -->> createControl_ok.txt ..!! WARNING
CREATE CONTROLFILE REUSE SET DATABASE "SID9" RESETLOGS ARCHIVELOG -->> createControl_ok2.txt --> Control file created.
..!! WARNING
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name SID8 in file header does not match given name of SID9
ORA-01110: data file 1: '/oracle/oradata/SID9/system01.dbf'
====================================
in create controlfile script there is a "reuse" statement set it to "set" ; then db name is changed ;SID8 to SID9
datafile has its SID embeded but can be changed during create controlfile. with SET
====================================
CREATE CONTROLFILE REUSE SET DATABASE "SID9" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/SID9/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/SID9/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/SID9/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/SID9/system01.dbf',
'/oracle/oradata/SID9/undotbs01.dbf',
'/oracle/oradata/SID9/sysaux01.dbf',
'/oracle/oradata/SID9/users01.dbf',
'/oracle/oradata/SID9/example01.dbf'
CHARACTER SET WE8ISO8859P9
;
100 add tempfile to temp tablespace
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/SID9/temp01.dbf'SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
110 create spfile and rebounce db
create spfile from pfile='
create spfile from pfile='/oracle/product/db/10.2.0/dbs/pfileEditSID9.ora';
shutdown immediate;
exit
rm pfileEditSID9.ora
sqlplus / as sysdba
startup;
That was all;
Thank You
Tamer ÖNEM
2008/12/23
DataGuard lost archive ; How to resyncronize?
One of my customer had a huge database (about 30 TB) which had a disastery solution based on phisical stadby database using dataguard.Version is 10.2.0.3. By some reason log transfer and apply service stopped .Created archive logs on primary db filled the space . To keep db running archived logs were deleted without being set to standby side. So Dataguard is no more sync with primary side.
As we had lost archived logs , no backup and not sent to anywhere what should I do ?
Setting up dataguard from scratch costs too much space and time for such big db.
Rman helps.
Action plan is:
1-determine last SCN on standby db
2-Stop log apply and transport services.
3-Backup primary database incremental ; from SCN last applied on standby db.
4-Transfer backup sets to standby side.
5-Register backup sets to stanby db
6-Recover standby db ;
7-Create new standby control file
8-OPTIONAL - Transfer newly created files.
9-Re-start log apply and transfer services.
Details are with commands used:
1-determine last SCN on standby db
PRIMARY
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
3360225821
STANDBY
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
3215410716
2-Stop log apply and transport services.
2.1 stop redo sent on primary
alter system set log_archive_dest_state_2 ='defer' scope=both ;
2.2 stop redo apply on standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3-Backup primary database incremental ; from SCN last applied on standby db.
--for faster backup try with multi channel
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
allocate channel ch5 device type disk;
allocate channel ch6 device type disk;
BACKUP INCREMENTAL FROM SCN 3215410716 DATABASE FORMAT '/intl_migration/cdrdb/backup/tmpForStandby_%U' tag 'FORSTANDBY';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;
}
4-Transfer backup sets to standby side.
because the incremental backup was 1 TB size ; I needed to seperate under different mount points.
Don't worry about keeping them in different folders. We will register them.
SOURCE FOLDERS
/intl_migration/cdrdb/backup/
DEST FOLDER
/medftp/backupDG
/app3/backupDG/
bin
prompt
lcd /intl_migration/cdrdb/backup/
cd /medftp/backupDG
mput tmpForStandby_rkk2lbe4_1_1 tmpForStandby_rlk2lbe5_1_1 tmpForStandby_rmk2lbe7_1_1 tmpForStandby_rnk2lbe9_1_1 tmpForStandby_rok2lbeb_1_1 tmpForStandby_rpk2lbed_1_1 tmpForStandby_rqk2m14c_1_1 tmpForStandby_rrk2m19j_1_1 tmpForStandby_rsk2m2bt_1_1 tmpForStandby_rtk2m2eu_1_1 tmpForStandby_ruk2m2km_1_1 tmpForStandby_rvk2m3m2_1_1
bin
prompt
lcd /intl_migration/cdrdb/backup/
cd /app3/backupDG/
mput tmpForStandby_s0k2mmu4_1_1 tmpForStandby_s1k2mn2d_1_1 tmpForStandby_s2k2mnlu_1_1 tmpForStandby_s3k2mnut_1_1 tmpForStandby_s4k2mob3_1_1 tmpForStandby_s5k2moee_1_1 tmpForStandby_s6k2nc22_1_1 tmpForStandby_s7k2ncda_1_1 tmpForStandby_s8k2nd6s_1_1 tmpForStandby_s9k2ne6n_1_1 tmpForStandby_sak2ne8i_1_1 tmpForStandby_sbk2nf3c_1_1 tmpForStandby_ssk2o28c_1_1
5-Register backup sets to stanby db
OnStandby db
rman target /
RMAN> CATALOG START WITH '/app3/backupDG/tmpForStandby';
RMAN> CATALOG START WITH '/medftp/backupDG/tmpForStandby';
6-Recover standby db ;
one important note ;
because this is a backup taken for only phisical standby db sync ; noredo key word is required.
See : http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#sthref955
RMAN>
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
allocate channel ch5 device type disk;
allocate channel ch6 device type disk;
allocate channel ch7 device type disk;
allocate channel ch8 device type disk;
RECOVER DATABASE NOREDO;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;
release channel ch7;
release channel ch8;
}
7-Create new standby control file
Before re-starting log apply service on standby db; create a new standby controlfile in primary db , copy it to standby .Creating a new controlfile is my suggestion because during non transferred and applied logs ; some chages may be done affecting controlfile like adding redo members, adding datafile, adding new tablespaces...etc
7-1 shutdown standby db instance
7-2 create new standby control file move it to standby side destinations (generally 3).
SQL> alter database create standby controlfile as '/tmp/stby.ctl'; --on primary db
scp /tmp/stby.ctl oracle@stdbyserver:/oradata/ctl<1,2,3>/ctl.dbf
7-3 start standby db in mount , and start log apply service MenagedRecoveryProcess;
SQL> startup mount;
8-OPTIONAL - Transfer newly created files.
If new datafiles were added during the time that dataguard had been stopped as it happened to me; you need to copy the newly created files .They were not included incremental backup set;
and not created cause of stopped MRP.
8-1 determine all datafiles from database (remember we have just created a new controlfile , both primary and standby has same information)
SQL> spool '/tmp/hede.txt';
SQL> select 'file ' ,name from v$datafile;
# sh /tmp/hede.txt > fileSatus.txt
# cat fileSatus.txt grep cannot
/oradata/file004.dbf : cannot open
/oradata/file005.dbf : cannot open
Means we have to copy these 2 files to standby side.
8-2 After determining missing datafiles ; backup them as image copy in primary db ,copy to standby side.
BACKUP AS COPY DATAFILE '/oradata/file004.dbf' FORMAT '/tmp/file004.dbf' TAG stdbyImgCopy;
BACKUP AS COPY DATAFILE '/oradata/file005.dbf' FORMAT '/tmp/file005.dbf' TAG stdbyImgCopy;
scp /tmp/file004.dbf oracle@stdbyserver:/oradata/file004.dbf
scp /tmp/file005.dbf oracle@stdbyserver:/oradata/file005.dbf
9-Re-start log apply and transfer services.
9.1 start redo sent on primary
alter system set log_archive_dest_state_2 ='enable' scope=both ;
9.2 start redo apply on standby
SQL> startup mount;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
9-3 check if for any problems; you may encounter problems. Check alert.log and status of proceesses
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
After success of this operation We were freed of time and space to re-establish all 30 TB database.
A similar workaound is documented in metalink for Oracle 9i : Doc ID:290817.1
Best Regards.
As we had lost archived logs , no backup and not sent to anywhere what should I do ?
Setting up dataguard from scratch costs too much space and time for such big db.
Rman helps.
Action plan is:
1-determine last SCN on standby db
2-Stop log apply and transport services.
3-Backup primary database incremental ; from SCN last applied on standby db.
4-Transfer backup sets to standby side.
5-Register backup sets to stanby db
6-Recover standby db ;
7-Create new standby control file
8-OPTIONAL - Transfer newly created files.
9-Re-start log apply and transfer services.
Details are with commands used:
1-determine last SCN on standby db
PRIMARY
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
3360225821
STANDBY
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
3215410716
2-Stop log apply and transport services.
2.1 stop redo sent on primary
alter system set log_archive_dest_state_2 ='defer' scope=both ;
2.2 stop redo apply on standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3-Backup primary database incremental ; from SCN last applied on standby db.
--for faster backup try with multi channel
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
allocate channel ch5 device type disk;
allocate channel ch6 device type disk;
BACKUP INCREMENTAL FROM SCN 3215410716 DATABASE FORMAT '/intl_migration/cdrdb/backup/tmpForStandby_%U' tag 'FORSTANDBY';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;
}
4-Transfer backup sets to standby side.
because the incremental backup was 1 TB size ; I needed to seperate under different mount points.
Don't worry about keeping them in different folders. We will register them.
SOURCE FOLDERS
/intl_migration/cdrdb/backup/
DEST FOLDER
/medftp/backupDG
/app3/backupDG/
bin
prompt
lcd /intl_migration/cdrdb/backup/
cd /medftp/backupDG
mput tmpForStandby_rkk2lbe4_1_1 tmpForStandby_rlk2lbe5_1_1 tmpForStandby_rmk2lbe7_1_1 tmpForStandby_rnk2lbe9_1_1 tmpForStandby_rok2lbeb_1_1 tmpForStandby_rpk2lbed_1_1 tmpForStandby_rqk2m14c_1_1 tmpForStandby_rrk2m19j_1_1 tmpForStandby_rsk2m2bt_1_1 tmpForStandby_rtk2m2eu_1_1 tmpForStandby_ruk2m2km_1_1 tmpForStandby_rvk2m3m2_1_1
bin
prompt
lcd /intl_migration/cdrdb/backup/
cd /app3/backupDG/
mput tmpForStandby_s0k2mmu4_1_1 tmpForStandby_s1k2mn2d_1_1 tmpForStandby_s2k2mnlu_1_1 tmpForStandby_s3k2mnut_1_1 tmpForStandby_s4k2mob3_1_1 tmpForStandby_s5k2moee_1_1 tmpForStandby_s6k2nc22_1_1 tmpForStandby_s7k2ncda_1_1 tmpForStandby_s8k2nd6s_1_1 tmpForStandby_s9k2ne6n_1_1 tmpForStandby_sak2ne8i_1_1 tmpForStandby_sbk2nf3c_1_1 tmpForStandby_ssk2o28c_1_1
5-Register backup sets to stanby db
OnStandby db
rman target /
RMAN> CATALOG START WITH '/app3/backupDG/tmpForStandby';
RMAN> CATALOG START WITH '/medftp/backupDG/tmpForStandby';
6-Recover standby db ;
one important note ;
because this is a backup taken for only phisical standby db sync ; noredo key word is required.
See : http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#sthref955
RMAN>
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
allocate channel ch5 device type disk;
allocate channel ch6 device type disk;
allocate channel ch7 device type disk;
allocate channel ch8 device type disk;
RECOVER DATABASE NOREDO;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;
release channel ch7;
release channel ch8;
}
7-Create new standby control file
Before re-starting log apply service on standby db; create a new standby controlfile in primary db , copy it to standby .Creating a new controlfile is my suggestion because during non transferred and applied logs ; some chages may be done affecting controlfile like adding redo members, adding datafile, adding new tablespaces...etc
7-1 shutdown standby db instance
7-2 create new standby control file move it to standby side destinations (generally 3).
SQL> alter database create standby controlfile as '/tmp/stby.ctl'; --on primary db
scp /tmp/stby.ctl oracle@stdbyserver:/oradata/ctl<1,2,3>/ctl.dbf
7-3 start standby db in mount , and start log apply service MenagedRecoveryProcess;
SQL> startup mount;
8-OPTIONAL - Transfer newly created files.
If new datafiles were added during the time that dataguard had been stopped as it happened to me; you need to copy the newly created files .They were not included incremental backup set;
and not created cause of stopped MRP.
8-1 determine all datafiles from database (remember we have just created a new controlfile , both primary and standby has same information)
SQL> spool '/tmp/hede.txt';
SQL> select 'file ' ,name from v$datafile;
# sh /tmp/hede.txt > fileSatus.txt
# cat fileSatus.txt grep cannot
/oradata/file004.dbf : cannot open
/oradata/file005.dbf : cannot open
Means we have to copy these 2 files to standby side.
8-2 After determining missing datafiles ; backup them as image copy in primary db ,copy to standby side.
BACKUP AS COPY DATAFILE '/oradata/file004.dbf' FORMAT '/tmp/file004.dbf' TAG stdbyImgCopy;
BACKUP AS COPY DATAFILE '/oradata/file005.dbf' FORMAT '/tmp/file005.dbf' TAG stdbyImgCopy;
scp /tmp/file004.dbf oracle@stdbyserver:/oradata/file004.dbf
scp /tmp/file005.dbf oracle@stdbyserver:/oradata/file005.dbf
9-Re-start log apply and transfer services.
9.1 start redo sent on primary
alter system set log_archive_dest_state_2 ='enable' scope=both ;
9.2 start redo apply on standby
SQL> startup mount;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
9-3 check if for any problems; you may encounter problems. Check alert.log and status of proceesses
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
After success of this operation We were freed of time and space to re-establish all 30 TB database.
A similar workaound is documented in metalink for Oracle 9i : Doc ID:290817.1
Best Regards.
2008/12/19
Ethernet Card Changes , RAC crs stops
One of my customer had changed their Linux Server's mainboard.It was one of 2 nodes running oracle RAC. But after this change they complaint that crs was not working fine.
I connected to box and see it was really not working. After exploring I found that the ethernet card was disabled due to nonmatching MAC adress in config file and phisical hardware.This was interconnect used for priv addresses. The solution was commenting out MAC adress in config file.
[root@db1 tmp]# cat /etc/sysconfig/network-scripts/ifcfg-eth1
DEVICE=eth1
BOOTPROTO=none
#HWADDR=00:17:A4:F6:DF:96 ## this line is commented out because of mainboard and embeded ethernet card change ..!
ONBOOT=yes
TYPE=Ethernet
NETMASK=255.255.255.0
IPADDR=192.168.50.11
USERCTL=no
IPV6INIT=no
PEERDNS=yes
[root@db1 network-scripts]#
I connected to box and see it was really not working. After exploring I found that the ethernet card was disabled due to nonmatching MAC adress in config file and phisical hardware.This was interconnect used for priv addresses. The solution was commenting out MAC adress in config file.
[root@db1 tmp]# cat /etc/sysconfig/network-scripts/ifcfg-eth1
DEVICE=eth1
BOOTPROTO=none
#HWADDR=00:17:A4:F6:DF:96 ## this line is commented out because of mainboard and embeded ethernet card change ..!
ONBOOT=yes
TYPE=Ethernet
NETMASK=255.255.255.0
IPADDR=192.168.50.11
USERCTL=no
IPV6INIT=no
PEERDNS=yes
[root@db1 network-scripts]#
Subscribe to:
Posts (Atom)
