The effect of physical DG Primary on adding/deleting tablespaces (data files) on Standby

Last Update:2018-06-05 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

1. Physical Standby from SHUTDOWN state to READONLY state orclpdselectopen_modefromv $ database; the OPEN_MODE----------READONLY is in READONLY state by default after startup. ORCLPDGalterdatabaserecovermanagedstandbydatabasecancel; alterdatabasere

1. Physical Standby starts from the SHUTDOWN status to the read only status ORCLPDG select open_mode from v $ database; OPEN_MODE ---------- read only status by default after startup. ORCLPDG alter database recover managed standby database cancel; alter database re

1. Physical Standby starts from SHUTDOWN to READ ONLY
ORCLPDG> select open_mode from v $ database;

OPEN_MODE
----------
READ ONLY
The read only status is used by default after startup.

ORCLPDG> alter database recover managed standby database cancel;
Alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active

The REDO application has not been started before. An error will occur when you cancel the application.
2. From read only to REDO Application Status
ORCLPDG> alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG> select open_mode from v $ database;

OPEN_MODE
----------
MOUNTED
3. Cancel the REDO application and go to the read only status.
ORCLPDG> alter database recover managed standby database cancel;

Database altered.

ORCLPDG> select open_mode from v $ database;

OPEN_MODE
----------
MOUNTED

ORCLPDG> alter database open;

Database altered.

ORCLPDG> select open_mode from v $ database;

OPEN_MODE
----------
READ ONLY
Manage Primary database events that affect Standby
Create a tablespace or data file:
ORCLPRE> conn sys/safe@orcl_192.168.1.222 as sysdba
Connected.

ORCLPDG> conn sys/safe@orcls_192.168.1.223 as sysdba
Connected.

1. standby_file_management: auto
ORCLPDG> show parameter standby_file_management

NAME TYPE VALUE
-----------------------------------------------------------------------------
Standby_file_management string auto
ORCLPRE> create tablespace pri_tbs datafile '/u01/app/oracle/oradata/orcl/pri. dbf' size 10 m;

Tablespace created.

ORCLPRE> col tsname for a20
ORCLPRE> col dfname for a50
ORCLPRE> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

TSNAME DFNAME
----------------------------------------------------------------------
SYSTEM/u01/app/oracle/oradata/orcl/system01.dbf
UNDOTBS1/u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX/u01/app/oracle/oradata/orcl/sysaux01.dbf
USERS/u01/app/oracle/oradata/orcl/users01.dbf
EXAMPLE/u01/app/oracle/oradata/orcl/example01.dbf
RMANTBS/u01/rec_catalog/rmantbs. dbf
PRI_TBS/u01/app/oracle/oradata/orcl/pri. dbf

7 rows selected.

ORCLPRE> alter system switch logfile;

System altered.
Standby:
ORCLPDG> col dfname for a50
ORCLPDG> col tsname for a20
ORCLPDG> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

6 rows selected.
REDO not enabled:
ORCLPDG> alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG> alter database recover managed standby database cancel;

Database altered.

ORCLPDG> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

7 rows selected.
2. standby_file_management: manual
ORCLPDG> alter system set standby_file_management = manual;

System altered.
Test:
ORCLPRE> create tablespace pri_tbs2 datafile '/u01/app/oracle/oradata/orcl/pri2.dbf' size 5 m;

Tablespace created.

ORCLPRE> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

8 rows selected.
ORCLPRE> alter system switch logfile;

System altered.

Application REDO:
ORCLPDG> alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG> alter database recover managed standby database cancel;
Alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active
View/u01/app/oracle/admin/orclstd/bdump/alert_orclstd.log:

Errors in file/u01/app/oracle/admin/orclstd/bdump/orclstd_mrp0_5968.trc:
ORA-01111: name for data file 8 is unknown-rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/10.2.0/db_1/dbs/unnamed1_8'
ORA-01157: cannot identify/lock data file 8-see DBWR trace file
ORA-01111: name for data file 8 is unknown-rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/10.2.0/db_1/dbs/unnamed1_8'
Data file 8 cannot be identified, that is, the added PRI_TBS2/u01/app/oracle/oradata/orcl/pri2.dbf. The REDO application is forced to be suspended and rename to correct file is required.
Then, View
ORCLPDG> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

