ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

You cannot create backups on which you’ve used the keep option in the
FRA. If you want to create backups that have nonstandard retention criteria,
you will have to use a non-FRA location to create them. This often
requires the use of the format parameter, as shown in this example:

backup database format ’/data/backup/%U’ keep until time
”sysdate+300” ;


RMAN> backup database tag=keep_bkp keep until time 'sysdate+300';

Starting backup at 18-APR-12
current log archived

using channel ORA_DISK_1
backup will be obsolete on date 12-FEB-13
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/oratest/datafile/system.256.773672945
input datafile file number=00012 name=/data/tbs/idx_big_01.dbf
input datafile file number=00007 name=/data/tbs/tab_small_01.dbf
input datafile file number=00008 name=/data/tbs/tab_med_01.dbf
input datafile file number=00010 name=/data/tbs/idx_small_01.dbf
input datafile file number=00011 name=/data/tbs/idx_med_01.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-12
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/18/2012 15:12:27
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/oratest/datafile/sysaux.257.773672955
channel ORA_DISK_1: starting piece 1 at 18-APR-12
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/18/2012 15:12:28
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/data/tbs/tab_big_01.dbf
input datafile file number=00003 name=+DATA/oratest/datafile/undotbs1.258.773672957
input datafile file number=00004 name=+DATA/oratest/datafile/users.259.773672963
input datafile file number=00006 name=+DATA/oratest/datafile/tab.267.774023547
channel ORA_DISK_1: starting piece 1 at 18-APR-12
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/18/2012 15:12:28
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/oratest/datafile/example.265.773673233
channel ORA_DISK_1: starting piece 1 at 18-APR-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/18/2012 15:12:28
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

ASM directories

-- DIRECTORIES

SQL> alter diskgroup data_test mount;

Diskgroup altered.

SQL> alter diskgroup data_test add directory '+DATA_TEST/DIR';

Diskgroup altered.

SQL> alter diskgroup data_test rename directory '+DATA_TEST/DIR' to '+data_test/dir_test';

Diskgroup altered.

ASM ORA-15032: not all alterations performed, ORA-15283

SQL> alter diskgroup data_test offline disk disk4;
alter diskgroup data_test offline disk disk4
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.0.0 or higher


SQL> alter diskgroup data_test set attribute 'compatible.asm'='11.2';

Diskgroup altered.

SQL> select * from v$asm_disk where name='DATA_TEST';

no rows selected

SQL> select * from v$asm_diskgroup where name='DATA_TEST';

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY                                       V
------------------------------------------------------------ -
           3 DATA_TEST                              512       4096
             1048576 MOUNTED     NORMAL       1800       1694           0
         106                       0            847             0

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY                                       V
------------------------------------------------------------ -
11.2.0.0.0
10.1.0.0.0                                                   N


SQL> alter diskgroup data_test offline disk disk4;
alter diskgroup data_test offline disk disk4
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.0.0 or higher


SQL> alter diskgroup data_test set attribute 'compatible.rdbms'='11.2';

Diskgroup altered.

SQL> select * from v$asm_diskgroup where name='DATA_TEST';

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY                                       V
------------------------------------------------------------ -
           3 DATA_TEST                              512       4096
             1048576 MOUNTED     NORMAL       1800       1694           0
         106                       0            847             0

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY                                       V
------------------------------------------------------------ -
11.2.0.0.0
11.2.0.0.0                                                   N


SQL> alter diskgroup data_test offline disk disk4;

Diskgroup altered.

SQL> select * from v$asm_operation;

no rows selected

SQL> alter diskgroup data_test online all;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
           3 ONLIN RUN           1          0          0          0          0
          0

Add ASM disk to Solaris

-- DISCOVER DISKS IN SOLARIS
# devfsadm
#
-- FORMAT DISK PARTITION TABLE
# format
Searching for disks...done


