备案控制台
探索云世界
开发者社区 数据库 文章 正文

11g数据库迁移ASM

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

[oracle@PROD1 ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 2 20:34:02 2016


Copyright (c) 1982, 2013, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> show parameter pfile;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilePROD1.ora
在+DATA磁盘组中创建对应的目录PROD1；PROD1/parameter/;
[oracle@PROD1 ~]$ su - grid
Password: 
[grid@PROD1 ~]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd DATA/
ASMCMD> ls
ASM/
DB_UNKNOWN/
PROD1/
ASMCMD> cd PROD1
ASMCMD> ls
parameter/
ASMCMD> cd parameter/  
ASMCMD> pwd
+DATA/PROD1/parameter
ASMCMD> ls

ASMCMD> cp /u01/app/oracle/product/11.2.0//dbhome_1/dbs/spfilePROD1.ora .
copying /u01/app/oracle/product/11.2.0//dbhome_1/dbs/spfilePROD1.ora -> +DATA/PROD1/parameter/spfilePROD1.ora

ASMCMD> exit
[grid@PROD1 ~]$ su - oracle
Password: 
[oracle@PROD1 ~]$ cd $ORACLE_HOME/dbs
[oracle@PROD1 dbs]$ ls
fls  hc_+ASM.dat  hc_PROD1.dat  init+ASM.ora  init.ora  lkPROD1  orapwPROD1  spfilePROD1.ora
[oracle@PROD1 dbs]$ ls
fls  hc_+ASM.dat  hc_PROD1.dat  init+ASM.ora  init.ora  lkPROD1  orapwPROD1  spfilePROD1.ora
[oracle@PROD1 dbs]$ mv spfilePROD1.ora spfilePROD1.ora.bak

[oracle@PROD1 dbs]$ vi initPROD1.ora
SPFILE='+data/prod1/parameter/spfilePROD1.ora'

[oracle@PROD1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 2 20:44:03 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount force;
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size    2257800 bytes
Variable Size  536874104 bytes
Database Buffers  285212672 bytes
Redo Buffers    6586368 bytes
SQL> show parameter pfile;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string +DATA/prod1/parameter/spfilepr
od1.ora

迁移控制文件
SQL> show parameter control_files;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files     string /u01/app/oracle/oradata/PROD1/
control01.ctl, /u01/app/oracle
/oradata/PROD1/control02.ctl
SQL> alter system set control_files='+data' scope=spfile;
System altered.
SQL> startup mount force;
ORACLE instance started.


Total System Global Area  830930944 bytes
Fixed Size    2257800 bytes
Variable Size  536874104 bytes
Database Buffers  285212672 bytes
Redo Buffers    6586368 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> ho rman target /


Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jan 2 20:51:00 2016


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: PROD1 (not mounted)


RMAN> restore controlfile from '/u01/app/oracle/oradata/PROD1/control01.ctl';


Starting restore at 02-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK


channel ORA_DISK_1: copied control file copy
output file name=+DATA/prod1/controlfile/current.260.900103889
Finished restore at 02-JAN-16

RMAN> exit
Recovery Manager complete.

SQL> show parameter control_files;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files     string +DATA/prod1/controlfile/curren
t.260.900103889

迁移数据文件
SQL> alter database mount;

Database altered.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/system01.dbf
/u01/app/oracle/oradata/PROD1/sysaux01.dbf
/u01/app/oracle/oradata/PROD1/undotbs01.dbf
/u01/app/oracle/oradata/PROD1/users01.dbf

SQL> ho rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jan 2 20:58:57 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD1 (DBID=2121445908, not open)

RMAN> backup as copy database format '+data';

Starting backup at 02-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
output file name=+DATA/prod1/datafile/system.269.900104401 tag=TAG20160102T205959 RECID=1 STAMP=900104464
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
output file name=+DATA/prod1/datafile/sysaux.257.900104465 tag=TAG20160102T205959 RECID=2 STAMP=900104509
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
output file name=+DATA/prod1/datafile/undotbs1.256.900104511 tag=TAG20160102T205959 RECID=3 STAMP=900104512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/prod1/controlfile/backup.270.900104513 tag=TAG20160102T205959 RECID=4 STAMP=900104515
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
output file name=+DATA/prod1/datafile/users.271.900104517 tag=TAG20160102T205959 RECID=5 STAMP=900104516
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 02-JAN-16
channel ORA_DISK_1: finished piece 1 at 02-JAN-16
piece handle=+DATA/prod1/backupset/2016_01_02/nnsnf0_tag20160102t205959_0.272.900104517 tag=TAG20160102T205959 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-JAN-16

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/prod1/datafile/system.269.900104401"
datafile 2 switched to datafile copy "+DATA/prod1/datafile/sysaux.257.900104465"
datafile 3 switched to datafile copy "+DATA/prod1/datafile/undotbs1.256.900104511"
datafile 4 switched to datafile copy "+DATA/prod1/datafile/users.271.900104517"

RMAN> recover database;

Starting recover at 02-JAN-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 02-JAN-16

迁移临时表空间
SQL> alter database open RESETLOGS;

Database altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/temp01.dbf

SQL> alter tablespace temp add tempfile '+data';

Tablespace altered.

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/PROD1/temp01.dbf';

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/prod1/tempfile/temp.273.900104921

迁移日志文件


SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/redo03.log
/u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo01.log
SQL> show parameter create

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size     integer 8388608
create_stored_outlines     string
db_create_file_dest     string
db_create_online_log_dest_1     string
db_create_online_log_dest_2     string
db_create_online_log_dest_3     string
db_create_online_log_dest_4     string
db_create_online_log_dest_5     string

SQL> alter system set db_create_online_log_dest_1='+data';

System altered.

SQL> ho vi redo.sql
SET SERVEROUTPUT ON;


DECLARE


   CURSOR rlc IS


      SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL


      FROM   V$LOG


      UNION


      SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL


      FROM   V$STANDBY_LOG


      ORDER BY 1;


   stmt     VARCHAR2(2048);


BEGIN


   FOR rlcRec IN rlc LOOP


      IF (rlcRec.srl = 'YES') THEN


         stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||


                 rlcRec.thr || ' SIZE ' || rlcRec.bytes;


         EXECUTE IMMEDIATE stmt;


         stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;


         EXECUTE IMMEDIATE stmt;


      ELSE


         stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||


                 rlcRec.thr || ' SIZE ' ||  rlcRec.bytes;


         EXECUTE IMMEDIATE stmt;


         BEGIN


            stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;


            DBMS_OUTPUT.PUT_LINE(stmt);


            EXECUTE IMMEDIATE stmt;


         EXCEPTION


            WHEN OTHERS THEN


               EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';


               EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';


               EXECUTE IMMEDIATE stmt;


         END;


      END IF;


   END LOOP;


END;


/
SQL> @redo.sql
ALTER DATABASE DROP LOGFILE GROUP 1
ALTER DATABASE DROP LOGFILE GROUP 2
ALTER DATABASE DROP LOGFILE GROUP 3


PL/SQL procedure successfully completed.

SQL> select name from v$controlfile
  2  union all
  3  select name from v$datafile
  4  union all
  5  select member from v$logfile;


NAME
--------------------------------------------------------------------------------
+DATA/prod1/controlfile/current.260.900103889
+DATA/prod1/datafile/system.269.900104401
+DATA/prod1/datafile/sysaux.257.900104465
+DATA/prod1/datafile/undotbs1.256.900104511
+DATA/prod1/datafile/users.271.900104517
+DATA/prod1/onlinelog/group_2.276.900105383
+DATA/prod1/onlinelog/group_1.275.900105377
+DATA/prod1/onlinelog/group_4.274.900105375


8 rows selected.
迁移完成；
























,
文章标签：
服务网格
数据库备份
关系型数据库
Oracle
数据库
SQL
Perl
OLAP
关键词：
数据库服务网格
苍雪明南
目录
相关文章
1436047922066202
|
2月前
|
存储 Oracle 关系型数据库
【数据库数据恢复】Oracle数据库ASM磁盘组掉线的数据恢复案例
oracle数据库ASM磁盘组掉线，ASM实例不能挂载。数据库管理员尝试修复数据库，但是没有成功。
1436047922066202
37 1
【数据库数据恢复】Oracle数据库ASM磁盘组掉线的数据恢复案例
snowofsummer
|
文字识别 Oracle NoSQL
oracle 11g 单机asm配置
oracle 11g 单机asm配置
snowofsummer
487 0
ziheng
|
SQL Oracle 网络协议
Oracle ASM 管理的数据库恢复为单实例数据库
ziheng
917 0
余二五
|
存储 Oracle 关系型数据库
建立ORACLE RAC数据库时所用的一些选项（使用ASM存储）
余二五
1101 0
技术小胖子
|
存储 Oracle 关系型数据库
WIN7上模拟ASM安装ORACLE 11g
技术小胖子
1062 0
余二五
|
Oracle 关系型数据库 数据库
Oracle 11g RAC ASM 错误之(1)
余二五
963 0
余二五
|
SQL 存储 Oracle
oracle数据库存储管理--ASM
余二五
1213 0
余二五
|
存储 Oracle 关系型数据库
oracle 11g 使用ASM存储迁移
余二五
912 0
余二五
|
存储 Oracle 关系型数据库
Oracle ASM数据库故障数据恢复解决方案
余二五
908 0
技术小美
|
SQL Oracle 关系型数据库
创建ASM实例及ASM数据库
技术小美
1159 0

热门文章

最新文章

  • 1
    成都晨云信息技术完成阿里云PolarDB数据库产品生态集成认证
  • 2
    一文搞懂数据库中的“锁”（图文详解）
  • 3
    【C/C++ 数据库 sqlite3】SQLite C语言API返回值深入解析
  • 4
    Perl 教程 之 Perl 数据库连接 1
  • 5
    阿里云数据库服务器价格表，数据库创建、连接和使用教程
  • 6
    在业务处理分析一体化的背景下，开发者如何平衡OLTP和OLAP数据库的技术需求与选型？
  • 7
    在Python Web开发过程中：数据库与缓存，MySQL和NoSQL数据库的主要差异是什么？
  • 8
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 9
    【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
  • 10
    MySQL数据库，可以使用二进制日志（binary log）进行时间点恢复
  • 1
    如何通过计算巢在ACK集群上使用Istio服务网格
    33
  • 2
    RAC创建ASM磁盘组时配置多路径和UDEV
    94
  • 3
    服务网格中授权策略对性能的影响大吗？
    191
  • 4
    在服务网格ASM中，如何实现限流防护的指标监控
    384
  • 5
    oracle asm 磁盘显示offline
    30
  • 6
    asm 磁盘故障处理日志
    24
  • 7
    【数据库数据恢复】Oracle数据库ASM磁盘组掉线的数据恢复案例
    37
  • 8
    使用ASM流量泳道的全链路灰度发布实践
    178
  • 9
    java asm、cglib
    39
  • 10
    模型服务网格：云原生下的模型服务管理
    78383

    • 相关课程

    更多
  • 数据库的前世今生
  • 数据库核心概念
  • 从传统数据库到云数据库演进
  • 数据库常见问题排查
  • 数据库及SQL/MySQL基础
  • 高校精品课-西安交通大学 -数据库理论与技术

    • 相关电子书

    更多
  • 2022 DTCC-阿里云一站式数据库上云最佳实践
  • 云时代的数据库技术趋势
  • 超大型金融机构国产数据库全面迁移成功实践

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 云原生HTAP数据库，让你的交易和分析一库搞定
  • Excel文件转存到RDS数据库
  • 快速体验PolarDB开源数据库
  • 手把手教你把IoT传感器数据存储到数据库
  • WorkBench操作云上数据库
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）