ORA-00907: Missing Right Parenthesis

Today to run a RMAN script to perform a restore of a database gives me this error;


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03002: failure of set command at 08/20/2015 08:41:34
ORA-00907: missing right parenthesis

Error literally says, missing a right parenthesis, but here is another mistake, I thought it originally occurred, and so I have made you a post. This is the part of the code is wrong (excerpt from script);


run {
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=SRVBCK_PROD,
NSR_DATA_VOLUME_POOL=HotBackup_Full,
NSR_CLIENT=Lion)';
set until time "to_date( 'AUG 17 2015 05:00:00' , 'MON DD RRRR HH24:MI:SS')";
SET NEWNAME FOR DATAFILE '+STORAGE_PRIMARY/DATAFILE/CLOUD_SYSTEM_1.DBS' TO '+RESTORE-TEMPORAL/CLOUD_SYSTEM_1.DBS';
SET NEWNAME FOR DATAFILE '+STORAGE_PRIMARY/DATAFILE/CLOUD_SYSAUX_1.DBS' TO '+RESTORE-TEMPORAL/CLOUD_SYSAUX_1.DBS';

The problem is that I put the month in Spanish, the language is in English, changing it to “aug” the problem is solved, as shown below;

run {
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=SRVBCK_PROD,
NSR_DATA_VOLUME_POOL=HotBackup_Full,
NSR_CLIENT=Lion)';
set until time "to_date( 'AUG 17 2015 05:00:00' , 'MON DD RRRR HH24:MI:SS')";
SET NEWNAME FOR DATAFILE '+STORAGE_PRIMARY/DATAFILE/CLOUD_SYSTEM_1.DBS' TO '+RESTORE-TEMPORAL/CLOUD_SYSTEM_1.DBS';
SET NEWNAME FOR DATAFILE '+STORAGE_PRIMARY/DATAFILE/CLOUD_SYSAUX_1.DBS' TO '+RESTORE-TEMPORAL/CLOUD_SYSAUX_1.DBS';

Is curious error, it really is syntax and Oracle can not fit more or indicate an “invalid value” or “invalid month”.

HTH – Antonio NAVARRO

ORA-01555 Using A”S OF TIMESTAMP” Clause

Today called me a person from development team to tell me that needed to recover rows from a table that have been accidentally deleted. The first logic leads you to do is make use of the “flashback query” feature, whe I run the next query I get a ORA-01555, the classic snapshot too old;


SATV*ANTO> select count (*)
 2 from cloud.hco_catalog_longstar as of timestamp to_timestamp('2015-08-17 13:00:00', 'YYYY-MM-DD HH24:MI:SS');
