Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Milind.

Asked: March 04, 2017 - 5:56 am UTC

Last updated: March 07, 2017 - 3:23 am UTC

Version: 11.2.0.2

Viewed 1000+ times

You Asked

Hi AskTOM team,

-- On standby database I am using real time redo apply. My standby database is started using following commands.

STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

-- On primary database
conn scott/tiger
CREATE TABLE emp4 AS SELECT * FROM emp;

I do not see emp4 table on standby database. With real time redo apply this new table or committed data on primary database should be visible immediately on standby database or not?
This emp4 table is only visible on standby, if I perform log switch on primary.
When redo apply takes place exactly?

Extra information - I am not using DG broker. Stand by redo logs are created.

Warm Regards.
Milind

and Connor said...

I cant reproduce your problem. Here's my setup

primary: np12
standby: np12s

On primary:

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=np12s ASYNC VALID_FOR=
                                                 (ONLINE_LOGFILES,PRIMARY_ROLE)
                                                  DB_UNIQUE_NAME=np12s


On secondary:

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.



I have standby logs in place. So now I'll create a table on the primary and immediately check the standby

--
-- primary
--
SQL> set time on
11:16:57 SQL> create table scott.new_tab as select * from scott.emp;

Table created.

--
-- secondary
--
SQL> set time on
11:17:00 SQL> desc scott.new_tab
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ---------------
 EMPNO                                                                   NOT NULL NUMBER(4)
 ENAME                                                                            VARCHAR2(10)
 JOB                                                                              VARCHAR2(9)
 MGR                                                                              NUMBER(4)
 HIREDATE                                                                         DATE
 SAL                                                                              NUMBER(7,2)
 COMM                                                                             NUMBER(7,2)
 DEPTNO                                                                           NUMBER(2)

11:17:18 SQL> select count(*) from scott.new_tab;

  COUNT(*)
----------
        14


So check your alert logs - make sure that the standby logs are correctly being used. If nothing seems out of the ordinary, it could be time to have a chat to Support, but you can see from my example, your asserrtion is correct that changes should be applied "immediately".

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Milind, March 07, 2017 - 5:21 am UTC

Dear Connor,

Thanks a lot for response.

After restarting database now redo apply is working as expected and demonstrated by you. I did not find any suspicious message in alert.log file.

Warm Regards.
Milind