Create directory under admin
g3u0214c:home/oracle $ cd $ORACLE_ADMIN
g3u0214c:oracle/admin $ pwd
/u01/app/oracle/admin
g3u0214c:oracle/admin $ mkdir SPPD
g3u0214c:oracle/admin $ cd SPPD
g3u0214c:admin/SPPD $ mkdir bdump
g3u0214c:admin/SPPD $ mkdir cdump
g3u0214c:admin/SPPD $ mkdir create
g3u0214c:admin/SPPD $ mkdir pfile
g3u0214c:admin/SPPD $ mkdir udump
g3u0214c:admin/SPPD $
Create directories in origlog/mirrlog/oradata
g3u0214c:u02/origlog $ cd /u02/origlog
g3u0214c:u02/origlog $ pwd
/u02/origlog
g3u0214c:u02/origlog $ mkdir SPPD
g3u0214c:u02/origlog $ cd /u03/mirrlog
g3u0214c:u03/mirrlog $ pwd
/u03/mirrlog
g3u0214c:u03/mirrlog $ mkdir SPPD
g3u0214c:u03/mirrlog $ cd /u05/oradata
g3u0214c:u05/oradata $ pwd
/u05/oradata
g3u0214c:u05/oradata $ mkdir SPPD
Create directories in flash_recovery
g3u0214c:u05/oradata $ cd /u04/app/oracle/flash_recovery
g3u0214c:oracle/flash_recovery $ pwd
/u04/app/oracle/flash_recovery
g3u0214c:oracle/flash_recovery $ mkdir SPPD
Create directories in archive destination
g3u0214c:db_1/dbs $ cd /u01/app/oracle/arch/db_12
g3u0214c:arch/db_12 $ ll
total 0
drwxr-xr-x 2 root root 96 Nov 3 19:01 lost+found
g3u0214c:arch/db_12 $ mkdir SPPD
Create init.ora
Make a copy SPPD init file
Change database name
Change file paths
Change memory size parameter
Create pwfile
g3u0214c:arch/db_12 $ set termout on
g3u0214c:arch/db_12 $ orapwd file=orapwSPPD password=syspass
g3u0214c:arch/db_12 $ ll
total 6
drwxr-xr-x 2 oracle dba 96 Nov 20 02:23 SPPD
drwxr-xr-x 2 root root 96 Nov 3 19:01 lost+found
-rw-r----- 1 oracle dba 3072 Nov 20 02:28 orapwSPPD
Move pwfile to pfile directory
g3u0214c:arch/db_12 $ mv orapwSPPD $ORACLE_ADMIN/SPPD/pfile
g3u0214c:arch/db_12 $ cd $ORACLE_ADMIN/SPPD/pfile
g3u0214c:SPPD/pfile $ ll
total 10
-rw-r--r-- 1 oracle dba 1604 Nov 20 02:25 initSPPD1.ora
-rw-r----- 1 oracle dba 3072 Nov 20 02:28 orapwSPPD
g3u0214c:SPPD/pfile $ pwd
/u01/app/oracle/admin/SPPD/pfile
Create link to pwfile on node1
g3u0214c:SPPD/pfile $ ln -s /u01/app/oracle/admin/SPPD/pfile/orapwSPPD
$ORACLE_HOME/dbs/orapwSPPD1
Create link to pwfile on node 2
g3u0215c:SPPD/pfile $ cd $ORACLE_ADMIN/SPPD/pfile
g3u0215c:SPPD/pfile $ pwd
/u01/app/oracle/admin/SPPD/pfile
g3u0215c:SPPD/pfile $ ll
total 10
-rw-r--r-- 1 oracle dba 1604 Nov 20 02:25 initSPPD1.ora
-rw-r----- 1 oracle dba 3072 Nov 20 02:28 orapwSPPD
g3u0215c:SPPD/pfile $ ln -s /u01/app/oracle/admin/SPPD/pfile/orapwSPPD
$ORACLE_HOME/dbs/orapwSPPD2
Create temp file
g3u0214c:home/oracle $ cd /u05/oradata/SPPD
g3u0214c:oradata/SPPD $ ll
total 0
g3u0214c:oradata/SPPD $ dd if=/dev/zero of=SPPDtemp_01.dbf bs=1024 count=5120000
5120000+0 records in
5120000+0 records out
Modify tnsnames.ora
## SPPD added by Ke Guo on 11/19/2006
LISTENER_SPPD =
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = g3u0214c.houston.hp.com)(PORT = 1525))
(ADDRESS = (PROTOCOL = TCP)(HOST = g3u0215c.houston.hp.com)(PORT = 1525))
)
LISTENER_SPPD1 =
(DESCRIPTION =
(SDU = 32768)
(ADDRESS = (PROTOCOL = TCP)(Host = g3u0214c.houston.hp.com)(Port = 1525))
)
LISTENER_SPPD2 =
(DESCRIPTION =
(SDU = 32768)
(ADDRESS = (PROTOCOL = TCP)(Host = g3u0215c.houston.hp.com)(Port = 1525))
)
SPPD =
(DESCRIPTION =
(SDU = 32768)
(enable = broken)
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = gvu0248.houston.hp.com)(PORT = 1525))
(ADDRESS = (PROTOCOL = TCP)(HOST = gvu0249.houston.hp.com)(PORT = 1525))
(CONNECT_DATA =
(SERVICE_NAME = SPPD)
)
)
SPPD1 =
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = g3u0214c.houston.hp.com)(Port = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = SPPD )
(INSTANCE_NAME = SPPD1)
)
)
SPPD2 =
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = g3u0215c.houston.hp.com)(Port = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = SPPD )
(INSTANCE_NAME = SPPD2)
)
)
Create DB
Modify init.ora file and change CLSUTER_DATABASE=FALSE
Make a copy of the crdb.sql and crdb_2.sql from SPPD
g3u0214c:SPPD/create $ export ORACLE_SID=SPPD1
g3u0214c:SPPD/create $ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Nov 20 03:58:38 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options
SQL> startup nomount pfile=/u01/app/oracle/admin/SPPD/pfile/initSPPD1.ora
ORACLE instance started.
Total System Global Area 1786773504 bytes
Fixed Size 2251568 bytes
Variable Size 394077392 bytes
Database Buffers 1388314624 bytes
Redo Buffers 2129920 bytes
SQL> @crdb.sql
Database created.
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 SPPD1
g3u0214c
10.2.0.2.0 20-NOV-06 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL> @crdb_2.sql
Add undo and redo for the 2nd instance
create undo tablespace UNDO_02
datafile '/u05/oradata/SPPD/SPPDundo_02_01.dbf' size 300M;
alter database add logfile thread 2
group 4 ('/u02/origlog/SPPD/SPPDlog_t2_g4_m1.dbf', '/u03/mirrlog/SPPD/SPPDlog_t2_g4_m2.dbf')
SIZE 10M,
group 5 ('/u02/origlog/SPPD/SPPDlog_t2_g5_m1.dbf', '/u03/mirrlog/SPPD/SPPDlog_t2_g5_m2.dbf')
SIZE 10M,
group 6 ('/u02/origlog/SPPD/SPPDlog_t2_g6_m1.dbf', '/u03/mirrlog/SPPD/SPPDlog_t2_g6_m2.dbf')
SIZE 10M;
alter database enable public thread 2;
Change to cluster database
*.cluster_database=TRUE
Shutdown and restart
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !pwd
/u01/app/oracle/admin/SPPD/pfile
SQL> !ll
total 10
-rw-r--r-- 1 oracle dba 1604 Nov 20 04:23 initSPPD1.ora
-rw-r----- 1 oracle dba 3072 Nov 20 02:28 orapwSPPD
SQL> startup pfile=/u01/app/oracle/admin/SPPD/pfile/initSPPD1.ora
ORACLE instance started.
Total System Global Area 1786773504 bytes
Fixed Size 2251568 bytes
Variable Size 389883088 bytes
Database Buffers 1392508928 bytes
Redo Buffers 2129920 bytes
Database mounted.
Database opened.
SQL>
Create spfile and modify pfile under $ORACLE_HOME/dbs to point to it
SQL> create spfile='/u01/app/oracle/admin/SPPD/pfile/spfileSPPD.ora' from
pfile='/u01/app/oracle/admin/SPPD/pfile/initSPPD1.ora';
File created.
g3u0214c:db_1/dbs $ vi initSPPD1.ora
g3u0215c:db_1/dbs $ vi initSPPD2.ora
spfile='/u01/app/oracle/admin/SPPD/pfile/spfileSPPD.ora'
restart both instance
add database to crs
g3u0214c:home/oracle $ which srvctl
/u01/app/oracle/product/10.2.0/crs/bin/srvctl
g3u0214c:home/oracle $ srvctl add database -d SPPD -o $ORACLE_HOME
g3u0214c:home/oracle $ srvctl add instance -d SPPD -i SPPD1 -n g3u0214c
g3u0214c:home/oracle $ srvctl add instance -d SPPD -i SPPD2 -n g3u0215c
g3u0214c:home/oracle $ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....D1.inst application OFFLINE OFFLINE
ora....D2.inst application OFFLINE OFFLINE
ora.SPPD.db application OFFLINE OFFLINE
……
Shutdown from sql plus and restart db using srvctl
g3u0214c:home/oracle $ srvctl start database -d SPPD
g3u0215c:home/oracle $ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....D1.inst application ONLINE ONLINE g3u0214c
ora....D2.inst application ONLINE ONLINE g3u0215c
ora.SPPD.db application ONLINE ONLINE g3u0213c
….
Turn on archived log mode
Alter system set log_archive_start = true scope=spfile
Srvctl stop database –d SPPD
Srvctl start database –d SPPD –o mount
Alter database archivelog;
Srvctl start database –d SPPD –o open;
Turn flashback on
Alter system set DB_FLASHBACK_RETENTION_TARGET=1440;
srvctl stop database –d SPPD
srvctl start instance –d SPPD –I SPPD1 –o mount
1. ALTER DATABASE FLASHBACK ON;
Setup backup
Add rman catalog db in tnsnames.ora file if not there
SQL> execute
rmancatalog_setup('omni','rman','/u01/app/oracle/admin/SPPD/backups/rman_snapshot_ctl.f', NULL);
This init parameter is not set correctly:
PL/SQL procedure successfully completed.
g3u0214c:oracle/rman_backups $ export ORACLE_SID=SPPD1
g3u0214c:oracle/rman_backups $ rman target / catalog rman@RMAN01I cmdfile=db_initial_setup.cmd
Recovery Manager: Release 10.2.0.2.0 - Production on Tue Nov 21 21:40:21 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: SPPD (DBID=1380908705)
recovery catalog database Password:
connected to recovery catalog database
RMAN> REGISTER DATABASE;
2> CONFIGURE CHANNEL DEVICE TYPE SBT PARMS='SBT_LIBRARY=/opt/omni/lib/libob2oracle
8_64bit.so';
3> CONFIGURE DEFAULT DEVICE TYPE TO SBT;
4> CONFIGURE CONTROLFILE AUTOBACKUP ON;
5> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT TO
'control_file_%F';
6> CONFIGURE BACKUP OPTIMIZATION ON;
7> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 45 DAYS;
8> #CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/u01/app/oracle/admin/<DBNAME>/backups/rman_snapshot_ctl.f
';
9> ##CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
10>
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS
'SBT_LIBRARY=/opt/omni/lib/libob2oracle 8_64bit.so';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO
'control_file_%F';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 45 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
Recovery Manager complete.
Send backup request to backup team
From: Guo, Ke
Sent: Tuesday, November 21, 2006 3:49 PM
To: Johnston, Mike
Subject: DCC Database Backup Request - SPPD
Server name: g3u0214c,g3u0215c
Virtual server names: gcu00025.houston.hp.com
Database name: SPPD
Database Backup Specification: Small
ORACLE_HOME: /u01/app/oracle/product/10.2.0/db_1
Rman catalog service: (rman, <password to be sent
separately>, RMAN01I.WORLD)
Database Service Name: (omnibackup, <password to be sent
separately>, SPPD.WORLD)
