Tuesday, November 18, 2014

Oracle XE 11.2 on Oracle Linux 6.6

Trying to get a server for development and researching a long time, I decided immerse into Oracle Application Express (APEX). In spite Oracle offers free account in their hosting, I wanted my own server. This leads to an administration issue before you could begin using APEX, but I am certified in this field.
Chose this environment:
  1. Oracle Linux, because I wanted no conflicts with XE (eXpress Edition)
  2. Oracle Database 11.2 XE is free (limited to: 1G ram, 1 CPU, 12G database size) but enough to development (and little production)
  3. RMAN for backup which does not have any similar, not by the long shot
  4. APEX for development which does not have any similar (in its category), not by the long shot
For installation, I followed this.

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
Additional I installed only on may laptop (from where I will do the most of the work) rlwrap to get arrows key running. Works like a charm! Check this.

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

Sunday, November 16, 2014

Oracle SQLPlus with arrow keys

Oracle SQLPlus come with up/down arrows keys functionality. We can install and compile rlwrap to supply this.


Welcome to Ubuntu 14.04.1 LTS (GNU/Linux 3.13.0-39-generic x86_64)

 * Documentation:  https://help.ubuntu.com/

Last login: Sun Nov 16 13:12:50 2014 from localhost

elder@chancleta-PC:~$ sudo apt-get install libreadline-dev
[sudo] password for elder: 
Reading state information... Done
libreadline-dev is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 23 not upgraded.


elder@chancleta-PC:~$ cd tmp
elder@chancleta-PC:/tmp$ wget http://utopia.knoware.nl/~hlub/uck/rlwrap/rlwrap-0.
.42.tar.gz
--2014-11-16 13:15:05--  http://utopia.knoware.nl/~hlub/uck/rlwrap/rlwrap-0.42.tar.gz
Resolving utopia.knoware.nl (utopia.knoware.nl)... 213.197.30.29
Connecting to utopia.knoware.nl (utopia.knoware.nl)|213.197.30.29|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 279608 (273K) [application/x-gzip]
Saving to: ‘rlwrap-0.42.tar.gz.2’


 0% [                                       ] 0           --.-K/s              
14% [====>                                  ] 41,737       204KB/s             
28% [==========>                            ] 80,833       196KB/s             
42% [===============>                       ] 119,929      194KB/s             
60% [======================>                ] 169,161      199KB/s             
81% [==============================>        ] 228,529      216KB/s             
100%[======================================>] 279,608      228KB/s   in 1.2s   

2014-11-16 13:15:07 (228 KB/s) - ‘rlwrap-0.42.tar.gz.2’ saved [279608/279608]

elder@chancleta-PC:/tmp$ cd rlwrap-0.42/
elder@chancleta-PC:/tmp/rlwrap-0.42$ ./configure 
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking whether make supports nested variables... yes
checking whether make sets $(MAKE)... (cached) yes
checking whether build environment is sane... yes
checking for style of include used by make... GNU
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables... 
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking whether gcc understands -c and -o together... yes
checking dependency style of gcc... gcc3
checking how to run the C preprocessor... gcc -E
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking minix/config.h usability... no
checking minix/config.h presence... no
checking for minix/config.h... no
checking whether it is safe to define __EXTENSIONS__... yes
checking for gcc... (cached) gcc
checking whether we are using the GNU C compiler... (cached) yes
checking whether gcc accepts -g... (cached) yes
checking for gcc option to accept ISO C89... (cached) none needed
checking whether gcc understands -c and -o together... (cached) yes
checking dependency style of gcc... (cached) gcc3
checking how to run the C preprocessor... gcc -E
checking for perl... /usr/bin/perl
checking for strip... strip
checking for ANSI C header files... (cached) yes
checking for sys/wait.h that is POSIX.1 compatible... yes
checking errno.h usability... yes
checking errno.h presence... yes
checking for errno.h... yes
checking fcntl.h usability... yes
checking fcntl.h presence... yes
checking for fcntl.h... yes
checking libutil.h usability... no
checking libutil.h presence... no
checking for libutil.h... no
checking for stdlib.h... (cached) yes
checking for string.h... (cached) yes
checking sched.h usability... yes
checking sched.h presence... yes
checking for sched.h... yes
checking sys/ioctl.h usability... yes
checking sys/ioctl.h presence... yes
checking for sys/ioctl.h... yes
checking for sys/wait.h... (cached) yes
checking sys/resource.h usability... yes
checking sys/resource.h presence... yes
checking for sys/resource.h... yes
checking stddef.h usability... yes
checking stddef.h presence... yes
checking for stddef.h... yes
checking termios.h usability... yes
checking termios.h presence... yes
checking for termios.h... yes
checking for unistd.h... (cached) yes
checking for stdint.h... (cached) yes
checking time.h usability... yes
checking time.h presence... yes
checking for time.h... yes
checking getopt.h usability... yes
checking getopt.h presence... yes
checking for getopt.h... yes
checking regex.h usability... yes
checking regex.h presence... yes
checking for regex.h... yes
checking curses.h usability... no
checking curses.h presence... no
checking for curses.h... no
checking termcap.h usability... no
checking termcap.h presence... no
checking for termcap.h... no
checking for term.h... no
checking for ncurses/term.h... no
checking argument type of tputs putc function... char
checking whether your getopt() correctly understands double colons in option string... yes
checking for an ANSI C-conforming const... yes
checking for pid_t... yes
checking whether time.h and sys/time.h may both be included... yes
checking whether gcc needs -traditional... no
checking return type of signal handlers... void
checking for getopt_long... yes
checking for getopt_long... (cached) yes
checking for isastream... yes
checking for mkstemps... yes
checking for pselect... yes
checking for putenv... yes
checking for readlink... yes
checking for sched_yield... yes
checking for setenv... yes
checking for setitimer... yes
checking for setsid... yes
checking for setrlimit... yes
checking for sigaction... yes
checking for snprintf... yes
checking for strlcpy... no
checking for strlcat... no
checking for strnlen... yes
checking for system... yes
checking for openpty in -lutil... yes
checking for openpty... yes
checking for getpty... no
checking for grantpt... yes
checking for unlockpt... yes
checking for getpt... yes
checking for pty/tty type... checking pty.h usability... yes
checking pty.h presence... yes
checking for pty.h... yes
OPENPTY
configure: checking for pty ranges...
checking for tgetent... no
checking for tgetent in -ltinfo... yes
checking for readline in -lreadline... yes
checking for tigetnum... yes
checking readline/readline.h usability... yes
checking readline/readline.h presence... yes
checking for readline/readline.h... yes
checking whether your readline headers know about rl_set_screen_size... yes
checking whether your readline library knows about rl_set_screen_size... checking for rl_set_screen_size... yes
checking whether your readline knows about rl_variable_value... yes
checking whether your readline knows about rl_readline_version... yes
Will rlwrap find command's working directory under /proc/<commands pid>/cwd? let's see...
checking for /proc/12795/cwd/configure.ac... yes
checking whether we can find command line under <opt_proc_mountpoint>/<pid>/cmdline and mirror it by overwriting our own *argv... yes


