AI 助理
备案控制台登录注册
开发者社区 数据库 文章 正文

oracle-排查dg环境文件不开Auto同步导致的错误

2022-07-26 135
版权
举报
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

故障：

主库表空间添加文件，备库同步挂了。

排查

主库alert log:

ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move276.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:04:44 2022
Thread 1 cannot allocate new log, sequence 248280
Private strand flush not complete
  Current log# 4 seq# 248279 mem# 0: /oradata01/orcl/redo04.log
Thread 1 advanced to log sequence 248280 (LGWR switch)
  Current log# 1 seq# 248280 mem# 0: /oradata01/orcl/redo01.log
Tue Jul 26 12:04:48 2022
LNS: Standby redo logfile selected for thread 1 sequence 248280 for destination LOG_ARCHIVE_DEST_2
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move276.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move277.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:04:55 2022
Archived Log entry 316646 added for thread 1 sequence 248279 ID 0xfffffffff4ad5d16 dest 1:
Tue Jul 26 12:04:57 2022
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move277.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move278.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move278.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move279.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:05:09 2022
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move279.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move280.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move280.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:07:13 2022
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move281.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move281.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move282.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:07:24 2022
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move282.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move283.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move283.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move284.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move284.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move285.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:07:38 2022
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move285.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:09:58 2022
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move286.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move286.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move287.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move287.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move288.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:10:12 2022
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move288.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move289.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move289.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move290.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:10:23 2022
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move290.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:11:44 2022
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move291.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move291.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move292.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:11:54 2022
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move292.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move293.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move293.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move294.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:12:05 2022
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move294.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move295.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Completed: ALTER TABLESPACE orcl_move ADD DATAFILE '/datalv4/db/oradata/orcl/orcl_move295.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G
Tue Jul 26 12:13:35 2022


        

          
        

        
        

          

          AI 代码解读

备库

alert log

Media Recovery Waiting for thread 1 sequence 248280 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 248280 Reading mem 0
  Mem# 0: /data01/redo_log/orcl/stdredo01.log
Tue Jul 26 12:04:58 2022
Archived Log entry 498722 added for thread 1 sequence 248279 ID 0xfffffffff4ad5d16 dest 1:
File #321 added to control file as 'UNNAMED00321' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Errors in file /data01/u01/app/oracle/diag/rdbms/orclp/orcl/trace/orcl_pr00_10158200.trc:
ORA-01274: cannot add datafile '/datalv4/db/oradata/orcl/orcl_move276.dbf' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 12258879621859
Tue Jul 26 12:05:02 2022
MRP0: Background Media Recovery process shutdown (orcl)
Tue Jul 26 12:13:48 2022

        

          
        

        
        

          

          AI 代码解读

原来是STANDBY_FILE_MANAGEMENT设成了manual导致路径不对

查看数据文件，发现有个文件路径不对

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data01/oradata01/orcl/orcl_move264.dbf
/data01/oradata01/orcl/orcl_move265.dbf
/data01/oradata01/orcl/orcl_move266.dbf
/data01/oradata01/orcl/orcl_move267.dbf
/data01/oradata01/orcl/orcl_move268.dbf
/data01/oradata01/orcl/orcl_move269.dbf
/data01/oradata01/orcl/orcl_move270.dbf
/data01/oradata01/orcl/orcl_move271.dbf
/data01/oradata01/orcl/orcl_move272.dbf
/data01/oradata01/orcl/orcl_move273.dbf
/data01/oradata01/orcl/orcl_move274.dbf

NAME
--------------------------------------------------------------------------------
/data01/oradata01/orcl/orcl_move275.dbf
/data01/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00321

321 rows selected.


        

          
        

        
        

          

          AI 代码解读

查看dg参数设置

SQL> show parameter conver;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /oradata01/orcl/, /data01/
                                                 oradata01/orcl/, /datalv3/
                                                 db/oradata/orcl/, /data01/
                                                 oradata01/orcl/, /datalv4/
                                                 db/oradata/orcl/, /data01/
                                                 oradata01/orcl/
log_file_name_convert                string      /oradata01/orcl/, /data01/
                                                 redo_log/orcl/
SQL> 
/oradata01/orcl/, /data01/oradata01/orcl/, 
/datalv3/db/oradata/orcl/, /data01/oradata01/orcl/, 
/datalv4/db/oradata/orcl/, /data01/oradata01/orcl/

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL> 

        

          
        

        
        

          

          AI 代码解读

