skip to main |
skip to sidebar
===========SOLUTION 3
SUTIATION SUMMARY:
old scn controlfiles + different incarnation in controlfile&datafile
SCENARIO CREATION
1-db is open;
2-rman is used for online backup, ## rman backup
3-shutdown database ;
4-after rman hot backup a file system cold backup is done. ## file system backup --> control files are used as catalog. so rman backup will be avaliable in following steps.See registering backups to catalog.!
5-start database ;
6-switch log file 5 times; ## different SCN
7-alter database backup controlfile to trace;
8-shutdown db ;
9-startup nomount;
10-re-create controlfile from trace;
11-alter database open resetlogs; ## different incarnation creation,
12-switch log file 5 times;
13-shutdown db ;
14-copy controlfiles from file system backup; ## control files differ by SCN and incarnation
15-copy some datafiles from cold backup; ## some datafiles differ by SCN and incarnation
SCENARIO READY
status of database after step 7 ;
RMAN backup created in step 2
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 PARENT 1 19-DEC-06
2 2 SID2 384861248 PARENT 564488 20-MAR-09
3 3 SID2 384861248 CURRENT 1903057 17-APR-09
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
3 B F A DISK 22-APR-09 1 1 NO TAG20090422T093549 ## hot backup
4 B F A DISK 22-APR-09 1 1 NO TAG20090422T093655 ## controlfile spfile autobackup
RMAN> list backupset 3 ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.80G DISK 00:00:55 22-APR-09
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20090422T093549
Piece Name: /oracle/oradata/FRA/SID2/backupset/2009_04_22/o1_mf_nnndf_TAG20090422T093549_4yxgp6b7_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2041786 22-APR-09 /oracle/oradata/SID2/system01.dbf
2 Full 2041786 22-APR-09 /oracle/oradata/SID2/undotbs01.dbf
3 Full 2041786 22-APR-09 /oracle/oradata/SID2/sysaux01.dbf
4 Full 2041786 22-APR-09 /oracle/oradata/SID2/users01.dbf
5 Full 2041786 22-APR-09 /oracle/oradata/SID2/example01.dbf
6 Full 2041786 22-APR-09 /oracle/oradata/SID2/drop01.dbf
7 Full 2041786 22-APR-09 /oracle/oradata/SID2/drop02.dbf
8 Full 2041786 22-APR-09 /oracle/oradata/SID2/drop03.dbf
9 Full 2041786 22-APR-09 /oracle/oradata/SID2/adtest01.dbf
10 Full 2041786 22-APR-09 /oracle/oradata/SID2/UKS001.dbf
RMAN> list backupset 4 ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 6.83M DISK 00:00:00 22-APR-09
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20090422T093655
Piece Name: /oracle/oradata/FRA/SID2/autobackup/2009_04_22/o1_mf_s_684841015_4yxgr7mq_.bkp
Control File Included: Ckp SCN: 2041812 Ckp time: 22-APR-09
SPFILE Included: Modification time: 22-APR-09
File system backup created in step 4
drwxr-xr-x 5 oracle dba 4096 Mar 20 13:19 ..
drwxr-x--- 3 oracle dba 4096 Mar 20 13:40 FRA
drwxr-x--- 2 oracle dba 4096 Apr 17 10:24 SID2
drwxr-xr-x 5 oracle dba 4096 Apr 22 09:46 .
drwxr-x--- 2 oracle dba 4096 Apr 22 09:47 SID2_backup
SQL> shutdown immediate;
SQL> startup nomount;
CREATE CONTROLFILE REUSE DATABASE "SID2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/SID2/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/SID2/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/SID2/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/SID2/system01.dbf',
'/oracle/oradata/SID2/undotbs01.dbf',
'/oracle/oradata/SID2/sysaux01.dbf',
'/oracle/oradata/SID2/users01.dbf',
'/oracle/oradata/SID2/example01.dbf',
'/oracle/oradata/SID2/drop01.dbf',
'/oracle/oradata/SID2/drop02.dbf',
'/oracle/oradata/SID2/drop03.dbf',
'/oracle/oradata/SID2/adtest01.dbf',
'/oracle/oradata/SID2/UKS001.dbf'
CHARACTER SET WE8ISO8859P9
;
Control file created.
SQL>
## HINT when create controlfile is issued db is mounted; you do not explicity mount it.
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 CURRENT 1903057 17-APR-09
SQL> alter database open resetlogs; ## new incarnation created..!!
Database altered.
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 PARENT 1903057 17-APR-09
2 2 SID2 384861248 CURRENT 2043251 22-APR-09
RMAN>
## Now we have a different incarnation number
SQL> alter system switch logfile; -- execute this a few more times ..
System altered.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.
SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
------------------ ------------- ------------ ----------
22-APR-09 09:57:26 2042410 2042413 8
22-APR-09 09:57:31 2042413 2042415 9
22-APR-09 09:57:32 2042415 2043251 10
22-APR-09 10:27:48 2043251 2043388 1
22-APR-09 10:29:17 2043388 2043390 2
22-APR-09 10:29:18 2043390 2043393 3
22-APR-09 10:29:21 2043393 2043395 4
22-APR-09 10:29:21 2043395 2043397 5
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
[oracle@gulcan oradata]$ cp SID2_backup/control0* SID2/ ## all controlfiles have old snc and incarnation
[oracle@gulcan oradata]$ cp SID2_backup/users01.dbf SID2/ ## users01.dbf datafile has old scn and incarnation.
Scenario now ready .
MAYDAY MAYDAY .. :)
SQL> startup;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2071256 bytes
Variable Size 117441832 bytes
Database Buffers 41943040 bytes
Redo Buffers 6316032 bytes
Database mounted.
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'
RMAN> recover database;
Starting recover at 22-APR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=56 devtype=DISK
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/22/2009 10:53:36
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'
RMAN>
as you see it seems we are stuck
there may be some possible ways; I will try
restore from last rman backup + register archived logs to catalog + apply archived logs
SOLUTION 1
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.
SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
------------------ ------------- ------------ ----------
20-MAR-09 13:40:19 564488 595962 1
.......
14-APR-09 21:02:40 1749055 1791086 59
15-APR-09 20:00:19 1791086 1824816 60
16-APR-09 12:42:26 1824816 1824818 61
16-APR-09 12:42:29 1824818 1824820 62
16-APR-09 12:42:32 1824820 1824822 63
16-APR-09 12:42:34 1824822 1862796 64
17-APR-09 08:48:27 1862796 1862798 65
17-APR-09 08:48:28 1862798 1862801 66
17-APR-09 08:48:30 1862801 1862803 67
17-APR-09 08:48:30 1862803 1882805 68
17-APR-09 08:58:27 1882805 1882948 69
17-APR-09 09:00:10 1882948 1882950 70
17-APR-09 09:00:11 1882950 1882953 71
17-APR-09 09:00:13 1882953 1882955 72
17-APR-09 09:00:13 1882955 1902957 73
17-APR-09 09:03:47 1902957 1903057 74
17-APR-09 10:33:01 1903057 1940815 1
18-APR-09 07:00:09 1940815 1998639 2
19-APR-09 21:00:25 1998639 2041019 3
77 rows selected.
controlfile is lack of archied log information normally. we will try to register newly created archived logs.
alter database register logfile '/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_10_4yxkqnpr_.arc';
....
alter database register logfile '/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_9_4yxkqnl6_.arc';
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level
RMAN> restore database ;
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/oradata/SID2/system01.dbf
restoring datafile 00002 to /oracle/oradata/SID2/undotbs01.dbf
restoring datafile 00003 to /oracle/oradata/SID2/sysaux01.dbf
restoring datafile 00004 to /oracle/oradata/SID2/users01.dbf
restoring datafile 00005 to /oracle/oradata/SID2/example01.dbf
restoring datafile 00006 to /oracle/oradata/SID2/drop01.dbf
restoring datafile 00007 to /oracle/oradata/SID2/drop02.dbf
restoring datafile 00008 to /oracle/oradata/SID2/drop03.dbf
restoring datafile 00009 to /oracle/oradata/SID2/adtest01.dbf
restoring datafile 00010 to /oracle/oradata/SID2/UKS001.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 22-APR-09
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2041786 generated at 04/22/2009 09:35:50 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_4_%u_.arc
ORA-00280: change 2041786 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
RETURN PRESSED
ORA-00283: recovery session canceled due to errors
ORA-19906: recovery target incarnation changed during recovery
ORA-01112: media recovery not started
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 PARENT 1 19-DEC-06
2 2 SID2 384861248 PARENT 564488 20-MAR-09
3 3 SID2 384861248 PARENT 1903057 17-APR-09
4 4 SID2 384861248 CURRENT 2043251 22-APR-09
RMAN> RESET DATABASE TO INCARNATION 4 ;
database reset to incarnation 4
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2041786 generated at 04/22/2009 09:35:50 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_4_%u_.arc
ORA-00280: change 2041786 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
... return pressed
Log applied.
Media recovery complete.
SQL> alter database open RESETLOGS ;
Database altered.
DONE..!!
That was really cool..!
Backup is one of the primary tasks of a dba.But how about restore and recover ?I know many sites that take regular backups and never test if they could restore,recover.In the post I created scenario some of which I have encountered.Because of many examples I will have a few posts about this subject.
===========SOLUTION 1
SUTIATION SUMMARY:
rman or sqlplus to recover ; one of controlfiles and some of datafile has old scn
SCENARIO CREATION
SQL> shutdown immediate;
#cp -R /oracle/oradata/SID2 /oracle/oradata/SID2_backup
SQL> startup;
SQL> alter system switch log file ; -- X5 times
SQL> shutdown abort;
#cp /oracle/oradata/SID2_backup/control01.ctl /oracle/oradata/SID2/control01.ctl ## controlfile01 has old scn
#cp /oracle/oradata/SID2_backup/system01.dbf /oracle/oradata/SID2/system01.dbf ## datafile01 has old scn
SQL> shutdown abort;
SCENARIO READY
SQL> startup mount;
ORACLE instance started.
ORA-00214: control file '/oracle/oradata/SID2/control02.ctl' version 1352
inconsistent with file '/oracle/oradata/SID2/control01.ctl' version 1327
cp /oracle/oradata/SID2/control02.ctl /oracle/oradata/SID2/control01.ctl
SQL> alter database mount;
Database altered.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'
HINT ..!
if you try recovering from sqlplus it does not know about archived logs..!!
Although both candidates can recover database rman is strongly recommended to use here ..!!
### ************************* SQLPLUS DOES NOT KNOW ABOUT ARCHIVED LOGS
SQL> recover datafile 1 ;
ORA-00279: change 1862553 generated at 04/17/2009 08:45:42 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_%u_.arc
ORA-00280: change 1862553 for thread 1 is in sequence #64
Specify log: {=suggested | filename | AUTO | CANCEL}
--RETURN PRESSED TO ACCEPT suggested archived logs..!
Log applied.
Media recovery complete.
SQL> alter database open ;
Database altered.
### ************************* RMAN KNOWS ABOUT ARCHIVED LOGS
RMAN> recover datafile 1 ;
Starting recover at 17-APR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=53 devtype=DISK
starting media recovery
archive log thread 1 sequence 64 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_4yj61c6n_.arc
archive log thread 1 sequence 65 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_65_4yj61d9g_.arc
archive log thread 1 sequence 66 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_66_4yj61g6m_.arc
archive log thread 1 sequence 67 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_67_4yj61gx8_.arc
archive log filename=/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_4yj61c6n_.arc thread=1 sequence=64
archive log filename=/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_65_4yj61d9g_.arc thread=1 sequence=65
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-APR-09
DB IS NOW READY TO OPEN
SQL> alter database open ;
Database altered.
===========SOLUTION 2
SUTIATION SUMMARY:
rman to recover ; old controlfile is used from cold backup..!
SCENARIO CREATION
SQL>shutdown immediate;
# copy all datafiles as filesystem cold backup
[oracle@gulcan oradata]# cp SID2_backup/control0* SID2/
cp: overwrite `SID2/control01.ctl'? y
cp: overwrite `SID2/control02.ctl'? y
cp: overwrite `SID2/control03.ctl'? y
SCENARIO READY
SQL> startup mount ;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2071256 bytes
Variable Size 117441832 bytes
Database Buffers 41943040 bytes
Redo Buffers 6316032 bytes
Database mounted.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/oracle/oradata/SID2/undotbs01.dbf'
ORA-01207: file is more recent than control file - old control file
### ****************************** RMAN is not using backup controlfile statement..
RMAN> recover database using backup controlfile until cancel;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "using": expecting one of: "archivelog, auxiliary, allow, check, delete, from, high, noredo, noparallel, parallel, ;, skip, tablespace, test, until, undo"
RMAN-01007: at line 1 column 18 file: standard input
RMAN>
### ****************************** SQLPLUS is using backupcontrolfile ..
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1862553 generated at 04/17/2009 08:45:42 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_%u_.arc
ORA-00280: change 1862553 for thread 1 is in sequence #64
Specify log: {=suggested | filename | AUTO | CANCEL}
all suggested recovery was done ..!!
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/oracle/oradata/SID2/undotbs01.dbf'
..... HINT ..!! when warning declared "RECOVER succeeded but OPEN RESETLOGS would get error below.."
it means we need more recovery but we have no archived log remaining unapplied.
so the redo data should be in redo log files which are the source of archived log files .!!
re-apply restore operation using redolog files as input files.
see member,groups..etc for redo log files.
SQL> select * from v$logfile;
/oracle/oradata/SID2/redo01.log
/oracle/oradata/SID2/redo02.log
/oracle/oradata/SID2/redo03.log
SQL> recover database using backup controlfile until cancel;
Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/oradata/SID2/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open RESETLOGS;
Database altered.
SQL>
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.
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
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
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.
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]#
