ORA-19597: file blocksize 8192 does not match set blocksize of 16384

Recently, I have backup failed with ORA-19597: file blocksize 8192 does not match set blocksize of 16384.

error message image

OERR
Error: ORA 19597 Text: file %s blocksize %s does not match set blocksize of %s ---------------------------------------------------------------------------------------------------------------------------

Cause: A file was specified for inclusion in a backup set but it has a 
 logical block size different from the rest of the files in the backup 
 set. All files in a backup set must have the same logical block size. 
Action: Specify a file that has the same block size as the rest of the files 
 in the backup set. The conversation is still active and more files can 
 be specified.

Possibly root cause for this error is having include the current controlfile in each backuppiece with tablespaces of different blocksizes confused RMAN.

Resolution
For Oracle Database 9i is not necessary to include the statement "include current controlfile".

RMAN> configure controlfile autobackup on;

backup script image

Once a successful full backup is complete, proceed to run 'include current controlfile' in your backup script, the issue should not be recurred.

Specifying Alternate Archive Destination

My implementation is on Oracle database 9i.

Check for the current archive log destination.
SQL> show parameter log_archive_dest_1

NAME                                 TYPE       VALUE
-------------------------------- ---------- ------------------------------
log_archive_dest_1              string     LOCATION=/jli6/arch

Check for the current archive log destination status.
 SQL>  show parameter log_archive_dest_state_1

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
log_archive_dest_state_1             string     enable

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/jli7/arch MANDATORY NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/jli6/arch MANDATORY' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ALTERNATE SCOPE=BOTH;





SQL> ALTER SYSTEM SWITCH LOGFILE;

apcrss32:/jli7/arch [LIPLPDB] $ ls
1_72912.dbf

If failover to alternate happen, we might need to manual enable the original archive destination status.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ALTERNATE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/jli7/arch NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' SCOPE=BOTH;

Status for log destinations
i.   ENABLE indicates that the database can use the destination. 
ii.  DEFER indicates that the location is temporarily disabled. 
iii. ALTERNATE indicates that the destination is an alternate. The availability state of an alternate destination is DEFER. If its parent destination fails, the availability state of the alternate becomes ENABLE. 
* ALTERNATE cannot be specified for destinations LOG_ARCHIVE_DEST_11 to LOG_ARCHIVE_DEST_31.

Extra information (Oracle Database 11gR2)
Archive to only a single destination, refer initialization parameter
LOG_ARCHIVE_DEST
 
Archive to multiple destinations, refer initialization parameter 
LOG_ARCHIVE_DEST_n
 
Archive only to a primary and secondary destination, refer initialization parameter 
LOG_ARCHIVE_DEST
LOG_ARCHIVE_DUPLEX_DEST

References
http://docs.oracle.com/cd/B10501_01/server.920/a96653/log_arch_dest_param.htm#77201
https://docs.oracle.com/cd/E18283_01/server.112/e17120/archredo004.htm#

SQL Plan Baselines

Loading SQL Plans into SPM using AWR

Through this query, we can know the execution plans for specific sql_id.
SELECT plan_table_output FROM TABLE (Dbms_Xplan.display_awr('49xkga3u105ab'));

Create a SQL Tuning Set (STS)
EXEC Dbms_Sqltune.create_sqlset(sqlset_name => '49xkga3u105ab_sqlset', 
description => 'QUERY TEST');

Take initial and final snapshot id 

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Load every execution plan in STS
DECLARE
baseline_ref_cur Dbms_Sqltune.SQLSET_CURSOR;
BEGIN
OPEN baseline_ref_cur FOR
SELECT VALUE(p) FROM TABLE(
Dbms_Sqltune.SELECT_WORKLOAD_REPOSITORY(8055, 8077,
                               'sql_id='||CHR(39)||'49xkga3u105ab'||CHR(39)||'',
                               NULL, NULL, NULL, NULL, NULL, NULL, 'ALL')) p;
Dbms_Sqltune.LOAD_SQLSET('49xkga3u105ab_sqlset', baseline_ref_cur);
END;
/
Load single execution plan in STS
DECLARE
baseline_ref_cur Dbms_Sqltune.SQLSET_CURSOR;
BEGIN
OPEN baseline_ref_cur FOR
SELECT VALUE(p) FROM TABLE(
Dbms_Sqltune.SELECT_WORKLOAD_REPOSITORY(5868, 5869,         
                            'sql_id='||CHR(39)||'1ffbnq9wwkhtz'||CHR(39)||' AND
                            plan_hash_value=1705166898', NULL, NULL, NULL, NULL,
                            NULL, NULL, 'ALL')) p;
Dbms_Sqltune.LOAD_SQLSET(‘1ffbnq9wwkhtz_sqlset_test’, baseline_ref_cur);
END;
/

Verify STS created is right
SELECT NAME,OWNER, CREATED, STATEMENT_COUNT
FROM DBA_SQLSET
WHERE NAME='49xkga3u105ab_sqlset';

Verify the execution plan right to load
SELECT * FROM TABLE(Dbms_Xplan.display_sqlset('49xkga3u105ab_sqlset','49xkga3u105ab'));

Load STS into SPM (SQL Plan Management)
DECLARE
my_int PLS_INTEGER;
BEGIN
my_int := Dbms_Spm.load_plans_from_sqlset (
sqlset_name => '49xkga3u105ab_sqlset',
sqlset_owner => 'SYS',
fixed => 'YES',
enabled => 'YES');
DBMS_OUTPUT.PUT_LINE(my_int);
END;
/

Verify the execution plan is load into SQL Plan Baselines
SELECT SQL_HANDLE, PLAN_NAME, SQL_TEXT, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES;

Actually, you can disable the baselines plan anytime with query.
DECLARE
   l_plans PLS_INTEGER;
BEGIN
   l_plans := Dbms_Spm.alter_sql_plan_baseline (
      sql_handle         => 'SQL_c3ff4afc21522d6e',
      plan_name         => 'SQL_PLAN_c7zuazhhp4bbf90decb9c',
      attribute_name   => 'enabled',
      attribute_value  => 'NO'
   );
END;