Opening the standby database for Query
Closing the standby database after Query
In standby database in effect, sizing of datafile
In standby database in effect, adding a datafile/Tablespace
If standby database shutdown for some time
If connection is broken between PROD and ST. By Site:
Activating the standby database (in case of PROD site loss)
1.
Create the database in
prod.
2.
Enable archivelog.
3.
Shutdown and take cold
backup.
4.
Configure listener with
explicitly registering the standby database name in Standby site. Remember the
database is yet to be created. Test the same after restarting the listener by:
lsnrctl services
This should show the database service.
5.
Configure the
“tnsnames.ora” in prod for that connection.
6.
Start the prod database.
7.
From “svrmgrl” or “sqlplus” issue the
commands
8.
“Alter
system archive log current”
9.
“Alter database create standby controlfile
as ‘/ORADATA/pbs/u01/standctrlusha.dbf’ ”
10. Shutdown
the prod database.
11. Create
the directory structure in Standby site exactly replicating the Production
structure.
12. FTP the datafiles in primary site to the
standby site.
13. FTP the
logfiles in primary site to the standby site.
14. FTP the
standby controlfile created in standby site and copy the same so as the stanby
database when activated, will serve as control files.
15. Modify the production init.ora
16. log_archive_dest_1 = "location=/ORADATA/pbs/u27/arc mandatory reopen=300"
17. log_archive_dest_state_1 = enable
18. log_archive_dest_2 = "service=standby_db mandatory reopen=60"
19. log_archive_dest_state_2 = enable
20. log_archive_min_succeed_dest = 1
21. log_archive_max_processes = 3
22. Modify the standby init.ora
23. standby_archive_dest = /ORADATA/sbs/u27/starc
24. log_archive_min_succeed_dest = 1
25. log_archive_max_processes = 3
26. db_file_name_convert = "pbs","sbs"
27. log_file_name_convert = " pbs
","sbs"
28. Start the
standby database as in the following steps in svrmgrl.
29. STARTUP NOMOUNT
30. ALTER DATABASE MOUNT STANDBY DATABASE;
31. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
32. After the last command in svrmgrl, the
prompt will not return. Hence this should be executed from console window of
standby site where the connection will be secured.
1.
From another telnet
session, run svrmgrl and give the command
2.
ALTER DATABASE
RECOVER MANAGED STANDBY DATABASE CANCEL;
3. ALTER DATABASE OPEN READ ONLY;
4.
After this the database
will be open for query.
1.
From svrmgrl give the
following command.
2. SHUTDOWN IMMEDIATE;
3. STARTUP NOMOUNT
4.
ALTER DATABASE
MOUNT STANDBY DATABASE;
5. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
6. Between steps 2 and 3 if any archivelog files are produced in primary site they will NOT be transferred automatically to the standby site. These have to be transferred manually. (Ref metalink: Note: 109852.1).
1.
No problem, takes place
automatically unless disk space in standby site is lacking.
1.
Create the
datafile/& tablespace in PROD.
2.
From “svrmgrl” in PROD
give the following command
3. ALTER SYSTEM SWITCH LOG FILE;
4.
The recovery process
will stop giving Error.
5.
FTP the new datafile in
corresponding location.
6.
In svrmgrl in standby
database give the command.
7. ALTER DATABASE CREATE DATAFILE '<datafile name>' AS '<datafile name>';
8.
Place the Standby
database in recovery mode, using command
9. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
1.
Copy the archive log
files produced by PROD in corresponding location.
2.
Place the standby
database in recovery mode.
1.
This can be found out
from the alert.log file of PROD where there will be an entry like
ARC0: Beginning to archive log# 1 seq# 54
ARC0: RFS network connection lost at host 'standby_db'
ARC0: Error creating standby archivelog file at host 'standby_db' Code '3113'
ARC0: Error creating archivelog file 'skt_s'
2.
If reestablishing connection takes time then
from svrmgrl in PROD give the command (before all log files are filled up)
3. alter system set log_archive_dest_state_2 = disable
4.
When connection is
reestablished, transfer the archive
files to standby site.
5.
Recover the database
manually by giving the following commands from svremgrl in standby
6. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
7. recover standby database;
8.
After recovery, again
issue the command
9. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
10. In PROD, give the command
11. alter system set log_archive_dest_state_2=enable;
12. alter system set log_archive_dest_2 = 'service=standby_db reopen=30';
13. This will reopen the connection and reset any errors
occoured in archival.
1.
Caution. Once
performing the same, you cannot make the database in standby mode without again
recreating the database.
2.
From svrmgrl in standby
site, issue the command.
3. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
4.
From “alert.log” of the
standby database find out the last archive log file applied.
5.
Transfer any other
archive log file (if possible) from PROD to the standby site and apply them.
6. ALTER DATABASE RECOVER [FROM 'pathname'] STANDBY DATABASE;
7.
After this stop the
recovery by giving the command
8. ALTER DATABASE RECOVER CANCEL;
a.
Convert the standby
database to a production environment by giving the command
b. ALTER DATABASE ACTIVATE STANDBY DATABASE;
c.
The database is now a full-fledged production database. This
database can work without the PROD site.
d.
Shutdown the standby database.
e.
Take a cold backup.
f.
Start the database.
g.
Redirect the users to this database.