Configuring the database

In this section we will configure our database to allow us to run all the scenarios in this appendix.

Making sure an spfile is used to start the database

Log on to the database as sysdba and verify if an spfile is used:

SQL> CONNECT sys AS sysdba 
SQL> SHOW PARAMETER spfile; 

NAME   TYPE        VALUE
------ ----------- ----------------------------------------
spfile string      /u01/app/oracle/conf/orcl/spfileorcl.ora

If an spfile is used, a value is returned with the spfile location. If the spfile is not used, the string is empty. In that case, carry out the following command in order to start the database with the spfile:

SQL> CREATE spfile FROM pfile; 
SQL> STARTUP FORCE; 

Placing the database in the archivelog mode and activating Flashback

In order to make hot backups, the database needs to be in the archivelog mode.

Connect to your database and set the following parameters:

Note

Firstly, please be sure that paths used in this demo exist in your environment; if not, you can change it for existing paths in your machine.

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=2G;
SQL> ALTER SYSTEM SET db_recovery_file_dest ='/data/orcl/fast_recovery_area';
SQL> ALTER SYSTEM SET  log_archive_dest_1 ='LOCATION=/data/pdborcl/backups/archivelogs;
SQL> ALTER SYSTEM SET log_archive_dest_10 ='LOCATION=USE_DB_RECOVERY_FILE_DEST';
SQL> ALTER SYSTEM SET log_archive_format="orcl_%s_%t_%r.arc" SCOPE=spfile;
SQL> ALTER SYSTEM SET db_flashback_retention_target=720 SCOPE=spfile;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

Creating a new redo log group and associated files

As I have mentioned in Chapter 1, Understanding the Basics of Backup and Recovery, of this book, each group of redo log files needs to have at least two members in it; we will now create a new redo log group with two members in it.

Firstly, we will check the redo log groups and logfiles that already exist in the database. Have a look at the following command:

SQL> SET LINESIZE 200
SQL> SET PAGESIZE 200
SQL> COLUMN member FORMAT a50
SQL> COLUMN bytes FORMAT 999,999,999
SQL> SELECT group#, sequence#, bytes, members FROM v$log;
SQL> SELECT group#, member FROM v$logfile;
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 '/data/orcl/redologs
/redo_04a.rdo' SIZE 50m;
SQL> ALTER DATABASE ADD LOGFILE MEMBER'/data/orcl/redologs
/redo_04b.rdo' TO GROUP 4;