Мой блог

четверг, 8 октября 2009 г.

Резервное копирование баз данных RMAN

C:\Users\angor>rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jun 23 23:10:15 2018

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

connected to target database: TESTDB (DBID=2762894270)

RMAN> RUN {
2> ALLOCATE CHANNEL d1 TYPE DISK;
3> ALLOCATE CHANNEL d2 TYPE DISK;
4> ALLOCATE CHANNEL d3 TYPE DISK;
5> ALLOCATE CHANNEL d4 TYPE DISK;
6>
7> BACKUP
8>    FULL
9>    TAG Daily_Full_Backup
10>    DATABASE FILESPERSET=1;
11>
12> BACKUP
13>    CURRENT CONTROLFILE;
14>
15> SQL 'alter system archive log current';
16>
17> BACKUP
18>    ARCHIVELOG ALL
19> FORMAT 'c:\backups\testdb\arch\%d_%t_%s_%p.arch';
20>
21> DELETE NOPROMPT OBSOLETE;
22> CROSSCHECK BACKUP;
23> CROSSCHECK ARCHIVELOG ALL;
24> DELETE NOPROMPT EXPIRED BACKUP;
25> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
26> }

using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=622 device type=DISK

allocated channel: d2
channel d2: SID=623 device type=DISK

allocated channel: d3
channel d3: SID=746 device type=DISK

allocated channel: d4
channel d4: SID=868 device type=DISK

Starting backup at 23-JUN-18
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00003 name=C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_UNDOTBS1_FLW93Q12_.DBF
channel d1: starting piece 1 at 23-JUN-18
channel d2: starting full datafile backup set
channel d2: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSTEM_FLW91VTY_.DBF
channel d2: starting piece 1 at 23-JUN-18
channel d3: starting full datafile backup set
channel d3: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSAUX_FLW931TG_.DBF
channel d3: starting piece 1 at 23-JUN-18
channel d4: starting full datafile backup set
channel d4: specifying datafile(s) in backup set
input datafile file number=00004 name=C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_USERS_FLW94PXT_.DBF
channel d4: starting piece 1 at 23-JUN-18
channel d1: finished piece 1 at 23-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXBCLFP_.BKP tag=DAILY_FULL_BACKUP comment=NONE
channel d1: backup set complete, elapsed time: 00:00:09
channel d4: finished piece 1 at 23-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXBCRRP_.BKP tag=DAILY_FULL_BACKUP comment=NONE
channel d4: backup set complete, elapsed time: 00:00:28
channel d2: finished piece 1 at 23-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXBCMBW_.BKP tag=DAILY_FULL_BACKUP comment=NONE
channel d2: backup set complete, elapsed time: 00:01:29
channel d3: finished piece 1 at 23-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXBCQMS_.BKP tag=DAILY_FULL_BACKUP comment=NONE
channel d3: backup set complete, elapsed time: 00:01:28
Finished backup at 23-JUN-18

Starting backup at 23-JUN-18
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
channel d1: starting piece 1 at 23-JUN-18
channel d1: finished piece 1 at 23-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NCNNF_TAG20180623T231301_FLXBGGCC_.BKP tag=TAG20180623T231301 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-JUN-18

Starting Control File and SPFILE Autobackup at 23-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_23\O1_MF_S_979600385_FLXBGLOJ_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 23-JUN-18

sql statement: alter system archive log current

Starting backup at 23-JUN-18
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=23 STAMP=979600059
channel d1: starting piece 1 at 23-JUN-18
channel d2: starting archived log backup set
channel d2: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=25 STAMP=979600389
channel d2: starting piece 1 at 23-JUN-18
channel d3: starting archived log backup set
channel d3: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=24 STAMP=979600060
channel d3: starting piece 1 at 23-JUN-18
channel d4: starting archived log backup set
channel d4: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=26 STAMP=979600390
channel d4: starting piece 1 at 23-JUN-18
channel d1: finished piece 1 at 23-JUN-18
piece handle=C:\BACKUPS\TESTDB\ARCH\TESTDB_979600390_100_1.ARCH tag=TAG20180623T231310 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
channel d2: finished piece 1 at 23-JUN-18
piece handle=C:\BACKUPS\TESTDB\ARCH\TESTDB_979600390_101_1.ARCH tag=TAG20180623T231310 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:02
channel d3: finished piece 1 at 23-JUN-18
piece handle=C:\BACKUPS\TESTDB\ARCH\TESTDB_979600392_102_1.ARCH tag=TAG20180623T231310 comment=NONE
channel d3: backup set complete, elapsed time: 00:00:00
channel d4: finished piece 1 at 23-JUN-18
piece handle=C:\BACKUPS\TESTDB\ARCH\TESTDB_979600393_103_1.ARCH tag=TAG20180623T231310 comment=NONE
channel d4: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JUN-18

