If the backup is inconsistent, you need to use archived redo logs to make the database consistent.
If the backup is inconsistent, you need to use archived redo logs to make the database consistent.
When you drop a user, all the objects in the user's schema will be dropped permanently without using recycle bin. if you aren’t sure whether you will need a user’s objects later, but you want to deny access, simply leave the user and the user’s schema intact, use the following command:
SQL> REVOKE CREATE SESSION FROM username;
You can restrict user login attempts using profiles.
Profiles are a named set of password and resource limits
To create a new profile first connect as sysdba and create a new profile as folllows.
Create profile password_test
limit
failed_login_attempts 3;
If the user fails to log in within three attempts, the user’s accounts will be locked for a specified period or until the dba manually unlocks them.
Create a new user and assign the profile password_test to new user.
Create user test_user identified by test_user
grant create session to test_user
alter user test_user profile password_test
You can check the status of test_user account using the following statement.
Give wrong password for three times and observe the account status.Code:sql> select username, account_status 2 from dba_users 3 where username = 'test_user'; username account_status ------------------------------ ----------------- ssapllive open
Use the following command to unlock the account.Code:sql> select username, account_status 2 from dba_users 3 where username = 'test_user'; username account_status ------------------------------ ------------------------ test_user locked(timed)
Alter user test_user account unlock
Last edited by krishnaindia2007; 09-01-2008 at 11:46 PM.
How to enable archive log mode?
To enable archive log mode
1. Shut down the database
2. Take full offline backup
3.create pfile from spfile if you are spfile , Using the following command sql>create pfile='c:\pfile.ora' from spfile
4. Then edit pfile or init.ora file and specify the archive log destination
log_archive_start='true'
log_archive_dest='f:\archive'
log_archive_max_processes=4
To specify multiple destinations
log_archive_dest_1=’f:\archive’
log_archive_dest_2=’e:\archive’
5.creat spfile from pfile='c:\pfile.ora'
6.create archive destination folders
7.startup mount
8.alter database archivelog;
9.archive log list;
10.alter database open;
11.sho parameter log;
12. Alter database backup controlfile to trace; - this is for safety purpose
13. Sql>shutdown immediate;
14.sql>startup.
It is recommended that you take a full backup after you brought the database in archive log mode.
Last edited by krishnaindia2007; 09-18-2008 at 10:36 PM.
Creation of linux user and Creation of Oracle directory
1. Connect as root user
For adding user to linux ,assigning dba group to username
2 useradd -d /home/username -m -g dba -s /bin/sh username
3 passwd username
enter password for username
reenter password for username
4 connect as username in linux
5 cd $HOME
For read,write privileges to username
6 chmod 777 username
7 Add env variable to .bash_profile
8 Run .bash_profile
9 check connection of sqlplus
For Creation of oracle directory
1. connect as username
2 mkdir LOGFILES
3 chmod 755 LOGFILES
4 chmod g+w LOGFILES
5 for creating directory
sqlplus > conn system/xxxxxxxx
create or replace directory LOGDIR as '/home/username/LOGFILES'
grant read,write on directory LOGDIR to scott
sqlplus > conn scott/tiger
test the simple script
DECLARE
output_file utl_file.file_type;
begin
output_file := utl_file.fopen ('LOGDIR','x.txt', 'w');
utl_file.put_line (output_file, 'XXXXXXXXXXXX');
utl_file.fclose(output_file);
Exception
when others then null;
end;
6 Test the x.txt file in LOGFILES.
Last edited by susarlasireesha; 10-01-2008 at 02:42 AM.
Sireesha
sequence related queries
last number selected from sequence
next number from sequenceCode:sql> select sequence_name, last_number 2 from user_sequences 3 where sequence_name='smp_vdu_principals_sequence'; sequence_name last_number ------------------------------ ----------- smp_vdu_principals_sequence 21
Code:sql> select sequence_name, (last_number + increment_by) next_value 2 from user_sequences 3 where sequence_name ='smp_vdp_region_id_seq'; sequence_name next_value ------------------------------ ---------- smp_vdp_region_id_seq 22
Dynamic performanc views - I
Dynamic performance views are identified by the prefix v_$.
Public synonyms for these views have the prefix v$.
The v$ views provide a continually updated look at internal statistics
Instance related views
I.v$sga
It contains summary information on system global area.
It is same as show sga in sql *plus with the exeption that it also show the total.Code:sql> select * 2 from v$sga; Name Value -------------------- ---------- fixed size 454584 variable size 218103808 database buffers 25165824 redo buffers 667648
Code:sql> show sga; total system global area 244391864 bytes fixed size 454584 bytes variable size 218103808 bytes database buffers 25165824 bytes redo buffers 667648 bytes
Last edited by krishnaindia2007; 10-03-2008 at 11:48 PM.
Dynamic performanc views - II
II.v$sgastat
It constains detailed information on the system global area or contains information on sga segment sizes
Query to know freespace in sgaCode:sql> select * 2 from v$sgastat; pool name bytes ----------- -------------------------- ---------- fixed_sga 454584 buffer_cache 25165824 log_buffer 656384 shared pool errors 9612 shared pool enqueue 171860 shared pool kgk heap 3756 shared pool kqr m po 304168 shared pool kqr s po 62212 .................... Shared pool message pool freequeue 665792 shared pool ksxr pending messages que 841036 shared pool event statistics per sess 1913520 43 rows selected.
Code:sql> select * from v$sgastat w here name = 'free memory'; pool name bytes ----------- -------------------------- ---------- shared pool free memory 151228400 large pool free memory 8388608 java pool free memory 33554432
Last edited by krishnaindia2007; 10-03-2008 at 11:49 PM.
Dynamic performanc views - III
V$instance :- It displays information of the current instance.
Instance_role can be used to determine if an instance is an active instance (primary instance) or a secondary instance (in a standby environment).Code:SQL> SELECT instance_name, 2 version, 3 status, 4 instance_role 5 FROM v$instance; INSTANCE_NAME VERSION STATUS INSTANCE_ROLE --------------------------------------------------------------- orcl 9.2.0.6.0 OPEN PRIMARY_INSTANCE
Last edited by krishnaindia2007; 10-03-2008 at 11:53 PM.
Dynamic performanc views - IV
V$BGPROCESS :- This process describes the background processes.
V$BH:- This is a parallel server view. This view gives the status and number of pings for every buffer in the SGA.
V$BUFFER_POOL :- This view displays information about all buffer pools available for the instance.
Control File Related Views
V$CONTROL FILE :- This view lists the names of the control files.
V$CONTROLFILE_RECORD_SECTION :- This view displays information about the control file record sections.Code:SQL> SELECT name 2 FROM v$controlfile; NAME ------------------------------------------- E:\ORA9IDB\ORADATA\ORCL\CONTROL01.CTL E:\ORA9IDB\ORADATA\ORCL\CONTROL02.CTL E:\ORA9IDB\ORADATA\ORCL\CONTROL03.CTL
you can start up without needing to enter a user name or password. select run... From the start menu and type 'control userpasswords2', which will open the user accounts application. On the users tab, clear the box for users must enter a user name and password to use this computer, and click on ok. An automatically log on dialog box will appear; enter the user name and password for the account you want to use.
IN XP,,
The Start Menu can be leisurely when it decides to appear, but you can speed things along by changing the registry entry HKEY_CURRENT_USER/Control Panel/Desktop/MenuShowDelay from the default 400 to something a little snappier. Like 0.
You can lock your XP workstation with two clicks of the mouse. Create a new shortcut on your desktop using a right mouse click, and enter 'rundll32.exe user32.dll,LockWorkStation' in the location field. Give the shortcut a name you like. That's it -- just double click on it and your computer will be locked. And if that's not easy enough, Windows key + L will do the same.
IN XP,,,
You can delete files immediately, without having them move to the Recycle Bin first. Go to the Start menu, select Run... and type 'gpedit.msc'; then select User Configuration, Administrative Templates, Windows Components, Windows Explorer and find the Do not move deleted files to the Recycle Bin setting. Set it. Poking around in gpedit will reveal a great many interface and system options, but take care -- some may stop your computer behaving as you wish. (Professional Edition only).
Display legal notice on startup:
Wanna tell your friends about the do's and dont's in your computer when they login in your absence. Well you can do it pretty easily by displaying a legal notice at system start up.
REGEDIT
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\policies\system]
"legalnoticecaption"="enter your notice caption"
"legalnoticetext"="enter your legal notice text"
Turn Off System Recovery
Right click on My Computer and choose Properties. Click on the System Restore tab and check the box Turn off System Restore. (This will increase Windows performance & save disk space)
Win XP Won’t Completely Shutdown
• Goto Control Panel, then goto Power Options.
• Click on the APM Tab, then check the "Enable Advanced Power Management support."
• Shut down your PC. It should now successfully complete the Shut Down process.
Disable error reporting
• Open Control Panel
• Click on Performance and Maintenance.
• Click on System.
• Then click on the Advanced tab
• Click on the error-reporting button on the bottom of the windows.
• Select Disable error reporting.
• Click OK
• Click OK
Remove shortcut arrow from desktop icons
Here's how you can remove those shortcut arrows from your desktop icons in Windows XP.
1. Start regedit.
2. Navigate to HKEY_CLASSES_ROOTlnkfile
3. Delete the IsShortcut registry value.
You may need to restart Windows XP.