# 而这个序列也是真实存在的,说明是用户自动创建的 xb@PDB12C> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S K -------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - ISEQ$$_90071 1 1.0000E+28 1 N N 20 21 N N N N
drop table t2 purge; create table t2 (id number generated always as identity, name varchar2(10));
xb@PDB12C> insert into t2 values(1,'xb'); insert into t2 values(1,'xb') * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column
xb@PDB12C> insert into t2 values(null,'xb'); insert into t2 values(null,'xb') * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column
insert into t2(name) values('xb'); insert into t2(name) values('xb2'); insert into t2(name) values('xb3'); commit;
xb@PDB12C> select * from t2;
ID NAME ---------- ---------- 1 xb 2 xb2 3 xb3
xb@PDB12C> update t2 set id=4 where name='xb'; update t2 set id=4 where name='xb' * ERROR at line 1: ORA-32796: cannot update a generated always identity column
由于是使用的序列,那么也可以用到一些序列中的特性,可以指定不同的初始值和累加值
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
drop table t3 purge; create table t3 (id number generated always as identity (start with 2 increment by 2), name varchar2(10));
insert into t3(name) values('xb'); insert into t3(name) values('xb2'); insert into t3(name) values('xb3'); commit;
xb@PDB12C> select * from t3;
ID NAME ---------- ---------- 2 xb 4 xb2 6 xb3
通过USER_TAB_IDENTITY_COLS等视图可以查询到一些标识列的信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
set linesize 300 col table_name for a10 col COLUMN_NAME for a10 col IDENTITY_OPTIONS for a70 select * from USER_TAB_IDENTITY_COLS;
TABLE_NAME COLUMN_NAM GENERATION SEQUENCE_NAME IDENTITY_OPTIONS ---------- ---------- ---------- -------------------- ---------------------------------------------------------------------- T3 ID ALWAYS ISEQ$$_90075 START WITH: 2, INCREMENT BY: 2, MAX_VALUE: 999999999999999999999999999 9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N
T1 ID BY DEFAULT ISEQ$$_90079 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 999999999999999999999999999 9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N
T2 ID ALWAYS ISEQ$$_90073 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 999999999999999999999999999 9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N
DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<DEST_SID>/<PDBNAME1.xml>' connect as <USERNAME>@"<dest-cdb-connect-identifer>";
-or-
DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<DEST_SID>/<PDBNAME1.xml>':
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB12C READ WRITE NO 4 PDB3 READ WRITE NO
sys@ORA12C> select database_role from v$database;
DATABASE_ROLE ---------------- PRIMARY
生成pdb的xml文件
1 2 3 4 5 6 7 8 9 10 11
sys@ORA12C> alter session set container=pdb3;
Session altered.
BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => '/u01/app/oracle/oradata/pdb3.xml'); END; /
PL/SQL procedure successfully completed.
在备库机器上连接到源库的dg broker
1 2 3
DGMGRL> connect sys/oracle@ora12c_dg Connected to "ORA12C_DG" Connected as SYSDBA.
DGMGRL> migrate pluggable database immediate pdb3 to container cdb1 using '/u01/app/oracle/oradata/pdb3.xml'; Username: sys@cdb1 Password: Connected to "cdb1" Connected as SYSDBA.
Beginning migration of pluggable database PDB3. Source multitenant container database is ORA12C_DG. Destination multitenant container database is cdb1.
Connected to "ora12c" Closing pluggable database PDB3 on all instances of multitenant container database ora12c. Pluggable database PDB3 on database ORA12C_DG lags its primary database, ora12c, by 2129 seconds. Continuing with migration of pluggable database PDB3 to multitenant container database cdb1. Stopping Redo Apply services on source multitenant container database ORA12C_DG. Pluggable database description will be written to /u01/app/oracle/oradata/pdb3.xml. Closing pluggable database PDB3 on all instances of multitenant container database ORA12C_DG. Disabling media recovery for pluggable database PDB3. Restarting redo apply services on source multitenant container database ORA12C_DG. Creating pluggable database PDB3 on multitenant container database cdb1. Opening pluggable database PDB3 on all instances of multitenant container database cdb1. Unplugging pluggable database PDB3 from multitenant container database ora12c. Pluggable database description will be written to /tmp/ora_tfils2PLmU.xml. Dropping pluggable database PDB3 from multitenant container database ora12c. Migration of pluggable database PDB3 completed. Succeeded.
-- 检查目标端cdb的pdb状态 sys@CDB1> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB3 READ WRITE NO
-- 日志能看到并没有文件的拷贝动作 Completed: create pluggable database PDB3 using '/u01/app/oracle/oradata/pdb3.xml' nocopy standbys=none tempfile reuse alter pluggable database PDB3 open instances=all PDB3(4):Autotune of undo retention is turned on.
DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<PDBNAME1>/<PDBNAME1.xml>' connect as <USERNAME>@"<dest-cdb-connect-identifer>"; -or- DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<PDBNAME1>/<PDBNAME1.xml>': # This will prompt for user name and password
在Rman中执行duplicate database for standby from active database命令时,只会在备库创建一个单实例,你需要手动设置rac参数包括INSTANCE_NUMBER和INSTANCE_NAME等,在另一个主机上启用第二个实例,然后添加新创建的数据库到OCR中
PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- --------------------------------------------------------------------------- log_archive_dest_1 string
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=BOTH;
# 注意参数大小写敏感: ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,stbrac)' SCOPE=BOTH SID='*'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=stbrac ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbrac' SCOPE=BOTH SID='*';
alter system set log_archive_dest_state_1 = ENABLE SCOPE=BOTH SID='*'; alter system set log_archive_dest_state_2 = ENABLE SCOPE=BOTH SID='*';
# 应该返回EXCLUSIVE show parameter REMOTE_LOGIN_PASSWORDFILE
# FAL = fetch archive log ALTER SYSTEM SET FAL_SERVER='stbrac' SCOPE=BOTH SID='*'; ALTER SYSTEM SET FAL_CLIENT='rac' SCOPE=BOTH SID='*';
# 如果主备库文件目录一致,则不用设置 show parameter DB_FILE_NAME_CONVERT show parameter STANDBY_FILE_MANAGEMENT ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH SID='*';
# 设置ARCn进程最大值 ( default 4): ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8 SCOPE=BOTH SID='*';
主库开启数据库闪回
1 2
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET =2880 scope=BOTH; ALTER DATABASE FLASHBACK ON;
run { allocate channel c1 type disk; allocate channel c2 type disk; allocate auxiliary channel stbc1 type disk; DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE set 'db_unique_name'='stbrac' set instance_name='stbrac1' set instance_number='1' set db_create_online_log_dest_1='+FRA' set db_create_online_log_dest_2='+DATA' set db_recovery_file_dest='+FRA' set audit_file_dest='/u01/app/oracle/admin/stbrac/adump' set core_dump_dest='/u01/app/oracle/admin/stbrac/cdump' nofilenamecheck dorecover; }
set linesize 150 set pagesize 100 select 'alter database drop standby logfile member ''' || member || ''';' from v$logfile where type='STANDBY' and member like '+DATA%';
'ALTERDATABASEDROPSTANDBYLOGFILEMEMBER'''||MEMBER||''';' ------------------------------------------------------------------------------------------------------------------------------------------------------ alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_5.278.1005926909'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_6.277.1005926915'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_7.276.1005926921'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_8.275.1005926925'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_9.274.1005926931'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_10.273.1005926937'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_11.272.1005926943'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_12.271.1005926947'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_13.314.1005926953'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_14.315.1005926959';
-- 确认删除完毕 sys@STBRAC1> SELECT GROUP#, MEMBER FROM V$LOGFILE WHERE TYPE='STANDBY' ORDER BY 1,2;
ALTER SYSTEM SET FAL_SERVER=rac SCOPE=BOTH SID='*'; ALTER SYSTEM SET FAL_CLIENT=stbrac SCOPE=BOTH SID='*'; ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,stbrac)' SID='*'; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=rac ASYNC DB_UNIQUE_NAME=rac VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' SCOPE=BOTH SID='*';
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/STBRAC/PARAMETERFILE/spfilestbrac.ora
修改spfile中的INSTANCE_NAME和INSTANCE_NUMBER参数
1 2 3 4
ALTER SYSTEM SET INSTANCE_NUMBER=1 SCOPE=SPFILE SID='stbrac1'; ALTER SYSTEM SET INSTANCE_NUMBER=2 SCOPE=SPFILE SID='stbrac2'; ALTER SYSTEM SET INSTANCE_NAME='stbrac1' SCOPE=SPFILE SID='stbrac1'; ALTER SYSTEM SET INSTANCE_NAME='stbrac2' SCOPE=SPFILE SID='stbrac2';
alter database recover managed standby database using current logfile disconnect from session;
检查每个节点的dg配置情况
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
select inst_id, database_role, db_unique_name instance, open_mode, protection_mode, protection_level from gv$database;
INST_ID DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL ---------- ---------------- ------------------------------ -------------------- -------------------- -------------------- 1 PHYSICAL STANDBY stbrac READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 2 PHYSICAL STANDBY stbrac READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
select inst_id, database_mode, recovery_mode, protection_mode from gv$archive_dest_status where dest_name ='LOG_ARCHIVE_DEST_1';
INST_ID DATABASE_MODE RECOVERY_MODE PROTECTION_MODE ---------- --------------- ----------------------- -------------------- 2 OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE 1 OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE
检查redo传输服务
1 2 3 4 5 6 7 8 9 10 11
alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss'; select inst_id, sequence#, applied, first_time, next_time from gv$archived_log order by 2,1,4;
-- 主库建表 create table test as select * from dba_tables where 1=2;
set linesize 300 col start_time format a20 col item format a20 select to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, item , sofar, units from v$recovery_progress where item in ('Active Apply Rate', 'Average Apply Rate', 'Redo Applied');
START_TIME ITEM SOFAR UNITS -------------------- -------------------- ---------- -------------------------------- 2019-04-18 17:26:33 Active Apply Rate 1148 KB/sec 2019-04-18 17:26:33 Average Apply Rate 58 KB/sec 2019-04-18 17:26:33 Redo Applied 22 Megabytes
col name for a13 col value for a13 col unit for a30 set lines 132 select name, value, unit, time_computed from v$dataguard_stats where name in ('transport lag', 'apply lag');
NAME VALUE UNIT TIME_COMPUTED ------------- ------------- ------------------------------ ------------------------------ transport lag +00 00:00:00 day(2) to second(0) interval 04/18/2019 17:33:57 apply lag +00 00:00:00 day(2) to second(0) interval 04/18/2019 17:33:57
优化配置
配置主库归档日志删除策略
1 2
rman target / CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
启用备库的闪回
1 2 3 4
alter system set db_flashback_retention_target =2880; alter database recover managed standby database cancel; alter database flashback on; alter database recover managed standby database using current logfile disconnect from session;
配置dg broker
查看系统现有dg broker配置
1 2 3 4 5 6 7
sys@RAC1> show parameter dg_broker
PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- ------------------------------------------------------------------------------------- dg_broker_config_file1 string /u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr1rac.dat dg_broker_config_file2 string /u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr2rac.dat dg_broker_start boolean FALSE
rac环境中dgbroker的配置文件要存放在共享目录上,在ASM上新建一个目录
1 2 3
su - grid ASMCMD>cd +data/rac/ ASMCMD>mkdir DGCONFIG
停掉备库apply
1
alter database recover managed standby database cancel;
修改主库参数
1 2 3 4
alter system set LOG_ARCHIVE_DEST_2='' scope=both; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/RAC/DGCONFIG/dr1rac.dat' SCOPE=spfile sid='*'; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/RAC/DGCONFIG/dr2rac.dat' SCOPE=spfile sid='*'; alter system set dg_broker_start=true scope=both sid='*';
同时也修改备库
1 2 3 4 5 6 7 8
su - grid ASMCMD>cd +data/stbrac/ ASMCMD>mkdir DGCONFIG
alter system set LOG_ARCHIVE_DEST_2='' scope=both; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/STBRAC/DGCONFIG/dr1stbrac.dat' SCOPE=spfile sid='*'; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/STBRAC/DGCONFIG/dr2stbrac.dat' SCOPE=spfile sid='*'; alter system set dg_broker_start=true scope=both sid='*';
DGMGRL> switchover to 'stbrac'; Performing switchover NOW, please wait... Operation requires a connection to database "stbrac" Connecting ... Connected to "stbrac" Connected as SYSDBA. New primary database "stbrac" is opening... Oracle Clusterware is restarting database "rac" ... Connected to "rac" Connected to "rac" Switchover succeeded, new primary is "stbrac" DGMGRL> show configuration;
--重新swithover回来 DGMGRL> switchover to 'rac'; Performing switchover NOW, please wait... Operation requires a connection to database "rac" Connecting ... Connected to "rac" Connected as SYSDBA. New primary database "rac" is opening... Oracle Clusterware is restarting database "stbrac" ... Switchover succeeded, new primary is "rac"
ORA-00210: cannot open the specified control file ORA-00202: control file: '+FRA/STBRAC/CONTROLFILE/current.294.1005908473' ORA-17503: ksfdopn:2 Failed to open file +FRA/STBRAC/CONTROLFILE/current.294.1005908473 ORA-15001: diskgroup "FRA" does not exist or is not mounted ORA-15040: diskgroup is incomplete ORA-205 signalled during: alter database mount...
WARNING: failed to open a disk[/dev/asmdiskd] ORA-15025: could not open disk "/dev/asmdiskd" ORA-27041: unable to open file Linux-x86_64 Error: 13: Permission denied
检查asm的磁盘等信息均正常
1 2 3 4 5 6 7 8 9 10 11
[grid@stbracnode1 ~]$ asmcmd ASMCMD> lsdg State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 512 4096 4194304 51200 16784 0 16784 0 Y CRS/ MOUNTED EXTERN N 512 512 4096 4194304 61440 55336 0 55336 0 N DATA/ MOUNTED EXTERN N 512 512 4096 4194304 20480 17404 0 17404 0 N FRA/
[root@racnode1 ~]# /u01/app/12.2.0/grid/root.sh Performing root user operation.
The following environment variables are set as: ORACLE_OWNER= grid ORACLE_HOME= /u01/app/12.2.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]: Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root script. Now product-specific root actions will be performed. Relinking oracle with rac_on option Using configuration parameter file: /u01/app/12.2.0/grid/crs/install/crsconfig_params The log of current session can be found at: /u01/app/grid/crsdata/racnode1/crsconfig/rootcrs_racnode1_2019-04-11_10-21-10AM.log 2019/04/11 10:21:12 CLSRSC-594: Executing installation step 1 of 19: 'SetupTFA'. 2019/04/11 10:21:12 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector. 2019/04/11 10:21:46 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector. 2019/04/11 10:21:46 CLSRSC-594: Executing installation step 2 of 19: 'ValidateEnv'. 2019/04/11 10:21:52 CLSRSC-363: User ignored prerequisites during installation 2019/04/11 10:21:52 CLSRSC-594: Executing installation step 3 of 19: 'CheckFirstNode'. 2019/04/11 10:21:54 CLSRSC-594: Executing installation step 4 of 19: 'GenSiteGUIDs'. 2019/04/11 10:21:55 CLSRSC-594: Executing installation step 5 of 19: 'SaveParamFile'. 2019/04/11 10:22:08 CLSRSC-594: Executing installation step 6 of 19: 'SetupOSD'. 2019/04/11 10:22:09 CLSRSC-594: Executing installation step 7 of 19: 'CheckCRSConfig'. 2019/04/11 10:22:09 CLSRSC-594: Executing installation step 8 of 19: 'SetupLocalGPNP'. 2019/04/11 10:23:00 CLSRSC-594: Executing installation step 9 of 19: 'ConfigOLR'. 2019/04/11 10:23:12 CLSRSC-594: Executing installation step 10 of 19: 'ConfigCHMOS'. 2019/04/11 10:23:12 CLSRSC-594: Executing installation step 11 of 19: 'CreateOHASD'. 2019/04/11 10:23:18 CLSRSC-594: Executing installation step 12 of 19: 'ConfigOHASD'. 2019/04/11 10:23:34 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service' 2019/04/11 10:23:55 CLSRSC-594: Executing installation step 13 of 19: 'InstallAFD'. 2019/04/11 10:24:02 CLSRSC-594: Executing installation step 14 of 19: 'InstallACFS'. CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1' CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed CRS-4133: Oracle High Availability Services has been stopped. CRS-4123: Oracle High Availability Services has been started. 2019/04/11 10:24:24 CLSRSC-594: Executing installation step 15 of 19: 'InstallKA'. 2019/04/11 10:24:36 CLSRSC-594: Executing installation step 16 of 19: 'InitConfig'. CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1' CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed CRS-4133: Oracle High Availability Services has been stopped. CRS-4123: Oracle High Availability Services has been started. CRS-2672: Attempting to start 'ora.evmd' on 'racnode1' CRS-2672: Attempting to start 'ora.mdnsd' on 'racnode1' CRS-2676: Start of 'ora.mdnsd' on 'racnode1' succeeded CRS-2676: Start of 'ora.evmd' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.gpnpd' on 'racnode1' CRS-2676: Start of 'ora.gpnpd' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.cssdmonitor' on 'racnode1' CRS-2672: Attempting to start 'ora.gipcd' on 'racnode1' CRS-2676: Start of 'ora.cssdmonitor' on 'racnode1' succeeded CRS-2676: Start of 'ora.gipcd' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.cssd' on 'racnode1' CRS-2672: Attempting to start 'ora.diskmon' on 'racnode1' CRS-2676: Start of 'ora.diskmon' on 'racnode1' succeeded CRS-2676: Start of 'ora.cssd' on 'racnode1' succeeded
ASM failed to start. Check /u01/app/grid/cfgtoollogs/asmca/asmca-190411AM102519.log for details.
2019/04/11 10:25:41 CLSRSC-184: Configuration of ASM failed 2019/04/11 10:25:47 CLSRSC-258: Failed to configure and start ASM Died at /u01/app/12.2.0/grid/crs/install/crsinstall.pm line 2091. The command '/u01/app/12.2.0/grid/perl/bin/perl -I/u01/app/12.2.0/grid/perl/lib -I/u01/app/12.2.0/grid/crs/install /u01/app/12.2.0/grid/crs/install/rootcrs.pl ' execution failed
查看日志有报错
1 2 3 4 5
[main] [ 2019-04-11 10:25:29.659 CST ] [OracleHome.getVersion:1152] Current version from sqlplus: 12.2.0.1.0 [main] [ 2019-04-11 10:25:29.659 CST ] [UsmcaLogger.logInfo:156] Role SYSASM [main] [ 2019-04-11 10:25:29.659 CST ] [UsmcaLogger.logInfo:156] OS Auth true [main] [ 2019-04-11 10:25:40.692 CST ] [SQLEngine.done:2314] Done called [main] [ 2019-04-11 10:25:40.695 CST ] [USMInstance.configureLocalASM:3367] ORA-00845: MEMORY_TARGET not supported on this system
[root@racnode1 trace]# /u01/app/12.2.0/grid/crs/install/rootcrs.sh -deconfig -force Using configuration parameter file: /u01/app/12.2.0/grid/crs/install/crsconfig_params The log of current session can be found at: /u01/app/grid/crsdata/racnode1/crsconfig/crsdeconfig_racnode1_2019-04-11_11-06-32AM.log PRCR-1070 : Failed to check if resource ora.net1.network is registered CRS-0184 : Cannot communicate with the CRS daemon. PRCR-1070 : Failed to check if resource ora.helper is registered CRS-0184 : Cannot communicate with the CRS daemon. PRCR-1070 : Failed to check if resource ora.ons is registered CRS-0184 : Cannot communicate with the CRS daemon.
2019/04/11 11:06:45 CLSRSC-180: An error occurred while executing the command '/u01/app/12.2.0/grid/bin/srvctl config nodeapps' CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1' CRS-2673: Attempting to stop 'ora.ctssd' on 'racnode1' CRS-2673: Attempting to stop 'ora.evmd' on 'racnode1' CRS-2673: Attempting to stop 'ora.mdnsd' on 'racnode1' CRS-2673: Attempting to stop 'ora.gpnpd' on 'racnode1' CRS-2679: Attempting to clean 'ora.asm' on 'racnode1' CRS-2677: Stop of 'ora.evmd' on 'racnode1' succeeded CRS-2677: Stop of 'ora.ctssd' on 'racnode1' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'racnode1' CRS-2677: Stop of 'ora.mdnsd' on 'racnode1' succeeded CRS-2677: Stop of 'ora.cssd' on 'racnode1' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'racnode1' CRS-2677: Stop of 'ora.gpnpd' on 'racnode1' succeeded CRS-2681: Clean of 'ora.asm' on 'racnode1' succeeded CRS-2677: Stop of 'ora.gipcd' on 'racnode1' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed CRS-4133: Oracle High Availability Services has been stopped. 2019/04/11 11:07:16 CLSRSC-4006: Removing Oracle Trace File Analyzer (TFA) Collector. 2019/04/11 11:09:01 CLSRSC-4007: Successfully removed Oracle Trace File Analyzer (TFA) Collector. 2019/04/11 11:09:02 CLSRSC-336: Successfully deconfigured Oracle Clusterware stack on this node
[root@racnode1 trace]# /u01/app/12.2.0/grid/root.sh Performing root user operation.
The following environment variables are set as: ORACLE_OWNER= grid ORACLE_HOME= /u01/app/12.2.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of "dbhome" have not changed. No need to overwrite. The contents of "oraenv" have not changed. No need to overwrite. The contents of "coraenv" have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root script. Now product-specific root actions will be performed. Relinking oracle with rac_on option Using configuration parameter file: /u01/app/12.2.0/grid/crs/install/crsconfig_params The log of current session can be found at: /u01/app/grid/crsdata/racnode1/crsconfig/rootcrs_racnode1_2019-04-11_11-09-55AM.log 2019/04/11 11:09:58 CLSRSC-594: Executing installation step 1 of 19: 'SetupTFA'. 2019/04/11 11:09:58 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector. 2019/04/11 11:10:29 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector. 2019/04/11 11:10:29 CLSRSC-594: Executing installation step 2 of 19: 'ValidateEnv'. 2019/04/11 11:10:34 CLSRSC-363: User ignored prerequisites during installation 2019/04/11 11:10:34 CLSRSC-594: Executing installation step 3 of 19: 'CheckFirstNode'. 2019/04/11 11:10:37 CLSRSC-594: Executing installation step 4 of 19: 'GenSiteGUIDs'. 2019/04/11 11:10:38 CLSRSC-594: Executing installation step 5 of 19: 'SaveParamFile'. 2019/04/11 11:10:46 CLSRSC-594: Executing installation step 6 of 19: 'SetupOSD'. 2019/04/11 11:10:46 CLSRSC-594: Executing installation step 7 of 19: 'CheckCRSConfig'. 2019/04/11 11:10:46 CLSRSC-594: Executing installation step 8 of 19: 'SetupLocalGPNP'. 2019/04/11 11:11:24 CLSRSC-594: Executing installation step 9 of 19: 'ConfigOLR'. 2019/04/11 11:11:33 CLSRSC-594: Executing installation step 10 of 19: 'ConfigCHMOS'. 2019/04/11 11:11:33 CLSRSC-594: Executing installation step 11 of 19: 'CreateOHASD'. 2019/04/11 11:11:39 CLSRSC-594: Executing installation step 12 of 19: 'ConfigOHASD'. 2019/04/11 11:11:55 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service' 2019/04/11 11:12:15 CLSRSC-594: Executing installation step 13 of 19: 'InstallAFD'. 2019/04/11 11:12:20 CLSRSC-594: Executing installation step 14 of 19: 'InstallACFS'. CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1' CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed CRS-4133: Oracle High Availability Services has been stopped. CRS-4123: Oracle High Availability Services has been started. 2019/04/11 11:12:42 CLSRSC-594: Executing installation step 15 of 19: 'InstallKA'. 2019/04/11 11:12:48 CLSRSC-594: Executing installation step 16 of 19: 'InitConfig'. CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1' CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed CRS-4133: Oracle High Availability Services has been stopped. CRS-4123: Oracle High Availability Services has been started. CRS-2672: Attempting to start 'ora.evmd' on 'racnode1' CRS-2672: Attempting to start 'ora.mdnsd' on 'racnode1' CRS-2676: Start of 'ora.evmd' on 'racnode1' succeeded CRS-2676: Start of 'ora.mdnsd' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.gpnpd' on 'racnode1' CRS-2676: Start of 'ora.gpnpd' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.cssdmonitor' on 'racnode1' CRS-2672: Attempting to start 'ora.gipcd' on 'racnode1' CRS-2676: Start of 'ora.cssdmonitor' on 'racnode1' succeeded CRS-2676: Start of 'ora.gipcd' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.cssd' on 'racnode1' CRS-2672: Attempting to start 'ora.diskmon' on 'racnode1' CRS-2676: Start of 'ora.diskmon' on 'racnode1' succeeded CRS-2676: Start of 'ora.cssd' on 'racnode1' succeeded
Disk groups created successfully. Check /u01/app/grid/cfgtoollogs/asmca/asmca-190411AM111328.log for details.
2019/04/11 11:20:23 CLSRSC-482: Running command: '/u01/app/12.2.0/grid/bin/ocrconfig -upgrade grid oinstall' CRS-2672: Attempting to start 'ora.crf' on 'racnode1' CRS-2672: Attempting to start 'ora.storage' on 'racnode1' CRS-2676: Start of 'ora.storage' on 'racnode1' succeeded CRS-2676: Start of 'ora.crf' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.crsd' on 'racnode1' CRS-2676: Start of 'ora.crsd' on 'racnode1' succeeded CRS-4256: Updating the profile Successful addition of voting disk d5d63504c2084fd1bf20c9109c45188c. Successfully replaced voting disk group with +DATA. CRS-4256: Updating the profile CRS-4266: Voting file(s) successfully replaced ## STATE File Universal Id File Name Disk group -- ----- ----------------- --------- --------- 1. ONLINE d5d63504c2084fd1bf20c9109c45188c (/dev/asmdiskf) [DATA] Located 1 voting disk(s). CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1' CRS-2673: Attempting to stop 'ora.crsd' on 'racnode1' CRS-2677: Stop of 'ora.crsd' on 'racnode1' succeeded CRS-2673: Attempting to stop 'ora.storage' on 'racnode1' CRS-2673: Attempting to stop 'ora.crf' on 'racnode1' CRS-2673: Attempting to stop 'ora.gpnpd' on 'racnode1' CRS-2673: Attempting to stop 'ora.mdnsd' on 'racnode1' CRS-2677: Stop of 'ora.crf' on 'racnode1' succeeded CRS-2677: Stop of 'ora.gpnpd' on 'racnode1' succeeded CRS-2677: Stop of 'ora.storage' on 'racnode1' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'racnode1' CRS-2677: Stop of 'ora.mdnsd' on 'racnode1' succeeded CRS-2677: Stop of 'ora.asm' on 'racnode1' succeeded CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'racnode1' CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'racnode1' succeeded CRS-2673: Attempting to stop 'ora.ctssd' on 'racnode1' CRS-2673: Attempting to stop 'ora.evmd' on 'racnode1' CRS-2677: Stop of 'ora.evmd' on 'racnode1' succeeded CRS-2677: Stop of 'ora.ctssd' on 'racnode1' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'racnode1' CRS-2677: Stop of 'ora.cssd' on 'racnode1' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'racnode1' CRS-2677: Stop of 'ora.gipcd' on 'racnode1' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed CRS-4133: Oracle High Availability Services has been stopped. 2019/04/11 11:26:42 CLSRSC-594: Executing installation step 17 of 19: 'StartCluster'. CRS-4123: Starting Oracle High Availability Services-managed resources CRS-2672: Attempting to start 'ora.mdnsd' on 'racnode1' CRS-2672: Attempting to start 'ora.evmd' on 'racnode1' CRS-2676: Start of 'ora.evmd' on 'racnode1' succeeded CRS-2676: Start of 'ora.mdnsd' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.gpnpd' on 'racnode1' CRS-2676: Start of 'ora.gpnpd' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.gipcd' on 'racnode1' CRS-2676: Start of 'ora.gipcd' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.cssdmonitor' on 'racnode1' CRS-2676: Start of 'ora.cssdmonitor' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.cssd' on 'racnode1' CRS-2672: Attempting to start 'ora.diskmon' on 'racnode1' CRS-2676: Start of 'ora.diskmon' on 'racnode1' succeeded CRS-2676: Start of 'ora.cssd' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'racnode1' CRS-2672: Attempting to start 'ora.ctssd' on 'racnode1' CRS-2676: Start of 'ora.ctssd' on 'racnode1' succeeded CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.asm' on 'racnode1' CRS-2676: Start of 'ora.asm' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.storage' on 'racnode1' CRS-2676: Start of 'ora.storage' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.crf' on 'racnode1' CRS-2676: Start of 'ora.crf' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.crsd' on 'racnode1' CRS-2676: Start of 'ora.crsd' on 'racnode1' succeeded CRS-6023: Starting Oracle Cluster Ready Services-managed resources CRS-6017: Processing resource auto-start for servers: racnode1 CRS-6016: Resource auto-start has completed for server racnode1 CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources CRS-4123: Oracle High Availability Services has been started. 2019/04/11 11:31:30 CLSRSC-343: Successfully started Oracle Clusterware stack 2019/04/11 11:31:33 CLSRSC-594: Executing installation step 18 of 19: 'ConfigNode'. CRS-2672: Attempting to start 'ora.ASMNET1LSNR_ASM.lsnr' on 'racnode1' CRS-2676: Start of 'ora.ASMNET1LSNR_ASM.lsnr' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.asm' on 'racnode1' CRS-2676: Start of 'ora.asm' on 'racnode1' succeeded CRS-2672: Attempting to start 'ora.DATA.dg' on 'racnode1' CRS-2676: Start of 'ora.DATA.dg' on 'racnode1' succeeded 2019/04/11 11:43:18 CLSRSC-594: Executing installation step 19 of 19: 'PostConfig'. 2019/04/11 11:49:21 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded
specification does not match any backup in the repository
-- 当前并没有可用备份,对CDB和它的PDBs做一个全备
RMAN> backup database plus archivelog;
Starting backup at 26-MAR-19 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=3 RECID=1 STAMP=1002984384 input archived log thread=1 sequence=4 RECID=2 STAMP=1002984802 input archived log thread=1 sequence=5 RECID=3 STAMP=1003929462 channel ORA_DISK_1: starting piece 1 at 26-MAR-19 channel ORA_DISK_1: finished piece 1 at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/02ttdfrm_1_1 tag=TAG20190326T131742 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 26-MAR-19
Starting backup at 26-MAR-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ora12c/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ora12c/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ora12c/undotbs01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ora12c/users01.dbf channel ORA_DISK_1: starting piece 1 at 26-MAR-19 channel ORA_DISK_1: finished piece 1 at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 tag=TAG20190326T131745 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00010 name=/u01/app/oracle/oradata/ora12c/pdb12c/sysaux01.dbf input datafile file number=00009 name=/u01/app/oracle/oradata/ora12c/pdb12c/system01.dbf input datafile file number=00011 name=/u01/app/oracle/oradata/ora12c/pdb12c/undotbs01.dbf input datafile file number=00013 name=/u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g8kd5pwo_.dbf input datafile file number=00015 name=/u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs2_g8pl709m_.dbf input datafile file number=00012 name=/u01/app/oracle/oradata/ora12c/pdb12c/users01.dbf channel ORA_DISK_1: starting piece 1 at 26-MAR-19 channel ORA_DISK_1: finished piece 1 at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00006 name=/u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ora12c/pdbseed/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 26-MAR-19 channel ORA_DISK_1: finished piece 1 at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/05ttdfsc_1_1 tag=TAG20190326T131745 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 26-MAR-19
Starting backup at 26-MAR-19 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=6 RECID=4 STAMP=1003929491 channel ORA_DISK_1: starting piece 1 at 26-MAR-19 channel ORA_DISK_1: finished piece 1 at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/06ttdfsj_1_1 tag=TAG20190326T131811 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 26-MAR-19
Starting Control File and SPFILE Autobackup at 26-MAR-19 piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/c-380590835-20190326-00 comment=NONE Finished Control File and SPFILE Autobackup at 26-MAR-19
构建测试表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
drop table t1 purge;
create table t1 (id number); insert into t1 values(1); commit;
-- 检查当前scn xb@PDB12C> select current_scn from v$database;
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script: { # set requested point in time set until scn 1612170; # set destinations for recovery set and auxiliary set datafiles set newname for clone datafile 1 to new; set newname for clone datafile 9 to new; set newname for clone datafile 4 to new; set newname for clone datafile 11 to new; set newname for clone datafile 3 to new; set newname for clone datafile 10 to new; set newname for clone tempfile 1 to new; set newname for clone tempfile 3 to new; # switch all tempfiles switch clone tempfile all; # restore the tablespaces in the recovery set and the auxiliary set restore clone datafile 1, 9, 4, 11, 3, 10;
renamed tempfile 1 to /u01/aux/ORA12C/datafile/o1_mf_temp_%u_.tmp in control file renamed tempfile 3 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 26-MAR-19 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/aux/ORA12C/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/aux/ORA12C/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux/ORA12C/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 tag=TAG20190326T131745 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 26-MAR-19
contents of Memory Script: { # set requested point in time set until scn 1612170; # online the datafiles restored or switched sql clone "alter database datafile 1 online"; sql clone 'PDB12C' "alter database datafile 9 online"; sql clone "alter database datafile 4 online"; sql clone 'PDB12C' "alter database datafile 11 online"; sql clone "alter database datafile 3 online"; sql clone 'PDB12C' "alter database datafile 10 online"; # recover and open database read only recover clone database tablespace "SYSTEM", "PDB12C":"SYSTEM", "UNDOTBS1", "PDB12C":"UNDOTBS1", "SYSAUX", "PDB12C":"SYSAUX"; sql clone 'alter database open read only'; } executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
Starting recover at 26-MAR-19 using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /u01/arch/1_6_1002791605.dbf archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_1002791605.dbf archived log file name=/u01/arch/1_6_1002791605.dbf thread=1 sequence=6 archived log file name=/u01/arch/1_7_1002791605.dbf thread=1 sequence=7 media recovery complete, elapsed time: 00:00:00 Finished recover at 26-MAR-19
sql statement: alter database open read only
contents of Memory Script: { sql clone 'alter pluggable database PDB12C open read only'; } executing Memory Script
sql statement: alter pluggable database PDB12C open read only
contents of Memory Script: { sql clone "create spfile from memory"; shutdown clone immediate; startup clone nomount; sql clone "alter system set control_files = ''/u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl'' comment= ''RMAN set'' scope=spfile"; shutdown clone immediate; startup clone nomount; # mount database sql clone 'alter database mount clone database'; } executing Memory Script
sql statement: create spfile from memory
database closed database dismounted Oracle instance shut down
connected to auxiliary database (not started) Oracle instance started
sql statement: alter database mount clone database
contents of Memory Script: { # set requested point in time set until scn 1612170; # set destinations for recovery set and auxiliary set datafiles set newname for datafile 13 to new; # restore the tablespaces in the recovery set and the auxiliary set restore clone datafile 13;
contents of Memory Script: { # set requested point in time set until scn 1612170; # online the datafiles restored or switched sql clone 'PDB12C' "alter database datafile 13 online"; # recover and open resetlogs recover clone database tablespace "PDB12C":"TBS1", "SYSTEM", "PDB12C":"SYSTEM", "UNDOTBS1", "PDB12C":"UNDOTBS1", "SYSAUX", "PDB12C":"SYSAUX" delete archivelog; alter clone database open resetlogs; } executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 13 online
Starting recover at 26-MAR-19 using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /u01/arch/1_6_1002791605.dbf archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_1002791605.dbf archived log file name=/u01/arch/1_6_1002791605.dbf thread=1 sequence=6 archived log file name=/u01/arch/1_7_1002791605.dbf thread=1 sequence=7 media recovery complete, elapsed time: 00:00:00 Finished recover at 26-MAR-19
create table t1 ( id number, col1 varchar2(100), col2 varchar2(100), col3 generated always as (id+100) virtual ) partition by range (id) ( partition p1 values less than (200), partition p2 values less than (400) );
xb@PDB12C> @desc t1 Name Null? Type ------------------------------- -------- ---------------------------- 1 ID NUMBER >>>>====已经看不到col1和col2两个字段 2 COL3 NUMBER
col column_name for a40 col data_type for a20 col data_length for 999999999999 col data_default for a20 col virtual_column for a20 SELECT column_name, data_type, data_length, data_default, virtual_column FROM user_tab_cols WHERE table_name = 'T1';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT VIRTUAL_COLUMN ---------------------------------------- -------------------- ------------- -------------------- -------------------- ID NUMBER 22 NO SYS_C00002_19031410:18:00$ VARCHAR2 100 NO COL2 VARCHAR2 100 NO COL3 NUMBER 22 "ID"+100 YES
drop table t2 purge; create table t2 as select * from t1 where 1=2; create index idx_t2 on t2(id);
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT VIRTUAL_COLUMN ---------------------------------------- -------------------- ------------- -------------------- -------------------- ID NUMBER 22 NO COL3 NUMBER 22 NO >>>>====新表字段并非虚拟列
insert into t2 select level from dual connect by rownum>=200;
commit;
alter table t1 exchange partition p1 with table t2 without validation update global indexes;
ERROR at line 3: ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns
create table t2 for exchange with table t1; create index idx_t2 on t2(id);
col column_name for a40 col data_type for a20 col data_length for 999999999999 col data_default for a20 col virtual_column for a20 SELECT column_name, data_type, data_length, data_default, virtual_column FROM user_tab_cols WHERE table_name = 'T2';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT VIRTUAL_COLUMN ---------------------------------------- -------------------- ------------- -------------------- -------------------- COL2 VARCHAR2 100 NO SYS_C00002_19031410:18:00$ VARCHAR2 100 NO ID NUMBER 22 NO COL3 NUMBER 22 "ID"+100 YES
TABLE : T OWNER : SYS SOURCE A : Statistics as of 18-DEC-18 11.03.39.000000 AM +08:00 SOURCE B : Statistics as of 19-DEC-18 11.03.39.000000 AM +08:00 PCTTHRESHOLD : 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sys@ORA12C> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T'); BEGIN DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T'); END;
* ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 36873 ORA-06512: at "SYS.DBMS_STATS", line 36507 ORA-06512: at "SYS.DBMS_STATS", line 8582 ORA-06512: at "SYS.DBMS_STATS", line 9461 ORA-06512: at "SYS.DBMS_STATS", line 35836 ORA-06512: at "SYS.DBMS_STATS", line 36716 ORA-06512: at line 1
具体参考前面所述文档的Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades部分
部分内容在后面的DBUA环节也可以勾选处理
搜集统计信息以减少停机时间
1
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
确认物化视图全部刷新完成
1 2
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
确认没有处于备份模式的文件
1
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
确认没有文件需要恢复
1
SELECT * FROM v$recover_file;
处理未完成的分布式事物
1 2 3 4 5
SELECT * FROM dba_2pc_pending;
SELECT local_tran_id FROM dba_2pc_pending; EXECUTE dbms_transaction.purge_lost_db_entry(''); commit;
确保主备同步
1 2 3
SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1) FROM v$parameter WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';
清除回收站
1
PURGE DBA_RECYCLEBIN
手动删除DB Control
主要目的是为了减少停机时间
1 2 3
emctl stop dbconsole
@emremove.sql
emremove.sql执行完毕后,需要手动删除ORACLE_HOME/HOSTNAME_SID and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID两个目录