课程第32/33次（2017-05-20星期六上下午）

botang · 发表于 2017-5-22 10:35:16

上完1Z0-053第7章
开始1Z0-053第20章克隆数据库（auxiliary channel）
1Z0-052共19章（上完12章），1Z0-053共21章（上完11章）和1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的23章

select * from v$log;
select * from v$datafile;
select * from v$controlfile;
select * from v$tempfile;
alter database drop logfile group 1;
alter database add logfile group 1 size 100M;
alter database drop logfile group 3;
alter database add logfile group 3 size 100M;
alter system switch logfile
alter system checkpoint
RMAN> run {
2> set newname for database to '+DATA';
3> restore database;
4> switch datafile all;
5> }

复制代码

asm2filesystem.rcv：

run {
allocate channel c1 device type disk format '/u01/app/oracle/oradata/orcl/system01.dbf';
backup as copy datafile '+DATA/ORCL/DATAFILE/SYSTEM.256.816169553';
release channel c1;
allocate channel c2 device type disk format '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
backup as copy datafile '+DATA/ORCL/DATAFILE/SYSAUX.257.816169553';
release channel c2;
allocate channel c3 device type disk format '/u01/app/oracle/oradata/orcl/undotbs1.dbf';
backup as copy datafile '+DATA/ORCL/DATAFILE/UNDOTBS1.258.816169553';
release channel c3;
allocate channel c4 device type disk format '/u01/app/oracle/oradata/orcl/users01.dbf';
backup as copy datafile '+DATA/ORCL/DATAFILE/USERS.259.816169553';
release channel c4;
allocate channel c5 device type disk format '/u01/app/oracle/oradata/orcl/example01.dbf';
backup as copy datafile '+DATA/ORCL/DATAFILE/EXAMPLE.265.816169651';
release channel c5;
}

复制代码

control8b.sql：

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+DATA/orcl/onlinelog/group_1.261.816169635',
'+FRA/orcl/onlinelog/group_1.257.816169637'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'+DATA/orcl/onlinelog/group_2.262.816169639',
'+FRA/orcl/onlinelog/group_2.258.816169639'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'+DATA/orcl/onlinelog/group_3.263.816169641',
'+FRA/orcl/onlinelog/group_3.259.816169641'
) SIZE 50M BLOCKSIZE 512
DATAFILE
'+DATA/orcl/datafile/SYSTEM.256.816169553',
'+DATA/orcl/datafile/SYSAUX.257.816169553',
'+DATA/orcl/datafile/UNDOTBS1.258.816169553',
'+DATA/orcl/datafile/EXAMPLE.265.816169651'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 1 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','''SBT_TAPE''');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','1 DEVICE TYPE ''SBT_TAPE'' PARMS ''ENV=(OB_MEDIA_FAMILY=station2)''');
-- Configure RMAN configuration record 7
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','2 DEVICE TYPE ''SBT_TAPE'' PARMS ''ENV=(OB_MEDIA_FAMILY=station2)''');
-- Configure RMAN configuration record 8
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 9
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DATAFILE BACKUP COPIES FOR DEVICE TYPE','''SBT_TAPE'' TO 1');
-- Configure RMAN configuration record 10
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ENCRYPTION FOR DATABASE','ON');
-- Configure RMAN configuration record 11
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('COMPRESSION ALGORITHM','''LOW'' AS OF RELEASE ''DEFAULT'' OPTIMIZE FOR LOAD TRUE');

复制代码

control8c.sql：

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 100M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 100M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 100M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs1.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 1 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','''SBT_TAPE''');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','1 DEVICE TYPE ''SBT_TAPE'' PARMS ''ENV=(OB_MEDIA_FAMILY=station90)''');
-- Configure RMAN configuration record 7
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','2 DEVICE TYPE ''SBT_TAPE'' PARMS ''ENV=(OB_MEDIA_FAMILY=station90)''');
-- Configure RMAN configuration record 8
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 9
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DATAFILE BACKUP COPIES FOR DEVICE TYPE','''SBT_TAPE'' TO 1');
-- Configure RMAN configuration record 10
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ENCRYPTION FOR DATABASE','OFF');
-- Configure RMAN configuration record 11
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('COMPRESSION ALGORITHM','''LOW'' AS OF RELEASE ''DEFAULT'' OPTIMIZE FOR LOAD TRUE');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '+FRA';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' REUSE;
-- End of tempfile additions.
--

复制代码

dbtest.rcv：

run {
duplicate target database to dbtest
from active database
nofilenamecheck
spfile
set
control_files='+DATA','+FRA'
set
db_file_name_convert='+DATA/orcl/','+DATA/dbtest'
set
log_file_name_convert='+DATA/orcl','+DATA/dbtest','+FRA/orcl','+FRA/dbtest'
set
audit_file_dest='/u01/app/oracle/admin/dbtest/adump';
}

复制代码

dg11g.rcv：

run{
duplicate target database to db11g
from active database
nofilenamecheck
spfile
set
control_files='/u01/app/oracle/oradata/db11g/control01.ctl','/u01/app/oracle/oradata/db11g/control02.ctl','/u01/app/oracle/oradata/db11g/control03.ctl'
set
db_file_name_convert='+DATA/orcl/datafile/example.265.832197353','/u01/app/oracle/oradata/db11g/example01.dbf','+DATA/orcl/datafile/users.259.832197065','/u01/app/oracle/oradata/db11g/users01.dbf','+DATA/orcl/datafile/undotbs1.258.832197065','/u01/app/oracle/oradata/db11g/undotbs01.dbf','+DATA/orcl/datafile/sysaux.257.832197065','/u01/app/oracle/oradata/db11g/sysaux01.dbf','+DATA/orcl/datafile/system.256.832197063','/u01/app/oracle/oradata/db11g/system01.dbf'
set
log_file_name_convert='+DATA/orcl/onlinelog/group_1.261.832197287','/u01/app/oracle/oradata/db11g/redo01a.log','+FRA/orcl/onlinelog/group_1.257.832197291','/u01/app/oracle/oradata/db11g/redo01b.log','+DATA/orcl/onlinelog/group_3.263.832197301','/u01/app/oracle/oradata/db11g/redo03a.log','+FRA/orcl/onlinelog/group_3.259.832197305','/u01/app/oracle/oradata/db11g/redo03b.log','+DATA/orcl/onlinelog/group_2.262.832197293','/u01/app/oracle/oradata/db11g/redo02a.log','+FRA/orcl/onlinelog/group_2.258.832197299','/u01/app/oracle/oradata/db11g/redo02b.log';
set
audit_file_dest='/u01/app/oracle/admin/db11g/adump'
set
db_create_file_dest=''
set
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
}

复制代码