19 Backup & Restore a database with RMAN cold.

Again let's talk about backups. There are two types of backup: 

• Saying backups cold. In this case the base is inaccessible to users and it performs a simple file copy appropriate to restore in case of trouble.

• The hot backup. Aurrez you understand, it is a backup open database while user continues to work on their preferred base. It is usually this type of configuration found in production mode or can not afford to stop a database to save.

However, in some cases (Bureau study, Publisher, test environment, ..) it may be desirable to set up a backup strategy without it becoming too complicated. 

Again, a bit of thought before acting will save time later and avoid crisis situations. 

Start from the premise that the base is large and arrange for any table contains many columns of type CLOB. 

Notes: The table imports with CLOB columns are very long, because processed line by line. 

Solution 1: export for backup / import for restoration. 

Compared our case study, it may be possible but may take considerable time. 

So in case of data loss, restoration can be problematic:

• Duration of import / export relatively long.

• In panic mode, we do not think at all. And you can forget, for example to create tables containing CLOB (if you still use the utility imp.exe) to redirect tablespace (impdp.exe), and probably other issues that do not come to mind.

Solution 2: Stopping the base, and all copies of the database file or from a tape backup location. 

So to restore, just stop again and copy the database files. 

As everyone knows, database administration and recovery tests in most editors are a high priority. (Irony -)). So when restoring the probality missing a data file is quite high and hit the restoration of your database becomes more complicated. 

I suggest you stop the "DIY" and uses this ORACLE kindly put at our disposal. 

I am willing to nominate Recovery Manager (RMAN). 

To restore, it is necessary to have a backup. 

Before effecuter it, you connect yourself with a test user and create some tables and fill. 

Unless you (which should be the case) schemes with applications you need. 

Step 1:'s perform a cold backup database: 

Start> Run> cmd 

 sqlplus / nolog

 CONNECT / AS SYSDBA

 SHUTDOWN IMMEDIATE;

 QUIT

RMAN target /

RMAN> STARTUP MOUNT;

RMAN> BACKUP DATABASE FULL;

RMAN> ALTER DATABASE OPEN;

RMAN> QUIT

Note: Throughout this example, we see that RMAN has authority to stop or start a base! 

Step 2: Returning in SQL PLUS 

 TRUNCATE TABLE T1; 

 DROP TABLE T3; 

sqlplus / nolog 

SQL> CONNECT LAO / LAO - (this is my test with three user table t1, t2, t3. Original?)

Whoops! I wanted to do the opposite! 

Quickly go into panic mode; 

SQL> CONNECT / AS SYSDBA

SQL> SHUTDOWN IMMEDIATE;

And while my database is stopped, a colleague of goodwill deletes files related to SYSTEM tablespace (which of course were not saved) ==> laws MURPHY (maximum pain in the ass). 

You can always go with your dump! 

Step 3: stop the panic and remembered that we had taken the time to think to solve this kind of problems. 

Start> Run> cmd 

SET ORACLE_SID = oradb (if ever there are several bases on your computer). 

RMAN target /

RMAN> STARTUP MOUNT;

RMAN> RESTORE DATABASE;

.... If all goes well, a little message that says "End of restore in DD / MM / YY" 

RMAN> QUIT 

Just a little effort, 

sqlplus / nolog

SQL> CONNECT / AS SYSDBA

SQL> RECOVER DATABASE UNTIL CANCEL;

The insults you receive some of our friend ORACLE. 

Simply type without trembling and then CANCEL 

SQL> ALTER DATABASE OPEN RESETLOGS; 

Oh and magic, a message saying "Database changed" 

Let's crazy! 

SQL> CONNECT LAO / LAO 

Maintanant and you can check! the tables are all the, and with the same number of lines at the time of backup. 

Conclusion: This method is the same that the proposed solution number 2: one small detail is that RMAN handles for you to know which files to backup and especially where they are! 

2 Purge listener.log

Tonight I decided to talk about the listener.log. For information on your server, you have a "LISTENER" that listens for connection requests to the ORACLE database. 
Listener.log file keeps track of these requests. This can be useful in case of trouble. 
Firstly if you want to disable this trace, simply add the following line listener.ora file. 
TRACE_LEVEL_LISTENER = OFF 

I recall that the file in question is located in the directory / ORACLE_HOME / Network / Admin 
Listener.log file when it is in the directory / ORACLE_HOME / Network / log 

If you do not monitor this file, it may grow to substantial way. I think notammenet web applications that spend their time asking connections and to liberate. I happened to see several files listener.log GO. 

In addition to taking up space unnecessarily on the server, the log file is difficielment exploitable when the need arises. 

