Posts

Posted on

OracleDB12c New Feature: SQL*Plus NOLOGINTIME Option

Oracle Database 12c has added a lot of features to the database and supporting tools. SQL*Plus is no exception. The login or invoking of SQL*Plus includes more command line options.

As in the previous releases, the -H command line option shows the help information.

By default SQL*Plus displays the last successful login time, similar to most Unix distributions. The -NOLOGINTIME option turns it off.

When using the CONNECT syntax after login to SQL*Plus, the last login time is not displayed.

The security features of 12c database is very much enhanced, hence the database tracks the last login time of each user. This information is displayed during the SQL*Plus login to the database.

You do not have to mess with the AUDIT_TRAIL anymore to find the last login time, last login tracking is captured by default.

 

Posted on

OracleDB12c New Feature: More to Online Datafile Move

As we already know, now with Oracle Database 12c, it is easier to move a data file. There is more to it… Let us see few operations in 11g, and how they all got simplified with Oracle 12c online file move feature…

1. Rename a file or move a file from one file system to another

  • Take tablespace offline, thus users/application cannot access the content.
  • Copy the file to new destination using OS commands
  • Use ALTER DATABASE RENAME FILE ‘x’ TO ‘y’;
  • Make the tablespace online

With Oracle Database 12c, all you need to do is one statement, and not worry about data file being inaccessible during the operation.

  • ALTER DATABASE MOVE DATAFILE ‘x’ TO ‘y’;

2. Move a file from one ASM diskgroup to another, Move a file from file system to ASM, Move a file from ASM to file system

  • Take the data file to be moved offline using ALTER DATABASE DATAFILE ‘+DG1/mydb/datafile/x1.dbf’ OFFLINE
  • Connect to RMAN and perform copy using COPY DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO ‘+DG2’  [Note, if you are copying a non-OMF file, make sure to include full path and file name for the destination].
  • Find the name of the new file if using OMF by querying DBA_DATA_FILES.
  • Rename the file using ALTER DATABASE RENAME FILE  ‘+DG1/mydb/datafile/x1.dbf’ TO  ‘+DG2/mydb/datafile/x1.dbf’
  • Connect to RMAN to switch to the new file using SWITCH DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO COPY
  • Recover the data file to make it consistent using RECOVER DATAFILE  ‘+DG2/mydb/datafile/x1.dbf’ TO
  • Finally, make the file online using ALTER DATABASE DATAFILE  ‘+DG2/mydb/datafile/x1.dbf’ ONLINE;

With Oracle Database 12c, all you need to do is one statement, and not worry about data file being inaccessible during the operation.

ASM to ASM

  • ALTER DATABASE MOVE DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO  ‘+DG2’;

File System to ASM

  • ALTER DATABASE MOVE DATAFILE  ‘/u01/db/x1.dbf’ TO  ‘+DG2’;

ASM to File System

  • ALTER DATABASE MOVE DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO  ‘/u01/db1/x2.dbf”;

The TO clause can be omitted for OMF, where unique data file name will be created on the location specified by DB_CREATE_FILE_DEST.

There are couple of keywords you may use with the MOVE operation.

  • KEEP does not delete the file from the source location.
  • REUSE will overwrite the file, if file with same name exists in the destination location.