ORA-01111: name for data file 6 is unknown - on standby after adding datafile to primary

alert.log file on standby:

Managed Standby Recovery starting Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file E:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_pr00_3232.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00006'
Managed Standby Recovery not using Real Time Apply
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

Check for the files taht need recover on standby:

SQL> select * from v$recover_file where error like '%FILE%';

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
         6 ONLINE  ONLINE
FILE MISSING                                                               0



SQL>

Get the details on missing datafile from the primary:

SQL> select file#, name from v$datafile;

     FILE#
----------
NAME
--------------------------------------------------------------------------------

         1
E:\APP\ORACLE\ORADATA\STANDBY1\SYSTEM01.DBF

         2
E:\APP\ORACLE\ORADATA\STANDBY1\SYSAUX01.DBF

         3
E:\APP\ORACLE\ORADATA\STANDBY1\UNDOTBS01.DBF


     FILE#
----------
NAME
--------------------------------------------------------------------------------

         4
E:\APP\ORACLE\ORADATA\STANDBY1\USERS01.DBF

         5
E:\APP\ORACLE\ORADATA\STANDBY1\EXAMPLE01.DBF

         6
E:\APP\ORACLE\ORADATA\STANDBY1\AUD_AUX01.DBF


6 rows selected.


Check MRP is not running on standby:

SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

no rows selected



SQL> alter database create datafile 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATAB
ASE\UNNAMED00006' as 'E:\APP\ORACLE\ORADATA\ORCL\AUD_AUX01.DBF';
alter database create datafile 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\U
NNAMED00006' as 'E:\APP\ORACLE\ORADATA\ORCL\AUD_AUX01.DBF'
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management
is automatic.





SQL> alter system set standby_file_management=MANUAL scope=both;

System altered.

SQL> alter database create datafile 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATAB
ASE\UNNAMED00006' as 'E:\APP\ORACLE\ORADATA\ORCL\AUD_AUX01.DBF';

Database altered.


Check recovery process on standby:


SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

no rows selected

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCO
NNECT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             285213864 bytes
Database Buffers          541065216 bytes
Redo Buffers                6565888 bytes
Database mounted.
SQL>
SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

no rows selected

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME                                                          PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------

   1518564                     4 YES    681574400
13-JAN-14 02.08.19.000000000 PM
                                                                            YES
SWITCHOVER_START_GRP


SQL> drop restore point switchover_start_grp;

Restore point dropped.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCO
NNECT;

Database altered.

SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

PROCESS   DELAY_MINS
--------- ----------
MRP0               0

SQL>

ORA-38881: Cannot drop tablespace

SQL> drop tablespace aud_aux including contents and datafiles;
drop tablespace aud_aux including contents and datafiles
*
ERROR at line 1:
ORA-38881: Cannot drop tablespace AUD_AUX on primary database due to guaranteed
restore points.


SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,        GUARANTEE_FLASHBACK_D
ATABASE,STORAGE_SIZE        FROM V$RESTORE_POINT;

NAME
--------------------------------------------------------------------------------

       SCN
----------
TIME
---------------------------------------------------------------------------
DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------------- --- ------------
SWITCHOVER_START_GRP
   1518415
13-JAN-14 02.07.10.000000000 PM
                    4 YES    629145600


SQL> drop restore point switchover_start_grp;

Restore point dropped.

SQL> drop tablespace aud_aux including contents and datafiles;

Tablespace dropped.

SQL>

Restart Data Guard physical standby database

On primary:

# Defer the standby archive log destination

SQL> alter system set log_archive_dest_state_2=defer scope=both;

System altered.

SQL> alter system switch logfile;

System altered.

Shutdown and startup mount the standby database:

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             285213864 bytes
Database Buffers          541065216 bytes
Redo Buffers                6565888 bytes
Database mounted.
SQL>

In case Data Guard Broker is configured, no other steps are required after restart of standby database.

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account ORA-00849: SGA_TARGET 838860800 cannot be set to more than MEMORY_MAX_TARGET 0.

I encountered this error when switching from AMM to ASMM.

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 908M
memory_target                        big integer 908M
shared_memory_address                integer     0
SQL> alter system set sga_target=800M scope=spfile;