于是修改路径有问题的这个文件

alter database create datafile '/data01/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00321' as '/data01/oradata01/orcl/orcl_move276.dbf';

        

          
        

        
        

          

          AI 代码解读
alert log 
Tue Jul 26 12:39:05 2022
alter database create datafile '/data01/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00321' as '/data01/oradata01/orcl/orcl_move276.dbf'
Completed: alter database create datafile '/data01/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00321' as '/data01/oradata01/orcl/orcl_move276.dbf'

        

          
        

        
        

          

          AI 代码解读
查看文件 
$ ls -ltr /data01/oradata01/orcl/orcl_move276.dbf
-rw-r-----    1 oracle   oinstall 1073750016 Jul 26 12:39 /data01/oradata01/orcl/orcl_move276.dbf
$ ls -ltr /data01/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00321
ls: 0653-341 The file /data01/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00321 does not exist.
$ 


select name from v$datafile ;
NAME
------------------------------------------------------------
/data01/oradata01/orcl/orcl_move272.dbf
/data01/oradata01/orcl/orcl_move273.dbf
/data01/oradata01/orcl/orcl_move274.dbf
/data01/oradata01/orcl/orcl_move275.dbf
/data01/oradata01/orcl/orcl_move276.dbf

269 rows selected.

        

          
        

        
        

          

          AI 代码解读

SQL>

修改文件同步参数，开启同步

SQL> alter system set standby_file_management=auto scope=both;

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
standby_file_management              string     AUTO
SQL> 

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;

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

SQL> 

        

          
        

        
        

          

          AI 代码解读

dg正常了，文件是同步过来了，但发现新问题：文件前都带了空格

select name from v$datafile ;
/data01/oradata01/orcl/orcl_move273.dbf
/data01/oradata01/orcl/orcl_move274.dbf
/data01/oradata01/orcl/orcl_move275.dbf
/data01/oradata01/orcl/orcl_move276.dbf
/data01/oradata01/orcl/ orcl_move277.dbf
/data01/oradata01/orcl/ orcl_move278.dbf
/data01/oradata01/orcl/ orcl_move279.dbf
/data01/oradata01/orcl/ orcl_move280.dbf
/data01/oradata01/orcl/ orcl_move281.dbf
/data01/oradata01/orcl/ orcl_move282.dbf
/data01/oradata01/orcl/ orcl_move283.dbf
/data01/oradata01/orcl/ orcl_move284.dbf
/data01/oradata01/orcl/ orcl_move285.dbf
/data01/oradata01/orcl/ orcl_move286.dbf
/data01/oradata01/orcl/ orcl_move287.dbf
/data01/oradata01/orcl/ orcl_move288.dbf
/data01/oradata01/orcl/ orcl_move289.dbf
/data01/oradata01/orcl/ orcl_move290.dbf
/data01/oradata01/orcl/ orcl_move291.dbf
/data01/oradata01/orcl/ orcl_move292.dbf
/data01/oradata01/orcl/ orcl_move293.dbf
/data01/oradata01/orcl/ orcl_move294.dbf
/data01/oradata01/orcl/ orcl_move295.dbf

        

          
        

        
        

          

          AI 代码解读
应该是db_file_name_convert配置有问题，但show parameter db_file_name_convert看不出有空格，于是生成pfile查看,果然是有空间 
db_file_name_convert='/oradata01/orcl/','/data01/oradata01/orcl/','/datalv3/db/oradata/orcl/','/data01/oradata01/orcl/','/datalv4/db/oradata/orcl/','/data01/oradata01/orcl/ '


        

          
        

        
        

          

          AI 代码解读

于是，修改文件路径。。。

先把同步停了，文件同步参数改成manual

alter database recover managed standby database cancel;
 alter system set standby_file_management=MANUAL scope=both;
 
 SQL> select open_mode from v$database;

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

SQL>  show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL> 

        

          
        

        
        

          

          AI 代码解读

修改db_file_name_convert参数，需要重启生效。

SQL> alter system  set db_file_name_convert='/oradata01/orcl/','/data01/oradata01/orcl/','/datalv3/db/oradata/orcl/','/data01/oradata01/orcl/','/datalv4/db/oradata/orcl/','/data01/oradata01/orcl/' scope=spfile;
System altered.

        

          
        

        
        

          

          AI 代码解读

rename数据文件也需要重启到mount状态才行，于是重启数据库。

SQL> startup mount force;
ORACLE instance started.

