Różności …

[oracle@vmlnx01 admin]$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener.  This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action:
//  - Wait a moment and try to connect a second time.
//  - Check which services are currently known by the listener by executing:
//    lsnrctl services
//  - Check that the SERVICE_NAME parameter in the connect descriptor of the
//    net service name used specifies a service known by the listener.
//  - If an easy connect naming connect identifier was used, check that
//    the service name specified is a service known by the listener.
//  - Check for an event in the listener.log file.

lsnrctl services

i dodać odpowiedni service do tnsnames.ora

export LD_LIBRARY_PATH=”/usr/lib/oracle/12.1/client64/lib/”

http://www.oracledistilled.com/virtualbox/create-and-mount-virtualbox-shared-folders/

FAST RECOVERY AREA

http://umardba.blogspot.com/2009/12/how-to-change-flash-recovery-area-to.html

sql> select value from v$parameter where name like ‘%db_recovery%;
sql> select value from v$paremeter where name=’control_files’;
sql> show parameter control_files;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+disk1' SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH;

SQL> startup mount

SQL> alter database archivelog;
SQL> alter database open;
sql> alter system switch logfile;
rman> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
RMAN> show all;
RMAN> list backup;
RMAN> list expired backup;
http://gavinsoorma.com/2009/12/11g-release-2-rman-backup-compression/  
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BASIC’;
Unfortunately, unless you have purchased the Advanced Compression Option, your only choice is BASIC.
rman>list backup summary;
rman>DELETE BACKUPSET 145913,145914,145915,145916,145917;
rman>delete obsolete;
rman> crosscheck backup;
rman> crosscheck copy;
rman> list archivelog all;
rman> delete backup;
rman> DELETE NOPROMPT ARCHIVELOG ALL;

rman> delete noprompt archivelog until time 'SYSDATE-10';
SELECT * FROM v$recovery_file_dest; SELECT * FROM v$recovery_area_usage;
RMAN (Oracle docs)
http://docs.oracle.com/cd/E29597_01/backup.1111/e10642/rcmmaint.htm
ORACLE 9i
rman> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/ora_df%t_s%s_s%p';

Retention policy
rman> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

Droping online redo logs, if the redo log is Status is CURRENT or ACTIVE, the on-line redo log cannot be dropped. When trying to drop CURRENT online redo log, Oracle will return “ORA-01623: log 1 is current log for instance testdb (thread 1) – cannot drop” error message. When trying to drop online redo log in ACTIVE status, Oracle will return “ORA-01624: log 1 needed for crash recovery of instance testdb (thread 1)”

SQL> ALTER DATABASE
ADD LOGFILE GROUP 4 (‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04A.LOG’,
‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04B.LOG’)
SIZE 200M;

SQL>alter database add logfile member
‘/u01/oracle/ica/log11.ora’ to group 1;
Wszystkie redologi w grupie mają taką samą zawartość. Oracle zaleca aby w grupie były co najmniej dwa redologi.
W przypadku “cannot allocate new log” – należy dodać nowe grupy logów.

sql> alter database drop logfile group 1;
gdy grupa ma status active należy wykonać:
sql> alter system checkpoint;
sql> alter system switch logfile;

REDO LOG STATUS:

• USED. Indicates either that a log has just been added (never used), or that a RESETLOGS command has been issued.
• CURRENT. Indicates a valid log that is in use.
• ACTIVE. Indicates a valid log file that is not currently in use.
• CLEARING. Indicates a log is being re-created as an empty log due to DBA action.
• CLEARING CURRENT. Means that a current log is being cleared of a closed thread. If a log stays in this status, it could indicate there is some failure in the log switch.
• INACTIVE. Means that the log is no longer needed for instance recovery but may be needed for media recovery.

The v$logfile table has a status indicator that gives these additional codes:

• INVALID. File is inaccessible.
• STALE. File contents are incomplete (such as when an instance is shut down with SHUTDOWN ABORT or due to a system crash).
• DELETED. File is no longer used.

parameter: db_recovery_file_dest
/u01/app/oracle/flash_recovery_area
c:\oracle\app\oracle\flash_recovery_area

parameter: db_recovery_file_dest_size

SQL> alter database open;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2048576000 SCOPE=BOTH;
SQL> shutdown abort
SQL> startup

informacja o oracle service name:
lsnrctl> services

sqlplus sys@”(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=IP)(PORT=NUMER_PORTU))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SID)))” as sysdba

SQLPLUS – komendy: http://docs.oracle.com/cd/B10501_01/server.920/a90842/ch13.htm

define _editor=vim
save nazwa append;

SQL> column colum_name alias alias_name
SQL> column colum_name clear

SQL> column colum_name entmap on
SQL> column colum_name entmap off

SQL> column colum_name fold_after
SQL> column colum_name fold_before
SQL> column colum_name format a25
SQL> column colum_name heading header_text

SQL> column colum_name justify left
SQL> column colum_name justify right
SQL> column colum_name justify center

SQL> column colum_name like expr|alias
SQL> column colum_name newline
SQL> column colum_name new_value variable
SQL> column colum_name print
SQL> column colum_name noprint
SQL> column colum_name old_value 

SQL> column colum_name on
SQL> column colum_name off 

SQL> column colum_name wrapped
SQL> column colum_name word_wrapped
SQL> column colum_name truncated

olumn column_name format a20
column column_name format a50 word_wrapped
column column_name format 999.999  -- Decimal sign
column column_name format 999,999  -- Seperate thousands
column column_name format $999     -- Include leading $ sign
http://www.adp-gmbh.ch/ora/sqlplus/

ORACLE 11g/10g

http://www.oracledistilled.com/oracle-database/backup-and-recovery/enabledisable-archive-log-mode-10g11g/

RMAN

rman> show controlfile autobackup;

rman> configure controlfile autobackup on;

rman> report schema;

rman> list backup;

rman> backup database;

http://setijoagus.wordpress.com/2008/11/07/oracle-11g-starting-the-oracle-enterprise-manager-console/

http://www.oracle-dba-online.com/export_and_import.htm

http://beginner-sql-tutorial.com/oracle-functions.htm

Instalacja Oracle10gXE

http://syntax.home.pl/OracleXEWindows.html

Źródła:

http://ns.prz-rzeszow.pl/~kswider/pbd/Oracle10gXE.pdf

CREATE TABLE HR.JS_USUN_PODWOJNE
(numerid NUMBER);
podwojne.ctl
LOAD DATA INFILE podwojne.csv
APPEND INTO TABLE HR.JS_USUN_PODWOJNE
FIELDS TERMINATED BY ‘,’
(NUMERID)
podwojne.csv
33
4534
35
645
78
2567
153

sqlldr ‘sys/haslo AS SYSDBA’ control=podwojne.ctl log=log2.log

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-load.html

http://www.adp-gmbh.ch/ora/misc/ext_table.html

http://www.dba-oracle.com/art_ext_tabs_spreadsheet.htm

http://www.dba-oracle.com/art_ext_tabs.htm