This is why it is highly advisable to purge from time to time. 
A relatively simple way to stop the listener consite (LSNRCTL STOP in a DOS console), then empty the file. See the delete and recreate. Well obviously, do not forget to restart the LISTENER (LSNRCTL START). 

Disadvantage: When the listener is stopped, it becomes impossible for a client to connect (ORA - 12541: TNS: no listener process). You agree quite easily that this method is not elegant. 

Another method requiring no worries for new client connections 

Example linux environment: LSNRCTL set log_file listener temp LSNRCTL set
rm listener
Log_file LSNRCTL set listener
rm listener_temp

The first line indicates that the log file is now listener_temp 
It deletes the old file (it can also be archived) 
Then reassigns a log file with name Current (log_file LSNRCTL set listener) 
Finally, it deletes the temporary file (rm listener_temp) 

Note: 

In a Windows environment, simply replace rm by del 

Ideally, it should generate an indicator that prevents the listener.log has reached a certain size in order to position themselves in a way "proactive" rather than reactive. 

2 Multiplexer Control Files Oracle (control-files)

Multiplexer Oracle control files.

How to add control files in Oracle.

How to multiplex the control files with a spile.

How to add a control file with a PFILE.

It is strongly recommended to multiplex control files also called ctl files (control-files).

Multiplexing can be implemented in the creation of the database or subsequently.

We'll add a control file in the next destination E: \ DATABASE \ -> CONTROL04.CTL.
Add an Oracle control file with a static parameter file PFILE.

Edit the PFILE is appointing initSID.ora, complete control file parameter by adding the path + name of the new control file.

 CONTROL_FILES = ('Coracledba\oradata\test_database\control.ctl'

                'D:\DATABASE\CONTROL02.CTL'

                 ‘E:\DATABASE\CONTROL03.CTL '

                 ‘E:\DATABASE\CONTROL04.CTL ')

Adds a control file with Oracle server parameter file SPFILE.

In console mode in Sql * Plus, use the ALTER SYSTEM SET parameter on CONTROL_FILES with the option SCOPE=SPFILE .

 SQL> ALTER SYSTEM SET CONTROL_FILES = 'Coracledba\oradata\test_database\control.ctl'

'D:\DATABASE\CONTROL02.CTL'

 E:\DATABASE\CONTROL03.CTL '

 E:\DATABASE\CONTROL04.CTL 'SCOPE =SPFILE;

Now we do an immediate shutdown of the database.

SQL> SHUTDOWN IMMEDIATE;
Copy a control file for Oracle multiplex.

Then the principle is the copy / paste a file ctl, we will copy either a control existing files 1, 2 or 3 in the new slot E:\DATABASE\ and then rename the file ctl CONTROL04.CTL.

Be careful to copy a file to multiplex coherent control (no worries if the database is closed normally ie without SHUTDOWN ABORT).

We start the Oracle database.

SQL> STARTUP;

You now have 4 control files.

You can also use this technique to move a control file or delete a file control.

7 Move Oracle Control File

Move an Oracle control file.

How to Move an Oracle Control File E:\DATABASE\CONTROL03.CTL to F:\DATABASE\CONTROL03.CTL?.

Two methods depending on the configuration PFILE or SPFILE in place.

If you use the static parameter file PFILE.

Edit the PFILE is appointing initSID.ora, change the setting in there CONTROL_FILES modifying the file path control. E: \ DB1 \ CONTROL03.CTL to F: \ DB1 \ CONTROL03.CTL

CONTROL_FILES = ('C:\Oracledba\oradata\test_database\control.ctl'  
'D:\DATABASE\CONTROL02.CTL'
'F:\DATABASE\CONTROL03.CTL')

SQL> shutdown immediate;

Then Cut / Paste the file control (or HOST MOVE) E:\DATABASE\CONTROL03.CTL to F:\DATABASE\CONTROL03.CTL then restart the database.

SQL> HOST MOVE

E:\DATABASE\CONTROL03.CTL F:\DATABASE\CONTROL03.CTL;

SQL> STARTUP

SQL> SHOW PARAMETER CONTROL_FILES;

If you use the server parameter file SPFILE.

 SQL> ALTER SYSTEM SET CONTROL_FILES = 'C:\Oracledba\oradata\test_database\control.ctl',
 'D:\DATABASE\CONTROL02.CTL',
'F:\DATABASE\CONTROL03.CTL' SCOPE = SPFILE;

SQL> shutdown immediate;

Then Cut / Paste the file control (or HOST MOVE) E: \ DB1 \ CONTROL03.CTL to F: \ DB1 \ CONTROL03.CTL then restart the database.

SQL> HOST MOVE E:\DATABASE\CONTROL03.CTL F:\DATABASE\CONTROL03.CTL;

SQL> STARTUP

SQL> SHOW PARAMETER CONTROL_FILES;