checking that generated files are newer than configure... done
checking that generated files are newer than configure... done
configure: creating ./config.status
config.status: creating Makefile
config.status: creating filters/Makefile
config.status: creating doc/Makefile
config.status: creating src/Makefile
config.status: creating doc/rlwrap.man
config.status: creating config.h
config.status: config.h is unchanged
config.status: executing depfiles commands

Now do:
    make (or gmake)  to build rlwrap
    make check       for instructions how to test it
    make install     to install it

elder@chancleta-PC:/tmp/rlwrap-0.42$ make
make  all-recursive
make[1]: Entering directory `/tmp/rlwrap-0.42'
Making all in doc
make[2]: Entering directory `/tmp/rlwrap-0.42/doc'
sed -e 's#@DATADIR@#/usr/local/share#'  rlwrap.man > rlwrap.1
make[2]: Leaving directory `/tmp/rlwrap-0.42/doc'
Making all in src
make[2]: Entering directory `/tmp/rlwrap-0.42/src'
gcc -DHAVE_CONFIG_H -I. -I..    -DDATADIR=\"/usr/local/share\"  -g -O2 -MT main.o -MD -MP -MF .deps/main.Tpo -c -o main.o main.c
mv -f .deps/main.Tpo .deps/main.Po
gcc -DHAVE_CONFIG_H -I. -I..    -DDATADIR=\"/usr/local/share\"  -g -O2 -MT signals.o -MD -MP -MF .deps/signals.Tpo -c -o signals.o signals.c
mv -f .deps/signals.Tpo .deps/signals.Po
gcc -DHAVE_CONFIG_H -I. -I..    -DDATADIR=\"/usr/local/share\"  -g -O2 -MT readline.o -MD -MP -MF .deps/readline.Tpo -c -o readline.o readline.c
mv -f .deps/readline.Tpo .deps/readline.Po
gcc -DHAVE_CONFIG_H -I. -I..    -DDATADIR=\"/usr/local/share\"  -g -O2 -MT pty.o -MD -MP -MF .deps/pty.Tpo -c -o pty.o pty.c
mv -f .deps/pty.Tpo .deps/pty.Po
gcc -DHAVE_CONFIG_H -I. -I..    -DDATADIR=\"/usr/local/share\"  -g -O2 -MT completion.o -MD -MP -MF .deps/completion.Tpo -c -o completion.o completion.c
mv -f .deps/completion.Tpo .deps/completion.Po
gcc -DHAVE_CONFIG_H -I. -I..    -DDATADIR=\"/usr/local/share\"  -g -O2 -MT term.o -MD -MP -MF .deps/term.Tpo -c -o term.o term.c
term.c: In function ‘my_tgetstr’:
term.c:54:21: warning: initialization makes pointer from integer without a cast [enabled by default]
   char *stringcap = tgetstr(id, &tb); /*  rl_get_termcap(id) only gets capabilities used by readline */
                     ^
term.c: In function ‘cursor_hpos’:
term.c:176:16: warning: assignment makes pointer from integer without a cast [enabled by default]
   instantiated = tgoto(term_cursor_hpos, 0, col); /* tgoto with a command that takes one parameter: parameter goes to 2nd arg ("vertical position"). */
                ^
