On primary database
ALTER DATABASE FORCE LOGGING;
Did this on primary and then dropped, still not 100% sure where this goes.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
('/u01/oracle/oradata/X1AR/redo01_stb.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11
('/u02/oracle/oradata/X1AR/redo02_stb.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12
('/u03/oracle/oradata/X1AR/redo03_stb.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13
('/u03/oracle/oradata/X1AR/redo04_stb.log') SIZE 50M;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
Create a password file if one does not already exist. Note password of sys needs to be same on both primary and standby.
For ease of modify parameters, I am making the primary database use pfile
create pfile from spfile;
cd /u01/oracle/product/10.2.0/dbs/
mv spfileX1DB.ora x_spfileX1DB.ora
shut immediate;
startup;
Parameters for primary database:
*.DB_NAME=X1DB
*.DB_UNIQUE_NAME=X1DB
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(X1DB,Z1DB)'
*.CONTROL_FILES='/u01/oracle/oradata/X1DB/control01.ctl','/u02/oracle/oradata/X1DB/control02.ctl','/u03/oracle/oradata/X1DB/control03.ctl'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u04/oracle/arch_X1DB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=X1DB'
*.LOG_ARCHIVE_DEST_2='SERVICE=Z1DB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Z1DB'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=Z1DB
*.FAL_CLIENT=X1DB
*.DB_FILE_NAME_CONVERT='Z1DB','X1DB'
*.LOG_FILE_NAME_CONVERT='/u01/oracle/oradata/Z1DB/','/u01/oracle/oradata/X1DB/','/u02/oracle/oradata/Z1DB/','/u02/oracle/oradata/X1DB/','/u03/oracle/oradata/Z1DB/','/u03/oracle/oradata/X1DB/'
*.STANDBY_FILE_MANAGEMENT=AUTO
Make sure primary database in ARCHIVELOG mode and restart
shut immediate;
startup mount;
ALTER DATABASE ARCHIVELOG;
alter database open;
mkdir /u01/oracle/oradata/Z1DB
mkdir /u02/oracle/oradata/Z1DB
mkdir /u03/oracle/oradata/Z1DB
mkdir /u04/oracle/ arch_Z1DB
Backup the primary database, I performed a cold backup, then startup priamy.
startup mount;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/Z1DB.ctl';
alter database open;
Copy control file to standby box
cd /u01/oracle/oradata/Z1DB
scp oracle@lnx-suppdb1:/tmp/Z1DB.ctl .
mv Z1DB.ctl control01.ctl
cp control01.ctl /u02/oracle/oradata/Z1DB/control02.ctl
cp control01.ctl /u03/oracle/oradata/Z1DB/control03.ctl
Copy pfile from primary
cd $ORACLE_HOME/dbs
scp oracle@lnx-suppdb1:$ORACLE_HOME/dbs/initX1DB.ora .
mv initX1DB.ora initZ1DB.ora
Modify the standby parameters.
Make sure all the bdump, cdump… folder are in correct location.
Create password file on standby (same passwd as primary)
orapwd file=$ORACLE_HOME/dbs/orapwZ1DB password=oracle entries=4
Make sure Primary box password file there, or create new one:
orapwd file=$ORACLE_HOME/dbs/orapwX1DB password=oracle entries=4
Make sure entries in tnsnames files and listener know about the databases.
Startup standby database:
export ORACLE_SID=Z1DB
First time round gave error since pfile did not have primary db’s SID, it should have X1DB.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 1879048192 bytes
Fixed Size 2021664 bytes
Variable Size 1308624608 bytes
Database Buffers 553648128 bytes
Redo Buffers 14753792 bytes
ORA-01103: database name 'X1DB' in control file is not 'Z1DB'
SQL> !
sqlplus /nolog
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Error on primary database:
Getting error on primary (alert file), fix was to shutdown both primary and sandby then reviewed both init files and tnsnames files, corrected variables and removed extra spaces from end.
Sat Nov 24 02:40:11 2007
Error 12154 received logging on to the standby
Sat Nov 24 02:40:11 2007
Errors in file /u01/oracle/admin/X1DB/bdump/x1db_arcp_14281.trc:
ORA-12154: Message 12154 not found; No message file for product=RDBMS, facility=ORA
PING[ARCp]: Heartbeat failed to connect to standby 'Z1DB'. Error is 12154.
Test Scenario 1
On primary, switch log file.
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
Alert of standby shows:
Sat Nov 24 22:05:00 2007
RFS[15]: Archived Log: '/u04/oracle/arch_Z1DB/1_44_639415217.arc'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[15]: No standby redo logfiles created
Sat Nov 24 22:05:05 2007
Media Recovery Log /u04/oracle/arch_Z1DB/1_44_639415217.arc
Media Recovery Waiting for thread 1 sequence 45 (in transit)
Test successful no the standby file system, can see:
/u04/oracle/arch_Z1DB
[oracle@lnx-qadb1 arch_Z1DB]$ ls -alrt
total 317556
..
-rw-r----- 1 oracle dba 4941824 Nov 24 22:05 1_44_639415217.arc
-rw-r----- 1 oracle dba 1024 Nov 24 22:29 1_46_639415217.arc
-rw-r----- 1 oracle dba 1398784 Nov 24 22:29 1_45_639415217.arc
..
Test Scenario 2
Execute below SQL on both primary and standby:
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#
Test successful, saw data on both, similar numbers
Test Scenario 3
On primary, add new tablespace
create tablespace INDEXS datafile '/u01/oracle/oradata/X1DB/X1DB_INDEXS_01.dbf'
size 51264K autoextend off logging extent management local
uniform size 512K segment space management auto;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
Alert of standby shows alert switches and adding of tablespace (test successful):
Sat Nov 24 21:48:48 2007
Media Recovery Log /u04/oracle/arch_Z1DB/1_41_639415217.arc
Recovery created file /u01/oracle/oradata/Z1DB/Z1DB_ INDEXS_01.dbf
Successfully added datafile 6 to media recovery
Datafile #6: '/u01/oracle/oradata/Z1DB/Z1DB_INDEXS_01.dbf'
Media Recovery Log /u04/oracle/arch_Z1DB/1_42_639415217.arc
Media Recovery Log /u04/oracle/arch_Z1DB/1_43_639415217.arc
Media Recovery Waiting for thread 1 sequence 44 (in transit)
Alhamdulillah saw Physical Standby 10g working successfully.
No comments:
Post a Comment