Starting Control File and SPFILE Autobackup at 23-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_23\O1_MF_S_979600394_FLXBGVTF_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 23-JUN-18

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           23     23-JUN-18
Backup Piece       23     23-JUN-18          C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXB0J4Q_.BKP
Backup Set           26     23-JUN-18
Backup Piece       26     23-JUN-18          C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXB0JW1_.BKP
Backup Set           25     23-JUN-18
Backup Piece       25     23-JUN-18          C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXB0RGD_.BKP
Backup Set           24     23-JUN-18
Backup Piece       24     23-JUN-18          C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXB0SMZ_.BKP
Archive Log          23     23-JUN-18          C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ARCHIVELOG\2018_06_23\O1_MF_1_1_FLXB4B5O_.ARC
Archive Log          24     23-JUN-18          C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ARCHIVELOG\2018_06_23\O1_MF_1_2_FLXB4DGL_.ARC
Backup Set           29     23-JUN-18
Backup Piece       29     23-JUN-18          C:\BACKUPS\TESTDB\ARCH\TESTDB_979600061_89_1.ARCH
Backup Set           39     23-JUN-18
Backup Piece       39     23-JUN-18          C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NCNNF_TAG20180623T231301_FLXBGGCC_.BKP
Backup Set           41     23-JUN-18
Backup Piece       41     23-JUN-18          C:\BACKUPS\TESTDB\ARCH\TESTDB_979600390_100_1.ARCH
Backup Set           43     23-JUN-18
Backup Piece       43     23-JUN-18          C:\BACKUPS\TESTDB\ARCH\TESTDB_979600392_102_1.ARCH
deleted backup piece
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXB0J4Q_.BKP RECID=23 STAMP=979599936
deleted backup piece
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXB0JW1_.BKP RECID=26 STAMP=979599936
deleted backup piece
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXB0RGD_.BKP RECID=25 STAMP=979599939
deleted backup piece
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXB0SMZ_.BKP RECID=24 STAMP=979599945
deleted archived log
archived log file name=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ARCHIVELOG\2018_06_23\O1_MF_1_1_FLXB4B5O_.ARC RECID=23 STAMP=979600059
deleted archived log
archived log file name=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ARCHIVELOG\2018_06_23\O1_MF_1_2_FLXB4DGL_.ARC RECID=24 STAMP=979600060
deleted backup piece
backup piece handle=C:\BACKUPS\TESTDB\ARCH\TESTDB_979600061_89_1.ARCH RECID=29 STAMP=979600064
deleted backup piece
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NCNNF_TAG20180623T231301_FLXBGGCC_.BKP RECID=39 STAMP=979600382
deleted backup piece
backup piece handle=C:\BACKUPS\TESTDB\ARCH\TESTDB_979600390_100_1.ARCH RECID=41 STAMP=979600391
deleted backup piece
backup piece handle=C:\BACKUPS\TESTDB\ARCH\TESTDB_979600392_102_1.ARCH RECID=43 STAMP=979600393
Deleted 10 objects

crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_23\O1_MF_S_979573499_FLWJ6DOO_.BKP RECID=10 STAMP=979579523
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_23\O1_MF_S_979579669_FLWP79OC_.BKP RECID=11 STAMP=979579673
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_23\O1_MF_S_979580029_FLWPLGGQ_.BKP RECID=20 STAMP=979586478
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_23\O1_MF_S_979586772_FLWX57NP_.BKP RECID=22 STAMP=979586775
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_23\O1_MF_S_979600054_FLXB47LV_.BKP RECID=28 STAMP=979600055
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_23\O1_MF_S_979600082_FLXB53Y6_.BKP RECID=34 STAMP=979600083
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXBCLFP_.BKP RECID=35 STAMP=979600290
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXBCRRP_.BKP RECID=36 STAMP=979600295
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXBCQMS_.BKP RECID=37 STAMP=979600295
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_23\O1_MF_NNNDF_DAILY_FULL_BACKUP_FLXBCMBW_.BKP RECID=38 STAMP=979600291
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_23\O1_MF_S_979600385_FLXBGLOJ_.BKP RECID=40 STAMP=979600386
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\BACKUPS\TESTDB\ARCH\TESTDB_979600390_101_1.ARCH RECID=42 STAMP=979600392
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\BACKUPS\TESTDB\ARCH\TESTDB_979600393_103_1.ARCH RECID=44 STAMP=979600393
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_23\O1_MF_S_979600394_FLXBGVTF_.BKP RECID=45 STAMP=979600395
Crosschecked 14 objects

validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ARCHIVELOG\2018_06_23\O1_MF_1_3_FLXBGO7P_.ARC RECID=25 STAMP=979600389
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ARCHIVELOG\2018_06_23\O1_MF_1_4_FLXBGP1W_.ARC RECID=26 STAMP=979600390
Crosschecked 2 objects