from cloud.hco_catalog_longstar as of timestamp to_timestamp('2015-08-17 13:00:00', 'YYYY-MM-DD HH24
 *
ERROR en línea 2:
ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" too small

The problem is that the undo_retention here is very low, an hour. The issue is that I’m asking data from yesterday. If the database had little activity/transactions and deleted rows were few,  maybe the data would be.

The solution is to use the database backup and clone database (this is a version 10g). As a measure could also be looking up the undo_retention, that seems  pretty lame

HTH – Antonio NAVARRO

Control File Is Growing

Today, the following message appears into a alert.log for a database I manage;

 
Expanded controlfile section 28 from 560 to 1120 records
Requested to grow by 560 records; added 8 blocks of records

To see what is the section 28, we have to look at the view V$CONTROLFILE_RECORD_SECTION. Although this view does not tell us the number of sections. In this case Oracle makes us easy, we only have to count the rows in order. With the following query, which has the virtual rownum we get the sections;

 
SELECT
  ROWNUM,
  TYPE,
  RECORDS_TOTAL,
  RECORDS_USED
FROM 
  V$CONTROLFILE_RECORD_SECTION

If we execute this statement, we get the following output, Looking at line 28;

 
    ROWNUM TYPE                         RECORDS_TOTAL RECORDS_USED
__________ ____________________________ _____________ ____________
         1 DATABASE                                 1            1
         2 CKPT PROGRESS                           19            0
         3 REDO THREAD                             16            1
         4 REDO LOG                                32            8
         5 DATAFILE                              4500         2926
         6 FILENAME                              4629         3013
         7 TABLESPACE                            4500          456
         8 TEMPORARY FILENAME                    4500           57
         9 RMAN CONFIGURATION                       7            1
        10 LOG HISTORY                          18150        13026
        11 OFFLINE RANGE                        10210         1794
        12 ARCHIVED LOG                         18150        13032
        13 BACKUP SET                            1021         1021
        14 BACKUP PIECE                          1003         1003
        15 BACKUP DATAFILE                       1056            0
        16 BACKUP REDOLOG                        3424         3424
        17 DATAFILE COPY                         1000         1000
        18 BACKUP CORRUPTION                     1113            0
        19 COPY CORRUPTION                       1021            0
        20 DELETED OBJECT                        4084         4084
        21 PROXY COPY                            1000            0
        22 BACKUP SPFILE                          226            0
        23 DATABASE INCARNATION                   145            1
        24 FLASHBACK LOG                         2048            0
        25 RECOVERY DESTINATION                     1            0
        26 INSTANCE SPACE RESERVATION            1055            1
        27 REMOVABLE RECOVERY FILES              1000            0
        28 RMAN STATUS                           1120         1120
        29 THREAD INSTANCE NAME MAPPING            16           16
        30 MTTR                                    16           16
        31 DATAFILE HISTORY                        52            0
        32 STANDBY DATABASE MATRIX                 10           10
        33 GUARANTEED RESTORE POINT              2048            0
        34 RESTORE POINT                         2080            0

Thus, RMAN STATUS section is what has grown, it makes sense because of this database is having changes in RMAN backups since a couple of weeks. As a reminder, in the Control File are two types of records, Circular reuse records and Noncircular reuse records. From the doc;

Circular reuse records

These records contain noncritical information that is eligible to be overwritten if needed. When all available record slots are full, the database either expands the control file to make room for a new record or overwrites the oldest record. Examples include records about:

LOG HISTORY
OFFLINE RANGE
ARCHIVED LOG
BACKUP SET
BACKUP PIECE
BACKUP DATAFILE
BACKUP REDOLOG
DATAFILE COPY
BACKUP CORRUPTION
COPY CORRUPTION
DELETED OBJECT
PROXY COPY

Noncircular reuse records

These records contain critical information that does not change often and cannot be overwritten. Examples of information include tablespaces, data files, online redo log files, and redo threads. Oracle Database never reuses these records unless the corresponding object is dropped from the tablespace. Examples of non-circular controlfile sections (the ones that can only expand)

DATABASE (info)
CKPT PROGRESS (Checkpoint progress)
REDO THREAD, REDO LOG (Logfile)
DATAFILE (Database File)
FILENAME (Datafile Name)
TABLESPACE
TEMPORARY FILENAME
RMAN CONFIGURATION

NOTE;
———
Reading and writing the control file blocks is different from reading and writing data blocks. For the control file, Oracle Database reads and writes directly from the disk to the program global area (PGA). Each process allocates a certain amount of its PGA memory for control file blocks.

HTH – Antonio NAVARRO

SID Take Precedence Over *

Today was changing parameters in a RAC, as applied to all three instances, I used the option SID = ‘*’ in the command alter system set. He problem has come to start the first instance, as it needed to bounce him to take the change. I look at the parameter after started to confirm and I see this change as before. After a little research I see the spfile.ora has defined the SIDs of the three request for parameters, ie

  • sieb1.memory_max_target
  • sieb2.memory_max_target
  • sieb3.memory_max_target
  • *.memory_max_target

As I changed the parameter to *, you have not changed the values for sieb1, sieb2 and sieb3. Using RTFM I see;

The SID clause lets you specify the SID of the instance where the value will take effect.

Specify SID = ‘*’ if you want Oracle Database to change the value of the parameter for all instances that do not already have an explicit setting for this parameter.

Specify SID = ‘sid’ if you want Oracle Database to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID = ‘*’.

In my case it is already defined. The solution is to set up one by one sieb1, sieb2 and sieb3 (this is the chosen one) or erase all three.

HTH – Antonio NAVARRO

How To See The Retention Time For AWR

With this query we can see the time of retention of data for the AWR, the value is rounded to days, also we can see the execution interval snapshots;

SELECT
   EXTRACT( DAY FROM snap_interval) * 1440 +
   EXTRACT( HOUR FROM snap_interval) * 60 +
   EXTRACT( MINUTE FROM snap_interval ) AS "Snapshot (Minutes)",
   EXTRACT( DAY FROM retention) AS "Retention (Days)"
FROM 
   dba_hist_wr_control;

Attention, if value is zero it means that the data will be stored forever.

You can change your retention time with the following command, eg for one day retention (value in minutes);

sql> exec dbms_workload_repository.modify_snapshot_settings(retention => 1440);

HTH – Antonio NAVARRO

How To Estimate Size For SYSAUX Tablespace

SYSAUX tablespace size depends on several variables, one or the principal is the AWR, since the SYSAUX tablespace is where keep all data from the AWR. An example of using for a SYSAUX tablespaces (from production enviroment) is the following;

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size:                       4,550.8 MB
|
| Total size of SM/AWR                     4,017.4 MB (  88.3% of SYSAUX )
| Total size of SM/OPTSTAT                   289.1 MB (   6.4% of SYSAUX )
| Total size of LOGMNR                        92.8 MB (   2.0% of SYSAUX )
| Total size of SM/ADVISOR                    67.6 MB (   1.5% of SYSAUX )
| Total size of SM/OTHER                      27.1 MB (   0.6% of SYSAUX )
| Total size of WM                             7.4 MB (   0.2% of SYSAUX )
| Total size of SMON_SCN_TIME                  6.3 MB (   0.1% of SYSAUX )
| Total size of JOB_SCHEDULER                  4.8 MB (   0.1% of SYSAUX )
| Total size of EM_MONITORING_USER             3.2 MB (   0.1% of SYSAUX )
| Total size of SQL_MANAGEMENT_BASE            2.0 MB (   0.0% of SYSAUX )
| Total size of PL/SCOPE                       1.6 MB (   0.0% of SYSAUX )
| Total size of XSOQHIST                       1.4 MB (   0.0% of SYSAUX )
| Total size of AO                             1.4 MB (   0.0% of SYSAUX )
| Total size of LOGSTDBY                       1.4 MB (   0.0% of SYSAUX )
| Total size of STREAMS                        1.0 MB (   0.0% of SYSAUX )
| Total size of AUTO_TASK                      0.3 MB (   0.0% of SYSAUX )
| Total size of Others                        26.2 MB (   0.6% of SYSAUX )

Oracle provides the utlsyxsz.sql script, which is located in $ORACLE_HOME/rdbms/admin/, using it can estimate the physical size for datafile (or datafiles) that need the AWR to save its data, of course, there are other tools that also use the SYSAUX tablespace as a repository and can distort the estimation.
Run the script;

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL> sta ?/rdbms/admin/utlsyxsz.sql

Specify the name of report;

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: anr_rpt.txt

We will estimate for executions every 30 minutes and store three months (90 days);

~~~~~~~~~~~~~~~~~~~~
AWR Space Estimation
~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Automatic Workload Repository (AWR)
| in SYSAUX, we need the following values:
|
|     - Interval Setting (minutes)
|     - Retention Setting (days)
|     - Number of Instances
|     - Average Number of Active Sessions
|     - Number of Datafiles

|
| For 'Interval Setting',
|   Press <return> to use the current value:     60.0 minutes
|   otherwise enter an alternative
|
Enter value for interval: 30

**   Value for 'Interval Setting': 30

|
| For 'Retention Setting',
|   Press <return> to use the current value:    180.0 days
|   otherwise enter an alternative
|
Enter value for retention: 90

It ask the number of instances (if RAC), and the value of active sessions, by default take the current value, which is what we use in this case;

Enter value for num_instances: 1

**   Value for 'Number of Instances': 1

|
| For 'Average Number of Active Sessions',
|   Press <return> to use the current value:   78.00
|   otherwise enter an alternative
|
Enter value for active_sessions:

We give the result for AWR;

| ***************************************************
| Estimated size of AWR:                   6,266.5 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval -        30 minutes
|           Retention -     90.00 days
|       Num Instances -         1
|     Active Sessions -     78.00
|           Datafiles -      2819
| ***************************************************

The report also give us estimates for other repositories like LOGMNR, History Optimizer Statistics, etc.

HTH – Antonio NAVARRO