AVAILABLE DISK SELECTIONS:
       0. c0d1 <DEFAULT cyl 3913 alt 2 hd 255 sec 63>
          /pci@0,0/pci-ide@7,1/ide@1/cmdk@1,0
       1. c1t0d0 <DEFAULT cyl 1302 alt 2 hd 255 sec 63>
          /pci@0,0/pci15ad,1976@10/sd@0,0
       2. c1t1d0 <DEFAULT cyl 2556 alt 2 hd 128 sec 32>
          /pci@0,0/pci15ad,1976@10/sd@1,0
       3. c1t2d0 <DEFAULT cyl 1021 alt 2 hd 64 sec 32>
          /pci@0,0/pci15ad,1976@10/sd@2,0
Specify disk (enter its number): 3
selecting c1t2d0
[disk formatted]


FORMAT MENU:
        disk       - select a disk
        type       - select (define) a disk type
        partition  - select (define) a partition table
        current    - describe the current disk
        format     - format and analyze the disk
        fdisk      - run the fdisk program
        repair     - repair a defective sector
        label      - write label to the disk
        analyze    - surface analysis
        defect     - defect list management
        backup     - search for backup labels
        verify     - read and display labels
        save       - save new disk/partition definitions
        inquiry    - show vendor, product and revision
        volname    - set 8-character volume name
        !<cmd>     - execute <cmd>, then return
        quit

format> fdisk
No fdisk table exists. The default partition for the disk is:

  a 100% "SOLARIS System" partition

Type "y" to accept the default partition,  otherwise type "n" to edit the
 partition table.
y
format>

format> part

PARTITION MENU:
        0      - change `0' partition
        1      - change `1' partition
        2      - change `2' partition
        3      - change `3' partition
        4      - change `4' partition
        5      - change `5' partition
        6      - change `6' partition
        7      - change `7' partition
        select - select a predefined table
        modify - modify a predefined partition table
        name   - name the current table
        print  - display the current table
        label  - write partition map and label to the disk
        !<cmd> - execute <cmd>, then return
        quit
partition> 4
Part      Tag    Flag     Cylinders        Size            Blocks
  4 unassigned    wm       0               0         (0/0/0)          0

Enter partition id tag[unassigned]:
Enter partition permission flags[wm]:
Enter new starting cyl[0]:
Enter partition size[0b, 0c, 0e, 0.00mb, 0.00gb]: 900m
partition> print
Current partition table (unnamed):
Total disk cylinders available: 1020 + 2 (reserved cylinders)

Part      Tag    Flag     Cylinders        Size            Blocks
  0 unassigned    wm       0               0         (0/0/0)          0
  1 unassigned    wm       0               0         (0/0/0)          0
  2     backup    wu       0 - 1019     1020.00MB    (1020/0/0) 2088960
  3 unassigned    wm       0               0         (0/0/0)          0
  4 unassigned    wm       0 -  899      900.00MB    (900/0/0)  1843200
  5 unassigned    wm       0               0         (0/0/0)          0
  6 unassigned    wm       0               0         (0/0/0)          0
  7 unassigned    wm       0               0         (0/0/0)          0
  8       boot    wu       0 -    0        1.00MB    (1/0/0)       2048
  9 unassigned    wm       0               0         (0/0/0)          0

partition> help
Expecting one of the following: (abbreviations ok):
        0      - change `0' partition
        1      - change `1' partition
        2      - change `2' partition
        3      - change `3' partition
        4      - change `4' partition
        5      - change `5' partition
        6      - change `6' partition
        7      - change `7' partition
        select - select a predefined table
        modify - modify a predefined partition table
        name   - name the current table
        print  - display the current table
        label  - write partition map and label to the disk
        !<cmd> - execute <cmd>, then return
        quit

partition> modify
Select partitioning base:
        0. Current partition table (unnamed)
        1. All Free Hog
Choose base (enter number) [0]? 1