specification does not match any backup in the repository

specification does not match any archived log in the repository
released channel: d1
released channel: d2
released channel: d3
released channel: d4

RMAN>

Резервирование базы данных перед UPGRADE

Microsoft Windows [Version 10.0.17134.112]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\Users\angor>rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 25 12:06:10 2018

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

connected to target database: TESTDB (DBID=2762894270)

RMAN> RUN
2> {
3> ALLOCATE CHANNEL d1 TYPE DISK;
4> ALLOCATE CHANNEL d2 TYPE DISK;
5> ALLOCATE CHANNEL d3 TYPE DISK;
6> ALLOCATE CHANNEL d4 TYPE DISK;
7>
8> BACKUP
9>      INCREMENTAL LEVEL 0 AS COMPRESSED BACKUPSET
10> DATABASE
11>      TAG BEFORE_UPGRADE_DB;
12>
13> SQL 'alter system archive log current';
14>
15> BACKUP
16> ARCHIVELOG ALL
17>      TAG BEFORE_UPGRADE_ALOG;
18>
19> BACKUP
20> CURRENT CONTROLFILE
21>      TAG BEFORE_UPGRADE_CTRL;
22> }

using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=748 device type=DISK

allocated channel: d2
channel d2: SID=870 device type=DISK

allocated channel: d3
channel d3: SID=7 device type=DISK

allocated channel: d4
channel d4: SID=133 device type=DISK

Starting backup at 25-JUN-18
channel d1: starting compressed incremental level 0 datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00003 name=C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_UNDOTBS1_FLW93Q12_.DBF
channel d1: starting piece 1 at 25-JUN-18
channel d2: starting compressed incremental level 0 datafile backup set
channel d2: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSTEM_FLW91VTY_.DBF
channel d2: starting piece 1 at 25-JUN-18
channel d3: starting compressed incremental level 0 datafile backup set
channel d3: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSAUX_FLW931TG_.DBF
channel d3: starting piece 1 at 25-JUN-18
channel d4: starting compressed incremental level 0 datafile backup set
channel d4: specifying datafile(s) in backup set
input datafile file number=00004 name=C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_USERS_FLW94PXT_.DBF
channel d4: starting piece 1 at 25-JUN-18
channel d1: finished piece 1 at 25-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D59MO_.BKP tag=BEFORE_UPGRADE_DB comment=NONE
channel d1: backup set complete, elapsed time: 00:00:10
channel d4: finished piece 1 at 25-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D5F1X_.BKP tag=BEFORE_UPGRADE_DB comment=NONE
channel d4: backup set complete, elapsed time: 00:00:24
channel d2: finished piece 1 at 25-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D5BL9_.BKP tag=BEFORE_UPGRADE_DB comment=NONE
channel d2: backup set complete, elapsed time: 00:00:56
channel d3: finished piece 1 at 25-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D5CLT_.BKP tag=BEFORE_UPGRADE_DB comment=NONE
channel d3: backup set complete, elapsed time: 00:00:56
Finished backup at 25-JUN-18

Starting Control File and SPFILE Autobackup at 25-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_25\O1_MF_S_979733266_FM1D73MS_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 25-JUN-18

sql statement: alter system archive log current

Starting backup at 25-JUN-18
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=30 STAMP=979733281
channel d1: starting piece 1 at 25-JUN-18
channel d2: starting archived log backup set
channel d2: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=31 STAMP=979733281
channel d2: starting piece 1 at 25-JUN-18
channel d1: finished piece 1 at 25-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_ANNNN_BEFORE_UPGRADE_ALOG_FM1D7LDQ_.BKP tag=BEFORE_UPGRADE_ALOG comment=NONE
channel d1: backup set complete, elapsed time: 00:00:10
channel d2: finished piece 1 at 25-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_ANNNN_BEFORE_UPGRADE_ALOG_FM1D7PM6_.BKP tag=BEFORE_UPGRADE_ALOG comment=NONE
channel d2: backup set complete, elapsed time: 00:00:07
Finished backup at 25-JUN-18

Starting backup at 25-JUN-18
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
channel d1: starting piece 1 at 25-JUN-18
channel d1: finished piece 1 at 25-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NCNNF_BEFORE_UPGRADE_CTRL_FM1D7ZG4_.BKP tag=BEFORE_UPGRADE_CTRL comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JUN-18

Starting Control File and SPFILE Autobackup at 25-JUN-18
piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_25\O1_MF_S_979733296_FM1D81TH_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 25-JUN-18
released channel: d1
released channel: d2
released channel: d3
released channel: d4

