Enable force logging
Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:
ALTER DATABASE FORCE LOGGING;
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> col FORCE_LOGGING for a20
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
FORCE_LOGGING
--------------------
YES
SQL>
Create a Password File
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SQL>
If it doesn't exist, use the following command to create one:
- On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)
- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)
Configure a Standby Redo log.
- 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> col member for a100
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
- Use the following command to determine your current log file groups:
SQL> set line 200
SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- -----------------------------------------------
3 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO03.LOG
2 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO02.LOG
1 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO01.LOG
SQL>
- 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:
SQL> ALTER DATABASE ADD STANDBY logfile GROUP 4 'D:\Oracle\app\oracle\oradata\testdg\stdredo04' SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY logfile GROUP 5 'D:\Oracle\app\oracle\oradata\testdg\stdredo05' SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY logfile GROUP 6 'D:\Oracle\app\oracle\oradata\testdg\stdredo06' SIZE 50M;
Database altered.
SQL> select group#, member from v$logfile;GROUP# MEMBER
---------- ----------------------------------------------------------------------------------------------------
3 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\REDO03.LOG
2 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\REDO02.LOG
1 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\REDO01.LOG
4 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\STDREDO04
5 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\STDREDO05
6 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\STDREDO06
6 rows selected.
SQL>
- To verify the results of the standby redo log groups creation, run the following query:
SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------------
3 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO03.LOG
2 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO02.LOG
1 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO01.LOG
4 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\STDREDO04
5 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\STDREDO05
6 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\STDREDO06
6 rows selected.
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
SQL>
Create pfile from spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string D:\ORACLE\APP\ORACLE\ADMIN\TES
TDG\PFILE\SPFILETESTDG.ORA
SQL> SQL> create pfile='D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\Pfile-temp-test10g-for-stdby.ora' from spfile;
File created.
SQL>
HERE IS THE CONTENT OF PFILE
testdg.__db_cache_size=301989888
testdg.__java_pool_size=4194304
testdg.__large_pool_size=4194304
testdg.__shared_pool_size=117440512
testdg.__streams_pool_size=0
*.audit_file_dest='D:\Oracle\app\oracle\admin\testdg\adump'
*.background_dump_dest='D:\Oracle\app\oracle\admin\testdg\bdump'
*.compatible='10.2.0.5.0'
*.control_files='D:\Oracle\app\oracle\oradata\testdg\control01.ctl','D:\Oracle\app\oracle\oradata\testdg\control02.ctl','D:\Oracle\app\oracle\oradata\testdg\control03.ctl'
*.core_dump_dest='D:\Oracle\app\oracle\admin\testdg\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest='D:\Oracle\app\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=8388608000
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdgXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=144703488
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.sga_target=434110464
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\Oracle\app\oracle\admin\testdg\udump'
DB_NAME=testdg
DB_UNIQUE_NAME=testdg
LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdg,testdgs)'
*.control_files='D:\Oracle\app\oracle\oradata\testdg\control01.ctl','D:\Oracle\app\oracle\oradata\testdg\control02.ctl','D:\Oracle\app\oracle\oradata\testdg\control03.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=testdg'
LOG_ARCHIVE_DEST_2=
'SERVICE=testdgs LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=testdgs'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT='ARC%S_%R.%T'
LOG_ARCHIVE_MAX_PROCESSES=30
######Primary Database: Standby Role Initialization Parameters##########
FAL_SERVER=testdgs
FAL_CLIENT=tet10g
DB_FILE_NAME_CONVERT='D:\app\oracle\oradata\testdgs','D:\Oracle\app\oracle\oradata\testdg'
LOG_FILE_NAME_CONVERT='D:\app\oracle\oradata\testdgs','D:\Oracle\app\oracle\oradata\testdg','D:\app\oracle\product\flash_recovery_area','D:\Oracle\app\oracle\flash_recovery_area'
STANDBY_FILE_MANAGEMENT=AUTO
3.1.5 Enable Archiving
If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>
Create standby controle file
SQL> alter database create standby controlfile as 'D:\ORACLE\APP\ORACLE\ORADATA\standctrl.ctl';
Database altered.
SQL>
No comments:
Post a Comment