Linux 7.9 平台下 Oracle 19c 本机克隆实例
旧实例: ora19c
新实例: ora19c2
1.生成旧实例参数文件及控制文件
[oracle@db01 ~]$ export ORACLE_SID=ora19c # 设置旧实例的SID
[oracle@db01 ~]$ sqlplus / as sysdba
[oracle@db01 ~]$ create pfile='/tmp/pfile.ora' from spfile; # 参数文件
[oracle@db01 ~]$ alter database backup controlfile to trace as '/tmp/control_rebuild.trc'; # 控制文件
2.记录旧实例实例文件位置
SQL> select name from v$datafile;
SQL> select MEMBER from v$logfile;
SQL> select FILE_NAME from dba_temp_files;
3.关闭旧实例数据库
shutdowm immediate;
4.编辑新实例参数文件(新实例名,旧db_name)
[oracle@db01 ~]$ vim /tmp/pfile.ora
ora19c2.__db_cache_size=1543503872
ora19c2.__java_pool_size=67108864
ora19c2.__large_pool_size=67108864
ora19c2.__oracle_base='/u01/app/oracle' #ORACLE_BASE set from environment
ora19c2.__pga_aggregate_target=2348810240
ora19c2.__sga_target=4362076160
ora19c2.__shared_io_pool_size=0
ora19c2.__shared_pool_size=2617245696
ora19c2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ora19c2/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ora19c2/control01.ctl','/u01/app/oracle/oradata/ora19c2/control02.ctl'
*.db_block_size=8192
*.db_name='ora19c'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora19cXDB)'
*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=10.22.82.152)(PORT=1521))'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=778m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2334m
*.star_transformation_enabled='TRUE'
*.undo_tablespace='UNDOTBS1'
5.拷贝文件(旧实例上的DATAFILE均需要复制)
cp /opt/oracle/oradata/ora19c/system01.dbf /opt/oracle/oradata/ora19c2/system01.dbf
cp /opt/oracle/oradata/ora19c/sysaux01.dbf /opt/oracle/oradata/ora19c2/sysaux01.dbf
cp /opt/oracle/oradata/ora19c/undotbs01.dbf /opt/oracle/oradata/ora19c2/undotbs01.dbf
cp /opt/oracle/oradata/ora19c/users01.dbf /opt/oracle/oradata/ora19c2/users01.dbf
cp /opt/oracle/oradata/ora19c/temp01.dbf /opt/oracle/oradata/ora19c2/temp01.dbf
....
6.编辑备份出来的控制文件，获取生成新实例控制文件语句
[oracle@db01 ~]$ vim /tmp/control_rebuild.trc
7.新实例启动到nomount状态,创建控制文件,注意修改路径到新文件所在地方,db_name不变.
[oracle@db01 ~]$ export ORACLE_SID=ora19c2 # 设置新实例SID
[oracle@db01 ~]$ sqlplus / as sysdba
SQL> create spfile from pfile='/tmp/pfile.ora';
SQL> STARTUP NOMOUNT
SQL> CREATE CONTROLFILE set DATABASE "ora19c" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/oracle/oradata/ora19c2/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/opt/oracle/oradata/ora19c2/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/opt/oracle/oradata/ora19c2/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/opt/oracle/oradata/ora19c2/system01.dbf', -- 文章仅保留了默认的DATAFILE
'/opt/oracle/oradata/ora19c2/sysaux01.dbf', -- 旧实例上的DATAFILE均需要补充到此处
'/opt/oracle/oradata/ora19c2/undotbs01.dbf',
'/opt/oracle/oradata/ora19c2/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
8.新实例创建密码文件
[oracle@db01 ~]$ orapwd file='$ORACLE_HOME/dbs/orapwora19c2' password=oracle entries=5 force=y;
9.启动新实例到mount 状态
SQL> alter database open resetlogs;
10.修改db_name
[oracle@db01 ~]$ nid target=sys/oracle@ORA19C dbname=ora19c2
也可以这样,直接读环境变量
[oracle@db01 ~]$ nid target= / dbname=ora19c2
11.修改完成后数据库会停掉,修改参数文件里db_name为新名字后起库
[oracle@db01 ~]$ vim /tmp/pfile.ora
12.启动新实例
[oracle@db01 ~]$ sqlplus / as sysdba
SQL> create spfile from pfile='/tmp/pfile.ora';
SQL> startup mount;
SQL> alter database open resetlogs;
13.验证
[oracle@localhost ~]$ ps -ef | grep pmon
oracle 17147 1 0 21:47 ? 00:00:00 ora_pmon_ora19c2
oracle 17386 1 0 21:51 ? 00:00:00 ora_pmon_ora19c
oracle 18174 17310 0 22:10 pts/2 00:00:00 grep --color=auto pmon
[oracle@localhost ~]$ lsnrctl status
大功告成
执行数据库克隆时报ORA-01103错误
问题描述：
数据库克隆时，在执行startup mount时报错。
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 71304572 bytes
Database Buffers 88080384 bytes
Redo Buffers 7139328 bytes
ORA-01103: database name 'DB1' in control file is not 'DB2'
这表示测试数据库中的参数DB_NAME跟控制文件中的不一致。
解决办法:重建控制文件。
1) 删除目前的控制文件
2) 先备份原DB1的控制文件
SQL> alter database backup controlfile to trace as '/tmp/control_rebuild.trc';
3)查找相关的trace文件， 文件中包含创建控制文件的SQL语句。
打开它，修改相关参数：
1. REUSE DATABASE “ORCL” 改为 SET DATABASE “TESTDB”
2. NORESETLOGS 改为 RESETLOGS
3. 修改相关的路径名
4)重建控制文件
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 71304572 bytes
Database Buffers 88080384 bytes
Redo Buffers 7139328 bytes
SQL> CREATE CONTROLFILE SET DATABASE "DB2" RESETLOGS FORCE LOGGING NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'D:ORADATADB2REDO01.LOG' SIZE 50M,
9 GROUP 2 'D:ORADATADB2REDO02.LOG' SIZE 50M,
10 GROUP 3 'D:ORADATADB2REDO03.LOG' SIZE 50M
11 DATAFILE
12 'D:ORADATADB2SYSTEM01.DBF',
13 'D:ORADATADB2UNDOTBS01.DBF',
14 'D:ORADATADB2SYSAUX01.DBF',
15 'D:ORADATADB2USERS.DBF'
16 CHARACTER SET ZHS16GBK
17 ;
5)打开DB
SQL> ALTER DATABASE OPEN RESETLOGS;<!--more-->
参考资料：
本机克隆oracle实例
Linux 7.9 平台下 Oracle 19.12 单实例使用RMAN迁移至单实例
执行数据库克隆时报ORA-01103错误