RMAN> list backup summary tag=BEFORE_UPGRADE_DB;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
72      B 0 A DISK        25-JUN-18       1       1       YES        BEFORE_UPGRADE_DB
73      B 0 A DISK        25-JUN-18       1       1       YES        BEFORE_UPGRADE_DB
74      B 0 A DISK        25-JUN-18       1       1       YES        BEFORE_UPGRADE_DB
75      B 0 A DISK        25-JUN-18       1       1       YES        BEFORE_UPGRADE_DB

RMAN> list backup summary tag=BEFORE_UPGRADE_ALOG;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
77      B A A DISK        25-JUN-18       1       1       NO         BEFORE_UPGRADE_ALOG
78      B A A DISK        25-JUN-18       1       1       NO         BEFORE_UPGRADE_ALOG

RMAN> list backup summary tag=BEFORE_UPGRADE_CTRL;
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
70      B F A DISK        25-JUN-18       1       1       NO         BEFORE_UPGRADE_CTRL
71      B F A DISK        25-JUN-18       1       1       NO         BEFORE_UPGRADE_CTRL
79      B F A DISK        25-JUN-18       1       1       NO         BEFORE_UPGRADE_CTRL

Содержимое зарегистрированного “бакапа” в контрольном файле:

RMAN> list backupset tag=BEFORE_UPGRADE_CTRL;
List of Backup Sets
===================

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
70      Full    10.31M     DISK        00:00:04     25-JUN-18
        BP Key: 70   Status: AVAILABLE Compressed: NO Tag: BEFORE_UPGRADE_CTRL
        Piece Name: C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NCNNF_BEFORE_UPGRADE_CTRL_FM1CNQ5Z_.BKP
Control File Included: Ckp SCN: 948823       Ckp time: 25-JUN-18

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71      Full    10.31M     DISK        00:00:04     25-JUN-18
        BP Key: 71   Status: AVAILABLE Compressed: NO Tag: BEFORE_UPGRADE_CTRL
        Piece Name: C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NCNNF_BEFORE_UPGRADE_CTRL_FM1CPQL0_.BKP
Control File Included: Ckp SCN: 948859       Ckp time: 25-JUN-18

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
79      Full    10.31M     DISK        00:00:02     25-JUN-18
        BP Key: 79   Status: AVAILABLE Compressed: NO Tag: BEFORE_UPGRADE_CTRL
        Piece Name: C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NCNNF_BEFORE_UPGRADE_CTRL_FM1D7ZG4_.BKP
Control File Included: Ckp SCN: 949494       Ckp time: 25-JUN-18

RMAN>

Находим имя зарезервированного CONTROLFILE:

RMAN> LIST BACKUP OF CONTROLFILE;

List of Backup Sets
===================

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
70      Full    10.31M     DISK        00:00:04     25-JUN-18
        BP Key: 70   Status: AVAILABLE Compressed: NO Tag: BEFORE_UPGRADE_CTRL
        Piece Name: C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NCNNF_BEFORE_UPGRADE_CTRL_FM1CNQ5Z_.BKP
Control File Included: Ckp SCN: 948823       Ckp time: 25-JUN-18

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71      Full    10.31M     DISK        00:00:04     25-JUN-18
        BP Key: 71   Status: AVAILABLE Compressed: NO Tag: BEFORE_UPGRADE_CTRL
        Piece Name: C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NCNNF_BEFORE_UPGRADE_CTRL_FM1CPQL0_.BKP
Control File Included: Ckp SCN: 948859       Ckp time: 25-JUN-18

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
76      Full    10.34M     DISK        00:00:02     25-JUN-18
        BP Key: 76   Status: AVAILABLE Compressed: NO Tag: TAG20180625T120746
        Piece Name: C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_25\O1_MF_S_979733266_FM1D73MS_.BKP
Control File Included: Ckp SCN: 949440       Ckp time: 25-JUN-18

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
79      Full    10.31M     DISK        00:00:02     25-JUN-18
        BP Key: 79   Status: AVAILABLE Compressed: NO Tag: BEFORE_UPGRADE_CTRL
        Piece Name: C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NCNNF_BEFORE_UPGRADE_CTRL_FM1D7ZG4_.BKP
Control File Included: Ckp SCN: 949494       Ckp time: 25-JUN-18

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
80      Full    10.34M     DISK        00:00:02     25-JUN-18
        BP Key: 80   Status: AVAILABLE Compressed: NO Tag: TAG20180625T120816
        Piece Name: C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_25\O1_MF_S_979733296_FM1D81TH_.BKP
Control File Included: Ckp SCN: 949500       Ckp time: 25-JUN-18

RMAN>

RMAN> SHUTDOWN IMMEDIATE;

database closed
database dismounted
Oracle instance shut down

RMAN>exit;

==========
Установка обновлений
==========

Откат изменений с использованием hot backup (RMAN)

C:\Users\angor>rman target=/ nocatalog

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 25 12:26:01 2018

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