TSNAME DFNAME
----------------------------------------------------------------------
SYSTEM/u01/app/oracle/oradata/orclstd/system01.dbf
UNDOTBS1/u01/app/oracle/oradata/orclstd/undotbs01.dbf
SYSAUX/u01/app/oracle/oradata/orclstd/sysaux01.dbf
USERS/u01/app/oracle/oradata/orclstd/users01.dbf
EXAMPLE/u01/app/oracle/oradata/orclstd/example01.dbf
RMANTBS/u01/app/oracle/oradata/orclstd/rmantbs. dbf
PRI_TBS/u01/app/oracle/oradata/orclstd/pri. dbf
PRI_TBS2/u01/app/oracle/10.2.0/db_1/dbs/unnamed1_8 // The path is seriously incorrect.
Modify the data file name to the correct path:
ORCLPDG> alter database create datafile '/u01/app/oracle/10.2.0/db_1/dbs/unnamed1_8 'as'/u01/app/oracle/oradata/orclstd/pri2.dbf ';

Database altered.
Reapply the REDO Application

ORCLPDG> alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG> alter database recover managed standby database cancel;

Database altered.

ORCLPDG> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

8 rows selected.

Delete tablespace:
Modify the Standby Parameter auto

ORCLPDG> alter system set standby_file_management = auto;

System altered.
Delete the tablespace on the Primary side
ORCLPRE> drop tablespace pri_tbs2 including contents and datafiles;

Tablespace dropped.
Including contents and datafiles automatically delete the corresponding physical files while deleting the tablespace.
ORCLPRE> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

7 rows selected.
Switch log:
ORCLPRE> alter system switch logfile;

System altered.
Application REDO:
ORCLPDG> alter database recover managed standby database disconnect from session;

Database altered.
ORCLPDG> alter database recover managed standby database cancel;

Database altered.
ORCLPDG> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

