BILAAVAL, FIRST MEHL:
My mind is filled with such a great joy; I have blossomed forth in Truth. I am enticed by the love of my Husband Lord, the Eternal, Imperishable Lord God. The Lord is everlasting, the Master of masters. Whatever He wills, happens. O Great Giver, You are always kind and compassionate. You infuse life into all living beings. I have no other spiritual wisdom, meditation or worship; the Name of the Lord alone dwells deep within me. I know nothing about religious robes, pilgrimages or stubborn fanaticism; O Nanak, I hold tight to the Truth. || 1 ||
Index of all the posts of Gurpartap Singh's Blog
I had a problem where I was dealing witha multiple terabyte database on RAC and couple of years ago the DBA added the file to the tablespace in ASM diskgroup but the file file was added with '+' sign in front of the diskgroup name. So, the file got create in $ORACLE_HOME/dbs. Instead of moving that file to ASM and recovering it, the file was "offline dropped". Now after few years, we need to move this database to another machine and we don't have the storage for RMAN backups for this monster size database. This problematic tablespace belongs to the important tablespace that cannot be skipped. We were provided with very fast network only and no space for storing backup or low level clone. With RMAN active clone, I got the following error.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/13/2014 16:21:14
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01152: file 4 was not restored from a sufficiently old backup
ORA-01110: data file 4: '+DATA/prabgun/datafile/users.278.842113199'
RMAN active clone failed on the missing file as we cannot use the "skip tablespace" feature as it's one of the
most important tablespace. import export of ths huge database was not an option. On source and target side
we had grid infrastructure installed with one Oracle database up and running on it. On source i.e. teh database we want to move was "SIMAR" on target the database that was running was "SDIEGO". I used the following oracle package to do this clone:
DBMS_FILE_TRANSFER.put_file
Here are the detail steps:
-- Login to the target server (where you want to make the clone).
CONN system/gurpartap
-- Create the destination directory object.
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '+DATA';
-- Login to the local server.
Add the following entry in tnsnames.ora:
TARGET =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac70.rac.meditate.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SDIEGO.rac.meditate.com)
)
)
Now connect to the database as:
CONN system/gurpartap@local
-- Create the source directory object, database link.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '+DATA/SIMAR/DATAFILE';
CREATE DATABASE SDIEGO target CONNECT TO system IDENTIFIED BY gurpartap USING 'TARGET';
Now I see on this database as stated in the problem, we have a missing data file so make it offline.
This file was created in tablespace USERS with '+' in from of the ASM diskgroup and so got created in the
ORACLE_HOME. So, the mistake that was done couple of years ago was that instead of moning this file to ASM,
it was offline dropped. I will re-do offline drop again, just to make sure as:
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/simar/datafile/ggdata.274.839364817
+DATA/simar/datafile/data_encrypt.273.839255003
+DATA/simar/datafile/data_encrypt.270.839255001
+DATA/simar/datafile/data_encrypt.264.839254791
+DATA/simar/datafile/goldengate.258.839254791
+DATA/simar/datafile/undotbs2.265.839254793
+DATA/simar/datafile/example.257.839254791
+DATA/simar/datafile/users.269.839254793
+DATA/simar/datafile/undotbs1.259.839254791
+DATA/simar/datafile/sysaux.256.839254791
+DATA/simar/datafile/system.271.839254791
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.3/dbs/MISSING00012
12 rows selected.
SQL> alter database datafile '/u01/app/oracle/product/11.2.0.3/dbs/MISSING00012' offline drop;
Database altered.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/simar/datafile/ggdata.274.839364817
+DATA/simar/datafile/data_encrypt.273.839255003
+DATA/simar/datafile/data_encrypt.270.839255001
+DATA/simar/datafile/data_encrypt.264.839254791
+DATA/simar/datafile/goldengate.258.839254791
+DATA/simar/datafile/undotbs2.265.839254793
+DATA/simar/datafile/example.257.839254791
+DATA/simar/datafile/users.269.839254793
+DATA/simar/datafile/undotbs1.259.839254791
+DATA/simar/datafile/sysaux.256.839254791
+DATA/simar/datafile/system.271.839254791
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.3/dbs/MISSING00012
12 rows selected.
SQL>
Now before moving the datafiles place the database in backup mode as:
alter database begin backup;
Log is as:
SQL> alter database begin backup;
Database altered.
Now lets move the files to the target ASM using the following script:
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'USERS.269.839254793', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'USERS.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.264.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.264.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.270.839255001', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.270.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.273.839255003', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.273.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'EXAMPLE.257.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'EXAMPLE.257.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'GGDATA.274.839364817', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'GGDATA.274.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'GOLDENGATE.258.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'GOLDENGATE.258.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'SYSAUX.256.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'SYSAUX.256.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'SYSTEM.271.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'SYSTEM.271.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'UNDOTBS1.259.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'UNDOTBS1.259.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'UNDOTBS2.265.839254793', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'UNDOTBS2.265.transfer.dbf', destination_database => 'SDIEGO'); END;
/
Log is as:
SQL> BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'USERS.269.839254793', destination_directory_obj ect => 'DB_FILES_DIR2', destination_file_name => 'USERS.transfer.dbf', destination_database => 'SDIEGO'); END;
/
2
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.264.839254791', destination_directory_o bject => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.264.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.270.839255001', destination_directory_o bject => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.270.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.273.839255003', destination_directory_o bject => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.273.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'EXAMPLE.257.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'EXAMPLE.257.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'GGDATA.274.839364817', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'GGDATA.274.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'GOLDENGATE.258.839254791', destination_directory_obj ect => 'DB_FILES_DIR2', destination_file_name => 'GOLDENGATE.258.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'SYSAUX.256.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'SYSAUX.256.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'SYSTEM.271.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'SYSTEM.271.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'UNDOTBS1.259.839254791', destination_directory_objec t => 'DB_FILES_DIR2', destination_file_name => 'UNDOTBS1.259.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'UNDOTBS2.265.839254793', destination_directory_objec t => 'DB_FILES_DIR2', destination_file_name => 'UNDOTBS2.265.transfer.dbf', destination_database => 'SDIEGO'); END;
/
PL/SQL procedure successfully completed.
SQL> SQL> 2
PL/SQL procedure successfully completed.
SQL> 2
PL/SQL procedure successfully completed.
SQL> 2
PL/SQL procedure successfully completed.
SQL> 2
PL/SQL procedure successfully completed.
SQL> 2
PL/SQL procedure successfully completed.
SQL> 2
PL/SQL procedure successfully completed.
SQL> 2
PL/SQL procedure successfully completed.
SQL> 2
PL/SQL procedure successfully completed.
SQL> 2
PL/SQL procedure successfully completed.
SQL> 2
PL/SQL procedure successfully completed.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL>
Now alter the database to end backup using following:
alter database end backup;
Log is as:
SQL> alter database end backup;
alter database end backup
*
ERROR at line 1:
ORA-01260: warning: END BACKUP succeeded but some files found not to be in
backup mode
Ignore the ORA- error here as the missing file was never placed in te backup mode.
Now lets go to the target database and see if the files are there:
ASMCMD> pwd
+DATA/SDIEGO/DATAFILE
ASMCMD> ls -lart
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => ENCRYPT.271.839195159
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => EXAMPLE.264.830971505
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => GGDATA.272.839362901
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => GOLDENGATE.270.839193779
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => READ.269.838771155
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => SYSAUX.257.830971359
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => SYSTEM.256.830971357
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => UNDOTBS1.258.830971359
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => UNDOTBS2.265.830971971
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => USERS.259.830971359
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/DATA_ENCRYPT.270.transfer.dbf => FILE_TRANSFER.275.842352323
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/UNDOTBS2.265.transfer.dbf => FILE_TRANSFER.277.842352555
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/DATA_ENCRYPT.273.transfer.dbf => FILE_TRANSFER.280.842352325
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/EXAMPLE.257.transfer.dbf => FILE_TRANSFER.281.842352327
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/GGDATA.274.transfer.dbf => FILE_TRANSFER.282.842352359
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/GOLDENGATE.258.transfer.dbf => FILE_TRANSFER.283.842352369
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/SYSAUX.256.transfer.dbf => FILE_TRANSFER.284.842352395
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/SYSTEM.271.transfer.dbf => FILE_TRANSFER.285.842352481
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/UNDOTBS1.259.transfer.dbf => FILE_TRANSFER.286.842352547
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/USERS.transfer.dbf => FILE_TRANSFER.288.842352313
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/DATA_ENCRYPT.264.transfer.dbf => FILE_TRANSFER.289.842352315
ASMCMD> ls
ENCRYPT.271.839195159
EXAMPLE.264.830971505
FILE_TRANSFER.275.842352323
FILE_TRANSFER.277.842352555
FILE_TRANSFER.280.842352325
FILE_TRANSFER.281.842352327
FILE_TRANSFER.282.842352359
FILE_TRANSFER.283.842352369
FILE_TRANSFER.284.842352395
FILE_TRANSFER.285.842352481
FILE_TRANSFER.286.842352547
FILE_TRANSFER.288.842352313
FILE_TRANSFER.289.842352315
GGDATA.272.839362901
GOLDENGATE.270.839193779
READ.269.838771155
SYSAUX.257.830971359
SYSTEM.256.830971357
UNDOTBS1.258.830971359
UNDOTBS2.265.830971971
USERS.259.830971359
ASMCMD>
I had placed word "FILE_TRANSFER" in front of each file so that I can easily recognise which files we
trasfered and which are of the old database.
Now update the target server's /etc/oratab to reflect the name of the new database by adding following line to it:
PRABGUN1:/u01/app/oracle/product/11.2.0.3:N
Now oratab will look like following:
#
+ASM1:/u01/app/12.1.0.0/grid:N: # line added by Agent
DUMMY:/u01/app/oracle/product/11.2.0.3:N
SDIEGO1:/u01/app/oracle/product/11.2.0.3:N:
PRABGUN1:/u01/app/oracle/product/11.2.0.3:N
SDIEGO:/u01/app/oracle/product/11.2.0.3:N: # line added by Agent
Now source the environment of instance PRABGUN1 as:
. oraenv
PRABGUN1
Output is:
23:31 : oracle : rac70.rac.meditate.com : @+ASM1 : /home/oracle
$ . oraenv
ORACLE_SID = [+ASM1] ? PRABGUN1
The Oracle base remains unchanged with value /u01/app/oracle
23:31 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /home/oracle
$
Now create the parameter file as in $ORACLE_HOME/dbs and name it as 'initPRABGUN1.ora'
Following are the contents of the file 'initPRABGUN1.ora'
23:32 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$ cat initPRABGUN1.ora
_compression_compatibility= "11.2.0"
*.aq_tm_processes=1
*.archive_lag_target=0
*.audit_trail='DB'
*.cluster_database=FALSE
*.compatible='11.2.0.3.0'
*.control_file_record_keep_time=14
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_file_multiblock_read_count=64
*.db_flashback_retention_target=1440
*.db_name='PRABGUN'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=1G
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=true
*.fast_start_mttr_target=0
*.fast_start_parallel_rollback='LOW'
*.global_names=FALSE
*.instance_name='PRABGUN1'
*.instance_number=1
*.job_queue_processes=10
*.large_pool_size=0
*.log_archive_dest_1='LOCATION=+FLASH'
*.log_archive_format='PRABGUN%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_buffer=4190208
*.log_checkpoints_to_alert=false
*.open_cursors=250
*.open_links=4
*.optimizer_mode='choose'
*.parallel_max_servers=25
*.pga_aggregate_target=50M
*.processes=100
*.query_rewrite_enabled='true'
*.query_rewrite_integrity='trusted'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=50
*.sessions=250
*.sga_max_size=368m
*.sga_target=368m
*.shared_pool_size=0
*.star_transformation_enabled='true'
*.streams_pool_size=0
*.tape_asynch_io=true
*.timed_statistics=true
*.undo_management='auto'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='*'
*.workarea_size_policy='AUTO'
23:32 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$
Now lets start the database in the nomount state as:
. oraenv
PRABGUN1
sqlplus / as sysdba
startup nomount;
Following is the log:
23:32 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$ . oraenv
ORACLE_SID = [PRABGUN1] ? PRABGUN1
The Oracle base remains unchanged with value /u01/app/oracle
23:34 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 384143360 bytes
Fixed Size 2228624 bytes
Variable Size 125832816 bytes
Database Buffers 251658240 bytes
Redo Buffers 4423680 bytes
SQL>
Now using the following command, lets create the controlfile as:
CREATE CONTROLFILE SET DATABASE "PRABGUN" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA' SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.275.842352323',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.277.842352555',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.280.842352325',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.281.842352327',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.282.842352359',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.283.842352369',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.284.842352395',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.285.842352481',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.286.842352547',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.288.842352313',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.289.842352315'
CHARACTER SET AL32UTF8
;
Here is the log:
SQL> CREATE CONTROLFILE SET DATABASE "PRABGUN" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
2 3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '+DATA' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '+DATA' SIZE 50M BLOCKSIZE 512
10 -- STANDBY LOGFILE
DATAFILE
11 12 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.275.842352323',
13 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.277.842352555',
14 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.280.842352325',
15 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.281.842352327',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.282.842352359',
16 17 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.283.842352369',
18 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.284.842352395',
19 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.285.842352481',
20 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.286.842352547',
21 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.288.842352313',
22 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.289.842352315'
23 CHARACTER SET AL32UTF8
24 ;
Control file created.
SQL>
Once we create teh controlfile, database is in mount state. Now lets recvoer the database and open it:
From the source database get the current SCN till we want to recover using command:
select curent_scn from v$database:
I got number '17959782'
On target database as sysdba execute teh following command and start the recovery:
sqlplus / as sysdba
recover database until change 17959782 using backup controlfile;
I scp the required archive logs from source server to the target server and applied them
Log is as:
23:34 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 16 23:39:17 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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>SQL> recover database until change 17959782 using backup controlfile;
ORA-00279: change 17959484 generated at 03/16/2014 10:45:00 needed for thread 1
ORA-00289: suggestion : +FLASH
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'PRABGUN'
ORA-00280: change 17959484 for thread 1 is in sequence #25
Specify log: {
/home/oracle/thread_1_seq_25.364.842352867
ORA-00279: change 17959749 generated at 03/16/2014 10:54:26 needed for thread 1
ORA-00289: suggestion : +FLASH
ORA-00280: change 17959749 for thread 1 is in sequence #26
ORA-00278: log file '/home/oracle/thread_1_seq_25.364.842352867' no longer
needed for this recovery
Specify log: {
/home/oracle/thread_1_seq_26.363.842352879
ORA-00279: change 17959760 generated at 03/16/2014 10:54:38 needed for thread 1
ORA-00289: suggestion : +FLASH
ORA-00280: change 17959760 for thread 1 is in sequence #27
ORA-00278: log file '/home/oracle/thread_1_seq_26.363.842352879' no longer
needed for this recovery
Specify log: {
/home/oracle/thread_1_seq_27.362.842352887
ORA-00279: change 17959768 generated at 03/16/2014 10:54:47 needed for thread 1
ORA-00289: suggestion : +FLASH
ORA-00280: change 17959768 for thread 1 is in sequence #28
ORA-00278: log file '/home/oracle/thread_1_seq_27.362.842352887' no longer
needed for this recovery
Specify log: {
/home/oracle/thread_1_seq_28.361.842352891
ORA-00279: change 17959774 generated at 03/16/2014 10:54:50 needed for thread 1
ORA-00289: suggestion : +FLASH
ORA-00280: change 17959774 for thread 1 is in sequence #29
ORA-00278: log file '/home/oracle/thread_1_seq_28.361.842352891' no longer
needed for this recovery
Specify log: {
/home/oracle/thread_1_seq_29.360.842352913
Log applied.
Media recovery complete.
SQL>
Now lets open the database using the following command as:
SQL> alter database open resetlogs;
Database altered.
SQL>
Lets update the parameter file with the location of control file as we started with pfile and at that time controlfile was not created :
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 14
control_files string +DATA/prabgun/controlfile/curr
ent.276.842352827, +FLASH/prab
gun/controlfile/current.362.84
2352831
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
So, in parameter file i.e. 'initPRABGUN1.ora' at $ORACLE_HOME/dbs add the following line:
*.control_files='+DATA/prabgun/controlfile/current.276.842352827','+FLASH/prabgun/controlfile/current.362.842352831'
Now parameter file looks like following:
23:46 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$ cat initPRABGUN1.ora
_compression_compatibility= "11.2.0"
*.aq_tm_processes=1
*.archive_lag_target=0
*.audit_trail='DB'
*.cluster_database=FALSE
*.control_files='+DATA/prabgun/controlfile/current.276.842352827','+FLASH/prabgun/controlfile/current.362.842352831'
*.compatible='11.2.0.3.0'
*.control_file_record_keep_time=14
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_file_multiblock_read_count=64
*.db_flashback_retention_target=1440
*.db_name='PRABGUN'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=1G
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=true
*.fast_start_mttr_target=0
*.fast_start_parallel_rollback='LOW'
*.global_names=FALSE
*.instance_name='PRABGUN1'
*.instance_number=1
*.job_queue_processes=10
*.large_pool_size=0
*.log_archive_dest_1='LOCATION=+FLASH'
*.log_archive_format='PRABGUN%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_buffer=4190208
*.log_checkpoints_to_alert=false
*.open_cursors=250
*.open_links=4
*.optimizer_mode='choose'
*.parallel_max_servers=25
*.pga_aggregate_target=50M
*.processes=100
*.query_rewrite_enabled='true'
*.query_rewrite_integrity='trusted'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=50
*.sessions=250
*.sga_max_size=368m
*.sga_target=368m
*.shared_pool_size=0
*.star_transformation_enabled='true'
*.streams_pool_size=0
*.tape_asynch_io=true
*.timed_statistics=true
*.undo_management='auto'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='*'
*.workarea_size_policy='AUTO'
23:46 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$
Now add temp file to the temp tablespace using the following command
alter tablespace temp add tempfile '+DATA' size 10m;
Log is as:
SQL> alter tablespace temp add tempfile '+DATA' size 10m;
Tablespace altered.
SQL>
Now bounce the database to make sure everything looks good as follows:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 384143360 bytes
Fixed Size 2228624 bytes
Variable Size 125832816 bytes
Database Buffers 251658240 bytes
Redo Buffers 4423680 bytes
Database mounted.
Database opened.
SQL>
Now you can add it to OCR and add instances on other nodes to this database.
RAAMKALEE, FIRST MEHL:
As you come, so will you leave, you fool; as you were born, so will you die. As you enjoy pleasures, so will you suffer pain. Forgetting the Naam, the Name of the Lord, you will fall into the terrifying world-ocean. || 1 || Gazing upon your body and wealth, you are so proud. Your love for gold and sexual pleasures increases; why have you forgotten the Naam, and why do you wander in doubt? || 1 || Pause || You do not practice truth, abstinence, self-discipline or humility;
the ghost within your skeleton has turned to dry wood. You have not practiced charity, donations, cleansing baths or austerities. Without the Saadh Sangat, the Company of the Holy, your life has gone in vain. || 2 ||
No comments:
Post a Comment