connected to target database (not started)

RMAN> STARTUP NOMOUNT;
Oracle instance started

Total System Global Area    7734296576 bytes

Fixed Size                     8764936 bytes
Variable Size               1543504376 bytes
Database Buffers            6174015488 bytes
Redo Buffers                   8011776 bytes

RMAN> RESTORE CONTROLFILE FROM 'C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NCNNF_BEFORE_UPGRADE_CTRL_FM1D7ZG4_.BKP';
Starting restore at 25-JUN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=251 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\CONTROLFILE\O1_MF_FLW90GMT_.CTL
output file name=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\CONTROLFILE\O1_MF_FLW90GWM_.CTL
Finished restore at 25-JUN-18

RMAN> ALTER DATABASE MOUNT;
Statement processed
released channel: ORA_DISK_1

RMAN> RESTORE DATABASE FORCE FROM tag=BEFORE_UPGRADE_DB;

Starting restore at 25-JUN-18
Starting implicit crosscheck backup at 25-JUN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
Crosschecked 9 objects
Finished implicit crosscheck backup at 25-JUN-18

Starting implicit crosscheck copy at 25-JUN-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-JUN-18

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\AUTOBACKUP\2018_06_25\O1_MF_S_979733296_FM1D81TH_.BKP
File Name: C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NCNNF_BEFORE_UPGRADE_CTRL_FM1D7ZG4_.BKP

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_UNDOTBS1_FLW93Q12_.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D59MO_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D59MO_.BKP tag=BEFORE_UPGRADE_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_USERS_FLW94PXT_.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D5F1X_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D5F1X_.BKP tag=BEFORE_UPGRADE_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSTEM_FLW91VTY_.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D5BL9_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D5BL9_.BKP tag=BEFORE_UPGRADE_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to C:\APP\ORACLE\ORADATA\TESTDB_OMEGA\DATAFILE\O1_MF_SYSAUX_FLW931TG_.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D5CLT_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\BACKUPSET\2018_06_25\O1_MF_NNND0_BEFORE_UPGRADE_DB_FM1D5CLT_.BKP tag=BEFORE_UPGRADE_DB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 25-JUN-18

RMAN> RECOVER DATABASE;

Starting recover at 25-JUN-18
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ARCHIVELOG\2018_06_25\O1_MF_1_8_FM1D778O_.ARC
archived log for thread 1 with sequence 9 is already on disk as file C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ARCHIVELOG\2018_06_25\O1_MF_1_9_FM1D7KM6_.ARC
archived log for thread 1 with sequence 10 is already on disk as file C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_1_FLW90QW4_.LOG
archived log file name=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ARCHIVELOG\2018_06_25\O1_MF_1_8_FM1D778O_.ARC thread=1 sequence=8
archived log file name=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ARCHIVELOG\2018_06_25\O1_MF_1_9_FM1D7KM6_.ARC thread=1 sequence=9
archived log file name=C:\APP\ORACLE\FAST_RECOVERY_AREA\TESTDB_OMEGA\TESTDB_OMEGA\ONLINELOG\O1_MF_1_FLW90QW4_.LOG thread=1 sequence=10
media recovery complete, elapsed time: 00:00:03
Finished recover at 25-JUN-18

RMAN> ALTER DATABASE OPEN RESETLOGS;
Statement processed

RMAN>

RMAN> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
testdb OPEN

RMAN>

Определим на какое время восстановлена БД:

RMAN> SELECT SCN_TO_TIMESTAMP(RESETLOGS_CHANGE#)
2>                FROM (SELECT RESETLOGS_CHANGE#
3>                      FROM V$DATABASE_INCARNATION
4>                      WHERE STATUS='CURRENT');

SCN_TO_TIMESTAMP(RESETLOGS_CHAN
-------------------------------
25-JUN-18 12.23.02.000000000 PM

RMAN>

среда, 7 октября 2009 г.

Oracle: DBMS_STATS.GATHER_DATABASE_STATS


DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),
block_sample     BOOLEAN DEFAULT FALSE,
method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree           NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade          BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab          VARCHAR2 DEFAULT NULL,
statid           VARCHAR2 DEFAULT NULL,
options          VARCHAR2 DEFAULT 'GATHER',
objlist          OUT ObjectTab,
statown          VARCHAR2 DEFAULT NULL,
gather_sys       BOOLEAN DEFAULT TRUE,
no_invalidate    BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE'))
);

estimate_percent Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_PARAM Procedure.

block_sample Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

method_opt Accepts:
■ FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
■ FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute size_clause]...]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms.
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the
columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure.

degree Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure.
NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters.
The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.

