1 / 20

Logical Standby

Logical Standby. Julian Dyke Independent Consultant. Web Version. juliandyke.com. Logical Standby. Archived Redo Logs. Primary Database. Standby Database. Standby Redo Log. Archived Redo Logs. Online Redo Log. Standby Database ARCH Redo Transmission. Primary Database.

sybil
Download Presentation

Logical Standby

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. LogicalStandby Julian Dyke Independent Consultant Web Version juliandyke.com

  2. LogicalStandby

  3. ArchivedRedoLogs PrimaryDatabase StandbyDatabase StandbyRedoLog ArchivedRedoLogs OnlineRedoLog Standby DatabaseARCH Redo Transmission Primary Database Standby Database LGWR RFS MRPLSP LOG_ARCHIVE_DEST_2 ARC0 ARC1 ARCn LOG_ARCHIVE_DEST_1

  4. ArchivedRedoLogs StandbyRedoLog ArchivedRedoLogs StandbyDatabase PrimaryDatabase OnlineRedoLog Standby DatabaseLGWR (ASYNC) Redo Transmission Primary Database Standby Database LGWR RFS MRPLSP LNSn ARCn ARCn LOG_ARCHIVE_DEST_1

  5. ArchivedRedoLogs OnlineRedoLog StandbyRedoLog ArchivedRedoLogs StandbyDatabase PrimaryDatabase Standby DatabaseLGWR (SYNC) Redo Transmission Primary Database Standby Database LGWR LNSn RFS MRPLSP ARCn ARCn LOG_ARCHIVE_DEST_1

  6. Logical StandbyPreparation • Used two separate servers • london1 and reading1 • Used DBCA to create one database on primary server • PRIMARY • Archiving must be enabled • NOLOGGING must be disabled SQL> ALTER DATABASE FORCE LOGGING; • Installed SCOTT/TIGER schema • $ORACLE_HOME/rdbms/admin/utlsampl.sql • Created physical standby on reading1

  7. Physical StandbyPreparation • On both servers, configure TNSNAMES.ORA LONDON = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = london1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = LONDON) ) ) READING = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = reading1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = READING) ) )

  8. Physical StandbyPreparation • On primary server create backup location mkdir /u01/oradata/PRIMARY/backup; • On primary server run RMAN to backup database $ORACLE_HOME/bin/rman NOCATALOG TARGET sys/oracle@PRIMARY • In RMAN configure backup CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/oradata/PRIMARY/backup/%F'; CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/oracle@PRIMARY'; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/admin/PRIMARY/backup/snapcf_PRIMARY.f';

  9. Physical StandbyPreparation • In RMAN backup database BACKUP FORMAT '/u01/oradata/PRIMARY/backup/%d_D_%T_%u_s%s_p%p' DATABASE; • In RMAN create controlfile for standby database BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/u01/oradata/PRIMARY/backup/%d_C_%U'; • In RMAN switch and archive current online redo log SQL "ALTER SYSTEM ARCHIVE LOG CURRENT"; • In RMAN backup archived redo logs BACKUPFILESPERSET 10ARCHIVELOG ALLFORMAT '/u01/oradata/PRIMARY/backup/%d_A_%T_%u_s%s_p%p';

  10. Physical StandbyPreparation • On standby server create administrative directories $ mkdir $ORACLE_BASE/admin/PRIMARY;$ mkdir $ORACLE_BASE/admin/PRIMARY/adump;$ mkdir $ORACLE_BASE/admin/PRIMARY/bdump;$ mkdir $ORACLE_BASE/admin/PRIMARY/cdump;$ mkdir $ORACLE_BASE/admin/PRIMARY/dpdump;$ mkdir $ORACLE_BASE/admin/PRIMARY/pfile;$ mkdir $ORACLE_BASE/admin/PRIMARY/udump; • On standby server create database location $ mkdir /u01/oradata/PRIMARY • On standby server create archived redo log location $ mkdir /u01/oradata/PRIMARY/arch • On standby server create backup location $ mkdir /u01/oradata/PRIMARY/backup;

  11. Physical StandbyPreparation • On standby server create password file $ORACLE_HOME/bin/orapwd \ file=$ORACLE_HOME/dbs/orapwPRIMARY \ password=oracle \ • Copy SPFILE from primary to standby scp london1:$ORACLE_HOME/dbs/spfilePRIMARY.ora \ reading1:$ORACLE_HOME/dbs • Note - initPRIMARY.ora is not required on either server

  12. Physical StandbyConfiguration • On primary server set parameters ALTER SYSTEM SET db_unique_name = LONDON SCOPE = SPFILE; ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/u01/oradata/PRIMARY/arch' SCOPE=SPFILE; ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=READING VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=READING' SCOPE=SPFILE; ALTER SYSTEM SET fal_client = LONDON SCOPE = SPFILE; ALTER SYSTEM SET fal_server = READING SCOPE = SPFILE; ALTER SYSTEM SET standby_file_management = AUTO SCOPE = SPFILE; • Restart the instance

  13. Physical StandbyConfiguration • On standby server configure LISTENER.ORA SID_LIST_LISTENER_SERVER4 = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = PRIMARY) ) ) • Reload the listener $ lsnrctl reload • Start the standby instance (NOMOUNT) $ export ORACLE_SID=PRIMARY$ sqlplus / as sysdbaSQL> startup nomount

  14. Physical StandbyConfiguration • On standby server set parameters ALTER SYSTEM SET db_unique_name = READING SCOPE = SPFILE; ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/u01/oradata/PRIMARY/arch' SCOPE = SPFILE; ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=LONDON VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LONDON' SCOPE=SPFILE; ALTER SYSTEM SET fal_client = READING SCOPE = SPFILE; ALTER SYSTEM SET fal_server = LONDON SCOPE = SPFILE; ALTER SYSTEM SET standby_file_management = AUTO SCOPE = SPFILE; • Restart the instance

  15. Physical StandbyConfiguration • On primary server run RMAN connecting to standby server as AUXILIARY $ORACLE_HOME/bin/rman NOCATALOG TARGET / AUXILIARY sys/oracle@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=reading1)(PORT=1521))(CONNECT_DATA=(SID=PRIMARY)))" • Run the following command to clone the database backup on the standby server RMAN> DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER NOFILENAMECHECK;

  16. Physical StandbyConfiguration • On standby server restart the instance SQL> SHUTDOWN IMMEDIATE SQL> STARTUP NOMOUNT • Mount the standby database SQL> ALTER DATABASE MOUNT STANDBY DATABASE; • Start managed recovery SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

  17. Logical Standby Configuration • On standby server cancel managed recovery SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; • On primary server modify the LOG_ARCHIVE_DEST_2 parameter SQL> ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=READING VALID_FOR=(ONLINE_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=READING' SCOPE=SPFILE;

  18. Logical Standby Configuration • On primary server build the logical standby dictionary SQL> EXECUTE LOGSTDBY.BUILD; • For a default database this generated about 8MB redo and updated the following objects:

  19. Logical Standby Configuration • On standby server enable recovery and change the name of the standby database SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY standby; • On standby server create a new password file $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwSTANDBY \password=oracle • On standby server restart the instance and reset the logs SQL> SHUTDOWN IMMEDIATESQL> STARTUPSQL> ALTER DATABASE OPEN RESETLOGS; • On standby server start SQL apply SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

  20. Thank you for your interest info@juliandyke.com

More Related