Chose this environment:
- Oracle Linux, because I wanted no conflicts with XE (eXpress Edition)
- Oracle Database 11.2 XE is free (limited to: 1G ram, 1 CPU, 12G database size) but enough to development (and little production)
- RMAN for backup which does not have any similar, not by the long shot
- APEX for development which does not have any similar (in its category), not by the long shot
Using a new virtual disk of 10G, created new VG and moved /u01/app/oracle to this one.
Using a new virtual disk of 15G, created new VG named "fra" for fast recovery area.
I have another post to help in the creation of additional space.
XE came without temporally file-system as I could see in alerts, but later Oracle itself create it. Check this.
In order to be accessed from remote, I had to modify firewall by adding rules 5 and 6 in INPUT chain with
- iptables -I INPUT 5 -p tcp --dport 1521 -j ACCEPT
- iptables -I INPUT 5 -p tcp --dport 8080 -j ACCEPT
- service iptables save
[root@oraclexe app]# service iptables status
Table: filter
Chain INPUT (policy DROP)
num target prot opt source destination
1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
2 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0
3 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
4 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
5 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:8080
6 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:1521
7 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain FORWARD (policy DROP)
num target prot opt source destination
1 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain OUTPUT (policy ACCEPT)
num target prot opt source destination
Used SQLPlus from my own laptop with Ubuntu 14.04 LTS where I installed Oracle Instant Client
- oracle-instantclient12.1-basiclite-12.1.0.2.0-1.x86_64.rpm
- oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
SQL> connect sys@oraclexe.cgc.local as sysdba
Enter password:
Connected.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Current log sequence 4
SQL> select group#, status, bytes from v$log;
GROUP# STATUS BYTES
---------- ---------------- ----------
1 INACTIVE 52428800
2 CURRENT 52428800
SQL> select group#, member from v$logfile;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
2
/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_b6hkw5o5_.log
1
/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_b6hkw4rv_.log
SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/XE/redo-c1.log','/fra/XE/onlinelog/redo-C2.log') size 50M;
Database altered.
SQL> select group#, status, bytes from v$log;
GROUP# STATUS BYTES
---------- ---------------- ----------
1 INACTIVE 52428800
2 CURRENT 52428800
3 UNUSED 52428800
SQL> select group#, status, bytes from v$log;
GROUP# STATUS BYTES
---------- ---------------- ----------
1 INACTIVE 52428800
2 ACTIVE 52428800
3 CURRENT 52428800
--Group 3 recently created is active, and using two files.
--Group 2 is ACTIVE (means that exists dirty buffers still not written)
--In order to force buffers be flushed, we use a CHECKPOINT
SQL> alter system checkpoint;
System altered.
SQL> select group#, status, bytes from v$log;
GROUP# STATUS BYTES
---------- ---------------- ----------
1 INACTIVE 52428800
2 INACTIVE 52428800
3 CURRENT 52428800
SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/XE/redo-A1.log','/fra/XE/onlinelog/redo-A2.log');
alter database add logfile group 1 ('/u01/app/oracle/oradata/XE/redo-A1.log','/fra/XE/onlinelog/redo-A2.log')
*
ERROR at line 1:
ORA-01184: logfile group 1 already exists
--It is necessary to use another format, weird?
SQL> alter database add logfile member '/u01/app/oracle/oradata/XE/redo-A1.log' to group 1;
Database altered.
SQL> alter database add logfile member '/fra/XE/onlinelog/redo-A2.log' to group 1;
Database altered.
SQL> select group#, member from v$logfile;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
2
/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_b6hkw5o5_.log
1
/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_b6hkw4rv_.log
3
/u01/app/oracle/oradata/XE/redo-c1.log
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
3
/fra/XE/onlinelog/redo-C2.log
1
/u01/app/oracle/oradata/XE/redo-A1.log
1
/fra/XE/onlinelog/redo-A2.log
6 rows selected.
SQL> alter database drop logfile member '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_b6hkw5o5_.log';
alter database drop logfile member '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_b6hkw5o5_.log'
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 2
ORA-01517: log member:
'/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_b6hkw5o5_.log'
--New members created has never been used, so I can not drop the old file
--until they be used at least one time.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#, status, bytes from v$log;
GROUP# STATUS BYTES
---------- ---------------- ----------
1 INACTIVE 52428800
2 INACTIVE 52428800
3 CURRENT 52428800
SQL> alter database drop logfile member '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_b6hkw5o5_.log';
Database altered.
SQL> alter database drop logfile member '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_b6hkw4rv_.log';
Database altered.
--Old logfiles droped!!
--Now we can move into ARCHIVELOG mode
--but it is needed to adjust the fast recovery area
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
SQL> show parameter recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 20G
recovery_parallelism integer 0
SQL> alter system set db_recovery_file_dest = '/fra';
System altered.
SQL> alter system set db_recovery_file_dest_size = 15G;
System altered.
SQL> show parameter recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /fra
db_recovery_file_dest_size big integer 15G
recovery_parallelism integer 0
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE.
SQL> startup mount;
SP2-0640: Not connected
--Remote connections are not allowed!!
--So, it is needed you have access locally to the server
[root@oraclexe fra]# sqlplus sys as sysdba ## See that it is a different server.
SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 16 20:50:57 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2227040 bytes
Variable Size 310379680 bytes
Database Buffers 96468992 bytes
Redo Buffers 4296704 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
--We can go back to the remote sqlplus again
SQL> connect sys@oraclexe.cgc.local as sysdba
Enter password:
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
--Now we are going to secure control files
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/XE/control.dbf
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/XE/con
trol.dbf
control_management_pack_access string NONE
--Wuao, only a control file!
--And named dbf as well
--Taking advantage of the opportunity
--I am going to use control files as recovery catalog
--so, I am going to modify 7 days to 14 days
--that it is the time that I am planning to use as recovery
SQL> alter system set control_file_record_keep_time = 14;
System altered.
SQL> alter system set control_file_record_keep_time = 14 scope=spfile;
System altered.
SQL> alter database backup controlfile to '/fra/XE/onlinelog/control.bk';
Database altered.
--Uhm, Oracle is wrong
--Alert says this
--alter database backup controlfile to trace
--Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_3385.trc
--Completed: alter database backup controlfile to trace
--But XE_ora_3385.trc says this
--@2|2|KY5j3umg1"3385|XE|
--M/KY5j3umg1~2191
--M?-Q5j3umg1~T931
SQL> alter database backup controlfile to trace as '/fra/XE/onlinelog/control.trc';
Database altered.
--Now I got it
SQL> create pfile from spfile;
File created.
SQL> ! ls -la /u01/app/oracle/product/11.2.0/xe/dbs/*XE.ora
-rw-r--r--. 1 oracle dba 823 Nov 17 10:42 /u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora
-rw-r-----. 1 oracle dba 2560 Nov 17 10:40 /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora
--initXE.ora is text parameter file, which we can edit
--spfileXE.ora is binary and used by default in startup
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
[root@oraclexe ~]# exit
logout
Connection to oraclexe.cgc.local closed.
elder@chancleta-PC:~$ ssh root@oraclexe.cgc.local
root@oraclexe.cgc.local's password:
Last login: Mon Nov 17 10:16:39 2014 from 172.16.1.13
[root@oraclexe ~]# sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 17 10:17:21 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ! cp /u01/app/oracle/oradata/XE/control.dbf /u01/app/oracle/oradata/XE/control01.ctl
SQL> ! chown oracle:dba /u01/app/oracle/oradata/XE/control01.ctl
SQL> ! cp /u01/app/oracle/oradata/XE/control.dbf /fra/XE/onlinelog/control02.ctl
SQL> ! chown oracle:dba /fra/XE/onlinelog/control02.ctl
--editing /data/product/11.2.0/xe/dbs/initXE.ora
-- this line "*.control_files=(/u01/app/oracle/oradata/XE/control01.ctl,/fra/XE/onlinelog/control02.ctl)"
SQL> startup mount pfile = '/u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora'
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2227040 bytes
Variable Size 314573984 bytes
Database Buffers 92274688 bytes
Redo Buffers 4296704 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2227040 bytes
Variable Size 314573984 bytes
Database Buffers 92274688 bytes
Redo Buffers 4296704 bytes
Database mounted.
Database opened.
--Created two similar scripts for weekly and daily backup
#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export PATH=$PATH:$ORACLE_HOME/bin
#export NLS_DATE_FORMAT=’DD-MON-YY HH24:MI:SS’
export DATE=$(date +%Y-%m-%d)
rman target=sys/inter1@localhost msgno log=/tmp/weekly_${DATE}.log <<EOF
run{
allocate channel c1 type disk;
report schema;
crosscheck backup of database;
crosscheck backup of archivelog all;
crosscheck backup of controlfile;
crosscheck backup of spfile;
crosscheck archivelog all;
delete noprompt force expired backup;
delete noprompt force expired archivelog all;
delete noprompt force expired copy;
delete noprompt force obsolete;
release channel c1;
}
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup AS COMPRESSED BACKUPSET incremental level 0 tag "WEEKLY" database
plus archivelog tag "Archivelog" delete input;
release channel c1;
release channel c2;
}
list backup summary;
list backupset;
list backup of controlfile;
exit;
EOF
--Installed on crontab
[root@oraclexe app]# crontab -l
#minute (0-59), hour (0-23, 0 = midnight), day (1-31), month (1-12), weekday (0-6, 0 = Sunday)
0 20 * * 0 /root/weekly.sh
0 20 * * 1-6 /root/daily.sh