granularity Granularity of statistics to collect (only pertinent if the table is partitioned).
'ALL' - gathers all (subpartition, partition, and global) statistics
'AUTO'- determines the granularity based on the partitioning type. This is the default value.
'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.
'GLOBAL' - gathers global statistics
'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
'PARTITION '- gathers partition-level statistics
'SUBPARTITION' - gathers subpartition-level statistics.

cascade Gather statistics on the indexes as well. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the indexes in the database in addition to gathering table and column statistics. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using the SET_PARAM
Procedure. Statistics for external tables are not collected by this procedure.
Exceptions
ORA-20000: Insufficient privileges.
ORA-20001: Bad input value.

stattab User statistics table identifier describing where to save the current statistics. The statistics table is assumed to reside in the same schema as the object being analyzed, so there must be one such table in each schema to use this option.

statid Identifier (optional) to associate with these statistics within stattab.

options Further specification of which objects to gather statistics for:
GATHER: Gathers statistics on all objects in the schema.
GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.
GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views.
Also, return a list of objects found to be stale. GATHER EMPTY: Gathers statistics on objects which currently have no statistics. Return a list of objects found to have no statistics.
LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.
LIST STALE: Returns a list of stale objects as determined by looking at the *_tab_modifications views.
LIST EMPTY: Returns a list of objects which currently have no statistics.

objlist List of objects found to be stale or empty

statown Schema containing stattab (if different from current schema)

gather_sys Gathers statistics on the objects owned by the 'SYS' user no_invalidate Does not nvalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent
cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

no_invalidate Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

example:

Tables that have stale statistics:

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/
– shows tables that have stale statistics
SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE

grant analyze any to user;

SQL> EXEC DBMS_STATS.gather_database_stats;
SQL> EXEC DBMS_STATS.gather_database_stats(estimate_percent => 10);

SQL> EXEC DBMS_STATS.delete_database_stats;

или так:

begin
DBMS_STATS.DELETE_DATABASE_STATS;
end;

-- script to gather database statisticsbegin

dbms_stats.gather_database_stats(
options=>’gather auto’,
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
degree=>3) ;
end;

dbms_stats.gather_database_stats (
estimate_percent => NULL,
method_opt => 'auto',
granularity => 'all,
cascade => 'true',
option => 'gather_auto'
);

begin
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER STALE',
gather_sys=>FALSE);

DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER EMPTY',
gather_sys=>FALSE);
end;

declare
a dbms_stats.objecttab;
begin
dbms_stats.gather_database_stats
(OPTIONS=>'LIST EMPTY',OBJLIST=>a);
for i in 1 .. a.count
loop
dbms_output.put_line( a(i).ownname );
dbms_output.put_line( a(i).objType );
dbms_output.put_line( a(i).objName );
dbms_output.put_line( a(i).PartName );
dbms_output.put_line( a(i).subPartName );
dbms_output.put_line( a(i).Confidence );
dbms_output.put_line
( '-------------------------' );
end loop;
end;

четверг, 1 октября 2009 г.

SSH Обмен ключами

Два сервера rac01 и rac02

1. Заходим под oracle на rac01

Выполняем команды:

mkdir ~/.ssh
chmod 755 ~/.ssh
/usr/bin/ssh-keygen -t rsa

На все вопросы ( Enter )

/usr/bin/ssh-keygen -t dsa

На все вопросы ( Enter )

2. Заходим под oracle на rac02

Выполняем команды:

mkdir ~/.ssh
chmod 755 ~/.ssh
/usr/bin/ssh-keygen -t rsa

На все вопросы ( Enter )

/usr/bin/ssh-keygen -t dsa

На все вопросы ( Enter )

3. Заходим под oracle на rac01

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
ssh rac02 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

На все вопросы ( Enter )

ssh rac02 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

На все вопросы ( Enter )

chmod 644 ~/.ssh/authorized_keys

4. Заходим под oracle на rac02

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
ssh rac01 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

На все вопросы ( Enter )

ssh rac01 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

На все вопросы ( Enter )

chmod 644 ~/.ssh/authorized_keys

5. Все, можно проверять:

Заходим под oracle на rac01

ssh rac02 uname -a

Заходим под oracle на rac02

ssh rac01 uname -a

Пароль запрашиваться не должен

суббота, 12 сентября 2009 г.

Oracle DBMS_UTILITY.ANALYZE_SCHEMA

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');

Oracle DBMS_STATS.GATHER_SCHEMA_STATS



exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname, estimate_percent, block_sample, method_opt, degree, granularity, 
cascade, stattab, statid, options, statown ,no_invalidate, gather_temp, gather_fixed);

Code examples:

exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT'
                              ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

EXEC DBMS_STATS.gather_schema_stats(ownname => 'SCOTT', estimate_percent => 25);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');

exec DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

Database Proactive Bundle Patch 12.1.0.2

Patch 20141343 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.4 (Jan2015)