Total System Global Area 1.4965E+11 bytes
Fixed Size                  2254176 bytes
Variable Size            2.2549E+10 bytes
Database Buffers         1.2670E+11 bytes
Redo Buffers              400384000 bytes
Database mounted.
SQL> select open_mode from v$database;

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

        

          
        

        
        

          

          AI 代码解读

rename 数据文件

alter database rename file '/data01/oradata01/orcl/ orcl_move277.dbf' to '/data01/oradata01/orcl/orcl_move277.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move278.dbf' to '/data01/oradata01/orcl/orcl_move278.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move279.dbf' to '/data01/oradata01/orcl/orcl_move279.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move280.dbf' to '/data01/oradata01/orcl/orcl_move280.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move281.dbf' to '/data01/oradata01/orcl/orcl_move281.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move282.dbf' to '/data01/oradata01/orcl/orcl_move282.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move283.dbf' to '/data01/oradata01/orcl/orcl_move283.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move284.dbf' to '/data01/oradata01/orcl/orcl_move284.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move285.dbf' to '/data01/oradata01/orcl/orcl_move285.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move286.dbf' to '/data01/oradata01/orcl/orcl_move286.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move287.dbf' to '/data01/oradata01/orcl/orcl_move287.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move288.dbf' to '/data01/oradata01/orcl/orcl_move288.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move289.dbf' to '/data01/oradata01/orcl/orcl_move289.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move290.dbf' to '/data01/oradata01/orcl/orcl_move290.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move291.dbf' to '/data01/oradata01/orcl/orcl_move291.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move292.dbf' to '/data01/oradata01/orcl/orcl_move292.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move293.dbf' to '/data01/oradata01/orcl/orcl_move293.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move294.dbf' to '/data01/oradata01/orcl/orcl_move294.dbf';
alter database rename file '/data01/oradata01/orcl/ orcl_move295.dbf' to '/data01/oradata01/orcl/orcl_move295.dbf';


        

          
        

        
        

          

          AI 代码解读

查看Rename成功

select name from v$datafile;

/data01/oradata01/orcl/orcl_move277.dbf
/data01/oradata01/orcl/orcl_move278.dbf
/data01/oradata01/orcl/orcl_move279.dbf
/data01/oradata01/orcl/orcl_move280.dbf
/data01/oradata01/orcl/orcl_move281.dbf
/data01/oradata01/orcl/orcl_move282.dbf
/data01/oradata01/orcl/orcl_move283.dbf
/data01/oradata01/orcl/orcl_move284.dbf
/data01/oradata01/orcl/orcl_move285.dbf

NAME
--------------------------------------------------------------------------------
/data01/oradata01/orcl/orcl_move286.dbf
/data01/oradata01/orcl/orcl_move287.dbf
/data01/oradata01/orcl/orcl_move288.dbf
/data01/oradata01/orcl/orcl_move289.dbf
/data01/oradata01/orcl/orcl_move290.dbf
/data01/oradata01/orcl/orcl_move291.dbf
/data01/oradata01/orcl/orcl_move292.dbf
/data01/oradata01/orcl/orcl_move293.dbf
/data01/oradata01/orcl/orcl_move294.dbf
/data01/oradata01/orcl/orcl_move295.dbf


        

          
        

        
        

          

          AI 代码解读

到os数据文件所在目录下，mv一下带空格的数据文件

mv " orcl_move277.dbf"  "orcl_move277.dbf"
mv " orcl_move278.dbf"  "orcl_move278.dbf"
mv " orcl_move279.dbf"  "orcl_move279.dbf"
mv " orcl_move280.dbf"  "orcl_move280.dbf"
mv " orcl_move281.dbf"  "orcl_move281.dbf"
mv " orcl_move282.dbf"  "orcl_move282.dbf"
mv " orcl_move283.dbf"  "orcl_move283.dbf"
mv " orcl_move284.dbf"  "orcl_move284.dbf"
mv " orcl_move285.dbf"  "orcl_move285.dbf"
mv " orcl_move286.dbf"  "orcl_move286.dbf"
mv " orcl_move287.dbf"  "orcl_move287.dbf"
mv " orcl_move288.dbf"  "orcl_move288.dbf"
mv " orcl_move289.dbf"  "orcl_move289.dbf"
mv " orcl_move290.dbf"  "orcl_move290.dbf"
mv " orcl_move291.dbf"  "orcl_move291.dbf"
mv " orcl_move292.dbf"  "orcl_move292.dbf"
mv " orcl_move293.dbf"  "orcl_move293.dbf"
mv " orcl_move294.dbf"  "orcl_move294.dbf"
mv " orcl_move295.dbf"  "orcl_move295.dbf"


        

          
        

        
        

          

          AI 代码解读

