$orapwd file=orapwTESTDR entries=30 ignorecase=Y
SQL> select * from V$PWFILE_USERS;
2. Both Primary and Standby should has same DB_NAME and Different DB_UNIQUE_NAME.SQL> select log_mode from v$database;If not, switch to archivelog mode.
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN;2. Enabled forced logging by issuing following command.
SQL> ALTER DATABASE FORCE LOGGING;3. Check the setting for DB_NAME & DB_UNIQUE_NAME parameters.
SQL> show parameter db_name; SQL> show parameter db_unique_name; SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='ebs' SCOPE=SPFILE;4. Verify the LOG_ARCHIVE_CONFIG database initialization parameter has been defined on the primary database(DB_UNIQUE_NAME for both primary and standby DB)
SQL> show parameter LOG_ARCHIVE_CONFIG; SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ebs,ebs_st)';5. Set suitable remote archive log destinations.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ebs_st NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ebs_st'; 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;7. It is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters.
SQL> ALTER SYSTEM SET FAL_SERVER=ebs_st(standby db name); SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;8. Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers.
ebs=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebst.aitkenspence.lk)(PORT=1526))
(CONNECT_DATA= (SID=ebs) ) )
ebs_st=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=demo.astech.lk)(PORT=1526))
(CONNECT_DATA= (SID=ebs) ) )
9. Listener file "$ORACLE_HOME/network/admin/listener.ora" of Primary DB.
ebs =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP)(HOST=ebst.aitkenspence.lk)(PORT=1526)) ) )
SID_LIST_ebs =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u01/ebs/db/tech_st/11.1.0)
(SID_NAME = ebs) ) )
10. Create a Standby controlfile for the standby database by issuing the following command on the primary database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/ebs_stby.ctl';11. Create standby logfile.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/ebs/db/apps_st/data/standby_redo01.log') SIZE 50M;
12. Take a backup of the primary database.SQL> show parameter db_name; SQL> show parameter db_unique_name; SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='ebs_st' SCOPE=SPFILE;3. Verify that the LOG_ARCHIVE_CONFIG database initialization parameter has been defined on the primary and standby database.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ebs,ebs_st)';4. It is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters.
SQL> ALTER SYSTEM SET FAL_SERVER=DB11G; (primary db name) SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;5. Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers.
ebs_st =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=demo.astech.lk)(PORT=1526))
(CONNECT_DATA=(SID=ebs)) )
ebs=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebst.aitkenspence.lk)(PORT=1526))
(CONNECT_DATA= (SID=ebs) ) )
6. Listener file "$ORACLE_HOME/network/admin/listener.ora" of Standby DB.
ebs =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP)(HOST=demo.astech.lk)(PORT=1526)) )
SID_LIST_EBS =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ebs_st)
(ORACLE_HOME = /u01/ebs/db/tech_st/11.1.0)
(SID_NAME = ebs) ) )
7. Create Redo logfile and standby logfile on standby server.
SQL> ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/ebs/online_redo01.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/ebs/db/apps_st/data/standby_redo01.log') SIZE 50M;
8. Startup standby database in mount mode using standby controlfile (created from primary).
SQL> startup nomount; SQL> alter database mount standby database;9. Start the apply process on standby server.
SQL> alter database recover managed standby database; SQL> alter database recover managed standby database disconnect; For Real-Time Apply SQL> alter database recover managed standby database using current logfile disconnect;10. Checking errors in both servers.
SQL> SELECT DEST_ID "ID",STATUS "DB_status",DESTINATION "Archive_dest",ERROR "Error" FROM V$ARCHIVE_DEST; SQL> select MESSAGE,to_char(timestamp,'DD-MON-YYYY hh24.mi.ss') date_time from v$dataguard_status;11. Check the new archived redo log has arrived at the standby server and been applied.
SQL>SELECT sequence#,first_time,next_time,archived,applied FROM v$archived_log ORDER BY sequence#; SQL>ALTER SYSTEM SWITCH LOGFILE;
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 14400,
interval => 30);
END;
/
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 100,
end_snap_id => 120,
baseline_name => 'test baseline');
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline (
baseline_name => 'test baseline',
cascade => FALSE);
END;
/
cd <$ORACLE_HOME>/rdbms/admin sqlplus / as sysdba sql> @spcreate.sql
SQL> @spdrop SQL> @spcreate
SQL> connect perfstat/perfstat_password SQL> exec statspack.snap;
SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
cd <$ORACLE_HOME>/rdbms/admin sqlplus / as sysdba SQL> @spreport.sqlyou have to provide at the prompt: BEGIN_SNAP, END_SNAP, REPORT_NAME (we can use a default value as well)
repeat_interval
|
Description
|
freq=hourly
|
Run
every hour
|
freq=daily;
byhour=3
|
Run
at 3 am every day
|
freq=daily;
byhour=8,20
|
Run
at 8 am and 8 pm every day
|
freq=monthly;
bymonthday=1
|
Run
on the first day of every month
|
freq=monthly;
bymonthday=-1
|
Run
on the last day of every month
|
freq=yearly;
bymonth=sep; bymonthday=20;
|
Run
yearly on September 20th
|