20075921 DB_BP Both DB Homes and Grid Home
19769479 OCW_PSU Both DB Homes and Grid Home
19769473 ACFS_PSU Only Grid Home
19872484 DBWLM_PSU Only Grid Home

Patch 20698050 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.7 (Apr2015)

20594149 DB_BP 12.1.0.2.7 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
20299022 OCW_PSU 12.1.0.2.3 Both DB Homes and Grid Home
20299018 ACFS_PSU 12.1.0.2.3 Only Grid Home
19872484 DBWLM_PSU 12.1.0.2.3 Only Grid Home

Patch 21188742 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.10 (Jul2015)

21125181 DB_BP 12.1.0.2.10 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
20831113 OCW_PSU 12.1.0.2.4 Both DB Homes and Grid Home
20299018 ACFS_PSU 12.1.0.2.4 Only Grid Home
19872484 DBWLM_PSU 12.1.0.2.4 Only Grid Home

Patch 21744410 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.13 (Oct2015)

21694919 DB_BP 12.1.0.2.13 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
21867380 OCW_PSU 12.1.0.2.5 Both DB Homes and Grid Home
21359761 ACFS_PSU 12.1.0.2.5 Only Grid Home
21436941 DBWLM_PSU 12.1.0.2.5 Only Grid Home

Patch 22243551 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.160119 (Jan2016)

21949015 DB_BP 12.1.0.2.160119 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
22329617 OCW_PSU 12.1.0.2.160119 Both DB Homes and Grid Home
21948341 ACFS_PSU 12.1.0.2.160119 Only Grid Home
21436941 DBWLM_PSU 12.1.0.2.5 Only Grid Home

Patch 22899531 - Database Proactive Patch 12.1.0.2.160419 (Apr2016)

22806133 DB_BP 12.1.0.2.160419 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
23006522 OCW_PSU 12.1.0.2.160419 Both DB Homes and Grid Home
22502518 ACFS_PSU 12.1.0.2.160419 Only Grid Home
21436941 DBWLM_PSU 12.1.0.2.5 Only Grid Home

Patch 23273686 - Database Proactive Patch 12.1.0.2.160719 (Jul2016)

23144544 DB_BP 12.1.0.2.160719 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
23520664 OCW_PSU 12.1.0.2.160719 Both DB Homes and Grid Home
23054341 ACFS_PSU 12.1.0.2.160719 Only Grid Home
21436941 DBWLM_PSU 12.1.0.2.5 Only Grid Home

Patch 24448103 - Database Proactive Patch 12.1.0.2.161018 (Oct2016)

24340679 DB_BP 12.1.0.2.161018 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
24846605 OCW_PSU 12.1.0.2.161018 Both DB Homes and Grid Home
24007012 ACFS_PSU 12.1.0.2.161018Foot1 Only Grid Home
21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home
Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.

Patch 24968615 - Database Proactive Bundle Patch 12.1.0.2.170117

24732088 DB_BP 12.1.0.2.170117 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
25101514 OCW_PSU 12.1.0.2.170117 Both DB Homes and Grid Home
24828643 ACFS_PSU 12.1.0.2.170117Foot1 Only Grid Home
21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home
Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.

Patch 25433352 - Database Proactive Bundle Patch 12.1.0.2.170418

25397136 DB_BP 12.1.0.2.170418 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
25481150 OCW_PSU 12.1.0.2.170418 Both DB Homes and Grid Home
25363750 ACFS_PSU 12.1.0.2.170418Foot1 Only Grid Home
21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home
Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.

Patch 26022196 - Database Proactive Bundle Patch 12.1.0.2.170718

25869760 DB_BP 12.1.0.2.170718 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
25869825 OCW_PSU 12.1.0.2.170718 Both DB Homes and Grid Home
25869830 ACFS_PSU 12.1.0.2.170718Foot1 Only Grid Home
21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home
Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.

Patch 26610322 - Database Proactive Bundle Patch 12.1.0.2.170814

26609798 DB_BP 12.1.0.2.170814 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
26609945 OCW_PSU 12.1.0.2.170814 Both DB Homes and Grid Home
25869830 ACFS_PSU 12.1.0.2.170718Foot1 Only Grid Home
21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home
Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.

Patch 26635880 - Database Proactive Bundle Patch 12.1.0.2.171017

26717470 DB_BP 12.1.0.2.171017 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
26392192 OCW_PSU 12.1.0.2.171017 Both DB Homes and Grid Home
26392164 ACFS_PSU 12.1.0.2.171017Foot1 Only Grid Home
21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home
Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.

Patch 27010930 - Database Proactive Bundle Patch 12.1.0.2.180116

26925263 DB_BP 12.1.0.2.180116 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
26925218 OCW_PSU 12.1.0.2.180116 Both DB Homes and Grid Home
26910974 ACFS_PSU 12.1.0.2.180116Foot2 Only Grid Home
26983807 DBWLM_PSU 12.1.0.2.180116Footref2 Only Grid Home
Footnote2 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.