修改文件同步参数，启动数据库到readonly，打开实时同步

SQL> alter system set standby_file_management=auto scope=both;

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> alter database open;

Database altered.

SQL> select open_mode from v$database; 

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

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database; 

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

SQL> 


        

          
        

        
        

          

          AI 代码解读

检查同步是否正常

主库查，no gap 
SQL> set line 300
SQL> col DB_UNIQUE_NAME for a20
SQL> col DEST_NAME for a20
col DESTINATION for a20
col DATABASE_MODE for a20
col recovery_mode for a25
col destination for a25
col gap_status for a10
SQL> SQL> SQL> SQL> SQL> SQL> col type for a10
SQL> col status for a10
select dest_id,dest_name,status,type,database_mode,recovery_mode,destination,db_unique_name,gap_status from v$archive_dest_status where status='VALID';
SQL> 
   DEST_ID DEST_NAME            STATUS     TYPE       DATABASE_MODE        RECOVERY_MODE             DESTINATION               DB_UNIQUE_NAME       GAP_STATUS
---------- -------------------- ---------- ---------- -------------------- ------------------------- ------------------------- -------------------- ----------
         1 LOG_ARCHIVE_DEST_1   VALID      LOCAL      OPEN                 IDLE                      /arch01/orcl/         NONE
         2 LOG_ARCHIVE_DEST_2   VALID      PHYSICAL   OPEN_READ-ONLY       MANAGED REAL TIME APPLY   orclp                 orclp            NO GAP

SQL> 
SQL> select thread#, sequence# from v$thread;

   THREAD#  SEQUENCE#
---------- ----------
         1     248290

SQL> 

        

          
        

        
        

          

          AI 代码解读
备库： 
SQL> select process, status, thread#, sequence# from v$managed_standby where process='MRP0';

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
MRP0      APPLYING_LOG          1     248290

SQL> 

        

          
        

        
        

          

          AI 代码解读
