Showing posts with label diaster recovery. Show all posts
Showing posts with label diaster recovery. Show all posts
Thursday, February 14, 2013
partition in a partitioned table truncated by mistake
Import partition data
A partition in a partitioned table was truncated by mistake and data needs to be imported from an export dump.
However instead of importing the data directly into the original table, the plan is to import the partition data into the temporary user and ratify the data before importing it to the original table using partition exchange.
Steps -
1. Create a temporary user.
2. Import partition data only into table in the temporary user
3. Ratify data.
4. Move segments in temporary table into new non-partitioned table.
5. Move this non-partitioned table into the original users tablespace.
6. Exchange partition between temporary and original partition and clean up.
NOTE -
ORIGINAL OWNER is PART_OWNER
TABLESPACE_NAME IS TEST_TBS
TABLE_NAME is TEST
PARTITION IS TEST_PART_DEC
1. Create temporary user with separate tablespace -
Temporary user called PART_RESTORE.
2. Import partition data only into table in the temporary user
imp userid/password file=test_export.dmp log=test_import.log fromuser=PART_OWNER touser=PART_RESTORE tables=TEST:TEST_PART_DEC feedback=10000 buffer = 64000 ignore=y &
3. RATIFY DATA IN PART_RESTORE SCHEMA.
4. Move segments fron temporary table into new non-partitioned table.
CREATE TABLE PART_RESTORE.TEST_TEMP
AS SELECT * FROM PART_RESTORE.TEST
WHERE ROWNUM<1;
5. Move this non-partitioned table into the original users tablespace.
ALTER TABLE PART_RESTORE.TEST_TEMP MOVE TABLESPACE TEST_TBS;
6. Exchange partition between temporary and original partition and clean up.
– set it as a nologging table
ALTER TABLE PART_RESTORE.TEST_TEMP NOLOGGING;
– move the data into the temp table.
INSERT /*+ APPEND */INTO PART_RESTORE.TEST_TEMP
SELECT *
FROM PART_RESTORE.TEST;
COMMIT;
— exchange the partition into the final PART_OWNER TABLE
ALTER TABLE PART_OWNER.TEST
EXCHANGE PARTITION TEST_PART_DEC
WITH TABLE PART_RESTORE.TEST_TEMP
UPDATE GLOBAL INDEXES;
– rebuild any unusable local indexes
ALTER TABLE PART_OWNER.TEST
MODIFY PARTITION TEST_PART_DEC
REBUILD UNUSABLE LOCAL INDEXES;
– gather stats on the new partition…
begin
DBMS_STATS.GATHER_TABLE_STATS (ownname => PART_OWNER, tabname => ‘TEST’, partname => ‘TEST_PART_DEC’, estimate_percent => 5, degree => 1, granularity => ‘ALL’, cascade => FALSE);
end;
/
– drop the ofsa_restored temp table…
DROP TABLE PART_RESTORE.TEST_TEMP ;
Wednesday, December 14, 2011
Switchover and Failover steps
SWITCH OVER
1. SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected
2. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
3. SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
4. SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
------------
TO_PRIAMRY
5. at standby ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
6. alter database open [if db opened read only since last time it was started]
else shutdown and restart
7. ALTER SYSTEM SWITCH LOGFILE;
FAILOVER
First resolve gap:
A) Identify and resolve any gaps in the archived redo log files.
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
B) Repeat A) until all gaps are resolved.
C) Copy any other missing archived redo log files.
SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
2> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
THREAD LAST
---------- ----------
1 100
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
now initiate failover at standby
1. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
2. ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
3. alter database open [if db opened read only since last time it was started]
else shutdown and restart
1. SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected
2. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
3. SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
4. SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
------------
TO_PRIAMRY
5. at standby ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
6. alter database open [if db opened read only since last time it was started]
else shutdown and restart
7. ALTER SYSTEM SWITCH LOGFILE;
FAILOVER
First resolve gap:
A) Identify and resolve any gaps in the archived redo log files.
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
B) Repeat A) until all gaps are resolved.
C) Copy any other missing archived redo log files.
SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
2> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
THREAD LAST
---------- ----------
1 100
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
now initiate failover at standby
1. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
2. ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
3. alter database open [if db opened read only since last time it was started]
else shutdown and restart
Monday, March 14, 2011
Multiplexing of Redo logs
SELECT group#, members, status, bytes FROM v$log;
SELECT group#, member, type FROM v$logfile;
ALTER DATABASE ADD LOGFILE MEMBER 'C:\REDO1011\REDO1.LOG' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER 'C:\REDO1011\REDO2.LOG' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER 'C:\REDO1011\REDO3.LOG' TO GROUP 3;
SELECT group#, members, status, bytes FROM v$log;
SELECT group#, member, type FROM v$logfile;
SELECT group#, member, type FROM v$logfile;
ALTER DATABASE ADD LOGFILE MEMBER 'C:\REDO1011\REDO1.LOG' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER 'C:\REDO1011\REDO2.LOG' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER 'C:\REDO1011\REDO3.LOG' TO GROUP 3;
SELECT group#, members, status, bytes FROM v$log;
SELECT group#, member, type FROM v$logfile;
Friday, August 6, 2010
Oracle 10g Standby Database
Oracle 10g Standby Database
--------------------------------------
PRODUCTION DATABASE: 10.100.0.65
STANDBY DATABASE: 10.100.0.32
-----------------I. Before you get started:-------------------
1. Make sure the operating system and platform architecture on the primary and standby systems are the same;
2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.
---------II. On the Primary Database Side:---------------------
Enable forced logging on your primary database:
Select FORCE_LOGGING from V$DATABASE;
ALTER DATABASE FORCE LOGGING;
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;
3) Create standby Redo log groups.
My primary database had 5 log file groups originally and I created 5 standby redo log groups using the following commands:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 SIZE 50M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;
-----------NO NEED--------------5) Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
6) Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
A) Create pfile from spfile for the primary database:
SQL>create pfile='d:\oracle\product\10.2.0\db_1\database\INITSNS6.ORA' from spfile;
B) Edit INITSNS6.ora to add the new primary and standby role parameters:
select * from v$parameter where name like '%log_archive_format%';
select * from v$parameter where name like '%standby%';
select * from v$parameter where name like '%remote_archive_enable%';
select * from v$parameter where name like '%log_archive_dest_state_%';
select * from v$parameter where name like '%convert%';
----------------------INITSNS1011.ORA------------------
sns6.__db_cache_size=1006632960
sns6.__java_pool_size=8388608
sns6.__large_pool_size=8388608
sns6.__shared_pool_size=645922816
sns6.__streams_pool_size=0
*.audit_file_dest='d:\oracle\product\10.2.0\admin\sns1011\adump'
*.audit_trail='DB'
*.background_dump_dest='d:\oracle\product\10.2.0\admin\sns1011\bdump'
*.compatible='10.2.0.3.0'
*.control_files='e:\snsd1011\control01.ora','e:\snsd1011\control02.ora','e:\snsd1011\control03.ora'
*.core_dump_dest='d:\oracle\product\10.2.0\admin\sns1011\cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='sns1011'
*.job_queue_processes=35
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(sns1011,sns1011sby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=D:\archive0910\sns1011\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sns1011'
*.LOG_ARCHIVE_DEST_2='SERVICE=sns1011sby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sns1011sby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=sns1011sby
*.FAL_CLIENT=sns1011
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\admin\sns1011sby','d:\oracle\product\10.2.0\admin\sns1011'
*.LOG_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\admin\sns1011sby','d:\oracle\product\10.2.0\admin\sns1011'
*.log_archive_format='ARC%S_%R.%T'
*.open_cursors=300
*.pga_aggregate_target=337641472
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.sga_max_size=1677721600
*.sga_target=1677721600
*.smtp_out_server='mail.uniconindia.in'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='d:\oracle\product\10.2.0\admin\sns1011\udump'
*.utl_file_dir='d:\ldoutput'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sns1011XDB)'
-------------------
C. Create spfile from pfile, and restart primary database using the new spfile.
SQL> shutdown immediate;
SQL> startup nomount pfile='d:\oracle\product\10.2.0\db_1\database\INITSNS1011.ORA';
SQL>create spfile from pfile='d:\oracle\product\10.2.0\db_1\database\INITSNS1011.ORA';
SQL>shutdown immediate;
SQL>Startup;
7) CREATE STANDBY CONTROLFILE
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database create standby controlfile as 'C:\SBY.ORA';
SQL>ALTER DATABASE OPEN;
8) take target db backup using rman and restore to standby
run RMAN backup script
----------------III. On the Standby Database Site:---------------
1. CREATE STANDBY DATABASE WITHOUT STARTUP DATABASE
2. Create directory STRUCTURE SAME AS PRIMARY DATABASE for data files. ALSO Create directory (multiplexing) for online logs.
create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
3. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy INITSNS1011.ora from Primary server to Standby server, to database folder C:\oracle\product\10.2.0\db_1\database.
2) Rename it to INITSNS1011SBY.ORA, and modify the file as follows
NOTE: The db_name in the standby's init file should be the same as the primary database.
--------------------------INITSNS1011SBY.ORA----------------------
sns6.__db_cache_size=1207959552
sns6.__java_pool_size=8388608
sns6.__large_pool_size=8388608
sns6.__shared_pool_size=343932928
sns6.__streams_pool_size=0
*.audit_file_dest='c:\oracle\product\10.2.0\admin\sns1011sby\adump'
*.background_dump_dest='c:\oracle\product\10.2.0\admin\sns1011sby\bdump'
*.compatible='10.2.0.3.0'
*.control_files='e:\snsd1011\control01.ora','e:\snsd1011\control02.ora','e:\snsd1011\control03.ora'
*.core_dump_dest='c:\oracle\product\10.2.0\admin\sns1011sby\cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='sns1011sby'
*.job_queue_processes=35
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(sns1011sby,sns1011)'
*.LOG_ARCHIVE_DEST_1='LOCATION=D:\archive0910\sns1011sby\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sns1011sby'
*.LOG_ARCHIVE_DEST_2='SERVICE=sns1011 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sns1011sby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=sns1011
*.FAL_CLIENT=sns1011sby
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='d:\oracle\product\10.2.0\admin\sns1011','c:\oracle\product\10.2.0\admin\sns1011sby'
*.LOG_FILE_NAME_CONVERT='d:\oracle\product\10.2.0\admin\sns1011','c:\oracle\product\10.2.0\admin\sns1011sby'
*.log_archive_format='ARC%S_%R.%T'
*.open_cursors=300
*.pga_aggregate_target=337641472
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.sga_max_size=1572864000
*.sga_target=1572864000
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*._ALLOW_RESETLOGS_CORRUPTION=TRUE
*.user_dump_dest='c:\oracle\product\10.2.0\admin\sns1011sby\udump'
*.utl_file_dir='e:\ldoutput'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sns1011SBYXDB)'
--------------------------------------------------------
4. Copy the Primary password file to standby and rename it to pwdsns6SBY.ora.
TO C:\oracle\product\10.2.0\db_1\database.
5. Copy the standby control file 'SBY.ORA' from primary to standby destinations ;
6. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID SNS1011SBY –STARTMODE manual
7. Configure listeners for the primary and standby databases.
--------------TNSNAMES.ORA--PRIMARY---------
SNS1011 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.65)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(Sid = SNS1011)
)
)
SNS1011SBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(Sid = SNS1011sby)
)
)
-----------------LISTENER.ORA----PRIMARY--------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = EXTPROC)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = SNS1011)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(SID_NAME = SNS1011)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.65)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1522))
)
)
--------------TNSNAMES.ORA--STANDBY---------
SNS1011SBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SNS1011sby)
)
)
SNS1011 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.65)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SNS1011)
)
)
-----------------LISTENER.ORA----STANDBY--------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = EXTPROC1)
(SID_NAME = PLSExtProc1)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = SNS1011sby)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = SNS1011sby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1522))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.65)(PORT = 1521))
)
)
------------------------
8.
RESTART LISTENER ON PRIMARY AND STANDBY DATABASE
LSNRCTL>RELOAD
CHECK TNSPING ON PRIMARY AND STANDBY DATABASE
$tnsping SNS1011
$tnsping SNS1011SBY
9. On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOME and ORACLE_SID.
set ORACLE_SID=sns6sby
oradim -new -sid sns6sby -SRVC OracleServicesns6sby -intpwd oracle -MAXUSERS 5 -STARTMODE auto -PFILE c:\oracle\product\10.2.0\db_1\database\initsns6sby.ora
10. Start up nomount the standby database and generate a spfile.
SQL>startup nomount pfile='C:\oracle\product\10.2.0\db_1\database\INITSNS1011sby.ORA';
SQL>create spfile from pfile='C:\oracle\product\10.2.0\db_1\database\INITSNS1011sby.ORA';
SQL>shutdown immediate;
SQL>startup mount;
11.
SET ORACLE_SID=sns6sby
RMAN TARGET SYS/ORACLE@SNS1011SBY
RESTORE CONTROLFILE FROM 'C:\SBY.ORA';
catalog backuppiece 'c:\05LICVI0';
restore database;
12. DUPLICATE DATABASE
NOTE: TARGET DB SHOULD BE MOUNT AND STANDBY SHOULD BE NOMOUNT STATE
rman target sys/oracle@SNS1011 auxiliary sys/oracle@SNS1011sby
RMAN>
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
13. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
-----
If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;
-------
14. Verify the standby database is performing properly:
A) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
B) On Primary, force a logfile switch:
SQL>alter system switch logfile;
C) On Standby, verify the archived redo log files were applied:
SQL>
15. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
on standby database
shut immediate;
startup mount
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;
on primary only one time
alter system switch logfile;
alter system switch logfile;
16. To create multiple standby databases, repeat this procedure.
17) Failover
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
--------------------
--------------------------------------
PRODUCTION DATABASE: 10.100.0.65
STANDBY DATABASE: 10.100.0.32
-----------------I. Before you get started:-------------------
1. Make sure the operating system and platform architecture on the primary and standby systems are the same;
2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.
---------II. On the Primary Database Side:---------------------
Enable forced logging on your primary database:
Select FORCE_LOGGING from V$DATABASE;
ALTER DATABASE FORCE LOGGING;
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;
3) Create standby Redo log groups.
My primary database had 5 log file groups originally and I created 5 standby redo log groups using the following commands:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 SIZE 50M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;
-----------NO NEED--------------5) Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
6) Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
A) Create pfile from spfile for the primary database:
SQL>create pfile='d:\oracle\product\10.2.0\db_1\database\INITSNS6.ORA' from spfile;
B) Edit INITSNS6.ora to add the new primary and standby role parameters:
select * from v$parameter where name like '%log_archive_format%';
select * from v$parameter where name like '%standby%';
select * from v$parameter where name like '%remote_archive_enable%';
select * from v$parameter where name like '%log_archive_dest_state_%';
select * from v$parameter where name like '%convert%';
----------------------INITSNS1011.ORA------------------
sns6.__db_cache_size=1006632960
sns6.__java_pool_size=8388608
sns6.__large_pool_size=8388608
sns6.__shared_pool_size=645922816
sns6.__streams_pool_size=0
*.audit_file_dest='d:\oracle\product\10.2.0\admin\sns1011\adump'
*.audit_trail='DB'
*.background_dump_dest='d:\oracle\product\10.2.0\admin\sns1011\bdump'
*.compatible='10.2.0.3.0'
*.control_files='e:\snsd1011\control01.ora','e:\snsd1011\control02.ora','e:\snsd1011\control03.ora'
*.core_dump_dest='d:\oracle\product\10.2.0\admin\sns1011\cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='sns1011'
*.job_queue_processes=35
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(sns1011,sns1011sby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=D:\archive0910\sns1011\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sns1011'
*.LOG_ARCHIVE_DEST_2='SERVICE=sns1011sby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sns1011sby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=sns1011sby
*.FAL_CLIENT=sns1011
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\admin\sns1011sby','d:\oracle\product\10.2.0\admin\sns1011'
*.LOG_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\admin\sns1011sby','d:\oracle\product\10.2.0\admin\sns1011'
*.log_archive_format='ARC%S_%R.%T'
*.open_cursors=300
*.pga_aggregate_target=337641472
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.sga_max_size=1677721600
*.sga_target=1677721600
*.smtp_out_server='mail.uniconindia.in'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='d:\oracle\product\10.2.0\admin\sns1011\udump'
*.utl_file_dir='d:\ldoutput'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sns1011XDB)'
-------------------
C. Create spfile from pfile, and restart primary database using the new spfile.
SQL> shutdown immediate;
SQL> startup nomount pfile='d:\oracle\product\10.2.0\db_1\database\INITSNS1011.ORA';
SQL>create spfile from pfile='d:\oracle\product\10.2.0\db_1\database\INITSNS1011.ORA';
SQL>shutdown immediate;
SQL>Startup;
7) CREATE STANDBY CONTROLFILE
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database create standby controlfile as 'C:\SBY.ORA';
SQL>ALTER DATABASE OPEN;
8) take target db backup using rman and restore to standby
run RMAN backup script
----------------III. On the Standby Database Site:---------------
1. CREATE STANDBY DATABASE WITHOUT STARTUP DATABASE
2. Create directory STRUCTURE SAME AS PRIMARY DATABASE for data files. ALSO Create directory (multiplexing) for online logs.
create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
3. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy INITSNS1011.ora from Primary server to Standby server, to database folder C:\oracle\product\10.2.0\db_1\database.
2) Rename it to INITSNS1011SBY.ORA, and modify the file as follows
NOTE: The db_name in the standby's init file should be the same as the primary database.
--------------------------INITSNS1011SBY.ORA----------------------
sns6.__db_cache_size=1207959552
sns6.__java_pool_size=8388608
sns6.__large_pool_size=8388608
sns6.__shared_pool_size=343932928
sns6.__streams_pool_size=0
*.audit_file_dest='c:\oracle\product\10.2.0\admin\sns1011sby\adump'
*.background_dump_dest='c:\oracle\product\10.2.0\admin\sns1011sby\bdump'
*.compatible='10.2.0.3.0'
*.control_files='e:\snsd1011\control01.ora','e:\snsd1011\control02.ora','e:\snsd1011\control03.ora'
*.core_dump_dest='c:\oracle\product\10.2.0\admin\sns1011sby\cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='sns1011sby'
*.job_queue_processes=35
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(sns1011sby,sns1011)'
*.LOG_ARCHIVE_DEST_1='LOCATION=D:\archive0910\sns1011sby\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sns1011sby'
*.LOG_ARCHIVE_DEST_2='SERVICE=sns1011 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sns1011sby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=sns1011
*.FAL_CLIENT=sns1011sby
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='d:\oracle\product\10.2.0\admin\sns1011','c:\oracle\product\10.2.0\admin\sns1011sby'
*.LOG_FILE_NAME_CONVERT='d:\oracle\product\10.2.0\admin\sns1011','c:\oracle\product\10.2.0\admin\sns1011sby'
*.log_archive_format='ARC%S_%R.%T'
*.open_cursors=300
*.pga_aggregate_target=337641472
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.sga_max_size=1572864000
*.sga_target=1572864000
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*._ALLOW_RESETLOGS_CORRUPTION=TRUE
*.user_dump_dest='c:\oracle\product\10.2.0\admin\sns1011sby\udump'
*.utl_file_dir='e:\ldoutput'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sns1011SBYXDB)'
--------------------------------------------------------
4. Copy the Primary password file to standby and rename it to pwdsns6SBY.ora.
TO C:\oracle\product\10.2.0\db_1\database.
5. Copy the standby control file 'SBY.ORA' from primary to standby destinations ;
6. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID SNS1011SBY –STARTMODE manual
7. Configure listeners for the primary and standby databases.
--------------TNSNAMES.ORA--PRIMARY---------
SNS1011 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.65)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(Sid = SNS1011)
)
)
SNS1011SBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(Sid = SNS1011sby)
)
)
-----------------LISTENER.ORA----PRIMARY--------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = EXTPROC)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = SNS1011)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(SID_NAME = SNS1011)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.65)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1522))
)
)
--------------TNSNAMES.ORA--STANDBY---------
SNS1011SBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SNS1011sby)
)
)
SNS1011 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.65)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SNS1011)
)
)
-----------------LISTENER.ORA----STANDBY--------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = EXTPROC1)
(SID_NAME = PLSExtProc1)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = SNS1011sby)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = SNS1011sby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1522))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.65)(PORT = 1521))
)
)
------------------------
8.
RESTART LISTENER ON PRIMARY AND STANDBY DATABASE
LSNRCTL>RELOAD
CHECK TNSPING ON PRIMARY AND STANDBY DATABASE
$tnsping SNS1011
$tnsping SNS1011SBY
9. On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOME and ORACLE_SID.
set ORACLE_SID=sns6sby
oradim -new -sid sns6sby -SRVC OracleServicesns6sby -intpwd oracle -MAXUSERS 5 -STARTMODE auto -PFILE c:\oracle\product\10.2.0\db_1\database\initsns6sby.ora
10. Start up nomount the standby database and generate a spfile.
SQL>startup nomount pfile='C:\oracle\product\10.2.0\db_1\database\INITSNS1011sby.ORA';
SQL>create spfile from pfile='C:\oracle\product\10.2.0\db_1\database\INITSNS1011sby.ORA';
SQL>shutdown immediate;
SQL>startup mount;
11.
SET ORACLE_SID=sns6sby
RMAN TARGET SYS/ORACLE@SNS1011SBY
RESTORE CONTROLFILE FROM 'C:\SBY.ORA';
catalog backuppiece 'c:\05LICVI0';
restore database;
12. DUPLICATE DATABASE
NOTE: TARGET DB SHOULD BE MOUNT AND STANDBY SHOULD BE NOMOUNT STATE
rman target sys/oracle@SNS1011 auxiliary sys/oracle@SNS1011sby
RMAN>
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
13. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
-----
If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;
-------
14. Verify the standby database is performing properly:
A) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
B) On Primary, force a logfile switch:
SQL>alter system switch logfile;
C) On Standby, verify the archived redo log files were applied:
SQL>
15. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
on standby database
shut immediate;
startup mount
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;
on primary only one time
alter system switch logfile;
alter system switch logfile;
16. To create multiple standby databases, repeat this procedure.
17) Failover
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
--------------------
Wednesday, July 14, 2010
Oracle 11g Standby Database
Oracle Standby database on same machine (for testing)
-----------------I. Before you get started:-------------------
1. Make sure the operating system and platform architecture on the primary and standby systems are the same;
2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.
---------II. On the Primary Database Side:---------------------
Enable forced logging on your primary database:
Select FORCE_LOGGING from V$DATABASE;
ALTER DATABASE FORCE LOGGING;
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;
3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;
5) Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
6) Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
A) Create pfile from spfile for the primary database:
SQL>create pfile='C:\app\kshitij\product\11.1.0\db_1\database\INITORCL.ORA' from spfile;
B) Edit INITORCL.ora to add the new primary and standby role parameters:
select * from v$parameter where name like '%log_archive_format%';
select * from v$parameter where name like '%standby%';
select * from v$parameter where name like '%remote_archive_enable%';
select * from v$parameter where name like '%log_archive_dest_state_%';
select * from v$parameter where name like '%convert%';
----------------------INITORCL.ORA------------------
orcl.__db_cache_size=536870912
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='c:\app\kshitij'
orcl.__pga_aggregate_target=301989888
orcl.__sga_target=939524096
orcl.__shared_pool_size=352321536
*.audit_file_dest='c:\app\kshitij\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='c:\app\kshitij\oradata\orcl\control01.ctl','c:\app\kshitij\oradata\orcl\control02.ctl','c:\app\kshitij\oradata\orcl\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orcl'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclsby)'
*.db_recovery_file_dest='c:\app\kshitij\flash_recovery_area'
*.LOG_ARCHIVE_DEST_1='LOCATION=c:\app\kshitij\flash_recovery_area\orcl\onlineLOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=orclsby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclsby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=orclsby
*.FAL_CLIENT=orcl
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='C:\app\kshitij\oradata\orclsby','C:\app\kshitij\oradata\orcl'
*.LOG_FILE_NAME_CONVERT='e:\app\kshitij\oradata\orclsby','C:\app\kshitij\oradata\orcl','e:\app\kshitij\flash_recovery_area\orclsby\onlineLOG','c:\app\kshitij\flash_recovery_area\orcl\onlineLOG'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='c:\app\kshitij'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=1229979648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
-------------------
C. Create spfile from pfile, and restart primary database using the new spfile.
SQL> shutdown immediate;
SQL> startup nomount pfile='C:\app\kshitij\product\11.1.0\db_1\database\INITORCL.ORA';
SQL>create spfile from pfile='C:\app\kshitij\product\11.1.0\db_1\database\INITORCL.ORA';
SQL>shutdown immediate;
SQL>Startup;
7) CREATE STANDBY CONTROLFILE
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database create standby controlfile as 'C:\SBY.ORA';
SQL>ALTER DATABASE OPEN;
8) take target db backup using rman and restore to standby
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\%F';
RMAN TARGET SYS/ORACLE@ORCL
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'C:\%U';
----------------III. On the Standby Database Site:---------------
1. CREATE STANDBY DATABASE WITHOUT STARTUP DATABASE
2. Create directory STRUCTURE SAME AS PRIMARY DATABASE for data files. ALSO Create directory (multiplexing) for online logs.
create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
3. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy INITORCL.ora from Primary server to Standby server, to database folder E:\app\kshitij\product\11.1.0\db_1\database.
2) Rename it to INITORCLSBY.ORA, and modify the file as follows
NOTE: The db_name in the standby's init file should be the same as the primary database.
--------------------------INITORCLSBY.ORA----------------------
orcl.__db_cache_size=536870912
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='e:\app\kshitij'
orcl.__pga_aggregate_target=301989888
orcl.__sga_target=939524096
orcl.__shared_pool_size=352321536
*.audit_file_dest='e:\app\kshitij\admin\orclsby\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='e:\app\kshitij\oradata\orclsby\control01.ctl','e:\app\kshitij\oradata\orclsby\control02.ctl','e:\app\kshitij\oradata\orclsby\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orclsby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orclsby,orcl)'
*.db_recovery_file_dest='e:\app\kshitij\flash_recovery_area'
*.LOG_ARCHIVE_DEST_1='LOCATION=e:\app\kshitij\flash_recovery_area\orclsby\onlineLOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclsby'
*.LOG_ARCHIVE_DEST_2='SERVICE=orclsby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclsby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=orcl
*.FAL_CLIENT=orclsby
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='c:\app\kshitij\oradata\orcl','e:\app\kshitij\oradata\orclsby'
*.LOG_FILE_NAME_CONVERT='c:\app\kshitij\oradata\orcl','e:\app\kshitij\oradata\orclsby','c:\app\kshitij\flash_recovery_area\orcl\onlineLOG','e:\app\kshitij\flash_recovery_area\orclsby\onlineLOG'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='e:\app\kshitij'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclsbyXDB)'
*.memory_target=1229979648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
--------------------------------------------------------
4. Copy the Primary password file to standby and rename it to pwdSBY.ora.
TO E:\app\kshitij\product\11.1.0\db_1\database.
5. Copy the standby control file 'SBY.ORA' from primary to standby destinations ;
6. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID ORCLSBY –STARTMODE manual
7. Configure listeners for the primary and standby databases.
--------------TNSNAMES.ORA--PRIMARY---------
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(Sid = orcl)
)
)
ORCLSBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(Sid = orclsby)
)
)
-----------------LISTENER.ORA----PRIMARY--------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = EXTPROC)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\app\kshitij\product\11.1.0\db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = C:\app\kshitij\product\11.1.0\db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1521))
)
)
--------------TNSNAMES.ORA--STANDBY---------
ORCLSBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kshitij-PC)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclsby)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kshitij-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
-----------------LISTENER.ORA----STANDBY--------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = EXTPROC1)
(SID_NAME = PLSExtProc1)
(ORACLE_HOME = e:\app\kshitij\product\11.1.0\db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = orclsby)
(ORACLE_HOME = e:\app\kshitij\product\11.1.0\db_1)
(SID_NAME = orclsby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1522))
)
)
------------------------
8.
RESTART LISTENER ON PRIMARY AND STANDBY DATABASE
LSNRCTL>RELOAD
CHECK TNSPING ON PRIMARY AND STANDBY DATABASE
$tnsping ORCL
$tnsping ORCLSBY
9. On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOME and ORACLE_SID.
10. Start up nomount the standby database and generate a spfile.
SQL>startup nomount pfile='E:\app\kshitij\product\11.1.0\db_1\database\INITORCLsby.ORA';
SQL>create spfile from pfile='E:\app\kshitij\product\11.1.0\db_1\database\INITORCLsby.ORA';
SQL>shutdown immediate;
SQL>startup mount;
11.
RMAN TARGET SYS/ORACLE@ORCLSBY
RESTORE CONTROLFILE FROM 'C:\SBY.ORA';
catalog backuppiece 'c:\05LICVI0';
restore database;
12. DUPLICATE DATABASE
NOTE: TARGET DB SHOULD BE MOUNT AND STANDBY SHOULD BE NOMOUNT STATE
rman target sys/oracle@orcl auxiliary sys/oracle@orclsby
RMAN>
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
13. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
-----
If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;
-------
14. Verify the standby database is performing properly:
A) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
B) On Primary, force a logfile switch:
SQL>alter system switch logfile;
C) On Standby, verify the archived redo log files were applied:
SQL>
15. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
To start real-time apply:
on standby database
shut immediate;
startup mount
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;
on primary only one time
alter system switch logfile;
alter system switch logfile;
16. To create multiple standby databases, repeat this procedure.
17) Failover
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
---------IV. Maintenance:-------
1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.
2. Cleanup the archive logs on Primary and Standby servers.
I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.
For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target /@ORCLSBY;
RMAN>backup archivelog all delete input;
To delete the archivelog backup files on the standby server, I run the following once a month:
RMAN>delete backupset;
3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won't be shipped to the standby server.
-----------------------
-----------------I. Before you get started:-------------------
1. Make sure the operating system and platform architecture on the primary and standby systems are the same;
2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.
---------II. On the Primary Database Side:---------------------
Enable forced logging on your primary database:
Select FORCE_LOGGING from V$DATABASE;
ALTER DATABASE FORCE LOGGING;
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;
3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;
5) Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
6) Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
A) Create pfile from spfile for the primary database:
SQL>create pfile='C:\app\kshitij\product\11.1.0\db_1\database\INITORCL.ORA' from spfile;
B) Edit INITORCL.ora to add the new primary and standby role parameters:
select * from v$parameter where name like '%log_archive_format%';
select * from v$parameter where name like '%standby%';
select * from v$parameter where name like '%remote_archive_enable%';
select * from v$parameter where name like '%log_archive_dest_state_%';
select * from v$parameter where name like '%convert%';
----------------------INITORCL.ORA------------------
orcl.__db_cache_size=536870912
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='c:\app\kshitij'
orcl.__pga_aggregate_target=301989888
orcl.__sga_target=939524096
orcl.__shared_pool_size=352321536
*.audit_file_dest='c:\app\kshitij\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='c:\app\kshitij\oradata\orcl\control01.ctl','c:\app\kshitij\oradata\orcl\control02.ctl','c:\app\kshitij\oradata\orcl\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orcl'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclsby)'
*.db_recovery_file_dest='c:\app\kshitij\flash_recovery_area'
*.LOG_ARCHIVE_DEST_1='LOCATION=c:\app\kshitij\flash_recovery_area\orcl\onlineLOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=orclsby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclsby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=orclsby
*.FAL_CLIENT=orcl
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='C:\app\kshitij\oradata\orclsby','C:\app\kshitij\oradata\orcl'
*.LOG_FILE_NAME_CONVERT='e:\app\kshitij\oradata\orclsby','C:\app\kshitij\oradata\orcl','e:\app\kshitij\flash_recovery_area\orclsby\onlineLOG','c:\app\kshitij\flash_recovery_area\orcl\onlineLOG'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='c:\app\kshitij'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=1229979648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
-------------------
C. Create spfile from pfile, and restart primary database using the new spfile.
SQL> shutdown immediate;
SQL> startup nomount pfile='C:\app\kshitij\product\11.1.0\db_1\database\INITORCL.ORA';
SQL>create spfile from pfile='C:\app\kshitij\product\11.1.0\db_1\database\INITORCL.ORA';
SQL>shutdown immediate;
SQL>Startup;
7) CREATE STANDBY CONTROLFILE
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database create standby controlfile as 'C:\SBY.ORA';
SQL>ALTER DATABASE OPEN;
8) take target db backup using rman and restore to standby
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\%F';
RMAN TARGET SYS/ORACLE@ORCL
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'C:\%U';
----------------III. On the Standby Database Site:---------------
1. CREATE STANDBY DATABASE WITHOUT STARTUP DATABASE
2. Create directory STRUCTURE SAME AS PRIMARY DATABASE for data files. ALSO Create directory (multiplexing) for online logs.
create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
3. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy INITORCL.ora from Primary server to Standby server, to database folder E:\app\kshitij\product\11.1.0\db_1\database.
2) Rename it to INITORCLSBY.ORA, and modify the file as follows
NOTE: The db_name in the standby's init file should be the same as the primary database.
--------------------------INITORCLSBY.ORA----------------------
orcl.__db_cache_size=536870912
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='e:\app\kshitij'
orcl.__pga_aggregate_target=301989888
orcl.__sga_target=939524096
orcl.__shared_pool_size=352321536
*.audit_file_dest='e:\app\kshitij\admin\orclsby\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='e:\app\kshitij\oradata\orclsby\control01.ctl','e:\app\kshitij\oradata\orclsby\control02.ctl','e:\app\kshitij\oradata\orclsby\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orclsby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orclsby,orcl)'
*.db_recovery_file_dest='e:\app\kshitij\flash_recovery_area'
*.LOG_ARCHIVE_DEST_1='LOCATION=e:\app\kshitij\flash_recovery_area\orclsby\onlineLOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclsby'
*.LOG_ARCHIVE_DEST_2='SERVICE=orclsby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclsby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=orcl
*.FAL_CLIENT=orclsby
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='c:\app\kshitij\oradata\orcl','e:\app\kshitij\oradata\orclsby'
*.LOG_FILE_NAME_CONVERT='c:\app\kshitij\oradata\orcl','e:\app\kshitij\oradata\orclsby','c:\app\kshitij\flash_recovery_area\orcl\onlineLOG','e:\app\kshitij\flash_recovery_area\orclsby\onlineLOG'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='e:\app\kshitij'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclsbyXDB)'
*.memory_target=1229979648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
--------------------------------------------------------
4. Copy the Primary password file to standby and rename it to pwdSBY.ora.
TO E:\app\kshitij\product\11.1.0\db_1\database.
5. Copy the standby control file 'SBY.ORA' from primary to standby destinations ;
6. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID ORCLSBY –STARTMODE manual
7. Configure listeners for the primary and standby databases.
--------------TNSNAMES.ORA--PRIMARY---------
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(Sid = orcl)
)
)
ORCLSBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(Sid = orclsby)
)
)
-----------------LISTENER.ORA----PRIMARY--------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = EXTPROC)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\app\kshitij\product\11.1.0\db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = C:\app\kshitij\product\11.1.0\db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1521))
)
)
--------------TNSNAMES.ORA--STANDBY---------
ORCLSBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kshitij-PC)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclsby)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kshitij-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
-----------------LISTENER.ORA----STANDBY--------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = EXTPROC1)
(SID_NAME = PLSExtProc1)
(ORACLE_HOME = e:\app\kshitij\product\11.1.0\db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = orclsby)
(ORACLE_HOME = e:\app\kshitij\product\11.1.0\db_1)
(SID_NAME = orclsby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1522))
)
)
------------------------
8.
RESTART LISTENER ON PRIMARY AND STANDBY DATABASE
LSNRCTL>RELOAD
CHECK TNSPING ON PRIMARY AND STANDBY DATABASE
$tnsping ORCL
$tnsping ORCLSBY
9. On Standby server, setup the environment variables to point to the Standby database.
Set up ORACLE_HOME and ORACLE_SID.
10. Start up nomount the standby database and generate a spfile.
SQL>startup nomount pfile='E:\app\kshitij\product\11.1.0\db_1\database\INITORCLsby.ORA';
SQL>create spfile from pfile='E:\app\kshitij\product\11.1.0\db_1\database\INITORCLsby.ORA';
SQL>shutdown immediate;
SQL>startup mount;
11.
RMAN TARGET SYS/ORACLE@ORCLSBY
RESTORE CONTROLFILE FROM 'C:\SBY.ORA';
catalog backuppiece 'c:\05LICVI0';
restore database;
12. DUPLICATE DATABASE
NOTE: TARGET DB SHOULD BE MOUNT AND STANDBY SHOULD BE NOMOUNT STATE
rman target sys/oracle@orcl auxiliary sys/oracle@orclsby
RMAN>
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
13. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
-----
If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;
-------
14. Verify the standby database is performing properly:
A) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
B) On Primary, force a logfile switch:
SQL>alter system switch logfile;
C) On Standby, verify the archived redo log files were applied:
SQL>
15. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
To start real-time apply:
on standby database
shut immediate;
startup mount
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;
on primary only one time
alter system switch logfile;
alter system switch logfile;
16. To create multiple standby databases, repeat this procedure.
17) Failover
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
---------IV. Maintenance:-------
1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.
2. Cleanup the archive logs on Primary and Standby servers.
I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.
For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target /@ORCLSBY;
RMAN>backup archivelog all delete input;
To delete the archivelog backup files on the standby server, I run the following once a month:
RMAN>delete backupset;
3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won't be shipped to the standby server.
-----------------------
Subscribe to:
Posts (Atom)