Oracle Grid Infrastructure Patch Set Update 11.2.0.4

Patch 19955028 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.5 (Jan2015) (Includes Database PSU 11.2.0.4.5)

19769489 DB_PSU Both DB Homes and Grid Home
19769476 OCW_PSU Both DB Homes and Grid Home
19769469 ACFS_PSU Only Grid Home

Patch 20485808 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.6 (Apr2015) (Includes Database PSU 11.2.0.4.6)

20299013 DB_PSU 11.2.0.4.6 (INCLUDES CPUAPR2015) Both DB Homes and Grid Home
20420937 OCW_PSU 11.2.0.4.6 Both DB Homes and Grid Home
20299019 ACFS_PSU 11.2.0.4.6 Only Grid Home

Patch 20996923 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.7 (Jul2015) (Includes Database PSU 11.2.0.4.7)

20760982 DB_PSU 11.2.0.4.7 (INCLUDES CPUJUL2015) Both DB Homes and Grid Home
20831122 OCW_PSU 11.2.0.4.7 Both DB Homes and Grid Home
20299019 ACFS_PSU 11.2.0.4.6 Only Grid Home

Patch 21523375 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.8 (Oct2015) (Includes Database PSU 11.2.0.4.8)

21352635 DB_PSU 11.2.0.4.8 (INCLUDES CPUOCT2015) Both DB Homes and Grid Home
21352649 OCW_PSU 11.2.0.4.8 Both DB Homes and Grid Home
21352642 ACFS_PSU 11.2.0.4.8 Only Grid Home

Patch 22191577 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.160119 (Jan2016) (Includes Database PSU 11.2.0.4.160119)

21948347 DB_PSU 11.2.0.4.160119 (INCLUDES CPUJAN2016) Both DB Homes and Grid Home
21948348 OCW_PSU 11.2.0.4.160119 Both DB Homes and Grid Home
21948355 ACFS_PSU 11.2.0.4.160119 Only Grid Home

Patch 22646198 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.160419 (Apr2016) (Includes Database PSU 11.2.0.4.160419)

22502456 DB_PSU 11.2.0.4.160419 (INCLUDES CPUAPR2016) Both DB Homes and Grid Home
22502549 OCW_PSU 11.2.0.4.160419 Both DB Homes and Grid Home
22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home

Patch 23274134 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.160719 (Jul2016) (Includes Database PSU 11.2.0.4.160719)

23054359 DB_PSU 11.2.0.4.160719 (INCLUDES CPUJUL2016) Both DB Homes and Grid Home
23054319 OCW_PSU 11.2.0.4.160719 Both DB Homes and Grid Home
22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home

Patch 24436338 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.161018 (Oct2016) (Includes Database PSU 11.2.0.4.161018)

24006111 DB_PSU 11.2.0.4.161018 (INCLUDES CPUOCT2016) Both DB Homes and Grid Home
23054319 OCW_PSU 11.2.0.4.160719 Both DB Homes and Grid Home
22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home

Patch 25476126 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.170418 (Apr2017) (Includes Database PSU 11.2.0.4.170418)

24732075 DB_PSU 11.2.0.4.170418 (INCLUDES CPUAPR2017) Both DB Homes and Grid Home
23054319 OCW_PSU 11.2.0.4.160719 Both DB Homes and Grid Home
22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home

Patch 26030799 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.170718 (Jul2017) (Includes Database PSU 11.2.0.4.170718)

25869727 DB_PSU 11.2.0.4.170718 (INCLUDES CPUJUL2017) Both DB Homes and Grid Home
25920335 OCW_PSU 11.2.0.4.170718 Both DB Homes and Grid Home
22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home

Patch 26610246 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.170814 (Jul2017) (Includes Database PSU 11.2.0.4.170814)

26609445 DB_PSU 11.2.0.4.170814 (INCLUDES CPUJUL2017) Both DB Homes and Grid Home
26609929 OCW_PSU 11.2.0.4.170814 Both DB Homes and Grid Home
22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home

Patch 26635745 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.171017 (Includes Database PSU 11.2.0.4.171017)

26392168 DB_PSU 11.2.0.4.171017 (INCLUDES CPUOCT2017) Both DB Homes and Grid Home
26609929 OCW_PSU 11.2.0.4.170814 Both DB Homes and Grid Home
22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home

Patch 27107360 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.180116 (Includes Database PSU 11.2.0.4.180116)

26925576 DB_PSU 11.2.0.4.180116 (INCLUDES CPUOCT2017) Both DB Homes and Grid Home
26609929 OCW_PSU 11.2.0.4.170814 Both DB Homes and Grid Home
22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home

четверг, 8 октября 2009 г.