mv -f .deps/term.Tpo .deps/term.Po
gcc -DHAVE_CONFIG_H -I. -I..    -DDATADIR=\"/usr/local/share\"  -g -O2 -MT ptytty.o -MD -MP -MF .deps/ptytty.Tpo -c -o ptytty.o ptytty.c
mv -f .deps/ptytty.Tpo .deps/ptytty.Po
gcc -DHAVE_CONFIG_H -I. -I..    -DDATADIR=\"/usr/local/share\"  -g -O2 -MT utils.o -MD -MP -MF .deps/utils.Tpo -c -o utils.o utils.c
mv -f .deps/utils.Tpo .deps/utils.Po
gcc -DHAVE_CONFIG_H -I. -I..    -DDATADIR=\"/usr/local/share\"  -g -O2 -MT string_utils.o -MD -MP -MF .deps/string_utils.Tpo -c -o string_utils.o string_utils.c
mv -f .deps/string_utils.Tpo .deps/string_utils.Po
gcc -DHAVE_CONFIG_H -I. -I..    -DDATADIR=\"/usr/local/share\"  -g -O2 -MT malloc_debug.o -MD -MP -MF .deps/malloc_debug.Tpo -c -o malloc_debug.o malloc_debug.c
mv -f .deps/malloc_debug.Tpo .deps/malloc_debug.Po
gcc -DHAVE_CONFIG_H -I. -I..    -DDATADIR=\"/usr/local/share\"  -g -O2 -MT filter.o -MD -MP -MF .deps/filter.Tpo -c -o filter.o filter.c
mv -f .deps/filter.Tpo .deps/filter.Po
gcc -DDATADIR=\"/usr/local/share\"  -g -O2   -o rlwrap main.o signals.o readline.o pty.o completion.o term.o ptytty.o utils.o string_utils.o malloc_debug.o filter.o  -lutil  -lreadline -ltinfo
make[2]: Leaving directory `/tmp/rlwrap-0.42/src'
Making all in filters
make[2]: Entering directory `/tmp/rlwrap-0.42/filters'
make[2]: Nothing to be done for `all'.
make[2]: Leaving directory `/tmp/rlwrap-0.42/filters'
make[2]: Entering directory `/tmp/rlwrap-0.42'
make[2]: Leaving directory `/tmp/rlwrap-0.42'
make[1]: Leaving directory `/tmp/rlwrap-0.42'
]0;elder@chancleta-PC: /tmp/rlwrap-0.42 elder@chancleta-PC:/tmp/rlwrap-0.42$ sudo make install
Making install in doc
make[1]: Entering directory `/tmp/rlwrap-0.42/doc'
make[2]: Entering directory `/tmp/rlwrap-0.42/doc'
make[2]: Nothing to be done for `install-exec-am'.
 /bin/mkdir -p '/usr/local/share/man/man1'
 /usr/bin/install -c -m 644 rlwrap.1 '/usr/local/share/man/man1'
make[2]: Leaving directory `/tmp/rlwrap-0.42/doc'
make[1]: Leaving directory `/tmp/rlwrap-0.42/doc'
Making install in src
make[1]: Entering directory `/tmp/rlwrap-0.42/src'
make[2]: Entering directory `/tmp/rlwrap-0.42/src'
 /bin/mkdir -p '/usr/local/bin'
  /usr/bin/install -c rlwrap '/usr/local/bin'
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/rlwrap-0.42/src'
make[1]: Leaving directory `/tmp/rlwrap-0.42/src'
Making install in filters
make[1]: Entering directory `/tmp/rlwrap-0.42/filters'
make[2]: Entering directory `/tmp/rlwrap-0.42/filters'
make[2]: Nothing to be done for `install-exec-am'.
 /bin/mkdir -p '/usr/local/share/man/man3'
 /usr/bin/install -c -m 644 RlwrapFilter.3pm '/usr/local/share/man/man3'
