This Document is about
RMAN restore from one host to another host. The database will be Refreshing
from PROD to Staging ENVIRONMENT.
PROD TARGET DATABASE
|
KUMARDB
|
Database Type
|
RAC
|
Cluster Nodes
|
3 Nodes
|
Instances
|
KUMARDB1
|
KUMARDB2
|
|
KUMARDB3
|
Staging TARGET DATABASE
|
SUNILDB
|
Database Type
|
RAC
|
Cluster Nodes
|
3 Nodes
|
Instances
|
SUNILDB1
|
SUNILDB2
|
|
SUNILDB3
|
Here are the high level steps:-
1. Have production backup ready, Controlfile
& spfile which is placed on Staging Area
2. Start the instance with nomount mode with
modified pfile
3. Restore the Controlfile from backup piece.
4. Update the correct control file names in
pfile.
5. Mount the database
6. Catalog the database backups
7. Restore
the database & Recover the
database
8. Open the database with reset logs
9. TURN OFF FLASH BACK ON DB if Turned on
10. Create the temp file if it is not
automatically added
11. Change DBNAME KUMARDB to SUNILDB
12. Create spfile on ASM disk
13. Copy the init & password to rest of the
nodes in cluster
14. Add instance entries to /etc/oratab for
rest of the nodes
15. Add Database and Instances to OCR
16. Database verification and validation.
17. Configure the Backups for Newly restored db (Optional Step )
Target DB info: - Below way I want to restore and make naming convention as like
below after restore complete
srvctl status
database -d SUNILDB
Instance SUNILDB1
is running on node host01
Instance SUNILDB2
is running on node host02
Instance SUNILDB3
is running on node host03
srvctl config
database -d SUNILDB
Database unique
name: SUNILDB
Database name:
SUNILDB
Oracle home:
/u01/app/oracle/product/11.2.0.4/DB
Oracle user:
oracle
Spfile: +SUNILDB_DATA/SUNILDB/spfileSUNILDB.ora
Domain:
Start options:
open
Stop options:
immediate
Database role:
PRIMARY
Management policy:
AUTOMATIC
Server pools:
SUNILDB
Database
instances: SUNILDB1,SUNILDB2
Disk Groups:
SUNILDB_DATA,BACKUP
Mount point paths:
Services:
Type: RAC
Database is
administrator managed
Step 1:
COPY the SPFILE to $ORACLE_HOME/dbs
Production Backup Located:
/u09/host01/KUMARDB
Spfile name : /u09/host01/KUMARDB/KUMARdb.pfile
Control file Backup
Piece: /u09/host01/KUMARDB/s_942152983.1345.942152983
cp KUMARdb.pfile
$ORACLE_HOME/dbs/initSUNILDB1.ora
2. Start
the instance with nomount mode with modified pfile
cd
$ORACLE_HOME/dbs
edit the parameter
as like below in initSUNILDB1.ora
remove any old
references file systems below are the change in my initSUNILDB.ora
Notes:
Ø
Just keep DB_NAME as Source db name and change Instance name as
what we want. DB_NAME we can change as we wanted down the steps by using NID
tool
Ø
Remove the parameters whatever its not required if you have any
standby parameters you can remove it like fal_server and
fal_client,dg_broker_start,dg_broker_file1 and dg_broker_file2 and so on.
Ø
Change parameter file values according to your environment
#*.cluster_database=TRUE ====> Turn OFF oracle RAC clustering by
setting False
*.remote_listener='host-scan.staging.cox.net:1521' ===> make sure you update rigth scan name
on new cluster name
*.audit_file_dest='/u00/app/oracle/admin/SUNILDB/adump'
#*.db_domain='WORLD'
=====> in lower env no domain
*.db_name='KUMARDB'
===> leave
like this other wise we cant restore we can change dbname with nid after
restore complete
*.db_unique_name='SUNILDB'
==> Change to SUNILDB from KUMARDB
#*.control_files='/u02/oradata/SUNILDB/
*.db_create_file_dest='+SUNILDB_DATA'
log_archive_dest_1='location=+BACKUP'
*.DB_CREATE_ONLINE_LOG_DEST_1='+REDO' ==> change as per environment
*.DB_CREATE_ONLINE_LOG_DEST_2='+SUNILDB_DATA'
==> change as per environment
#db_file_name_convert='+DATA_DG','+SUNILDB_DATA' ==> comment out its not requried
#log_file_name_convert='+DATA_DG','+REDO' ==> comment out its not requried
*.db_recovery_file_dest='+BACKUP' ==> change proper diskgroup
#*.dg_broker_start=TRUE ==> no dataguard broker in lower
Change U01 ==>
u00 since i dont have u01 mount point.
On All nodes:-
mkdir -p
/u01/app/oracle/admin/SUNILDB/adump
-- start with
above initSUNILDB1.ora database
SQL> startup
nomount pfile='/u01/app/oracle/product/11.2.0.4/DB/dbs/initSUNILDB1.ora'
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size
2252824 bytes
Variable Size
721424360 bytes
Database Buffers
520093696 bytes
Redo Buffers
8892416 bytes
Step
3:- Restore the Control file from
backup piece
rman target /
RMAN> RESTORE CONTROLFILE FROM '/u09/host01/KUMARDB/s_942152983.1345.942152983';
OUTPUT:-
Starting restore
at 26-APR-2017 15:40:29
using target
database control file instead of recovery catalog
allocated channel:
ORA_DISK_1
channel
ORA_DISK_1: SID=2415 device type=DISK
channel
ORA_DISK_1: restoring control file
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+REDO/SUNILdb/controlfile/current.295.942334833
output file name=+SUNILDB_DATA/SUNILdb/controlfile/current.333.942334833
Finished restore
at 26-APR-2017 15:40:36
Verify Restored
Files in ASM:-
[oracle@host01
dbs]$ asmcmd ls -lt +SUNILDB_DATA/SUNILDB/CONTROLFILE
OUTPUT:-
Type Redund Striped
Time Sys Name
CONTROLFILE UNPROT FINE
APR 26 15:00:00 Y current.333.942334833
[oracle@host01
dbs]$ asmcmd ls -lt REDO/SUNILDB/CONTROLFILE
Type Redund Striped
Time Sys Name
CONTROLFILE UNPROT FINE
APR 26 15:00:00 Y current.295.942334833
Step 4:
Update the correct control file names in pfile.
export
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/DB
export
PATH=$ORACLE_HOME/dbs:$PATH
Here is the
updated pfile info under $ORACLE_HOME/dbs
[oracle@host01
dbs]$ grep -i control initSUNILDB1.ora
*.control_file_record_keep_time=30
*.control_files='+SUNILDB_DATA/SUNILdb/controlfile/current.333.942334833','+REDO/SUNILdb/controlfile/current.295.942334833'
shutdown immediate
Step 5:
Mount the database
startup mount pfile='/u01/app/oracle/product/11.2.0.4/DB/dbs/initSUNILDB1.ora'
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size
2252824 bytes
Variable Size
721424360 bytes
Database Buffers
520093696 bytes
Redo Buffers
8892416 bytes
Database mounted.
Step 6:-
catalog the database backups
rman target /
catalog start with
'/u09/host01/KUMARDB';
OUTPTUT:-
RMAN> catalog
start with '/u09/host01/KUMARDB';
-- Starting
implicit crosscheck backup at 12-DEC-16
-- using target
database control file instead of recovery catalog
-- allocated
channel: ORA_DISK_1
-- channel
ORA_DISK_1: SID=1369 device type=DISK
-- Crosschecked 30
objects
-- Finished
implicit crosscheck backup at 12-DEC-16
--
-- Starting
implicit crosscheck copy at 12-DEC-16
-- using channel
ORA_DISK_1
-- Finished
implicit crosscheck copy at 12-DEC-16
--
-- searching for
all files in the recovery area
-- cataloging
files...
-- no files
cataloged
--
-- searching for
all files that match the pattern /XXXXX/XXXXX/SUNILDB/rman
--
-- List of Files
Unknown to the Database
--
=====================================
-- File Name: /XXXXX/XXXXX/SUNILDB/rman/nohup.out
-- File Name: /XXXXX/XXXXX/SUNILDB/rman/rman_full_backup.sh
-- File Name: /XXXXX/XXXXX/SUNILDB/rman/rman_full_backup.log
-- File Name: /XXXXX/XXXXX/SUNILDB/rman/snapcf_SUNILDB.f
--
-- Do you really want
to catalog the above files (enter YES or NO)? YES
-- cataloging
files...
-- cataloging done
--
-- List of
Cataloged Files
--
=======================
-- File Name: /XXXXX/XXXXX/SUNILDB/rman/snapcf_SUNILDB.f
-- File Name: /XXXXX/XXXXX/SUNILDB/rman/ctl_harmn70n_1_1
--
-- List of Files
Which Where Not Cataloged
--
=======================================
-- File Name: /XXXXX/XXXXX/SUNILDB/rman/nohup.out
-- RMAN-07517: Reason: The file header is
corrupted
-- File Name: /XXXXX/XXXXX/SUNILDB/rman/rman_full_backup.sh
-- RMAN-07517: Reason: The file header is
corrupted
-- File Name: /XXXXX/XXXXX/SUNILDB/rman/rman_full_backup.log
-- RMAN-07517: Reason: The file header is
corrupted
Step 7:-
Restore & Recovery Database
-- To Get redo log
file rename statements to place in run block.
set lines 100
select 'ALTER
DATABASE RENAME FILE ' || MEMBER || ' to +SAMP_DATA' || ';' from v$logfile order by group#
cd
/home/oracle/sunil
vi
rman_full_restore.sh
run {
allocate channel
c01 device type disk;
allocate channel
c02 device type disk;
allocate channel
c03 device type disk;
allocate channel
c04 device type disk;
allocate channel
c05 device type disk;
set newname for
database to '+SUNILDB_DATA';
SQL "ALTER
DATABASE RENAME FILE ''+REDO2/KUMARdb/XXXXX/XXXXX//onlinelog/group_101.276.913411639''
to ''+REDO'' ";
SQL "ALTER
DATABASE RENAME FILE ''+REDO1/KUMARdb/XXXXX/XXXXX//onlinelog/group_101.276.913411635''
to ''+REDO'' ";
SQL "ALTER
DATABASE RENAME FILE ''+REDO1/KUMARdb/XXXXX/XXXXX//onlinelog/group_102.277.913411641''
to ''+REDO'' ";
RESTORE DATABASE;
SWITCH DATAFILE
ALL;
SWITCH TEMPFILE
ALL;
RECOVER DATABASE;
release channel
c01;
release channel
c02;
release channel
c03;
release channel
c04;
release channel
c05;
}
[oracle@host01
SUNILDB]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/DB
[oracle@host01
SUNILDB]$ which rman
/u01/app/oracle/product/11.2.0.4/DB/bin/rman
[oracle@host01
SUNILDB]$ echo $ORACLE_SID
SUNILDB1
Run RMAN Job in Background
nohup rman target
"/" cmdfile "rman_full_restore.sh" msglog
"restore_recovery_SUNILDB_04_26_2017.log" &
OUTPUT:-
-- using target database control file instead of
recovery catalog
-- allocated
channel: c01
-- channel c01:
SID=1333 device type=DISK
--
-- allocated
channel: c02
-- channel c02:
SID=1369 device type=DISK
--
-- allocated
channel: c03
-- channel c03:
SID=1405 device type=DISK
--
-- allocated
channel: c04
-- channel c04:
SID=1441 device type=DISK
--
-- allocated
channel: c05
-- channel c05:
SID=1477 device type=DISK
--
-- executing
command: SET NEWNAME
--
-- sql statement:
alter database rename file ''+DATA_DG/SUNILDB/onlinelog/group_4268''
TO ''+REDO''
--
-- Starting
restore at 12-DEC-16
--
-- Starting
restore at 12-DEC-16
--
-- channel c01:
starting datafile backup set restore
-- channel c01:
specifying datafile(s) to restore from backup set
-- channel c01:
restoring datafile 00003 to +SUNILDB_DATA
-- channel c01:
reading from backup piece /XXXXX/XXXXX/SUNILDB/rman/dbf_h6rmn6r6_1_1
-- channel c02:
starting datafile backup set restore
-- channel c02:
specifying datafile(s) to restore from backup set
-- channel c02:
reading from backup piece /XXXXX/XXXXX/SUNILDB/rman/dbf_h7rmn6r6_1_1
-- channel c03:
starting datafile backup set restore
-- channel c03:
specifying datafile(s) to restore from backup set
-- channel c03:
restoring datafile 00001 to +SUNILDB_DATA
-- channel c03:
reading from backup piece /XXXXX/XXXXX/SUNILDB/rman/dbf_h5rmn6r5_1_1
-- channel c04:
starting datafile backup set restore
-- channel c04:
specifying datafile(s) to restore from backup set
-- channel c04:
reading from backup piece /XXXXX/XXXXX/SUNILDB/rman/dbf_h4rmn6r5_1_1
-- channel c05:
starting datafile backup set restore
-- channel c05:
specifying datafile(s) to restore from backup set
-- channel c05:
reading from backup piece /XXXXX/XXXXX/SUNILDB/rman/dbf_h3rmn6r5_1_1
-- channel c01:
piece handle=/XXXXX/XXXXX/SUNILDB/rman/dbf_h6rmn6r6_1_1 tag=FULL_BK
-- channel c01:
restored backup piece 1
-- channel c01:
restore complete, elapsed time: 00:01:15
-- channel c01:
starting datafile backup set restore
-- channel c01:
specifying datafile(s) to restore from backup set
-- channel c01:
restoring datafile 00009 to +SUNILDB_DATA
-- channel c01:
restoring datafile 00022 to +SUNILDB_DATA
-- channel c01:
reading from backup piece /XXXXX/XXXXX/SUNILDB/rman/dbf_h2rmn6r5_1_1
-- channel c02:
piece handle=/XXXXX/XXXXX/SUNILDB/rman/dbf_h7rmn6r6_1_1 tag=FULL_BK
-- channel c02:
restored backup piece 1
-- channel c02:
restore complete, elapsed time: 00:01:17
-- channel c04:
restore complete, elapsed time: 00:03:32
-- channel c01:
piece handle=/XXXXX/XXXXX/SUNILDB/rman/dbf_h2rmn6r5_1_1 tag=FULL_BK
-- channel c01:
restored backup piece 1
-- channel c01:
restore complete, elapsed time: 00:06:27
-- Finished
restore at 12-DEC-16
--
-- datafile 1
switched to datafile copy
-- input datafile
copy RECID=24 STAMP=930402222 file
name=+SUNILDB_DATA/SUNILDB/datafile/system.260.930401759
-- datafile 2
switched to datafile copy
-- input datafile
copy RECID=25 STAMP=930402222 file name=+SUNILDB_DATA/SUNILDB/datafile/sysaux.262.930401759
-- datafile 3
switched to datafile copy
-- input datafile
copy RECID=26 STAMP=930402222 file
name=+SUNILDB_DATA/SUNILDB/datafile/undotbs1.258.930401759
-- datafile 4
switched to datafile copy
-- input datafile
copy RECID=27 STAMP=930402223 file
name=+SUNILDB_DATA/SUNILDB/datafile/users.266.930401761
-- datafile 5
switched to datafile copy
-- input datafile
copy RECID=28 STAMP=930402223 file
name=+SUNILDB_DATA/SUNILDB/datafile/undotbs2.259.930401759
-- datafile 6 switched
to datafile copy
-- input datafile
copy RECID=29 STAMP=930402223 file
name=+SUNILDB_DATA/SUNILDB/datafile/undotbs3.263.930401761
-- datafile 7
switched to datafile copy
-- input datafile
copy RECID=30 STAMP=930402223 file name=+SUNILDB_DATA/SUNILDB/datafile/xxxxxx_data.267.930401761
-- datafile 8
switched to datafile copy
-- input datafile
copy RECID=31 STAMP=930402223 file name=+SUNILDB_DATA/SUNILDB/datafile/XXXXXX_tools_data_idx.268.930401761
--
--
-- Starting
recover at 12-DEC-16
--
-- starting media
recovery
--
-- channel c01:
starting archived log restore to default destination
-- channel c01:
restoring archived log
-- archived log
thread=3 sequence=14506
-- channel c01:
reading from backup piece /XXXXX/XXXXX/SUNILDB/rman/arch_hgrmn717_1_1
-- channel c02:
starting archived log restore to default destination
-- channel c02:
restoring archived log
-- channel c02:
reading from backup piece /XXXXX/XXXXX/SUNILDB/rman/arch_hhrmn71f_1_1
-- channel c02:
piece handle=/XXXXX/XXXXX/SUNILDB/rman/arch_hhrmn71f_1_1 tag=ARCHIVELOG_BK
-- channel c02:
restored backup piece 1
-- channel c02:
restore complete, elapsed time: 00:00:01
-- channel c01:
piece handle=/XXXXX/XXXXX/SUNILDB/rman/arch_hgrmn717_1_1 tag=ARCHIVELOG_BK
-- channel c01:
restored backup piece 1
-- channel c01:
restore complete, elapsed time: 00:00:07
-- archived log
file
name=+BACKUP/SUNILDB/archivelog/2016_12_12/thread_1_seq_16599.259.930402235
thread=1 sequence=16599
-- archived log
file
name=+BACKUP/SUNILDB/archivelog/2016_12_12/thread_1_seq_16600.256.930402231
thread=1 sequence=16600
-- channel
default: deleting archived log(s)
-- archived log
file name=+BACKUP/SUNILDB/archivelog/2016_12_12/thread_1_seq_16600.256.930402231
RECID=45758 STAMP=930402232
-- unable to find
archived log
-- archived log
thread=1 sequence=16601
-- released
channel: c01
-- released
channel: c02
-- released
channel: c03
-- released
channel: c04
-- released
channel: c05
-- RMAN-00571:
===========================================================
-- RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
-- RMAN-00571:
===========================================================
-- RMAN-03002:
failure of recover command at 12/12/2016 13:04:00
-- RMAN-06054:
media recovery requesting unknown archived log for thread 1 with sequence 16601
and starting SCN of 10770907615162
Step 8:
Open the database with reset logs
SQL> alter
database open resetlogs;
Step 9:-
TURN OFF FLASH BACK ON DB if Turned on
SQL> select
open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select
FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter
database flashback off;
Database altered.
SQL> select
FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
Step 10:-
Add tempfile if it is not automatically added
-- Check if Temp
is there or not
select file_name
from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
+SUNILDB_DATA/SUNILdb/tempfile/temp.267.942404435
-- Shutdown
database
shutdown immediate
-- rename spfile
which is restore as first step if you have any
mv spfileSUNILDB1.ora spfileSUNILDB1.ora.org
-- No need to
create the Temp file in this case.
Step 11:-
Change DBNAME TO SUNILDB FROM KUMARDB
i)
Create Password File
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=XXXXXXXX entries=16 force=y
change the dbname
with NID (shutdown immediate; startup
mount; nid target=/ dbname=SUNILDB)
ii)
Change Cluster_database=False if it’s true
SQL> show
parameter cluster
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
While changing the
DB_NAME we shoud keep CLUSTER_DATABASE=FALSE if not execute below command to
keep
SQL> alter
system set cluster_database=FALSE scope=spfile sid='*';
System altered.
SQL> shu
immediate
ORA-01109:
database not open
Database
dismounted.
ORACLE instance
shut down.
iii)
Keep database Mount state and change db name
SQL> startup
mount
nid
target=/ dbname=SUNILDB
OUTPUT:-
-- DBNEWID:
Release 11.2.0.4.0 - Production on Wed Apr 26 12:00:47 2017
--
-- Copyright (c)
1982, 2011, Oracle and/or its affiliates.
All rights reserved.
--
-- Connected to
database KUMARDB (DBID=1598455984)
--
-- Connected to
server version 11.2.0
--
-- Control Files
in database:
--
+SUNILDB_DATA/SUNILdb/controlfile/current.279.942252229
--
+REDO/SUNILdb/controlfile/current.295.942252229
--
-- Change database
ID and database name KUMARDB to SUNILDB? (Y/[N]) => Y
--
-- Proceeding with
operation
-- Changing
database ID from 1598455984 to 4252179825
-- Changing
database name from KUMARDB to SUNILDB
-- Control File
+SUNILDB_DATA/SUNILdb/controlfile/current.279.942252229 - modified
-- Control File
+REDO/SUNILdb/controlfile/current.295.942252229 - modified
-- Datafile
+SUNILDB_DATA/SUNILdb/datafile/system.296.94225761 - dbid changed, wrote new
name
-- Datafile
+SUNILDB_DATA/SUNILdb/datafile/sysaux.291.94225761 - dbid changed, wrote new
name
-- Datafile +SUNILDB_DATA/SUNILdb/datafile/undotbs1.287.94225755
- dbid changed, wrote new name
-- Datafile
+SUNILDB_DATA/SUNILdb/datafile/undotbs2.273.94225366 - dbid changed, wrote new
name
-- Datafile
+SUNILDB_DATA/SUNILdb/datafile/undotbs3.259.94225708 - dbid changed, wrote new
name
-- Datafile
+SUNILDB_DATA/SUNILdb/datafile/users.307.94225770 - dbid changed, wrote new
name
-- Datafile +SUNILDB_DATA/SUNILdb/datafile/XXXXXXx_data.270.94225366
- dbid changed, wrote new name
--
--
-- Datafile
+SUNILDB_DATA/SUNILdb/tempfile/temp.320.94231878 - dbid changed, wrote new name
-- Control File
+SUNILDB_DATA/SUNILdb/controlfile/current.279.942252229 - dbid changed, wrote
new name
-- Control File
+REDO/SUNILdb/controlfile/current.295.942252229 - dbid changed, wrote new name
-- Instance shut down
--
--
Database name changed to SUNILDB.
--
Modify parameter file and generate a new password file before restarting.
--
Database ID for database SUNILDB changed to 4252179825.
--
All previous backups and archived redo logs for this database are unusable.
--
Database is not aware of previous backups and archived logs in Recovery Area.
--
Database has been shutdown, open database with RESETLOGS option.
--
Succesfully changed database name and ID.
--
DBNEWID - Completed succesfully.
iv)
Change DB_NAME in init.ora
Change DB_NAME in
initSUNILDB1.ora
[oracle@host01
dbs]$ grep -i db_name initSUNILDB1.ora
*.db_name='SUNILDB'
v)
Open DB with Reset logs
SQL> startup
mount
ORACLE instance
started.
Total System
Global Area 3.4206E+10 bytes
Fixed Size 2270360 bytes
Variable Size 1.0335E+10 bytes
Database
Buffers 2.3824E+10 bytes
Redo Buffers 45649920 bytes
Database mounted.
SQL> alter
database open resetlogs;
SQL> show
parameter name
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string SUNILDB
db_unique_name string SUNILDB
global_names boolean FALSE
instance_name string SUNILDB1
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string SUNILDB
SQL> shutdown
immediate
vi)
Uncomment the few parameters in
initSUNILDB1.ora
grep cluster_database initSUNILDB1.ora
*.cluster_database=true
start DB with
CLUSTER_DATABASE=TRUE
SQL> startup
ORACLE instance
started.
Total System
Global Area 3.4206E+10 bytes
Fixed Size 2270360 bytes
Variable Size 1.0335E+10 bytes
Database
Buffers 2.3824E+10 bytes
Redo Buffers 45649920 bytes
Database mounted.
Database opened.
SQL> show
parameter cluster
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 3
cluster_interconnects string
Step
12:- Create the spfile in ASM disk
We could do this
step at the beginning too. However, i do
this step at the end for my convenience.
SQL> show
parameter spfile;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string
SQL>
SQL> create
spfile='+SUNILDB_DATA/SUNILDB/PARAMETERFILE/spfileSUNILDB.ora' from PFILE;
File created.
SQL> shutdown
immediate
-- Here i updated
the parameter file to point to SPFILE in ASM disk.
cat
initSUNILDB1.ora
SPFILE='+SAMP_DATA/sbwodb/PARAMETERFILE/spfileSBWODB.ora'
mv initSUNILDB1.ora initSUNILDB1.ora.org
[oracle@host01
dbs]$ cat initSUNILDB1.ora
SPFILE='+SUNILDB_DATA/SUNILDB/PARAMETERFILE/spfileSUNILDB.ora'
Step 13.
Now copy the init & password to rest of the nodes in cluster:-
cd
$ORACLE_HOME/dbs
scp orapwSUNILDB1
oracle@host02:/u01/app/oracle/product/11.2.0.4/DB/dbs/orapwSUNILDB2
scp orapwSUNILDB1
oracle@host03:/u01/app/oracle/product/11.2.0.4/DB/dbs/orapwSUNILDB3
scp
initSUNILDB1.ora
oracle@host02:/u01/app/oracle/product/11.2.0.4/DB/dbs/initSUNILDB2.ora
scp initSUNILDB1.ora
oracle@host03:/u01/app/oracle/product/11.2.0.4/DB/dbs/initSUNILDB3.ora
-- verify contents
ssh oracle@host02
cat /u01/app/oracle/product/11.2.0.4/DB/dbs/initSUNILDB2.ora
ssh oracle@host03
cat /u01/app/oracle/product/11.2.0.4/DB/dbs/initSUNILDB3.ora
Step 14:
Add instance intries to /etc/oratab for rest of the nodes
ssh host02
grep -i SUNILDB
/etc/oratab
SUNILDB2:/u00/app/oracle/product/11.2.0.4/db:N
ssh host03
grep -i SUNILDB
/etc/oratab
SUNILDB3:/u00/app/oracle/product/11.2.0.4/db:N
Add
required directories on rest of the nodes: - if required only
ssh oracle@host01
mkdir -p /u01/app/oracle/admin/SUNILDB/adump
ssh oracle@host02
mkdir -p /u01/app/oracle/admin/SUNILDB/adump
ssh oracle@host03
mkdir -p /u01/app/oracle/admin/SUNILDB/adump
Step 15:
Now Add Database to OCR/Cluster
-- To verify is
there any databases exists
srvctl config
database
-- add database
and instances
srvctl add
database -d SUNILDB -o /u01/app/oracle/product/11.2.0.4/DB -p
+SUNILDB_DATA/SUNILDB/PARAMETERFILE/spfileSUNILDB.ora
srvctl add
instance -d SUNILDB -i SUNILDB1 -n host01
srvctl add
instance -d SUNILDB -i SUNILDB2 -n host02
srvctl add
instance -d SUNILDB -i SUNILDB3 -n host03
> srvctl status
database -d SUNILDB
Instance SUNILDB1
is not running on node host01
Instance SUNILDB2
is not running on node host02
Instance SUNILDB3
is not running on node host03
> srvctl config
database -d SUNILDB
Database unique
name: SUNILDB
Database name:
Oracle home:
/u01/app/oracle/product/11.2.0.4/DB
Oracle user:
oracle
Spfile:
+SUNILDB_DATA/SUNILDB/PARAMETERFILE/spfileSUNILDB.ora
Domain:
Start options:
open
Stop options: immediate
Database role:
PRIMARY
Management policy:
AUTOMATIC
Server pools:
SUNILDB
Database
instances: SUNILDB1,SUNILDB2,SUNILDB3
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is
administrator managed
-- Start database
with srvctl ( we placed init files db_homes )
> srvctl start
database -d SUNILDB
> srvctl status
database -d SUNILDB
Instance SUNILDB1
is running on node host01
Instance SUNILDB2
is running on node host02
Instance SUNILDB3
is running on node host03
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 3
cluster_interconnects string
SQL> show
parameter pfile
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string
+SUNILDB_DATA/SUNILdb/parame
terfile/spfileSUNILdb.ora
Step 16)
Database verification and validation
col HOST_NAME for
a30
select
instance_name,host_name from gv$instance;
INSTANCE_NAME HOST_NAME
----------------
------------------------------
SUNILDB1 host01
SUNILDB3 host03
SUNILDB2 host02
select banner from
gv$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database
11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release
11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux:
Version 11.2.0.4.0 - Production
NLSRTL Version
11.2.0.4.0 - Production
Oracle Database
11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release
11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux:
Version 11.2.0.4.0 - Production
NLSRTL Version
11.2.0.4.0 - Production
Oracle Database
11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
BANNER
--------------------------------------------------------------------------------
PL/SQL Release
11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux:
Version 11.2.0.4.0 - Production
NLSRTL Version
11.2.0.4.0 - Production
15 rows selected.
Check
all Components registries are valid state or not
SQL> set line 200;
set pagesize 9999;
col COMP_ID format a15;
col COMP_NAME format a50;
select COMP_ID,COMP_NAME,STATUS from
dba_registry
/
COMP_ID COMP_NAME
STATUS
---------------
-------------------------------------------------- -----------
APEX Oracle Application Express VALID
AMD OLAP Catalog VALID
SDO Spatial
VALID
ORDIM Oracle Multimedia VALID
XDB Oracle XML Database VALID
--
--
RAC Oracle Real Application
Clusters VALID
17 rows selected.
Database
Size:-
select
ROUND(dbspace.u+logspace.aa,2) "USED SIZE",
ROUND(dbspace.t+logspace.aa,2) "TOTAL SIZE"
from
(select
sum(Free_space) f,sum(Used_space) u,sum(total_space) t
from
(SELECT Total.name
"Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0)
Used_space,
total_space
FROM
(select tablespace_name,
sum(bytes/1024/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace
B
where a.ts# = b.ts#
group by b.name
) Total
WHERE
Free.Tablespace_name(+) = Total.name
ORDER BY
Total.name)) dbspace,
(select
sum(bytes)/1024/1024/1024 aa from v$log) logspace
/
USED SIZE TOTAL SIZE
----------
----------
198.36
565.82
set pages 10000
select name from
gv$datafile
union
select name from
gv$controlfile
union
select member from
gv$logfile
union
select name from
gv$tempfile;
NAME
--------------------------------------------------------------------------------
+REDO/SUNILdb/controlfile/current.295.942334833
+REDO/SUNILdb/onlinelog/group_101.316.942405293
+REDO/SUNILdb/onlinelog/group_102.315.942405331
+REDO/SUNILdb/onlinelog/group_103.314.942405363
--
--
SQL> alter
system switch logfile;
System altered.
Switch few logs
and verify is there any errors on alert.log make sure any errors.
Step 17:
Configure BAckups and Take one Successful backup
1) Full backup
Script
run
{
allocate channel
c01 device type disk;
allocate channel
c02 device type disk;
allocate channel
c03 device type disk;
allocate channel
c04 device type disk;
allocate channel
c05 device type disk;
allocate channel
c06 device type disk;
BACKUP FORMAT '/u09/host01/SUNILDB/rman/dbf_%U'
DATABASE TAG Full_bk;
BACKUP CURRENT
ControlFile FORMAT '/u09/host01/SUNILDB/rman/ctl_%U';
sql 'alter system
archive log current';
BACKUP FORMAT '/u09/host01/SUNILDB/rman/arch_%U'
ARCHIVELOG ALL TAG Archivelog_bk DELETE INPUT;
RESTORE DATABASE
VALIDATE;
CROSSCHECK BACKUP
OF DATABASE;
REPORT OBSOLETE;
DELETE NOPROMPT
OBSOLETE;
release channel
c01;
release channel
c02;
release channel c03;
release channel
c04;
release channel
c05;
release channel
c06;
}
LIST BACKUP
SUMMARY;
2) Archive log Backups
run
{
BACKUP CURRENT
ControlFile FORMAT '/u09/host01/SUNILDB/rman/ctl_%U';
sql 'alter system
archive log current';
BACKUP FORMAT '/u09/host01/SUNILDB/rman/arch_%U'
ARCHIVELOG ALL TAG Archivelog_bk DELETE INPUT;
REPORT OBSOLETE;
DELETE NOPROMPT
OBSOLETE;
}
No comments:
Post a Comment