Data Guard 主庫建立表空間，備庫MRP無法啟動

主庫

SQL> select name from v$datafile;

NAME
----------------------------
/data/orcl/system01.dbf
/data/orcl/sysaux01.dbf
/data/orcl/undotbs01.dbf
/data/orcl/users01.dbf

備庫

SQL> select name from v$datafile;

NAME
-------------------------------------
/data/orcldg/datafile/system01.dbf
/data/orcldg/datafile/sysaux01.dbf
/data/orcldg/datafile/undotbs01.dbf
/data/orcldg/datafile/users01.dbf

主庫

create tablespace ceshi1 datafile '/data/orcl/ceshi1.dbf' size 100M autoextend on;


SQL> select name from v$datafile;

NAME
----------------------------
/data/orcl/system01.dbf
/data/orcl/sysaux01.dbf
/data/orcl/undotbs01.dbf
/data/orcl/users01.dbf
/data/orcl/ceshi1.dbf

備庫

SQL> select name from v$datafile;

NAME
--------------------------------------
/data/orcldg/datafile/system01.dbf
/data/orcldg/datafile/sysaux01.dbf
/data/orcldg/datafile/undotbs01.dbf
/data/orcldg/datafile/users01.dbf
/data/orcldg/datafile/ceshi1.dbf

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

PROCESS
---------
MRP0


ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT =MANUAL SID='*';

主庫

SQL> create tablespace ceshi2 datafile '/data/orcl/ceshi2.dbf' size 100M autoextend on;

Tablespace created.

SQL> select name from v$datafile;

NAME
------------------------------
/data/orcl/system01.dbf
/data/orcl/sysaux01.dbf
/data/orcl/undotbs01.dbf
/data/orcl/users01.dbf
/data/orcl/ceshi1.dbf
/data/orcl/ceshi2.dbf

6 rows selected.

備庫

SQL> select name from v$datafile;

NAME
-------------------------------------
/data/orcldg/datafile/system01.dbf
/data/orcldg/datafile/sysaux01.dbf
/data/orcldg/datafile/undotbs01.dbf
/data/orcldg/datafile/users01.dbf
/data/orcldg/datafile/ceshi1.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006

6 rows selected.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

no rows selected

SQL> col VALUE format a30
SQL> select name,value  from v$dataguard_stats;

NAME                             VALUE
-------------------------------- ------------------------------
transport lag                    +00 00:00:00
apply lag                        +00 00:01:42
apply finish time
estimated startup time           15

在$ORACLE_HOME/dbs下生成了一個相應的檔案控制程式碼，而實際上該檔案並不存在。

[root@0919dg2 ~]# ls /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006
ls: cannot access /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006: No such file or directory

使用create datafile的方式修復

alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006' as '/data/orcldg/datafile/ceshi2.dbf';

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE;

SQL>  select name from v$datafile;

NAME
--------------------------------------
/data/orcldg/datafile/system01.dbf
/data/orcldg/datafile/sysaux01.dbf
/data/orcldg/datafile/undotbs01.dbf
/data/orcldg/datafile/users01.dbf
/data/orcldg/datafile/ceshi1.dbf
/data/orcldg/datafile/ceshi2.dbf

SQL> select name,value  from v$dataguard_stats;

NAME                             VALUE
-------------------------------- ------------------------------
transport lag                    +00 00:00:00
apply lag                        +00 00:00:00
apply finish time
estimated startup time           15


ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT =AUTO SID='*';