make[2]: Leaving directory `/tmp/rlwrap-0.42/filters'
make[1]: Leaving directory `/tmp/rlwrap-0.42/filters'
make[1]: Entering directory `/tmp/rlwrap-0.42'
make[2]: Entering directory `/tmp/rlwrap-0.42'
make[2]: Nothing to be done for `install-exec-am'.
 /bin/mkdir -p '/usr/local/share/rlwrap'
 /bin/mkdir -p '/usr/local/share/rlwrap/filters'
 /usr/bin/install -c -m 644  filters/README filters/RlwrapFilter.pm filters/RlwrapFilter.3pm filters/count_in_prompt filters/pipeto filters/logger filters/null filters/unbackspace filters/pipeline filters/ftp_filter filters/history_format filters/simple_macro filters/template filters/scrub_prompt filters/paint_prompt filters/censor_passwords filters/listing '/usr/local/share/rlwrap/filters'
 /bin/mkdir -p '/usr/local/share/rlwrap/completions'
 /usr/bin/install -c -m 644  completions/testclient completions/coqtop '/usr/local/share/rlwrap/completions'
make  install-data-hook
make[3]: Entering directory `/tmp/rlwrap-0.42'
chmod a+x /usr/local/share/rlwrap/filters/* 
make[3]: Leaving directory `/tmp/rlwrap-0.42'
make[2]: Leaving directory `/tmp/rlwrap-0.42'
make[1]: Leaving directory `/tmp/rlwrap-0.42'
elder@chancleta-PC:/tmp/rlwrap-0.42$ alias sqlplus='rlwrap sqlplus64' #in my case I have sqlplus64 installed
elder@chancleta-PC:/tmp/rlwrap-0.42$ echo "alias sqlplus='rlwrap sqlplus64'" >> ~/.bashrc 
elder@chancleta-PC:/tmp/rlwrap-0.42$ exit

Monday, November 10, 2014

Resizing Linux


Having a Ubuntu Server 14.04 LTS running virtualized with a minimal installation in a 4G virtual disk:
I was thinking to create VM clones from the pattern, but in order to be useful, it is necessary reconfigure the disk space.
I made this in a new virtual machine, not in production, so you can do it at your own risk.

  1. add a 100G virtual disk to the clone
  2. created partition LVM
  3. created physical disk
  4. added to volume group
  5. resized root to 4G
  6. resized swap to 4G
  7. created new logical volume for var, usr, home and tmp
  8. moved var, usr, home and tmp to new space.

 

 Checking what we have. We see root and swap. We see a new disk with 100G.


Welcome to Ubuntu 14.04.1 LTS (GNU/Linux 3.13.0-32-generic i686)
* Documentation: https://help.ubuntu.com/
Last login: Mon Nov 10 09:46:27 2014 from 172.16.1.11
elder@JeOS:~$ sudo lsblk -o NAME,FSTYPE,SIZE,MOUNTPOINT,LABEL
[sudo] password for elder:
NAME FSTYPE SIZE MOUNTPOINT LABEL
sda 4G
├─sda1 ext2 243M /boot
├─sda2 1K
└─sda5 LVM2_member 3.8G
├─JeOS--vg-root (dm-0) ext4 3.2G /
└─JeOS--vg-swap_1 (dm-1) swap 508M [SWAP]
sdb 100G
sr0 1024M

 

Creating partition.


elder@JeOS:~$sudo sfdisk /dev/sdb << EOF
> ,,8e
> EOF
Checking that no-one is using this disk right now ...
OK
Disk /dev/sdb: 13054 cylinders, 255 heads, 63 sectors/track
Old situation:
Units = cylinders of 8225280 bytes, blocks of 1024 bytes, counting from 0
Device Boot Start End #cyls #blocks Id System
/dev/sdb1 0 - 0 0 0 Empty
/dev/sdb2 0 - 0 0 0 Empty
/dev/sdb3 0 - 0 0 0 Empty
/dev/sdb4 0 - 0 0 0 Empty
New situation:
Units = cylinders of 8225280 bytes, blocks of 1024 bytes, counting from 0
Device Boot Start End #cyls #blocks Id System
/dev/sdb1 0+ 13053 13054- 104856254+ 8e Linux LVM
/dev/sdb2 0 - 0 0 0 Empty
/dev/sdb3 0 - 0 0 0 Empty
/dev/sdb4 0 - 0 0 0 Empty
Warning: no primary partition is marked bootable (active)
This does not matter for LILO, but the DOS MBR will not boot this disk.
Successfully wrote the new partition table
Re-reading the partition table ...
If you created or changed a DOS partition, /dev/foo7, say, then use dd(1)
to zero the first 512 bytes: dd if=/dev/zero of=/dev/foo7 bs=512 count=1
(See fdisk(8).)
elder@JeOS:~$ sudo lsblk -o NAME,FSTYPE,SIZE,MOUNTPOINT,LABEL
NAME FSTYPE SIZE MOUNTPOINT LABEL
sda 4G
├─sda1 ext2 243M /boot
├─sda2 1K
└─sda5 LVM2_member 3.8G
├─JeOS--vg-root (dm-0) ext4 3.2G /
└─JeOS--vg-swap_1 (dm-1) swap 508M [SWAP]
sdb 100G
└─sdb1 100G
sr0 1024M
elder@JeOS:~$sudo pvdisplay
--- Physical volume ---
PV Name /dev/sda5
VG Name JeOS-vg
PV Size 3.76 GiB / not usable 2.00 MiB
Allocatable yes
PE Size 4.00 MiB
Total PE 962
Free PE 10
Allocated PE 952
PV UUID Xp75Xb-3Tj1-BwEm-QB79-AS2l-ZXsu-pYqzC2

 

Creating physical volume.


elder@JeOS:~$ sudo pvcreate /dev/sdb1
Physical volume "/dev/sdb1" successfully created
elder@JeOS:~$ sudo pvscan
PV /dev/sda5 VG JeOS-vg lvm2 [3.76 GiB / 40.00 MiB free]
PV /dev/sdb1 lvm2 [100.00 GiB]
Total: 2 [103.76 GiB] / in use: 1 [3.76 GiB] / in no VG: 1 [100.00 GiB]

 

Attaching to existing volume.

 

elder@JeOS:~$ sudo vgextend JeOS-vg /dev/sdb1
Volume group "JeOS-vg" successfully extended
elder@JeOS:~$ sudo pvscan
PV /dev/sda5 VG JeOS-vg lvm2 [3.76 GiB / 40.00 MiB free]
PV /dev/sdb1 VG JeOS-vg lvm2 [100.00 GiB / 100.00 GiB free]
Total: 2 [103.75 GiB] / in use: 2 [103.75 GiB] / in no VG: 0 [0 ]
elder@JeOS:~$ sudo lvscan
ACTIVE '/dev/JeOS-vg/root' [3.22 GiB] inherit
ACTIVE '/dev/JeOS-vg/swap_1' [508.00 MiB] inherit

 

Extending root to 4G.

 

elder@JeOS:~$ sudo lvextend -L4G /dev/JeOS-vg/root
Extending logical volume root to 4.00 GiB
Logical volume root successfully resized
elder@JeOS:~$ sudo df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/JeOS--vg-root 3260568 643788 2431436 21% /
none 4 0 4 0% /sys/fs/cgroup
udev 509464 4 509460 1% /dev
tmpfs 102600 332 102268 1% /run
none 5120 0 5120 0% /run/lock
none 512992 0 512992 0% /run/shm
none 102400 0 102400 0% /run/user
/dev/sda1 240972 22406 206125 10% /boot
elder@JeOS:~$ sudo resize2fs /dev/JeOS-vg/root
resize2fs 1.42.9 (4-Feb-2014)
Filesystem at /dev/JeOS-vg/root is mounted on /; on-line resizing required
old_desc_blocks = 1, new_desc_blocks = 1
The filesystem on /dev/JeOS-vg/root is now 1048576 blocks long.
elder@JeOS:~$ sudo df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/JeOS--vg-root 4063424 644620 3202004 17% /
none 4 0 4 0% /sys/fs/cgroup
udev 509464 4 509460 1% /dev
tmpfs 102600 332 102268 1% /run
none 5120 0 5120 0% /run/lock
none 512992 0 512992 0% /run/shm
none 102400 0 102400 0% /run/user
/dev/sda1 240972 22406 206125 10% /boot

 

 Extending swap to 4G.

 

elder@JeOS:~$ cat /proc/swaps
Filename Type Size Used Priority
/dev/dm-1 partition 520188 0 -1
elder@JeOS:~$ sudo lvscan
ACTIVE '/dev/JeOS-vg/root' [4.00 GiB] inherit
ACTIVE '/dev/JeOS-vg/swap_1' [508.00 MiB] inherit
elder@JeOS:~$ sudo swapoff /dev/JeOS-vg/swap_1
elder@JeOS:~$ sudo lvextend -L4G /dev/JeOS-vg/swap_1
Extending logical volume swap_1 to 4.00 GiB
Logical volume swap_1 successfully resized
elder@JeOS:~$ sudo mkswap /dev/JeOS-vg/swap_1
mkswap: /dev/JeOS-vg/swap_1: warning: don't erase bootbits sectors
on whole disk. Use -f to force.
Setting up swapspace version 1, size = 4194300 KiB
no label, UUID=1da2e07b-9f6d-45e1-888b-4461c18a83da
elder@JeOS:~$ sudo swapon /dev/JeOS-vg/swap_1
elder@JeOS:~$ sudo cat /proc/swaps
Filename Type Size Used Priority
/dev/dm-1 partition 4194300 0 -1
elder@JeOS:~$ sudo lvscan
ACTIVE '/dev/JeOS-vg/root' [4.00 GiB] inherit
ACTIVE '/dev/JeOS-vg/swap_1' [4.00 GiB] inherit

 

Creating logical volumes and formating for var, usr, home and tmp. 

 

elder@JeOS:~$ sudo lvcreate -L 800 -n home JeOS-vg
Logical volume "home" created
elder@JeOS:~$ sudo lvcreate -L 1G -n tmp JeOS-vg
Logical volume "tmp" created
elder@JeOS:~$ sudo lvcreate -L 80G -n var JeOS-vg
Logical volume "var" created
elder@JeOS:~$ sudo lvcreate -L 10G -n usr JeOS-vg
Logical volume "usr" created
elder@JeOS:~$ sudo mkfs.ext4 /dev/JeOS-vg/home
mke2fs 1.42.9 (4-Feb-2014)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
51296 inodes, 204800 blocks
10240 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=209715200
7 block groups
32768 blocks per group, 32768 fragments per group
7328 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840
Allocating group tables: 0/7 done
Writing inode tables: 0/7 done
Creating journal (4096 blocks): done
Writing superblocks and filesystem accounting information: 0/7 done
elder@JeOS:~$ sudo mkfs.ext4 /dev/JeOS-vg/tmp
mke2fs 1.42.9 (4-Feb-2014)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
65536 inodes, 262144 blocks
13107 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=268435456
8 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376
Allocating group tables: 0/8 done
Writing inode tables: 0/8 done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: 0/8 done
elder@JeOS:~$ sudo mkfs.ext4 /dev/JeOS-vg/var
mke2fs 1.42.9 (4-Feb-2014)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
5242880 inodes, 20971520 blocks
1048576 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=0
640 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424, 20480000
Allocating group tables: 0/640 done
Writing inode tables: 0/640 done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: 0/640 done
elder@JeOS:~$ sudo mkfs.ext4 /dev/JeOS-vg/usr
mke2fs 1.42.9 (4-Feb-2014)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
655360 inodes, 2621440 blocks
131072 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2684354560
80 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632
Allocating group tables: 0/80 done
Writing inode tables: 0/80 done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: 0/80 done

 

Here we need go into single user mode in order to move var, usr, home and tmp to the new locations created. All connections will be lost and be sure that you have access to console. 

 

elder@JeOS:~$ sudo telinit s #Single user mode, out of production
[sudo] password for elder:

 

Tmp is a temporaly folder and a copy is not needed. Mounting var, usr and home in temporal locations. 

 


root@JeOS:~# mkdir /mnt/home #it is not needed a tmp copy
root@JeOS:~# mkdir /mnt/var
root@JeOS:~# mkdir /mnt/usr
root@JeOS:~# mount /dev/mapper/JeOS—vg-home /mnt/home
root@JeOS:~# mount /dev/mapper/JeOS—vg-var /mnt/var
root@JeOS:~# mount /dev/mapper/JeOS—vg-usr /mnt/usr

Coping data  var, usr and home ...

root@JeOS:~# cp -ax /home/* /mnt/home
root@JeOS:~# cp -ax /var/* /mnt/var
root@JeOS:~# cp -ax /usr/* /mnt/usr
root@JeOS:~# mv /home /home.old
root@JeOS:~# mv /var /var.old
root@JeOS:~# mv /usr /usr.old
root@JeOS:~# mv /tmp /tmp.old
root@JeOS:~# mkdir /home
root@JeOS:~# mkdir /var
root@JeOS:~# mkdir /usr
root@JeOS:~# mkdir /tmp
root@JeOS:~# chmod 1777 /tmp #permissions for tmp

 

Modifing fstab with the /dev/mapper links founded in lsblk.


root@JeOS:~# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 4G 0 disk
|-sda1 8:1 0 243M 0 part /boot
|-sda2 8:2 0 1K 0 part
`-sda5 8:5 0 3.8G 0 part
|-JeOS--vg-root (dm-0) 252:0 0 4G 0 lvm /
`-JeOS--vg-swap_1 (dm-1) 252:1 0 4G 0 lvm [SWAP]
sdb 8:16 0 100G 0 disk
`-sdb1 8:17 0 100G 0 part
|-JeOS--vg-root (dm-0) 252:0 0 4G 0 lvm /
|-JeOS--vg-swap_1 (dm-1) 252:1 0 4G 0 lvm [SWAP]
|-JeOS--vg-home (dm-2) 252:2 0 800M 0 lvm
|-JeOS--vg-tmp (dm-3) 252:3 0 1G 0 lvm
|-JeOS--vg-var (dm-4) 252:4 0 80G 0 lvm
`-JeOS--vg-usr (dm-5) 252:5 0 10G 0 lvm
sr0 11:0 1 1024M 0 rom
root@JeOS:~# echo '/dev/mapper/JeOS--vg-home /home ext4 defaults 0 0' >> /etc/fstab
root@JeOS:~# echo '/dev/mapper/JeOS--vg-var /var ext4 defaults 0 0 ' >> /etc/fstab
root@JeOS:~# echo '/dev/mapper/JeOS--vg-usr /usr ext4 defaults 0 0 ' >> /etc/fstab
root@JeOS:~# echo '/dev/mapper/JeOS--vg-tmp /tmp ext4 defaults 0 0 ' >>/etc/fstab
root@JeOS:~# cat /etc/fstab
# /etc/fstab: static file system information.
#
# Use 'blkid' to print the universally unique identifier for a
# device; this may be used with UUID= as a more robust way to name devices
# that works even if disks are added and removed. See fstab(5).
#
# <file system> <mount point> <type> <options> <dump> <pass>
/dev/mapper/JeOS--vg-root / ext4 errors=remount-ro 0 1
# /boot was on /dev/sda1 during installation
UUID=fa4cb140-0c28-4c06-823e-2f0bb11d08ef /boot ext2 defaults 0 2
/dev/mapper/JeOS--vg-swap_1 none swap sw 0 0
/dev/mapper/JeOS--vg-home /home ext4 defaults 0 0
/dev/mapper/JeOS--vg-var /var ext4 defaults 0 0
/dev/mapper/JeOS--vg-usr /usr ext4 defaults 0 0
/dev/mapper/JeOS--vg-tmp /tmp ext4 defaults 0 0
/dev/mapper/JeOS--vg-root on / type ext4 (rw,errors=remount-ro)
proc on /proc type proc (rw,noexec,nosuid,nodev)
sysfs on /sys type sysfs (rw,noexec,nosuid,nodev)
none on /sys/fs/cgroup type tmpfs (rw)
none on /sys/fs/fuse/connections type fusectl (rw)
none on /sys/kernel/debug type debugfs (rw)
none on /sys/kernel/security type securityfs (rw)
udev on /dev type devtmpfs (rw,mode=0755)
devpts on /dev/pts type devpts (rw,noexec,nosuid,gid=5,mode=0620)
tmpfs on /run type tmpfs (rw,noexec,nosuid,size=10%,mode=0755)
none on /run/lock type tmpfs (rw,noexec,nosuid,nodev,size=5242880)
none on /run/shm type tmpfs (rw,nosuid,nodev)
none on /run/user type tmpfs (rw,noexec,nosuid,nodev,size=104857600,mode=0755)
none on /sys/fs/pstore type pstore (rw)
/dev/sda1 on /boot type ext2 (rw)
/dev/mapper/JeOS--vg-home on /home type ext4 (rw)
/dev/mapper/JeOS--vg-var on /var type ext4 (rw)
/dev/mapper/JeOS--vg-usr on /usr type ext4 (rw)
/dev/mapper/JeOS--vg-tmp on /tmp type ext4 (rw)

 

Mounting new entries and checking.

 

root@JeOS:~# mount -a
root@JeOS:~# ls /var
backups
cache
lib
local
lock
log
lost+found
mail
opt
run
spool
tmp
root@JeOS:~# reboot

 

For me everything was good after rebooting, so clean your own dirt.

 


elder@JeOS:~$ sudo rm -d /mnt/*
elder@JeOS:~$ sudo rm -r /*.old



Tuesday, August 5, 2014

Recuperar database crashed in another server

Para eso necesitamos que el backup de la base de datos esté en el nas y que los logs esten en el nas. El log file contiene la última transacción, y si el server se va completamente, con el backup y el log file podemos restaurarlo. Necesitamos backup consistentes y es muy bueno usar backup devices porque nos da el historial de los backup. El backup de los logs es necesario para descargar el log file y no dejar que crezca, y si lo usamos tendremos que tenerlo en cuenta para el restore.Para este ejemplo vamos a usar db1 con la database SmartCenturyUSA. Dado que tenemos una tabla “elder” que ya esta en el backup.

select * from elder;
id    name
1    Elderito
2    Ernestico
update elder set name='Elder' where name='Elderito';
--este cambio no esta en el backup, pero si en el log file (el cual esta en el nas).
select * from elder;
1    Elder
2    Ernestico

Ahora apagamos db1. Ojo, ha habido un checkpoint y por ende un flush de los buffers de la database hacia el fichero mdf, pero no me interesa ese mdf, porque ese server lo doy por perdido. Tambien me esta quedando el log file cerrado correctamente. Dios quiera que ante un evento, el log file no nos quede dañado! Solo nos queda hacer backup del log con más frecuencia. He leído de hacerlo cada 15 minutos pero nos daría 96 backup al dia y complicaria la recuperación, que por cierto no es automática (Oracle si). Hay que seleccionar backup por backup que participará en en el restore y si el dba se equivoca seleccionando los backup, va mal la cosa. Es decision de Intradata la frecuencia que quiera de los backup de los logs.

Encendemos db4 que sera el server que vendrá al rescate de db1 (que supuestamente se jodio completo).

Ante todo renombramos el log file y lo terminamos en -old para evitar ser reescrito.
Creamos un base de datos vacia con el mismo nombre de la que queremos recuperar e inmediatamente la ponemos offline. Esto es solo para poder backear el log file.

create database SmartCenturyUSA;
alter database SmartCenturyUSA set offline;


Tomamos nota del nombre del ldf creado. IMPORTANTE.
Borramos el mdf y el ldf creado.
Copiamos el ldf que termina en -old como el nombre del ldf que espera la database dummy.
Ahora intentamos ponerla online y nos dará error, pero es necesario para poder hacer el backup. Offline impide el backup! El backup quedará en la raiz del disco C:. Acto seguido la ponemos offline y la borramos. Si no la borramos, no conseguí hacer el restore nunca.

USE master;
GO
ALTER DATABASE SmartCenturyUSA SET ONLINE;
GO
backup log SmartCenturyUSA to disk = 'c:\tail.bak' with init, no_truncate;
GO
ALTER DATABASE SmartCenturyUSA SET OFFLINE;
GO
DROP DATABASE SmartCenturyUSA;
GO

A pesar de los errores que da el sql, tendremos un backup tail.bak en la raiz del disco duro y la database dummy borrada. Ya podemos borrar el ldf que se uso en la dummy. Nos queda restaurar el backup.

En databases hacemos click derecho y cogemos restore database.
From device, click en “...”
Backup media, cogemos backup device y click en add.
Seleccionamos db1backup.
Seleccionamos los backup a restaurar.
Mirando arriba, seleccionamos la database SmartCenturyUSA.
Por la izquierda, opciones. Ajustamos los nombres de los ficheros mdf y ldf.
Marcamos Overwrite the existing database(WITH REPLACE)
Recovery state= segunda opcion (RESTORE WITH NORECOVERY) Esto me permite agregar el tail.bak después.

Aparece la database con un mensaje (Restoring…) que significa que esta esperando mas restore.

Encima de ella hacemos restore.
From device y click “...”
File, Add, C:, tail.bak
Seleccionamos,  el cuadrito del restore de ese file.

select * from elder;
id    name
1    Elder
2    Ernestico

Tenemos la ultima transacion en db4

Tuesday, July 15, 2014

AsteriskNow 3.0.1


I wanted a phone system for an small company and I chose AsteriskNow because is just asterisk and freepbx, nothing more. I am going to use Polycom 330 phones and a TDM410 with four FXO and echo cancellation card.


Install cdrom.
Check connectivity.
Yum update.
nano /etc/xinetd.d/tftp

service tftp
{
socket_type = dgram
protocol = udp
wait = yes
user = root
server = /usr/sbin/in.tftpd
server_args = -s -c -v /tftpboot
disable = no
per_source = 11
cps = 100 2
flags = IPv4
}

-c allows create new files
-v logs into /var/log/messages
disable was yes, must be changed to no
server_args was pointing to /var/lib/tftpboot, must be to /tftpboot

service xinetd restart

Use the web interface, in the first execution ask about create an administrator user and password.
Admin, module admin, marcar “basic” “extended” “unsupported”, check online, process button.
Admin, module admin, marcar “OSS PBX End Point Manager”, process button.
Connectivity, “OSS End Point Advance Setting”
set IP address of phone server
set Time Zone
set Time Server to 0.north-america.pool.ntp.org
when you click button “update globals” you must get “updated”. In case of error, check.
Connectivity, “OSS End Point Package Manager”, install Polycom, install firmware “SoundPoint IP 3xx [320,330]”, enable “SoundPoint IP 330”.

Phones need to find a tftp server to download provisioning. You can do it manually by using the phone menu, or using a dhcp which pass the parameter. With windows dhcp, use the option 66 with parameter “tftp://172.16.1.75”. For VyOS “set service dhcp-server shared-network-name CASA subnet 172.16.1.0/24 tftp-server-name 172.16.1.75”.
Them, connectivity, “OSS End Point Device List”, search. It is likely that your test phone appears and link it to any extension created.

At this point, check your tftpboot folder and change permissions for logs subfolder to 777 and delete any files inside logs with length 0. Your phone must have solid black the icon and you can check your connection with asterisk dialing “*65+line1” hearing your extension number.

Reference tail /var/log/messages. Connection refused looks weird because everything is OK. I do not why these messages.

Jul 15 14:42:20 localhost in.tftpd[3021]: RRQ from 172.16.1.74 filename 2345-12200-001.bootrom.ld
Jul 15 14:42:20 localhost in.tftpd[3022]: RRQ from 172.16.1.74 filename 0004f21a2ac5.cfg
Jul 15 14:42:21 localhost in.tftpd[3021]: tftpd: read(ack): Connection refused
Jul 15 14:42:21 localhost in.tftpd[3023]: RRQ from 172.16.1.74 filename 2345-12200-001.sip.ld
Jul 15 14:42:22 localhost in.tftpd[3023]: tftpd: read(ack): Connection refused
Jul 15 14:42:26 localhost in.tftpd[3024]: WRQ from 172.16.1.74 filename logs/0004f21a2ac5-boot.log
Jul 15 14:42:57 localhost in.tftpd[3025]: RRQ from 172.16.1.74 filename 2345-12200-001.bootrom.ld
Jul 15 14:42:58 localhost in.tftpd[3025]: tftpd: read(ack): Connection refused
Jul 15 14:42:58 localhost in.tftpd[3026]: RRQ from 172.16.1.74 filename 0004f21a2ac5.cfg
Jul 15 14:42:59 localhost in.tftpd[3027]: RRQ from 172.16.1.74 filename 2345-12200-001.sip.ld
Jul 15 14:43:00 localhost in.tftpd[3027]: tftpd: read(ack): Connection refused
Jul 15 14:43:00 localhost in.tftpd[3028]: RRQ from 172.16.1.74 filename 0004f21a2ac5_reg.cfg
Jul 15 14:43:02 localhost in.tftpd[3029]: RRQ from 172.16.1.74 filename sip_3333.cfg
Jul 15 14:43:06 localhost in.tftpd[3030]: RRQ from 172.16.1.74 filename overrides/0004f21a2ac5-phone.cfg
Jul 15 14:43:07 localhost in.tftpd[3031]: RRQ from 172.16.1.74 filename overrides/0004f21a2ac5-web.cfg
Jul 15 14:43:08 localhost in.tftpd[3032]: RRQ from 172.16.1.74 filename licenses/000000000000-license.cfg
Jul 15 14:43:08 localhost in.tftpd[3033]: RRQ from 172.16.1.74 filename licenses/0004f21a2ac5-license.cfg
Jul 15 14:43:08 localhost in.tftpd[3034]: RRQ from 172.16.1.74 filename contacts/0004f21a2ac5-directory.xml
Jul 15 14:43:13 localhost in.tftpd[3035]: WRQ from 172.16.1.74 filename logs/0004f21a2ac5-app.log

Now, we need both lines registered with asterisk. Go,
Connectivity, “OSS End Point Template Manager”, Add new template, template name=two-lines, product select=Soundpoint IP 3xxx, clone template from=SoundPouint IP 330, save.
In current templates, edit, $mac_reg.cfg, find a line like this
reg.{$line}.lineKeys="{$lineKeys}"
replace it with
reg.{$line}.lineKeys="2"
save.

Connectivity, “OSS End Point Device List”, select two-lines template for each phone.
Done!