Part      Tag    Flag     Cylinders        Size            Blocks
  0       root    wm       0               0         (0/0/0)          0
  1       swap    wu       0               0         (0/0/0)          0
  2     backup    wu       0 - 1019     1020.00MB    (1020/0/0) 2088960
  3 unassigned    wm       0               0         (0/0/0)          0
  4 unassigned    wm       0               0         (0/0/0)          0
  5 unassigned    wm       0               0         (0/0/0)          0
  6        usr    wm       0               0         (0/0/0)          0
  7 unassigned    wm       0               0         (0/0/0)          0
  8       boot    wu       0 -    0        1.00MB    (1/0/0)       2048
  9 alternates    wm       0               0         (0/0/0)          0

Do you wish to continue creating a new partition
table based on above table[yes]?
Free Hog partition[6]? 7
Enter size of partition '0' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '1' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '3' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '4' [0b, 0c, 0.00mb, 0.00gb]: 900m
Enter size of partition '5' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '6' [0b, 0c, 0.00mb, 0.00gb]:

Part      Tag    Flag     Cylinders        Size            Blocks
  0       root    wm       0               0         (0/0/0)          0
  1       swap    wu       0               0         (0/0/0)          0
  2     backup    wu       0 - 1019     1020.00MB    (1020/0/0) 2088960
  3 unassigned    wm       0               0         (0/0/0)          0
  4 unassigned    wm       1 -  900      900.00MB    (900/0/0)  1843200
  5 unassigned    wm       0               0         (0/0/0)          0
  6        usr    wm       0               0         (0/0/0)          0
  7 unassigned    wm     901 - 1019      119.00MB    (119/0/0)   243712
  8       boot    wu       0 -    0        1.00MB    (1/0/0)       2048
  9 alternates    wm       0               0         (0/0/0)          0

Okay to make this the current partition table[yes]?
Enter table name (remember quotes): asm3

Ready to label disk, continue? yes

partition> print
Current partition table (asm3):
Total disk cylinders available: 1020 + 2 (reserved cylinders)

Part      Tag    Flag     Cylinders        Size            Blocks
  0 unassigned    wm       0               0         (0/0/0)          0
  1 unassigned    wm       0               0         (0/0/0)          0
  2     backup    wu       0 - 1019     1020.00MB    (1020/0/0) 2088960
  3 unassigned    wm       0               0         (0/0/0)          0
  4 unassigned    wm       1 -  900      900.00MB    (900/0/0)  1843200
  5 unassigned    wm       0               0         (0/0/0)          0
  6 unassigned    wm       0               0         (0/0/0)          0
  7 unassigned    wm     901 - 1019      119.00MB    (119/0/0)   243712
  8       boot    wu       0 -    0        1.00MB    (1/0/0)       2048
  9 unassigned    wm       0               0         (0/0/0)          0

partition>
partition> q


FORMAT MENU:
        disk       - select a disk
        type       - select (define) a disk type
        partition  - select (define) a partition table
        current    - describe the current disk
        format     - format and analyze the disk
        fdisk      - run the fdisk program
        repair     - repair a defective sector
        label      - write label to the disk
        analyze    - surface analysis
        defect     - defect list management
        backup     - search for backup labels
        verify     - read and display labels
        save       - save new disk/partition definitions
        inquiry    - show vendor, product and revision
        volname    - set 8-character volume name
        !<cmd>     - execute <cmd>, then return
        quit
format> verify

Primary label contents:

Volume name = <        >
ascii name  = <DEFAULT cyl 1020 alt 2 hd 64 sec 32>
pcyl        = 1022
ncyl        = 1020
acyl        =    2
bcyl        =    0
nhead       =   64
nsect       =   32
Part      Tag    Flag     Cylinders        Size            Blocks
  0 unassigned    wm       0               0         (0/0/0)          0
  1 unassigned    wm       0               0         (0/0/0)          0
  2     backup    wu       0 - 1019     1020.00MB    (1020/0/0) 2088960
  3 unassigned    wm       0               0         (0/0/0)          0
  4 unassigned    wm       1 -  900      900.00MB    (900/0/0)  1843200
  5 unassigned    wm       0               0         (0/0/0)          0
  6 unassigned    wm       0               0         (0/0/0)          0
  7 unassigned    wm     901 - 1019      119.00MB    (119/0/0)   243712
  8       boot    wu       0 -    0        1.00MB    (1/0/0)       2048
  9 unassigned    wm       0               0         (0/0/0)          0

format> q

-- CREATE DEVICE FOR  ASM PARTITION
# cd asmdisk
# ls
disk1  disk2  disk3
# ls -l
total 0
crw-r--r--   1 oracle   oinstall  30, 68 Apr  9 11:33 disk1
crw-r--r--   1 oracle   oinstall  30, 132 Apr  9 11:33 disk2
#
# mknod disk3 c 30 196
# ls -l
total 0
crw-r--r--   1 oracle   oinstall  30, 68 Apr  9 11:34 disk1
crw-r--r--   1 oracle   oinstall  30, 132 Apr  9 11:34 disk2
crw-r--r--   1 root     root      30, 196 Apr  9 11:34 disk3
#
# chown oracle:oinstall disk3
# ls
disk1  disk2  disk3
#
# ls -l
total 0
crw-r--r--   1 oracle   oinstall  30, 68 Apr  9 11:34 disk1
crw-r--r--   1 oracle   oinstall  30, 132 Apr  9 11:34 disk2
crw-r--r--   1 oracle   oinstall  30, 196 Apr  9 11:34 disk3

# su - oracle

-bash-3.2$ . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app/oracle
-bash-3.2$
-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 9 11:48:55 2012

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

SQL> conn / as sysasm
Connected.
SQL>

SQL> select path, group_number group_#, disk_number disk_#, mount_status,
header_status, state, total_mb, free_mb
from v$asm_disk
order by group_number;

/asmdisk/disk3
         0          0 CLOSED  CANDIDATE    NORMAL            0          0

/asmdisk/disk1
         1          0 CACHED  MEMBER       NORMAL         9216       6879

/asmdisk/disk2
         2          0 CACHED  MEMBER       NORMAL         5018       3480

SQL> show parameter asm_diskstring;

asm_diskstring                       string      /asmdisk/disk*

-- ASM DISKGROUP ATTRIBUTES

SQL> select GROUP_NUMBER,NAME,VALUE from v$asm_attribute

  2  where name not like 'template%';

           1

disk_repair_time

3.6h

           1

au_size

1048576

           1

sector_size

512

           1

compatible.asm

11.2.0.0.0

           1

compatible.rdbms

10.1.0.0.0

           1

cell.smart_scan_capable

FALSE

           1

access_control.enabled

FALSE

           1

access_control.umask

066

           2

disk_repair_time

3.6h

           2

au_size

1048576

           2

sector_size

512

           2

compatible.asm

11.2.0.0.0

           2

compatible.rdbms

10.1.0.0.0

           2

cell.smart_scan_capable

FALSE

           2

access_control.enabled

FALSE

           2

access_control.umask

066

16 rows selected.

-- ADD DISK

SQL> alter diskgroup data add disk '/asmdisk/disk3';

Diskgroup altered.

SQL> select * from v$asm_operation;

           1 REBAL RUN           1          1        149        209        550

          0

SQL> SELECT

NVL(a.name, '[CANDIDATE]')      disk_group_name

, b.path                          disk_file_path

, b.name                          disk_file_name

, b.failgroup                     disk_file_fail_group

FROM

v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)

ORDER BY

a.name; 

DISK_GROUP_NAME

------------------------------

DISK_FILE_PATH

--------------------------------------------------------------------------------

DISK_FILE_NAME                 DISK_FILE_FAIL_GROUP

------------------------------ ------------------------------