7 rows selected.
View physical files:
ORCLPDG> host dir/u01/app/oracle/oradata/orclstd /*

ORCLPDG> host dir/u01/app/oracle/oradata/orclstd /*
/U01/app/oracle/oradata/orclstd/example01.dbf
/U01/app/oracle/oradata/orclstd/orclstd01.ctl
/U01/app/oracle/oradata/orclstd/orclstd02.ctl
/U01/app/oracle/oradata/orclstd/orclstd03.ctl
/U01/app/oracle/oradata/orclstd/pfile_orclstd.ora
/U01/app/oracle/oradata/orclstd/pri. dbf
/U01/app/oracle/oradata/orclstd/redo01.log
/U01/app/oracle/oradata/orclstd/redo02.log
/U01/app/oracle/oradata/orclstd/redo03.log
/U01/app/oracle/oradata/orclstd/rmantbs. dbf
/U01/app/oracle/oradata/orclstd/sysaux01.dbf
/U01/app/oracle/oradata/orclstd/system01.dbf
/U01/app/oracle/oradata/orclstd/temp01.dbf
/U01/app/oracle/oradata/orclstd/undotbs01.dbf
/U01/app/oracle/oradata/orclstd/users01.dbf

It is indeed deleted.
Modify the Standby parameter manual:
ORCLPDG> alter system set standby_file_management = manual;

System altered.
ORCLPRE> drop tablespace pri_tbs including contents and datafiles;

Tablespace dropped.
ORCLPRE> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

6 rows selected.
Switch log:
ORCLPRE> alter system switch logfile;

System altered.

Enable the REDO application and view it on the Standby side:
ORCLPDG> alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG> alter database recover managed standby database cancel;

Database altered.
ORCLPDG> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

6 rows selected.

It seems that the file has been deleted, but look at the physical files in the operating system:
ORCLPDG> host dir/u01/app/oracle/oradata/orclstd /*

Only the tablespace and data files are deleted from the data dictionary. physical files involved in the tablespace must be manually deleted.
Rename a tablespace:
ORCLPRE> alter tablespace rmantbs offline;

Tablespace altered.

ORCLPRE> host mv/u01/rec_catalog/rmantbs. dbf/u01/rec_catalog/rmantbs01.dbf

ORCLPRE> alter tablespace rmantbs online;
Alter tablespace rmantbs online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6-see DBWR trace file
ORA-01110: data file 6: '/u01/rec_catalog/rmantbs. dbf'

It cannot be identified because it has been renamed in the operating system. You also need to modify the data file path in the data dictionary.
ORCLPRE> alter tablespace rmantbs rename datafile
2 '/u01/rec_catalog/rmantbs. dbf'
3
4 '/u01/rec_catalog/rmantbs01.dbf ';

Tablespace altered.

ORCLPRE> alter tablespace rmantbs online;

Tablespace altered.

ORCLPRE> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

6 rows selected.
ORCLPRE> alter system switch logfile;

System altered.

Switch logs to view Standby:
Manual modification:
ORCLPDG> host mv/u01/app/oracle/oradata/orclstd/rmantbs. dbf rmantbs01.dbf
Modify the data file path in the data dictionary:
ORCLPDG> alter database rename file '/u01/app/oracle/oradata/orclstd/rmantbs. dbf' to '/u01/app/oracle/oradata/orclstd/rmantbs01.dbf ';
Alter database rename file '/u01/app/oracle/oradata/orclstd/rmantbs. dbf' to'/u01/app/oracle/oradata/orclstd/rmantbs01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 6-new file
'/U01/app/oracle/oradata/orclstd/rmantbs01.dbf' not found
ORA-01110: data file 6: '/u01/app/oracle/oradata/orclstd/rmantbs. dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

View logs:
Errors in file/u01/app/oracle/admin/orclstd/bdump/orclstd_mrp0_7911.trc:
ORA-01110: data file 6: '/u01/app/oracle/oradata/orclstd/rmantbs. dbf'
ORA-01157: cannot identify/lock data file 6-see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/orclstd/rmantbs. dbf'
Mon Mar 24 01:53:57 2014
MRP0: Background Media Recovery process shutdown (orclstd)

It seems that this does not work ......

ORCLPDG> alter database create datafile '/u01/app/oracle/oradata/orclstd/rmantbs. dbf' as '/u01/app/oracle/oradata/orclstd/rmantbs01.dbf ';

Database altered.

ORCLPDG> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

6 rows selected.

ORCLPDG> host dir/u01/app/oracle/oradata/orclstd /*
/U01/app/oracle/oradata/orclstd/example01.dbf
/U01/app/oracle/oradata/orclstd/orclstd01.ctl
/U01/app/oracle/oradata/orclstd/orclstd02.ctl
/U01/app/oracle/oradata/orclstd/orclstd03.ctl
/U01/app/oracle/oradata/orclstd/pfile_orclstd.ora
/U01/app/oracle/oradata/orclstd/redo01.log
/U01/app/oracle/oradata/orclstd/redo02.log
/U01/app/oracle/oradata/orclstd/redo03.log
/U01/app/oracle/oradata/orclstd/rmantbs01.dbf
/U01/app/oracle/oradata/orclstd/sysaux01.dbf
/U01/app/oracle/oradata/orclstd/system01.dbf
/U01/app/oracle/oradata/orclstd/temp01.dbf
/U01/app/oracle/oradata/orclstd/undotbs01.dbf
/U01/app/oracle/oradata/orclstd/users01.dbf

ORCLPDG> alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG> alter database recover managed standby database cancel;

Database altered.
ORCLPDG> select ts. name tsname, df. name dfname from v $ tablespace ts, v $ datafile df where ts. ts # = df. ts #;

6 rows selected.

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More

The effect of physical DG Primary on adding/deleting tablespaces (data files) on Standby

Contact Us

What's Trending

Top 10 Tags

Top 10 Keywords

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support

The effect of physical DG Primary on adding/deleting tablespaces (data files) on Standby

Contact Us

What's Trending

Top 10 Tags

Top 10 Keywords

Trending Topic

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support