Transportable tablespace on standby (Doc ID 788176.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.4 [Release 10.2]
Oracle Database - Enterprise Edition - Version 10.2.0.5 to 10.2.0.5 [Release 10.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
***Checked for relevance on 31-MAY-2013***
GOAL
This note is applicable when the customers do not want to touch the primary for transportable tablespace and they want to move the tablespace from standby to some other database. It is recommended to do when there is a less activity on primary.
当客户不想在主数据库上执行传输表空间，并且客户希望将表空间从备用数据库移动到其他数据库时，本文适用。建议在主数据库上的活动较少时执行此操作。
SOLUTION
1. Check if the standby is in SYNC with primary 检查备用数据库是否与主数据库同步
SQL> select ads.dest_id,max(sequence#) "Current Sequence",max(log_sequence) "Last Archived" from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads where ad.dest_id=al.dest_id and al.dest_id=ads.dest_id group by ads.dest_id;
DEST_ID Current Sequence Last Archived
---------- ---------------- -------------
1 31 31
2 31 32
at standby,
SQL> select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;
Last Seq Recieved Last Seq Applied
----------------- ----------------
31 31
2. Check the value of db_recovery_file_dest_size and alter it based on the transactions on standby after opening the standby database in read_write mode,
在以read_write模式打开备用数据库后，检查db_recovery_file_dest_size的值并根据备用数据库上的事务进行更改
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G; System altered. SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u02/<db_recovery_file_dest>'; System altered.
3. Cancel the MRP (Managed recover process) 取消MRP（恢复管理进程）
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered.
4. Create guaranteed restore points(GRP) on standby 在备用数据库上创建保证的还原点（GRP）
SQL> CREATE RESTORE POINT before_application_patch GUARANTEE FLASHBACK DATABASE; Restore point created.
5. On primary defer the log_archive_dest_2, which is sending logs to standby
在主数据库上，将 log_archive_dest_2（将日志发送到备用数据库）设置defer
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER; System altered.
6. Activate and open the standby database 激活并打开备用数据库
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; Database altered. SQL> STARTUP MOUNT FORCE; ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 1220844 bytes Variable Size 167776020 bytes Database Buffers 436207616 bytes Redo Buffers 7163904 bytes Database mounted.
注意：检查保护模式是否为 MAX_AVAILABILITY 和 MAX_PROTECTION，然后将其降级为 MAX_PERFORMANCE BY
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; Database altered. SQL> ALTER DATABASE OPEN; Database altered.
7. Place the tablespace in read only mode 将表空间置于只读模式
SQL> alter tablespace stdby read only; Tablespace altered.
8. Do the Metadata export 执行元数据导出
$ exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=stdby file=tts.dmp log=exp_tts.log statistics=none Export: Release - Production on Fri Mar 6 18:14:39 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release - Production With the Partitioning, OLAP and Data Mining options Export done in US7ASCII character set and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace STDBY ... . exporting cluster definitions . exporting table definitions . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings.
[oracle@core1 db_recovery_file_dest]$ cat exp_tts.log Connected to: Oracle Database 10g Enterprise Edition Release - Production With the Partitioning, OLAP and Data Mining options Export done in US7ASCII character set and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace STDBY ... . exporting cluster definitions . exporting table definitions . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings.
注意：有关可移动表空间的其余步骤，请参见
Article-ID : Note 243304.1
Title: 10g : Transportable Tablespaces Across Different Platforms
9. Place the standby database in mount mode again 再次将备用数据库置于mount模式
SQL> STARTUP MOUNT FORCE; ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 1250428 bytes Variable Size 247466884 bytes Database Buffers 356515840 bytes Redo Buffers 7135232 bytes Database mounted.
10. Flashback to GRP and again shutdown and startup the standby database 闪回GRP，然后再次关闭并启动备用数据库
SQL> FLASHBACK DATABASE TO RESTORE POINT <RESTORE_POINT_NAME>; Flashback completed. SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Database altered. SQL> STARTUP MOUNT FORCE; ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 1250428 bytes Variable Size 247466884 bytes Database Buffers 356515840 bytes Redo Buffers 7135232 bytes Database mounted.
11. Start the MRP again 再次启动MRP
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; Database altered.
注意：如果主数据库上的事务较多，即备用数据库滞后于更多日志，那么我们必须在主数据库上进行增量备份并将其应用于备用数据库。
12. Enable the log_archive_dest_2 on primary 在主数据库上启用log_archive_dest_2
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; System altered.
注意：通过使用第一步中提到的查询来检查备用SYNC。
NOTE : truncate (or drop) sys.transport_set_violations and run dbms_tts.transport_set_check from the standby once the logical standby is opened read/write. This is because if they have run this check on the primary, it was propagated to the standby. If they have not cleared this on the standby, and run the check there, it will cause these errors when they try to do the export. Please include the errors when implementing the comment.
注意：一旦逻辑备用数据库以读写方式打开，则截断（或删除）sys.transport_set_violations并从备用数据库运行dbms_tts.transport_set_check。这是因为如果他们在主数据库上运行了此检查，则该检查将传播到备用数据库。如果他们没有在备用数据库上清除此错误，并在那里进行检查，则当他们尝试执行导出操作时，将导致这些错误。实施评论时，请包含错误。
ORA-39123: Data Pump transportable tablespace job aborted
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 902
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "SYS.DBMS_TTS", line 838
ORA-00955: name is already used by an existing object
Reference is 3-10275730211 for which this was the solution.
参考是3-10275730211，这是解决方案。