DATA

/asmdisk/disk1

DATA_0000                      DATA_0000

DATA

/asmdisk/disk3

DATA_0001                      DATA_0001

DISK_GROUP_NAME

------------------------------

DISK_FILE_PATH

--------------------------------------------------------------------------------

DISK_FILE_NAME                 DISK_FILE_FAIL_GROUP

------------------------------ ------------------------------

FRA

/asmdisk/disk2

FRA_0000                       FRA_0000

-- ADD DISK USING NAME


SQL> alter diskgroup data drop disk DATA_0001;

Diskgroup altered.

SQL> alter diskgroup data add disk '/asmdisk/disk3' NAME disk3;

Diskgroup altered.

SQL> select name from v$asm_disk;

NAME
------------------------------
DATA_0000
FRA_0000
DISK3

-- DROP DISKS

SQL> alter diskgroup data drop  disk DATA_0001;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE

------------ ----- ---- ---------- ---------- ---------- ---------- ----------

EST_MINUTES ERROR_CODE

----------- --------------------------------------------

           1 REBAL RUN           1          1        166        209        869

          0

SQL> /

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE

------------ ----- ---- ---------- ---------- ---------- ---------- ----------

EST_MINUTES ERROR_CODE

----------- --------------------------------------------

           1 REBAL WAIT          1

SQL> /

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE

------------ ----- ---- ---------- ---------- ---------- ---------- ----------

EST_MINUTES ERROR_CODE

----------- --------------------------------------------

           1 REBAL WAIT          1

SQL> /

no rows selected

SQL> select name, header_status, path from v$asm_disk;

NAME                           HEADER_STATU
------------------------------ ------------
PATH
--------------------------------------------------------------------------------
                               FORMER
/asmdisk/disk3

DATA_0000                      MEMBER
/asmdisk/disk1

FRA_0000                       MEMBER
/asmdisk/disk2

-- USING UNDROP DISKS

select NAME, HEADER_STATUS, MOUNT_STATUS, MODE_STATUS, STATE, REPAIR_TIMER/60 from V$ASM_DISK WHERE GROUP_NUMBER=1;

NAME                           HEADER_STATU MOUNT_S MODE_ST STATE

------------------------------ ------------ ------- ------- --------

REPAIR_TIMER/60

---------------

DATA_0000                      MEMBER       CACHED  ONLINE  NORMAL

              0

DATA_0001                      MEMBER       CACHED  ONLINE  NORMAL

              0

SQL> alter diskgroup data drop disk DATA_0001;

Diskgroup altered.

SQL> alter diskgroup data undrop disks;

Diskgroup altered.

SQL> select NAME, HEADER_STATUS, MOUNT_STATUS, MODE_STATUS, STATE, REPAIR_TIMER/60 from V$ASM_DISK WHERE GROUP_NUMBER=1;

NAME                           HEADER_STATU MOUNT_S MODE_ST STATE

------------------------------ ------------ ------- ------- --------

REPAIR_TIMER/60

---------------

DATA_0000                      MEMBER       CACHED  ONLINE  NORMAL

              0

DATA_0001                      MEMBER       CACHED  ONLINE  NORMAL

              0

-- DISKGROUP REBALANCE


SQL> alter diskgroup data rebalance;

Diskgroup altered.

select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE

------------ ----- ---- ---------- ---------- ---------- ---------- ----------

EST_MINUTES ERROR_CODE

----------- --------------------------------------------

           1 REBAL RUN           1          1          0          2          0

          0

-- ASM TEMPLATES

SQL> select * from v$asm_template
  2   where group_number=2;

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME                           PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
           2           60 UNPROT COARSE Y PARAMETERFILE                  COLD
COLD

           2           61 UNPROT COARSE Y ASMPARAMETERFILE               COLD
COLD

           2           63 UNPROT COARSE Y DUMPSET                        COLD
COLD


GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME                           PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
           2           64 UNPROT FINE   Y CONTROLFILE                    COLD
COLD

           2           65 UNPROT COARSE Y FLASHFILE                      COLD
COLD

           2           66 UNPROT COARSE Y ARCHIVELOG                     COLD
COLD


GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME                           PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
           2           67 UNPROT COARSE Y ONLINELOG                      COLD
COLD

           2           68 UNPROT COARSE Y DATAFILE                       COLD
COLD

           2           69 UNPROT COARSE Y TEMPFILE                       COLD
COLD


GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME                           PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
           2          170 UNPROT COARSE Y BACKUPSET                      COLD
COLD

           2          171 UNPROT COARSE Y AUTOBACKUP                     COLD
COLD

           2          172 UNPROT COARSE Y XTRANSPORT                     COLD
COLD


GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME                           PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
           2          173 UNPROT COARSE Y CHANGETRACKING                 COLD
COLD

           2          174 UNPROT COARSE Y FLASHBACK                      COLD
COLD

           2          175 UNPROT COARSE Y DATAGUARDCONFIG                COLD
COLD


GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME                           PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
           2          176 UNPROT COARSE Y OCRFILE                        COLD
COLD


16 rows selected.

ORA-00380 - cannot specify db_stringk_cache_size since stringK is the standard block size

SQL> show parameter db;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
clonedb                              boolean     FALSE
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
db_block_size                        integer     8192
db_cache_advice                      string      ON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 0

SQL> alter system set db_8k_cache_size=10m;
alter system set db_8k_cache_size=10m
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size

MySQL admin

mysql -u root -p                   -- Connect to Mysql

show databases;                     -- view databases
create database db_name;      -- create database db_name
create schema db_name;       -- create database db_name (schema <=> database)
use db_name;                        -- change database to db information_schema for all statements
show databases;                     -- list all databases
select database();                   -- show current database
help contents;                       -- access help path

create user test identified by 'test';                 -- create user
grant select on test.table_test to test;            -- grant select on table_test to user test
select user, host, password from mysql.user;  -- show all users
drop user test;                                               -- drop user
show grants;                                                  -- show user grants
show grants for 'user'@'host'                          -- show grants for a specific user

Tables are fully defined by using db_name.table_name.

desc table_name;                                           -- describe table
show tables;                                                   -- show database tables;
show index for table_name;                           -- show index for table_name
show table status;                                           -- list detailed table info
show create table table_name;                        -- list DDL for table_name create script
truncate table table_name;                             -- truncates table
analyze table test2;                                        -- analyze table
explain select * from table_tes where pk_col=1; -- view explain plan

select now();                                                  -- show system time
select @@tx_isolation;                                  -- show transaction isolation level  
show character set;                                        -- display a list of available character sets

set autocommit = 1;                                      -- default: set autocommit on

MySql administration (mysqladmin)


mysqladmin status
mysqladmin version



status -- Returns information on server state
password -- Changes a user password
shutdown -- Shuts down the MySQL server
reload -- Reloads the MySQL grant tables
refresh --  Resets all caches and logs
variables -- Returns values of all server variables
version -- Returns the server version
processlist -- Returns a list of all processes active on the server
kill -- Kills an active server process
ping -- Tests if the server is alive

Starting/stopping

/usr/local/mysql/support-files/mysql.server start
/usr/local/mysql/support-files/mysql.server stop
On Windows:
mysqladmin -u root -p shutdown

Enable SQL Plus autotrace

As user from SQL Plus:


SQL> set autotrace on;
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

As system from SQL Plus:


SQL> @?/rdbms/admin/utlxplan

Table created.

SQL> grant all on plan_table to public;

Grant succeeded.

As sys from SQL Plus:

SQL> @?/sqlplus/admin/plustrce.sql

SQL> grant plustrace to public;

Grant succeeded.

As user (a reconnect is required):

SQL> set autotrace on;