System altered.

SQL> alter system set sga_max_size=800M scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=200M scope=spfile;

System altered.

SQL> alter system set memory_max_target=0 scope=spfile;

System altered.

SQL> alter system set memory_target=0 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 838860800 cannot be set to more than MEMORY_MAX_TARGET 0.
SQL> alter system reset memory_max_target scope=spfile;
alter system reset memory_max_target scope=spfile
*

Workaround: Create pfile from spfile, edit pfile and remove lines with memory_target and memory_max_target, and then recreate spfile from pfile

SQL> create pfile from spfile;

File created.

SQL> startup pfile=e:\app\oracle\product\11.2.0\dbhome_1\database\INITorcl.ORA
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             230687912 bytes
Database Buffers          595591168 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> create spfile from pfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             230687912 bytes
Database Buffers          595591168 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 800M
sga_target                           big integer 800M
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 200M
SQL>

Correct implementation:

SQL> show parameter memory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 908M
memory_target                        big integer 908M
shared_memory_address                integer     0
SQL>
SQL>
SQL>
SQL> alter system set sga_target=800M scope=spfile;

System altered.

SQL> alter system set sga_max_size=800M scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=200M scope=spfile;

System altered.

SQL> alter system reset memory_target scope=spfile;

System altered.


SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 908M
memory_target                        big integer 908M
shared_memory_address                integer     0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             171967656 bytes
Database Buffers          654311424 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SQL>

Index/table fragmentation scripts

-- identify all tables with more than 20% of rows deleted

select
T.TABLE_NAME,
t.partition_name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,'999999.99') per_del,
ROUND(((SUM(T.UPDATES)+SUM(T.INSERTS)+SUM(T.DELETES))/(DECODE(a.NUM_ROWS,0,1,a.NUM_ROWS))*100.0),2) PER_CHG
from ALL_TAB_MODIFICATIONS T,
all_TABLES a
where T.timestamp >= TO_DATE('01-JAN-2001','dd-mon-yyyy')
and T.TABLE_NAME=a.TABLE_NAME
having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100 >=20
group by
T.TABLE_NAME, t.partition_name, a.NUM_ROWS
order by NUM_ROWS desc, t.table_name;

-- get fragmentation factor

select OWNER,TABLE_NAME, LAST_ANALYZED, NUM_ROWS,AVG_ROW_LEN,ROUND(blocks*8/1024) MB, ROUND(blocks*7297/AVG_ROW_LEN/NUM_ROWS,1)"factor" from DBA_TABLES
  where blocks>1000 and num_rows>0 and owner not like 'SYS%' order by 1,7;

-- get BLEVEL for indexes (BLEVEL>=4 must be rebuild)

select t.owner,t.table_name,round(t.blocks*8/1024) "tMB", i.index_name, round(i.leaf_blocks*8/1024) "iMB", i.blevel from DBA_INDEXES i, DBA_TABLES t where t.table_name=i.table_name AND t.blocks<i.leaf_blocks*3 and t.blocks>6400 and t.owner not like 'SYS%' order by 1,3;

Move from ASMM to AMM

In a RAC architecture is easy to move from ASMM to AMM

SQL> conn / as sysdba
Connected.

SQL> alter system set memory_max_target=9G scope=spfile sid='*';

System altered.

SQL> alter system set memory_target=8G scope=spfile sid='*';

System altered.

SQL> alter system set sga_target=0 scope=spfile sid='*';

System altered.

SQL> alter system set pga_aggregate_target=0 scope=spfile sid='*';

System altered.

Then for each instance do a stop and a start:

srvctl stop instance -d dbora -i dbora2
srvctl start instance -d dbora -i dbora2

srvctl stop instance -d dbora -i dbora1
srvctl start instance -d dbora -i dbora1

Anyway, if you try to increase even more memory_max_target, at restart you might get the following error:

srvctl start instance -d dbora -i dbora2

PRCR-1013 : Failed to start resource ora.dbora.db

PRCR-1064 : Failed to start resource ora.dbora.db on node node-2

CRS-5017: The resource action "ora.dbora.db start" encountered the following error:

ORA-00845: MEMORY_TARGET not supported on this system

This error is generated by the size of memory chunks in memory allocation that is different depending on the size of memory allocated.

A workaround might be to decrease MEMORY_MAX_TARGET or increase also MEMORY_TARGET

MYSQL - Purge binary logs

1. SHOW SLAVE STATUS on slave server

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.120.251.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000047
          Read_Master_Log_Pos: 925196802
               Relay_Log_File: relay-bin.000140
                Relay_Log_Pos: 925196965
        Relay_Master_Log_File: mysql-bin.000047
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 925196802
              Relay_Log_Space: 925197179
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 55f6585c-bd69-11e2-90a5-ac162d8b1c44
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)


2. SHOW BINARY LOGS on master server

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 |        540 |
| mysql-bin.000002 |        143 |
| mysql-bin.000003 |        143 |
| mysql-bin.000004 |        143 |
| mysql-bin.000005 |        143 |
| mysql-bin.000006 |        143 |
| mysql-bin.000007 |       4810 |
| mysql-bin.000008 |        224 |
| mysql-bin.000009 |    2090270 |
| mysql-bin.000010 | 1073795562 |
| mysql-bin.000011 | 1073787709 |
| mysql-bin.000012 | 1073755369 |
| mysql-bin.000013 | 1073760500 |
| mysql-bin.000014 |   13629930 |
| mysql-bin.000015 |      20507 |
| mysql-bin.000016 | 1073756709 |
| mysql-bin.000017 | 1073786502 |
| mysql-bin.000018 |  656410524 |
| mysql-bin.000019 |   11416561 |
| mysql-bin.000020 |   38092807 |
| mysql-bin.000021 | 1073764283 |
| mysql-bin.000022 | 1073763265 |
| mysql-bin.000023 | 1073758670 |
| mysql-bin.000024 | 1073761848 |
| mysql-bin.000025 | 1073767027 |
| mysql-bin.000026 | 1073807701 |
| mysql-bin.000027 | 1073815447 |
| mysql-bin.000028 | 1073746876 |
| mysql-bin.000029 | 1073752803 |
| mysql-bin.000030 | 1073810003 |
| mysql-bin.000031 | 1073742110 |
| mysql-bin.000032 | 1073807390 |
| mysql-bin.000033 | 1073759944 |
| mysql-bin.000034 | 1073742378 |
| mysql-bin.000035 | 1073764107 |
| mysql-bin.000036 | 1073775793 |
| mysql-bin.000037 |  996879580 |
| mysql-bin.000038 |    3833572 |
| mysql-bin.000039 |     240904 |
| mysql-bin.000040 |     610464 |
| mysql-bin.000041 | 1073800478 |
| mysql-bin.000042 | 1073791022 |
| mysql-bin.000043 | 1073796481 |
| mysql-bin.000044 | 1073756133 |
| mysql-bin.000045 | 1073764282 |
| mysql-bin.000046 | 1073796542 |
| mysql-bin.000047 |  925713238 |
+------------------+------------+
47 rows in set (0.01 sec)

3. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list. (mysql-bin.000046)

4. Copy all binary logs that will be deleted

[root@mysql]# cp mysql-bin.00000* /db/backup_logs/
[root@mysql]# cp mysql-bin.00001* /db/backup_logs/
[root@mysql]# cp mysql-bin.00002* /db/backup_logs/

[root@mysql]# cp mysql-bin.00003* /db/backup_logs/

5. Purge all log files except target log file

mysql> purge binary logs to 'mysql-bin.000039';

Query OK, 0 rows affected (1.00 sec)

6. List remaning binary logs on master

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000039 |     240904 |
| mysql-bin.000040 |     610464 |
| mysql-bin.000041 | 1073800478 |
| mysql-bin.000042 | 1073791022 |
| mysql-bin.000043 | 1073796481 |
| mysql-bin.000044 | 1073756133 |
| mysql-bin.000045 | 1073764282 |
| mysql-bin.000046 | 1073796542 |
| mysql-bin.000047 |  935732985 |
+------------------+------------+
9 rows in set (0.02 sec)