Step By Step

1. Oracle 11g r2 OEL 5.5 uzerine Kurulum 
2. Oracle 11g r2 Dataguard(Standby) Kurulumu
3. Linux Üzerine Oracle Weblogic 12.1.1 Kurulumu

Oracle 11g r2 Dataguard(Standby) Kurulumu

Kullanılan sistem bilgileri

Primary veritabanı DB_UNIQUE_NAME: ORCL

Standby veritabanı DB_UNIQUE_NAME:ORCLST

ORACLE_SID: ORCL

Primary hostname: linux1 (OEL Linux 5.5)

Standby hostname: linux2 (OEL Linux 5.5)

##### Primary tarafda ########

SQL>STARTUP MOUNT;

SQL>ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

SQL>ALTER DATABASE FORCE LOGGING;

[oracle@linux1 ~]$ scp $ORACLE_HOME/dbs/orapwORCL  oracle@linux2:$ORACLE_HOME/dbs

SQL>ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCLST)';

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLST SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLST';

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

SQL>ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

SQL>ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

SQL>ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

SQL>ALTER SYSTEM SET FAL_SERVER=ORCLST;

// Eger farkli directoryler olacaksa

--SQL>ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;

--SQL>ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G'  SCOPE=SPFILE;

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

-- primary tarafda

SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/oradata/ORCL/orcl_stby.ctl';

-- Network (Her iki tarafda)

tnsnames.ora

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = linux1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ORCL)

    )

  )

ORCLST =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = linux2)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ORCLST)

    )

  )

--listener standby tarafda

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = linux2)(PORT = 1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

        (SID_DESC =

        (GLOBAL_DBNAME = ORCLST)

        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

        (SID_NAME = ORCL)

        )

)

[oracle@linux1 ~]$ mkdir -p /u01/app/oracle/oradata/ORCL

[oracle@linux1 ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/ORCL

[oracle@linux1 ~]$ mkdir -p /u01/app/oracle/admin/ORCL/adump

####### primary tarafda #######

[oracle@linux1 ~]$ # Standby controlfile to all locations.

[oracle@linux1 ~]$ scp /u01/app/oracle/oradata/ORCL/orcl_stby.ctl oracle@linux2:/u01/app/oracle/oradata/ORCL/control01.ctl

[oracle@linux1 ~]$ scp /u01/app/oracle/oradata/ORCL/orcl_stby.ctl oracle@linux2:/u01/app/oracle/flash_recovery_area/ORCL/control02.ctl

####### standby tarafda #######

[oracle@linux1 ~]$ vi /home/oracle/pfile.ora

    DB_NAME=ORCL

SQL> STARTUP NOMOUNT PFILE='/home/oracle/pfile.ora';

####### primary tarafda #######

[oracle@linux1 ~]$ rman target sys/oracle@ORCL AUXILIARY sys/oracle@ORCLST;

rman> DUPLICATE TARGET DATABASE

             FOR STANDBY

             FROM ACTIVE DATABASE

             DORECOVER

             SPFILE

             SET db_unique_name='ORCLST' COMMENT 'Is standby'

             SET FAL_SERVER='ORCL' COMMENT 'Is primary'

             SET FAL_CLIENT='ORCLST'

            NOFILENAMECHECK;

####### Her iki tarafdada #######

SQL>ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo01.log') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo02.log') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo03.log') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo04.log') SIZE 50M;

#### Standby`de apply islemini baslatmak icin #######

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

#######iptal etmek icin#######

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

#### Test Log Transport ####
On the primary server, check the latest archived redo log and force a log switch.
     
SQL>ALTER SYSTEM SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

    SQL>SELECT sequence#, first_time, next_time, applied FROM v$archived_log  ORDER BY sequence#;

    SQL>ALTER SYSTEM SWITCH LOGFILE;

## standby

Check the new archived redo log has arrived at the standby server and been applied.

    SQL>ALTER SYSTEM SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

    SQL>SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.

###### Standby tarafda #######

To switch the standby database into read-only mode, do the following.

    SQL>SHUTDOWN IMMEDIATE;

    SQL>STARTUP MOUNT;

    SQL>ALTER DATABASE OPEN READ ONLY;

In 11g, Oracle introduced the Active Data Guard feature.
SQL>SHUTDOWN IMMEDIATE;

    SQL>STARTUP MOUNT;

    SQL>ALTER DATABASE OPEN READ ONLY;

    SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

    SQL>SELECT protection_mode FROM v$database;

    PROTECTION_MODE

    --------------------

    MAXIMUM PERFORMANCE

    -- Maximum Availability.

    SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';

    SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

    -- Maximum Performance.

    SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';

    SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

    -- Maximum Protection.

    SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';

    SQL>SHUTDOWN IMMEDIATE;

    SQL>STARTUP MOUNT;

    SQL>;ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

    SQL>ALTER DATABASE OPEN;

Faydali SQL`ler

Eğer sisteminiz archivelog’da çalışmıyorsa daha tutarsız ve anlık değişimlerden daha çok etkilenen şu sorguyu kullanabilirsiniz:

SELECT round(AVG(20 /

                   ((to_date(sonraki, 'HH24:MI') - to_date(logswicthzamani, 'HH24:MI')) * 24 * 60) * bytes

                     / (1024 * 1024)), 2) || ' MB' AS "Onerilen Redo"

FROM (SELECT bytes,

to_char(first_time, 'HH24:MI') AS logswicthzamani,

lead(to_char(first_time, 'HH24:MI'), 1) over(ORDER BY first_time) AS sonraki

FROM v$log) WHERE sonraki IS NOT NULL;

- Kullanilmayan indexler

select owner, index_name from dba_indexes where status='UNUSABLE';