文章标签：
数据库备份
数据库
关键词：
Oracle数据库一键上云文件
Oracle数据库一键上云dg
Oracle数据库一键上云同步
Oracle数据库一键上云环境
Oracle数据库一键上云排查
lemotree
+关注
92文章
目录
打赏
0
0
0
0
4
分享
相关文章
赵渝强老师
|
3月前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的控制文件与归档日志文件
本文介绍了Oracle数据库中的控制文件和归档日志文件。控制文件记录了数据库的物理结构信息，如数据库名、数据文件和联机日志文件的位置等。为了保护数据库，通常会进行控制文件的多路复用。归档日志文件是联机重做日志文件的副本，用于记录数据库的变更历史。文章还提供了相关SQL语句，帮助查看和设置数据库的日志模式。
赵渝强老师
117 1
【赵渝强老师】Oracle的控制文件与归档日志文件
jianz123
|
3月前
|
SQL Oracle 关系型数据库
Oracle 从 DMP 文件中恢复指定表的步骤
Oracle 从 DMP 文件中恢复指定表的步骤
jianz123
179 7
1436047922066202
|
3月前
|
Oracle 关系型数据库 数据库
Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
一台Oracle数据库打开报错，报错信息： “system01.dbf需要更多的恢复来保持一致性，数据库无法打开”。管理员联系我们数据恢复中心寻求帮助，并提供了Oracle_Home目录的所有文件。用户方要求恢复zxfg用户下的数据。 由于数据库没有备份，无法通过备份去恢复数据库。
1436047922066202
103 1
赵渝强老师
|
3月前
|
Oracle 关系型数据库 数据库
【赵渝强老师】Oracle的参数文件与告警日志文件
本文介绍了Oracle数据库的参数文件和告警日志文件。参数文件分为初始化参数文件（PFile）和服务器端参数文件（SPFile），在数据库启动时读取并分配资源。告警日志文件记录了数据库的重要活动、错误和警告信息，帮助诊断问题。文中还提供了相关视频讲解和示例代码。
赵渝强老师
114 1
1436047922066202
|
5月前
|
Oracle 关系型数据库 数据库
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例
打开oracle数据库报错“system01.dbf需要更多的恢复来保持一致性，数据库无法打开”。 数据库没有备份，无法通过备份去恢复数据库。用户方联系北亚企安数据恢复中心并提供Oracle_Home目录中的所有文件，急需恢复zxfg用户下的数据。 出现“system01.dbf需要更多的恢复来保持一致性”这个报错的原因可能是控制文件损坏、数据文件损坏，数据文件与控制文件的SCN不一致等。数据库恢复工程师对数据库文件进一步检测、分析后，发现sysaux01.dbf文件损坏，有坏块。 修复并启动数据库后仍然有许多查询报错，export和data pump工具使用报错。从数据库层面无法修复数据库。
1436047922066202
107 7
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例
赵渝强老师
|
3月前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的数据文件
在Oracle数据库中，数据库由多个表空间组成，每个表空间包含多个数据文件。数据文件存储实际的数据库数据。查询时，如果内存中没有所需数据，Oracle会从数据文件中读取并加载到内存。可通过SQL语句查看和管理数据文件。附有视频讲解及示例。
赵渝强老师
57 0
1243767626601764
|
4月前
|
Oracle 关系型数据库 数据库
oracle数据恢复—Oracle数据库文件损坏导致数据库打不开的数据恢复案例
打开oracle数据库时报错，报错信息：“system01.dbf需要更多的恢复来保持一致性，数据库无法打开”。急需恢复zxfg用户下的数据。 出现上述报错的原因有：控制文件损坏、数据文件损坏、数据文件与控制文件的SCN不一致等。数据恢复工程师对数据库文件做进一步检测分析后发现sysaux01.dbf文件有坏块。修复sysaux01.dbf文件，启动数据库依然有许多查询报错。export和data pump工具无法使用，查询告警日志并分析报错，确认发生上述错误的原因就是sysaux01.dbf文件损坏。由于该文件损坏，从数据库层面无法修复数据库。由于system和用户表空间的数据文件是正常的，
1243767626601764
145 0
以山向海
|
7月前
|
存储 Oracle 关系型数据库
关系型数据库Oracle执行RMAN脚本环境检查
【7月更文挑战第24天】
以山向海
136 4
以山向海
|
7月前
|
SQL Oracle 关系型数据库
关系型数据库Oracle设置 RMAN 环境:
【7月更文挑战第25天】
以山向海
100 2
以山向海
|
7月前
|
存储 负载均衡 Oracle
关系型数据库Oracle Real Application Clusters (RAC) 提供集群环境下的高可用性
【7月更文挑战第9天】
以山向海
123 1

热门文章

最新文章

  • 1
    如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能？
    10
  • 2
    【YashanDB知识库】oracle dblink varchar类型查询报错记录
    12
  • 3
    Oracle 的集群文件系统（Cluster File System）有哪些特点？
    5
  • 4
    Mysql、Oracle审计日志的开启
    12
  • 5
    MySQL 和 Oracle 的区别？
    16
  • 6
    linux8安装oracle 11g遇到的问题记录
    7
  • 7
    【YashanDB观点】论Oracle兼容性，我们需要做什么
    1
  • 8
    【YashanDB观点】论Oracle兼容性，我们需要做什么
    1
  • 9
    Oracle - 简单的 SELECT 的使用
    2
  • 10
    [20121115]关于oracle数据文件的第1块.txt
    1
  • 1
    【YashanDB 知识库】同样建表语句，大整型数字在 Oracle 插入成功，在 YashanDB 插入失败
    10
  • 2
    【YashanDB 知识库】oracle 与 yashanDB 的 jdbc 返回常量列"0.00"的精度和刻度不一致
    6
  • 3
    MySQL 和 Oracle 的区别？
    16
  • 4
    Mysql、Oracle审计日志的开启
    50
  • 5
    Oracle 的集群文件系统（Cluster File System）有哪些特点？
    50
  • 6
    如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能？
    73
  • 7
    【YashanDB知识库】oracle dblink varchar类型查询报错记录
    60
  • 8
    【YashanDB观点】论Oracle兼容性，我们需要做什么
    30
  • 9
    【YashanDB观点】论Oracle兼容性，我们需要做什么
    34
  • 10
    linux8安装oracle 11g遇到的问题记录
    77

    • 相关课程

    更多
  • 数据库常见问题排查
  • 从0到1数据库内核实战教程

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 推荐镜像

    更多
  • oracle
  • postgresql
  • mysql
    • 下一篇
    阿里云oss简介和如何对接使用
    目录
    AI助理

    你好，我是AI助理

    可以解答问题、推荐解决方案等